API网关之数据脚本开发

dub-1314

已于 2022-07-16 12:36:07 修改

阅读量586

点赞数 2

文章标签：数据库服务器 sql

于 2022-07-16 11:56:53 首次发布

本文链接：https://blog.csdn.net/qq_33594592/article/details/125458926

版权

文章目录

前言
一、数据API介绍
二、原理及实现

前言

本文介绍了一种数据脚本API的实现方式，支持多种数据源，数据源可以是关系数据库，也可以是NoSql数据库，甚至是csv、ES、xml、json、内存表等任何一种数据源。

一、数据API介绍

和函数API一样，数据API也是将一个数据库脚本包装成一个接口发布到网关上，这样南向、北向系统就可以通过网关去调用。
数据API的本质是一个数据库脚本，脚本有结构化SQL和存储过程两种，这里介绍的结构化SQL，功能强大，屏蔽了多种数据源语法、数据类型差异，用户使用只需编写标准结构化SQL即可，还自动支持SQL解析、校验、执行计划优化等功能，通过定义schema可以支持数据源的无限扩展，可以满足几乎所有业务场景。
存储过程兼容大多数关系数据库语法，能够根据数据源类型自动解析存储过程脚本，支持多种传参方式（IN、INOUT），以及INOUT、OUT、返回集合等多种返回值，作为结构化SQL的补充，可以满足一些复杂的业务场景。
数据脚本支持预编译防止SQL注入，考虑到作为网关的功能以及使用场景，不支持DDL语句，支持部分DML语句。

二、原理及实现

在这里插入图片描述

1.结构化SQL原理及实现

1）实现方案
为了降低用户使用难度，屏蔽各种数据源在语法及使用上的差异性，这里选用Appache的Calcite作为脚本解析及执行工具。
Calcite是一个非常强大的框架，非常适合我们的需求，它可以实现SQL的解析、校验、分析以及执行计划的优化，用户无需关注数据库语法，只需编写标准结构化SQL，而且用户编写SQL的水平要求也不高，复杂的SQL语句Calcite会帮助你优化执行计划，包括RBO和CBO都支持，只需要配置一些优化策略即可，优化后的SQL由Calcite去执行，执行时Calcite会根据不同的Dialect转换成对应的SQL，这些用户是完全无感知的。
此外因为是支持多数据源的，所以还需要一个动态数据源，去支持接口调用时数据源上下文的切换，这里选用的是spring boot的数据源连接池Hikari去管理数据库连接，还需要定义一个动态数据源DynamicDataSource，这个数据源实现了DataSoure接口，可以从数据库读取数据源连接信息，去动态的创建数据库连接。
2）实现过程

编写结构化SQL脚本

select t1.a,t1.b,t2.c from db.test1 t1,db.test2 t2 where t1.id = t2.id and  name = #{name};

封装成API
发布API到网关
在API网关做API发布，并且授权给应用，这样接口就可以使用了
调用API
使用Calcite解析、校验、分析、执行脚本

Class.forName("org.apache.calcite.jdbc.Driver");
Properties info = new Properties();
info.put("lex", Lex.MYSQL.name());
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();
String dataSourceName = scriptEntity.getDataSource();
// 获取数据源
DataSource ds = dataSourceUtil.getDataSource(designCfg.getFBlongAppId(), dataSourceName, null);
DynamicDataSource dataSource = ScriptManager.getDataSource(ds);
Schema schema = JdbcSchema.create(rootSchema, "db", dataSource, null, null);
rootSchema.add("db", schema);

创建schema

