5. 想在代码中验证sql的正确性？
1. 简介我们在平时的开发中可能会遇到需要验证一下sql是否正确，也就是需要check一下sql。
判断sql是否正确一般包含一下几点：
1. sql中使用的列是否存在2. sql语法是否正确3. sql中使用到的操作符/函数是否存在，有没有正确的使用我们可以用以下的sql示例来探究一下使用calcite如何校验sql
select u.sex, max(u.age)from user u inner join role r on u.role_id = r.idwhere r.id = 1group by u.sex2. Maven
<dependency> <groupId>org.apache.calcite</groupId> <artifactId>calcite-core</artifactId> <version>1.37.0</version></dependency><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.33</version></dependency>3. 验证
首先 在calcite中验证sql的正确性是通过使用calcite中的SqlValidator类进行校验的，但SqlValidator是一个接口， 通常是通过SqlValidatorUtil.newValidator(...)方法进行实例化的, 如下：
public static SqlValidatorWithHints newValidator(SqlOperatorTable opTab, SqlValidatorCatalogReader catalogReader, RelDataTypeFactory typeFactory, SqlValidator.Config config) { return new SqlValidatorImpl(opTab, catalogReader, typeFactory, config); }
[*]SqlOperatorTable：用来提供sql验证所需的操作符（SqlOperator）和函数（SqlFunction）例如：>, <, = 或max(),in()
[*]SqlValidatorCatalogReader：用来提供验证所需的元数据信息 例如： schema, table, column
[*]RelDataTypeFactory：处理数据类型的工厂类，用来提供类型、java类型和集合类型的创建和转化。针对不同的接口形式，calcite支持sql和java两种实现（SqlTypeFactoryImpl和JavaTypeFactoryImpl），当然这里用户可以针对不同的情况自行扩展
[*]SqlValidator.Config：可以自定义一些配置，例如是否开启类型隐式转换、是否开启 SQL 重写等等
3.1 创建SqlValidator
创建SqlValidator之前需要先实例化上述的四个入参对象，好在calcite提供了对应属性的默认实现，使得我们能很方便的创建SqlValidator对象
SqlValidator validator = SqlValidatorUtil.newValidator( SqlStdOperatorTable.instance(), catalogReader, // catalog信息需要自己手动创建 new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT), SqlValidator.Config.DEFAULT);这里除了SqlValidatorCatalogReader对象需要额外的自己创建，如果没有特殊的需求，我们都可以使用calcite提供的默认实现。
我们这里着重讲一下SqlValidatorCatalogReader对象如何创建
首先SqlValidatorCatalogReader使用来提供验证所需的catalog信息的，那我们就需要提供一下catalog信息（因为calcite需要做元数据的验证，比如表，字段是否存在，不提供元数据calcite谈何验证）
创建SqlValidatorCatalogReader有两种方式：
[*]通过数据源的方式，也就是我们知道执行sql的server信息，把连接信息给calcite，让calcite自己去获取元信息并进行验证，也就是这个时候需要去连接db才能进行验证
@SneakyThrowsprivate static CalciteCatalogReader createCatalogReaderWithDataSource() { Connection connection = DriverManager.getConnection("jdbc:calcite:"); CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class); SchemaPlus rootSchema = calciteConnection.getRootSchema(); DataSource dataSource = JdbcSchema.dataSource( "jdbc:mysql://localhost:3306/test", "com.mysql.cj.jdbc.Driver", "root", "123456" ); JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, "my_mysql", dataSource, null, null); rootSchema.add("my_mysql", jdbcSchema); calciteConnection.setSchema("my_mysql"); CalciteServerStatement statement = connection.createStatement().unwrap(CalciteServerStatement.class); CalcitePrepare.Context prepareContext = statement.createPrepareContext(); SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT); return new CalciteCatalogReader( prepareContext.getRootSchema(), prepareContext.getDefaultSchemaPath(), factory, calciteConnection.config());}
[*]手动添加catalog信息，不需要连库就能验证
private static CalciteCatalogReader createCatalogReaderWithMeta() { SchemaPlus rootSchema = Frameworks.createRootSchema(true); RelDataTypeSystem relDataTypeSystem = RelDataTypeSystem.DEFAULT; RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl(relDataTypeSystem); rootSchema.add("user", new AbstractTable() { @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { RelDataTypeFactory.Builder builder = typeFactory.builder(); builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); builder.add("age", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("sex", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); builder.add("role_id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); return builder.build(); } }); rootSchema.add("role", new AbstractTable() { @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { RelDataTypeFactory.Builder builder = typeFactory.builder(); builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); return builder.build(); } }); CalciteConnectionConfig connectionConfig = CalciteConnectionConfig.DEFAULT; return new CalciteCatalogReader( CalciteSchema.from(rootSchema), CalciteSchema.from(rootSchema).path(null), typeFactory, connectionConfig);}ok，至此创建SqlValidator所需的参数都已备齐，但是当执行验证方法的时候所需的参数并不是sql字符串而是SqlValidator.validate(SqlNode topNode), 那么SqlNode又要怎么创建 ?
3.2 解析Sql
SqlNode 顾名思义就是sql节点对象，直接通过SqlParser对象创建，如下
SqlParser.Config config = SqlParser.config() // 解析工厂 .withParserFactory(SqlParserImpl.FACTORY) // 也可以直接设置为对应数据库的词法分析器 // .withLex(Lex.MYSQL) // 不区分大小写 .withCaseSensitive(false) // 引用符号为反引号 .withQuoting(Quoting.BACK_TICK) // 未加引号的标识符在解析时不做处理 .withUnquotedCasing(Casing.UNCHANGED) // 加引号的标识符在解析时不做处理 .withQuotedCasing(Casing.UNCHANGED) // 使用默认的语法规则 .withConformance(SqlConformanceEnum.DEFAULT);// sql解析器final SqlParser parser = SqlParser.create(SQL, config);// 将sql转换为calcite的SqlNodeSqlNode sqlNode = parser.parseQuery();3.3 执行验证
通过上述的步骤 我们已经能创建SqlValidator对象并且能创建其验证时需要的SqlNode对象，其实很简单， 只要验证时不报错，即sql是正确的
try{ // 校验 sql validator.validate(sqlNode); log.info("sql is valid");}catch (Exception e) { log.error("sql is invalid", e);}4. 完整验证代码
4.1 通过SqlValidator进行验证
package com.ldx.calcite;import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import org.apache.calcite.adapter.jdbc.JdbcSchema;import org.apache.calcite.avatica.util.Casing;import org.apache.calcite.avatica.util.Quoting;import org.apache.calcite.config.CalciteConnectionConfig;import org.apache.calcite.jdbc.CalciteConnection;import org.apache.calcite.jdbc.CalcitePrepare;import org.apache.calcite.jdbc.CalciteSchema;import org.apache.calcite.prepare.CalciteCatalogReader;import org.apache.calcite.rel.type.RelDataType;import org.apache.calcite.rel.type.RelDataTypeFactory;import org.apache.calcite.rel.type.RelDataTypeSystem;import org.apache.calcite.schema.SchemaPlus;import org.apache.calcite.schema.impl.AbstractTable;import org.apache.calcite.server.CalciteServerStatement;import org.apache.calcite.sql.SqlNode;import org.apache.calcite.sql.fun.SqlStdOperatorTable;import org.apache.calcite.sql.parser.SqlParser;import org.apache.calcite.sql.parser.impl.SqlParserImpl;import org.apache.calcite.sql.type.BasicSqlType;import org.apache.calcite.sql.type.SqlTypeFactoryImpl;import org.apache.calcite.sql.type.SqlTypeName;import org.apache.calcite.sql.validate.SqlConformanceEnum;import org.apache.calcite.sql.validate.SqlValidator;import org.apache.calcite.sql.validate.SqlValidatorUtil;import org.apache.calcite.tools.Frameworks;import org.junit.jupiter.api.Test;import javax.sql.DataSource;import java.sql.Connection;import java.sql.DriverManager;@Slf4jpublic class SqlValidatorTest { private static final String SQL = "select u.sex, max(u.age) from `user` u inner join role r on u.role_id = r.id where r.id = 1 group by u.sex"; @Test @SneakyThrows public void given_sql_and_meta_then_validate_sql() { SqlParser.Config config = SqlParser.config() // 解析工厂 .withParserFactory(SqlParserImpl.FACTORY) // 也可以直接设置为对应数据库的词法分析器 // .withLex(Lex.MYSQL) // 不区分大小写 .withCaseSensitive(false) // 引用符号为反引号 .withQuoting(Quoting.BACK_TICK) // 未加引号的标识符在解析时不做处理 .withUnquotedCasing(Casing.UNCHANGED) // 加引号的标识符在解析时不做处理 .withQuotedCasing(Casing.UNCHANGED) // 使用默认的语法规则 .withConformance(SqlConformanceEnum.DEFAULT); // sql解析器 final SqlParser parser = SqlParser.create(SQL, config); // 将SQL转换为Calcite的SqlNode SqlNode sqlNode = parser.parseQuery(); // 创建 SqlValidator 来进行校验 SqlValidator validator = SqlValidatorUtil.newValidator( SqlStdOperatorTable.instance(), // 使用直接提供元信息的方式 createCatalogReaderWithMeta(), // 使用提供数据源的方式 //createCatalogReaderWithDataSource(), new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT), SqlValidator.Config.DEFAULT); try{ // 校验 sql validator.validate(sqlNode); log.info("sql is valid"); } catch (Exception e) { log.error("sql is invalid", e); } } private static CalciteCatalogReader createCatalogReaderWithMeta() { SchemaPlus rootSchema = Frameworks.createRootSchema(true); RelDataTypeSystem relDataTypeSystem = RelDataTypeSystem.DEFAULT; RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl(relDataTypeSystem); rootSchema.add("user", new AbstractTable() { @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { RelDataTypeFactory.Builder builder = typeFactory.builder(); builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); builder.add("age", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("sex", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); builder.add("role_id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); return builder.build(); } }); rootSchema.add("role", new AbstractTable() { @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { RelDataTypeFactory.Builder builder = typeFactory.builder(); builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); return builder.build(); } }); CalciteConnectionConfig connectionConfig = CalciteConnectionConfig.DEFAULT; return new CalciteCatalogReader( CalciteSchema.from(rootSchema), CalciteSchema.from(rootSchema).path(null), typeFactory, connectionConfig); } @SneakyThrows private static CalciteCatalogReader createCatalogReaderWithDataSource() { Connection connection = DriverManager.getConnection("jdbc:calcite:"); CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class); SchemaPlus rootSchema = calciteConnection.getRootSchema(); DataSource dataSource = JdbcSchema.dataSource( "jdbc:mysql://localhost:3306/test", "com.mysql.cj.jdbc.Driver", "root", "123456" ); JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, "my_mysql", dataSource, null, null); rootSchema.add("my_mysql", jdbcSchema); calciteConnection.setSchema("my_mysql"); CalciteServerStatement statement = connection.createStatement().unwrap(CalciteServerStatement.class); CalcitePrepare.Context prepareContext = statement.createPrepareContext(); SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT); return new CalciteCatalogReader( prepareContext.getRootSchema(), prepareContext.getDefaultSchemaPath(), factory, calciteConnection.config()); }}4.2 使用Planner对象进行验证
其实Planner.validate方法其底层使用的还是SqlValidator对象进行验证
package com.ldx.calcite;import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import org.apache.calcite.avatica.util.Casing;import org.apache.calcite.avatica.util.Quoting;import org.apache.calcite.rel.type.RelDataType;import org.apache.calcite.rel.type.RelDataTypeFactory;import org.apache.calcite.rel.type.RelDataTypeSystem;import org.apache.calcite.schema.SchemaPlus;import org.apache.calcite.schema.impl.AbstractTable;import org.apache.calcite.sql.SqlNode;import org.apache.calcite.sql.parser.SqlParser;import org.apache.calcite.sql.parser.impl.SqlParserImpl;import org.apache.calcite.sql.type.BasicSqlType;import org.apache.calcite.sql.type.SqlTypeName;import org.apache.calcite.sql.validate.SqlConformanceEnum;import org.apache.calcite.tools.FrameworkConfig;import org.apache.calcite.tools.Frameworks;import org.apache.calcite.tools.Planner;import org.apache.calcite.tools.ValidationException;import org.junit.jupiter.api.Test;@Slf4jpublic class SqlValidatorWithPlannerTest { private static final String SQL = "select u.sex, max(u.age) from `user` u inner join role r on u.role_id = r.id where r.id = 1 group by u.sex"; @Test @SneakyThrows public void given_sql_and_meta_then_validate_sql() { // 创建Calcite配置 FrameworkConfig config = createFrameworkConfig(); // 创建Planner Planner planner = Frameworks.getPlanner(config); // 解析SQL final SqlNode parse = planner.parse(SQL); try { // 获取SqlValidator进行校验 planner.validate(parse); log.info("sql is valid"); } catch (ValidationException e) { log.error("sql is invalid", e); } } private static FrameworkConfig createFrameworkConfig() { SchemaPlus rootSchema = Frameworks.createRootSchema(true); RelDataTypeSystem relDataTypeSystem = RelDataTypeSystem.DEFAULT; rootSchema.add("user", new AbstractTable() { @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { RelDataTypeFactory.Builder builder = typeFactory.builder(); builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); builder.add("age", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("sex", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); builder.add("role_id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); return builder.build(); } }); rootSchema.add("role", new AbstractTable() { @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) { RelDataTypeFactory.Builder builder = typeFactory.builder(); builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER)); builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR)); return builder.build(); } }); SqlParser.Config config = SqlParser.config() .withParserFactory(SqlParserImpl.FACTORY) .withQuoting(Quoting.BACK_TICK) .withCaseSensitive(false) .withUnquotedCasing(Casing.UNCHANGED) .withQuotedCasing(Casing.UNCHANGED) .withConformance(SqlConformanceEnum.DEFAULT); return Frameworks .newConfigBuilder() .defaultSchema(rootSchema) .parserConfig(config) .build(); }}
页:
[1]