想在代码中验证 sql 的正确性？
- 2025-08-01 福建
本文字数：9203 字
阅读完需：约 30 分钟
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.sex复制代码
2. 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有两种方式：
1、通过数据源的方式，也就是我们知道执行 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());}复制代码
2、手动添加 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", typeFactory.createSqlType(SqlTypeName.INTEGER)); 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", typeFactory.createSqlType(SqlTypeName.INTEGER)); 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(); }}复制代码
文章转载自：张铁牛
划线
评论
复制
发布于: 刚刚阅读数: 2
还未添加个人签名 2025-04-01 加入
还未添加个人简介
评论