/**
     * @param rootSchema: calcite根schema
     * @param ds:         数据源对象
     * @param sql:        优化后sql
     * @throws
     * @author dubin
     * @description sql优化器，包含以下功能：
     * 1、sql解析-MYSQL解析器；
     * 2、sql语法词法验证-表名、字段名、函数名、数据类型的检查；
     * 3、sql语义解析-将 SqlNode 转换为 RelNode
     * 4、sql优化器-HepPlanner 和 VolcanoPlanner优化器，分别对应基于代价CBO模式，和基于规则RBO模式
     * 5、优化后sql生成
     * @Date 2022/5/22 18:27
     * @return: java.lang.String
     */
    private String getOtimizeSql(SchemaPlus rootSchema, DataSource ds, String sql) throws SqlParseException {
        // sql解析
        SqlParser parser = SqlParser.create(sql, SqlParser.Config.DEFAULT.withLex(Lex.MYSQL));
        SqlNode sqlNode = parser.parseStmt();
        // sql语法校验
        SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
        CalciteCatalogReader calciteCatalogReader = new CalciteCatalogReader(
                CalciteSchema.from(rootSchema), CalciteSchema.from(rootSchema).path(null), factory,
                new CalciteConnectionConfigImpl(new Properties()));
        SqlValidator validator = SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(), calciteCatalogReader,
                factory, SqlValidator.Config.DEFAULT);
        SqlNode validateSqlNode = validator.validate(sqlNode);
        // sql语义分析
        final RexBuilder rexBuilder = new RexBuilder(factory);
        HepProgramBuilder builder = new HepProgramBuilder();
        RelOptPlanner planner = new HepPlanner(builder.build());
        final RelOptCluster cluster = RelOptCluster.create(planner, rexBuilder);
        final SqlToRelConverter.Config config = SqlToRelConverter.config();
        final SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(
                new RelOptTable.ViewExpander() {
                    @Override
                    public RelRoot expandView(RelDataType rowType, String queryString, List<String> schemaPath,
                                              @Nullable List<String> viewPath) {
                        return null;
                    }
                }, validator, calciteCatalogReader, cluster, StandardConvertletTable.INSTANCE, config);
        RelRoot root = sqlToRelConverter.convertQuery(validateSqlNode, false, true);
        root = root.withRel(sqlToRelConverter.flattenTypes(root.rel, true));
        final RelBuilder relBuilder = config.getRelBuilderFactory().create(cluster, null);
        root = root.withRel(RelDecorrelator.decorrelateQuery(root.rel, relBuilder));
        RelNode relNode = root.rel;
        // sql优化
        builder.addRuleInstance(PruneEmptyRules.PROJECT_INSTANCE); // 优化器规则，后期需要根据实际场景选择合适的优化规则
        HepPlanner hepPlanner = new HepPlanner(builder.build()); // 基于规则RBO
        hepPlanner.setRoot(relNode);
        relNode = hepPlanner.findBestExp();
            /*VolcanoPlanner planner = (VolcanoPlanner) relNode.getCluster().getPlanner(); // 基于代价CBO
            //获取期望的RelTraiset，这里是将Convention.None替换成EnumerableConvention
            RelTraitSet desired = relNode.getTraitSet().replace(EnumerableConvention.INSTANCE).simplify();
            //设置根节点，会从根节点开始迭代将所有子节点也注册到planner中
            planner.setRoot(planner.changeTraits(relNode, desired));
            RelNode result = planner.chooseDelegate().findBestExp();*/
        // 转换为需要的数据库类型的sql
        RelToSqlConverter relToSqlConverter = new RelToSqlConverter(getSqlDialect(ds.getSourceType()));
        SqlImplementor.Result visit = relToSqlConverter.visitRoot(relNode);
        SqlNode sqlNode1 = visit.asStatement();
        String newSql = sqlNode1.toSqlString(getSqlDialect(ds.getSourceType())).getSql();
        return newSql;
    }

sql解析、校验、分析及优化

// sql解析、校验及优化
String newSql = getOtimizeSql(rootSchema, ds, replaceResult.getNewContent());
// sql脚本预编译
PreparedStatement preparedStatement = calciteConnection.prepareStatement(newSql);
ResultSet resultSet = excuteSql(scriptEntity, resultStr, replaceResult, preparedStatement);

sql执行及返回结果

2.存储过程原理及实现

1）实现方案
存储过程的实现，需要定义一个存储过程解析器，用来解析语句分析出过程的参数和返回值，做一些基本的语法校验，这里是利用正则匹配定义了一个解析器，支持多种关系数据库语法，存储过程的驱动有两种方案，第一种使用Cacite就可以，使用方式和JDBC类似，有兴趣的读者可以深入研究其用法，这里介绍的第二种方案是我们更为熟悉的JDBC，相信很多java程序员都使用其API写过存储过程，这里就不做赘述。
此外因为是支持多数据源的，所以同样还需要一个动态数据源，和上述结构化SQL雷同。
2）实现过程

编写存储过程脚本，因为支持多数据源，以mysql为例

CREATE PROCEDURE procedure_test2(in dict_group varchar(50), in f_tenant_id int, out test varchar(50))
BEGIN
select f_dict_value into test 
from t_integration_apiis_api_dict_cfg 
where f_dict_group = dict_group and f_tenant_id = f_tenant_id;

select f_dict_name,f_dict_value,f_dict_group,f_dict_desc 
from t_integration_apiis_api_dict_cfg 
where f_dict_group = dict_group and f_tenant_id = f_tenant_id;
END;

封装API
发布接口到API网关
在API网关做API发布，并且授权给应用，这样接口就可以使用了
调用API
存储过程解析及预编译

