Shading - jdbc 源码分析(五) - sql 改写

上一篇文章我们分析了sharding-jdbc 的路由（路由），今天我们分析下sql改写。

闲聊:翻了Sharding-Sphere 的文档，也对SQL【解析、路由、改写、执行、归并】引擎做了介绍，大家有兴趣可以看看。

主要类：

• SQLRewriteEngine：SQL重写引擎，rewrite()方法进行SQL改写；generateSQL()方法生成SQL语句

执行逻辑：

我们继续以下面的SQL为例：

SELECT o.order_id FROM order o WHERE o.order_id = 4

• 下面的方法就是重写的逻辑（也包含路由,路由的逻辑上篇文章已经分析）

public SQLRouteResult route(final String logicSQL, final List<Object> parameters, final SQLStatement sqlStatement) {
        final Context context = MetricsContext.start("Route SQL");
        SQLRouteResult result = new SQLRouteResult(sqlStatement);
        if (sqlStatement instanceof InsertStatement && null != ((InsertStatement) sqlStatement).getGeneratedKey()) {
            processGeneratedKey(parameters, (InsertStatement) sqlStatement, result);
        }
        //路由
        RoutingResult routingResult = route(parameters, sqlStatement);
        //重写
        SQLRewriteEngine rewriteEngine = new SQLRewriteEngine(shardingRule, logicSQL, sqlStatement);
        //判断是否是单表路由
        boolean isSingleRouting = routingResult.isSingleRouting();
        //处理limit
        if (null != sqlStatement.getLimit()) {
            sqlStatement.getLimit().processParameters(parameters, !isSingleRouting);
        }
        //构建 SQLBuilder
        SQLBuilder sqlBuilder = rewriteEngine.rewrite(!isSingleRouting);
        if (routingResult instanceof CartesianRoutingResult) {
            for (CartesianDataSource cartesianDataSource : ((CartesianRoutingResult) routingResult).getRoutingDataSources()) {
                for (CartesianTableReference cartesianTableReference : cartesianDataSource.getRoutingTableReferences()) {
                    result.getExecutionUnits().add(new SQLExecutionUnit(cartesianDataSource.getDataSource(), rewriteEngine.generateSQL(cartesianTableReference, sqlBuilder)));
                }
            }
        } else {
            //创建sql最小执行单元
            for (TableUnit each : routingResult.getTableUnits().getTableUnits()) {
                result.getExecutionUnits().add(new SQLExecutionUnit(each.getDataSourceName(), rewriteEngine.generateSQL(each, sqlBuilder)));
            }
        }
        MetricsContext.stop(context);
        logSQLRouteResult(result, parameters);
        return result;
    }
复制代码

1. 获取RoutingResult路由结果： 这是routingResult结果

2. 构造SQLRewriteEngine

public SQLRewriteEngine(final ShardingRule shardingRule, final String originalSQL, final SQLStatement sqlStatement) {
        this.shardingRule = shardingRule;
        this.originalSQL = originalSQL;
        sqlTokens.addAll(sqlStatement.getSqlTokens());
        tableNames = sqlStatement.getTables().getTableNames();
        limit = sqlStatement.getLimit();
    }
复制代码

3. rewrite()构造SQLBuilder：

处理逻辑：把SQL分为2部分：需要替换的（例如：表名） 和不需要替换的；需要替换的表名替换成真实的表名(利用解析好的TableToken)，分别add 到SQLBuilder的segments集合中。这样生成真实SQL直接foreach segments就可以了

/**
     * SQL改写.
     *
     * @param isRewriteLimit 是否重写Limit
     * @return SQL构建器
     */
    public SQLBuilder rewrite(final boolean isRewriteLimit) {
        SQLBuilder result = new SQLBuilder();
        //如果没有sqlTokens，说明没有需要替换的标记
        if (sqlTokens.isEmpty()) {
            //直接add
            result.appendLiterals(originalSQL);
            return result;
        }
        int count = 0;
        sortByBeginPosition();
        //遍历SQL标记
        for (SQLToken each : sqlTokens) {
            if (0 == count) {
               //标记的位置是需要替换的开始index，这里直接add不需要替换的部分
                result.appendLiterals(originalSQL.substring(0, each.getBeginPosition()));
            }
            //需要替换table
            if (each instanceof TableToken) {
                appendTableToken(result, (TableToken) each, count, sqlTokens);
            } else if (each instanceof ItemsToken) {
                appendItemsToken(result, (ItemsToken) each, count, sqlTokens);
            } else if (each instanceof RowCountLimitToken) {
                appendLimitRowCount(result, (RowCountLimitToken) each, count, sqlTokens, isRewriteLimit);
            } else if (each instanceof OffsetLimitToken) {
                appendLimitOffsetToken(result, (OffsetLimitToken) each, count, sqlTokens, isRewriteLimit);
            }
            count++;
        }
        return result;
    }
    //appendTableToken：
    private void appendTableToken(final SQLBuilder sqlBuilder, final TableToken tableToken, final int count, final List<SQLToken> sqlTokens) {
        //获取真实tableName
        String tableName = tableNames.contains(tableToken.getTableName()) ? tableToken.getTableName() : tableToken.getOriginalLiterals();
        // addTableToken
        sqlBuilder.appendTable(tableName);
        int beginPosition = tableToken.getBeginPosition() + tableToken.getOriginalLiterals().length();
        int endPosition = sqlTokens.size() - 1 == count ? originalSQL.length() : sqlTokens.get(count + 1).getBeginPosition();
        //截取2个token之间，不需要替换的部分
        sqlBuilder.appendLiterals(originalSQL.substring(beginPosition, endPosition));
    }
复制代码

4. 判断routingResult的路由(是否是笛卡尔积路由)，我们这个SQL是简单路由： 遍历路由得到的TableUnit结果，generateSQL()生成SQL，构造SQLExecutionUnit

for (TableUnit each : routingResult.getTableUnits().getTableUnits()) {
                result.getExecutionUnits().add(new SQLExecutionUnit(each.getDataSourceName(), rewriteEngine.generateSQL(each, sqlBuilder)));
            }
复制代码

    /**
     * 生成SQL语句.
     * 
     * @param tableUnit 路由表单元
     * @param sqlBuilder SQL构建器
     * @return SQL语句
     */
    public String generateSQL(final TableUnit tableUnit, final SQLBuilder sqlBuilder) {
        return sqlBuilder.toSQL(getTableTokens(tableUnit));
    }
    //将逻辑表和真实表以map的形式关联
     private Map<String, String> getTableTokens(final TableUnit tableUnit) {
        Map<String, String> tableTokens = new HashMap<>();
        tableTokens.put(tableUnit.getLogicTableName(), tableUnit.getActualTableName());
        Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(tableUnit.getLogicTableName());
        if (bindingTableRule.isPresent()) {
            tableTokens.putAll(getBindingTableTokens(tableUnit, bindingTableRule.get()));
        }
        return tableTokens;
    }
    
    /**
     * 生成SQL语句.
     * foreach 每个segments，生成真正可执行的SQL
     * @param tableTokens 占位符集合
     * @return SQL语句
     */
    public String toSQL(final Map<String, String> tableTokens) {
        StringBuilder result = new StringBuilder();
        for (Object each : segments) {
            if (each instanceof TableToken && tableTokens.containsKey(((TableToken) each).tableName)) {
                result.append(tableTokens.get(((TableToken) each).tableName));
            } else {
                result.append(each);
            }
        }
        return result.toString();
    }
复制代码

最后：

小尾巴走一波，欢迎关注我的公众号，不定期分享编程、投资、生活方面的感悟:)