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.id
where r.id = 1
group 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才能进行验证

    @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());
}

  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的SqlNode
SqlNode 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;

@Slf4j
public 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;

@Slf4j
public 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();
    }
}

__EOF__

本文作者：张铁牛
本文链接：https://www.cnblogs.com/ludangxin/p/18730466.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   张铁牛  阅读(273)  评论(0)  编辑  收藏  举报
相关博文：
· 6. Calcite添加自定义函数
· 1. Calcite元数据创建
· 10分钟教你写一个数据库
· 如何用java校验SQL语句的合法性？（提供五种解决方案）
· SqlParser
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示