PlaceholderHelper.Procedure procedure =
PlaceholderHelper.analyzeProcedure(scriptEntity.getScript(), sourceType);
// 过程预编译,格式：{call Get_Order_By_Uuid(?,?,?,?)}
CallableStatement callableStatement = conn.prepareCall(procedure.getPropareSql());

存储过程执行

/**
    * @param procedure:         存储过程定义
    * @param callableStatement: 执行器
    * @throws
    * @author dubin
    * @description 执行存储过程：1、带结果集返回的存储过程；2、带输入输出参数的存储过程
    * @Date 2022/5/27 17:56
    * @return: com.alibaba.fastjson.JSONObject
    */
   private JSONObject excuteProcedure(PlaceholderHelper.Procedure procedure, CallableStatement callableStatement)
           throws SQLException {
       JSONObject result = new JSONObject();
       boolean hasResultSet = callableStatement.execute();
       // 返回结果集合的存储过程返回值封装
       if (hasResultSet) {
           ResultSet rs = callableStatement.getResultSet();
           List<Object> list = new ArrayList<>();
           while (rs.next()) {
               int columnCount = rs.getMetaData().getColumnCount();
               JSONObject row = new JSONObject();
               for (int i = 1; i <= columnCount; i++) {
                   Object obj = rs.getObject(i);
                   row.put(rs.getMetaData().getColumnName(i), obj);
               }
               list.add(row);
           }
           result.put("returnSet", list);
       }
       // out/inout类型的存储过程返回值封装
       JSONObject out = new JSONObject();
       List<PlaceholderHelper.Procedure.Param> outList =
               procedure.getParams().stream().filter(p -> StringUtils.equals(p.getInOut(), "out") ||
                       StringUtils.equals(p.getInOut(), "inout")).collect(Collectors.toList());
       outList.stream().forEach(o -> {
           try {
               Object obj = callableStatement.getObject(o.getIndex());
               out.put(o.getParamName(), obj);
           } catch (SQLException e) {
               e.printStackTrace();
               throw new BusinessException(ReturnCodeEnum.DATA_FAILED.getCode(),
                       MessageUtils.getMessage("message.manage.api.develop.script.sql.param.Illegal"));
           }
       });
       result.put("outSet", out);
       return result;
   }

3、动态数据源支持

因为不管是结构化SQL，还是存储过程，都是支持多数据源的，所以还需要一个动态数据源，去支持接口调用时数据源上下文的切换，这里选用的是spring boot的数据源连接池Hikari去管理数据库连接，还需要定义一个动态数据源DynamicDataSource，这个数据源实现了DataSoure接口，可以从数据库读取数据源连接信息，去动态的创建数据库连接。

/**
* @author dubin
* @version 1.0.0
* @ClassName DataSourceUtil.java
* @Description 数据组件数据源管理
* @createTime 2022年05月12日 14:07:00
*/
@Component
@Slf4j
public class DataSourceUtil {

   @Value("${datasource.data.jdbcUrl}")
   private String jdbcUrl;

   @Value("${datasource.data.driverClass}")
   private String driverClass;

   @Value("${datasource.data.userName}")
   private String userName;

   @Value("${datasource.data.password}")
   private String password;

   @Resource(name = "dynamicDataSource")
   private DynamicDataSource dataSource;

   private static DynamicDataSource dynamicDataSource;

   @Autowired
   private DataSourceRefresher dataSourceRefresher;

   @PostConstruct
   private void init() {
       dynamicDataSource = dataSource;
   }

   public DataSource getDataSource(Long appId, String dataSourceName, Integer sourceType) {
       DynamicDataSource dataSource = null;
       try {
           dataSource = customDataSource();
       } catch (SQLException | ClassNotFoundException e) {
           throw new BusinessException(ReturnCodeEnum.DATA_BAD.getCode(),
                   MessageUtils.getMessage("message.manage.api.develop.script.datasource.error", e.getMessage()));
       }
       NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
       String sql = "select f_source_name sourceName, f_host_ip hostIp, f_port_no portNo, f_user_name userName," +
               " f_password password, f_db_name dbName, f_source_type sourceType, f_belong_app_id belongAppId,  " +
               "f_pwd_key pwdKey ,f_address address " +
               "from t_integration_data_source_info " +
               "where f_belong_app_id = :appId and f_source_name = :dataSourceName ";
       MapSqlParameterSource param = new MapSqlParameterSource();
       param.addValue("appId", appId);
       param.addValue("dataSourceName", dataSourceName);
       if (Objects.nonNull(sourceType)) {
           sql = sql + "and f_source_type = :sourceType";
           param.addValue("sourceType", sourceType);
       }
       Object obj = null;
       try {
           obj = jdbcTemplate.queryForObject(sql, param, new BeanPropertyRowMapper<>(DataSource.class));
           Assert.isTrue(Objects.nonNull(obj),
                   MessageUtils.getMessage("message.manage.api.develop.script.datasource.notExist", dataSourceName));
       } catch (DataAccessException e) {
           e.printStackTrace();
           throw new BusinessException(ReturnCodeEnum.DATA_FAILED.getCode(), e.getMessage());
       }
       return (DataSource) obj;
   }

   public DynamicDataSource customDataSource() throws SQLException, ClassNotFoundException {
       Class.forName(driverClass);
       DataSourceProperties properties = new DataSourceProperties();
       properties.setUrl(jdbcUrl);
       properties.setDriverClassName(driverClass);
       properties.setUsername(userName);
       properties.setPassword(password);
       HikariDataSource hikariDataSource =
               properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
       dataSourceRefresher.refreshDataSource(hikariDataSource);
       return dynamicDataSource;
   }

}

/**
* @author dubin
* @version 1.0.0
* @ClassName DataSourceRefresher.java
* @Description TODO
* @createTime 2022年05月21日 16:40:00
*/
@Slf4j
@Component
public class DataSourceRefresher {
   private static final int MAX_RETRY_TIMES = 10;

   @Autowired
   private DynamicDataSource dynamicDataSource;

   private ScheduledExecutorService scheduledExecutorService = Executors.newSingleThreadScheduledExecutor();

   public synchronized void refreshDataSource(javax.sql.DataSource newDataSource) {
       try {
           log.info("refresh data source....");
           javax.sql.DataSource oldDataSource = dynamicDataSource.getAndSetDataSource(newDataSource);
           shutdownDataSourceAsync(oldDataSource);
       } catch (Throwable ex) {
           log.error("refresh data source error", ex);
       }
   }

   public void shutdownDataSourceAsync(javax.sql.DataSource dataSource) {
       scheduledExecutorService.execute(() -> doShutdownDataSource(dataSource));
   }

   private void doShutdownDataSource(javax.sql.DataSource dataSource) {
       if (dataSource instanceof HikariDataSource) {
           int retryTimes = 0;
           HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
           HikariPoolMXBean poolBean = hikariDataSource.getHikariPoolMXBean();
           while (poolBean.getActiveConnections() > 0 && retryTimes <= MAX_RETRY_TIMES) {
               try {
                   poolBean.softEvictConnections();
                   sleep1Second();
               } catch (Exception e) {
                   log.warn("doShutdownDataSource error ", e);
               } finally {
                   retryTimes++;
               }
           }
           hikariDataSource.close();
           log.info("shutdown data source success");
       }

   }

   private void sleep1Second() {
       try {
           TimeUnit.SECONDS.sleep(1);
       } catch (InterruptedException e) {
           e.printStackTrace();
       }
   }
}

/**
* @author dubin
* @version 1.0.0
* @ClassName DynamicDataSource.java
* @Description 动态数据源
* @createTime 2022年05月21日 11:57:00
*/
public class DynamicDataSource implements DataSource {

   private final AtomicReference<DataSource> dataSourceReference;

   public DynamicDataSource(DataSource dataSource) {
       this.dataSourceReference = new AtomicReference<>(dataSource);
   }

   DataSource getDataSource() {
       return dataSourceReference.get();
   }

   public DataSource getAndSetDataSource(DataSource newDataSource) {
       return dataSourceReference.getAndSet(newDataSource);
   }

   @Override
   public Connection getConnection() throws SQLException {
       return getDataSource().getConnection();
   }

   @Override
   public Connection getConnection(String username, String password) throws SQLException {
       return getDataSource().getConnection(username, password);
   }

   @Override
   public <T> T unwrap(Class<T> iface) throws SQLException {
       return getDataSource().unwrap(iface);
   }

   @Override
   public boolean isWrapperFor(Class<?> iface) throws SQLException {
       return getDataSource().isWrapperFor(iface);
   }

   @Override
   public PrintWriter getLogWriter() throws SQLException {
       return getDataSource().getLogWriter();
   }

   @Override
   public void setLogWriter(PrintWriter out) throws SQLException {
       getDataSource().setLogWriter(out);
   }

   @Override
   public void setLoginTimeout(int seconds) throws SQLException {
       getDataSource().setLoginTimeout(seconds);
   }

   @Override
   public int getLoginTimeout() throws SQLException {
       return getDataSource().getLoginTimeout();
   }

   @Override
   public Logger getParentLogger() throws SQLFeatureNotSupportedException {
       return getDataSource().getParentLogger();
   }
}