sharding-jdbc之SQL改写

【引用官网】在包含分表的场景中，需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。仅分库则不需要表名称的改写。除此之外，还包括补列和分页信息修正等内容，如图:

 

 

 

本文主要以SELECT i.* FROM t_order_1 o, t_order_item_1 i WHERE o.order_id = i.order_id and o.order_id = ? and o.user_id = ?一个简单查询语句，来分析ss大致如何来改写sql的，不同类型sql改写需自行查看对应的sql token生成器

• 比如分页查看OffsetTokenGenerator

 

1.BaseShardingEngine#shard执行改写，主要查看rewriteAndConvert方法

@RequiredArgsConstructor
public abstract class BaseShardingEngine {
    //分库分表规则
    private final ShardingRule shardingRule;
    //分片参数
    private final ShardingProperties shardingProperties;
    //分片元数据
    private final ShardingMetaData metaData;
    //路由钩子
    private final SPIRoutingHook routingHook = new SPIRoutingHook();
    
    /**
     * Shard.
     *
     * @param sql SQL
     * @param parameters parameters of SQL
     * @return SQL route result
     */
    public SQLRouteResult shard(final String sql, final List<Object> parameters) {
        List<Object> clonedParameters = cloneParameters(parameters);
        SQLRouteResult result = executeRoute(sql, clonedParameters);
        //sql改写，如何是Hint则不需要改写sql
        result.getRouteUnits().addAll(HintManager.isDatabaseShardingOnly() ? convert(sql, clonedParameters, result) : rewriteAndConvert(clonedParameters, result));
        if (shardingProperties.getValue(ShardingPropertiesConstant.SQL_SHOW)) {
            boolean showSimple = shardingProperties.getValue(ShardingPropertiesConstant.SQL_SIMPLE);
            SQLLogger.logSQL(sql, showSimple, result.getOptimizedStatement().getSQLStatement(), result.getRouteUnits());
        }
        return result;
    }
    ... ...
    private Collection<RouteUnit> convert(final String sql, final List<Object> parameters, final SQLRouteResult sqlRouteResult) {
        Collection<RouteUnit> result = new LinkedHashSet<>();
        for (RoutingUnit each : sqlRouteResult.getRoutingResult().getRoutingUnits()) {
            result.add(new RouteUnit(each.getDataSourceName(), new SQLUnit(sql, parameters)));
        }
        return result;
    }
    
    private Collection<RouteUnit> rewriteAndConvert(final List<Object> parameters, final SQLRouteResult sqlRouteResult) {
        //改写引擎
        SQLRewriteEngine rewriteEngine = new SQLRewriteEngine(shardingRule, sqlRouteResult, parameters, sqlRouteResult.getRoutingResult().isSingleRouting());
        Collection<RouteUnit> result = new LinkedHashSet<>();
        //遍历路由单元，
        //如t_order、t_order_item是绑定表关系，那么这里路由单元集合只有一个t_order
        for (RoutingUnit each : sqlRouteResult.getRoutingResult().getRoutingUnits()) {
            //添加sql改写后的路由单元
            result.add(new RouteUnit(each.getDataSourceName(),
                    //封装改写sql单元
                    rewriteEngine.generateSQL(each, getLogicAndActualTables(each, sqlRouteResult.getOptimizedStatement().getSQLStatement().getTables().getTableNames()))));
        }
        return result;
    }
    
    private Map<String, String> getLogicAndActualTables(final RoutingUnit routingUnit, final Collection<String> parsedTableNames) {
        Map<String, String> result = new HashMap<>();
        //遍历表单元
        for (TableUnit each : routingUnit.getTableUnits()) {
            String logicTableName = each.getLogicTableName().toLowerCase();
            //添加逻辑表:真实表 t_order:t_order_0
            result.put(logicTableName, each.getActualTableName());
            //根据绑定表添加剩余的解析表
            //比如t_order、t_order_item是绑定表，解析表为t_order、t_order_item，则添加t_order_item:t_order_item_0
            result.putAll(getLogicAndActualTablesFromBindingTable(routingUnit.getMasterSlaveLogicDataSourceName(), each, parsedTableNames));
        }
        //返回逻辑表对应的真实表
        return result;
    }
    
    private Map<String, String> getLogicAndActualTablesFromBindingTable(final String dataSourceName, final TableUnit tableUnit, final Collection<String> parsedTableNames) {
        Map<String, String> result = new LinkedHashMap<>();
        //根据逻辑表获取对应的绑定表 t_order、t_order_item
        Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(tableUnit.getLogicTableName());
        if (bindingTableRule.isPresent()) {
            result.putAll(getLogicAndActualTablesFromBindingTable(dataSourceName, tableUnit, parsedTableNames, bindingTableRule.get()));
        }
        return result;
    }
    
    private Map<String, String> getLogicAndActualTablesFromBindingTable(
            final String dataSourceName, final TableUnit tableUnit, final Collection<String> parsedTableNames, final BindingTableRule bindingTableRule) {
        Map<String, String> result = new LinkedHashMap<>();
        //遍历解析后的表 t_order、t_order_item
        for (String each : parsedTableNames) {
            String tableName = each.toLowerCase();
            //解析表和逻辑表不想等，且解析表是绑定表
            if (!tableName.equals(tableUnit.getLogicTableName().toLowerCase()) && bindingTableRule.hasLogicTable(tableName)) {
                //添加解析表对应的真实表
                result.put(tableName, bindingTableRule.getBindingActualTable(dataSourceName, tableName, tableUnit.getActualTableName()));
            }
        }
        return result;
    }
}    

 

2.改写SQL，SQLRewriteEngine#generateSQL

public final class SQLRewriteEngine {
    //规则
    private final BaseRule baseRule;
    //优化后的Statement
    private final OptimizedStatement optimizedStatement;
    //token
    private final List<SQLToken> sqlTokens;
    //sql构建者
    private final SQLBuilder sqlBuilder;
    //参数构建者
    private final ParameterBuilder parameterBuilder;
    
    public SQLRewriteEngine(final ShardingRule shardingRule, final SQLRouteResult sqlRouteResult, final List<Object> parameters, final boolean isSingleRoute) {
        baseRule = shardingRule;
        this.optimizedStatement = getEncryptedOptimizedStatement(shardingRule.getEncryptRule().getEncryptorEngine(), sqlRouteResult.getOptimizedStatement());
        //占位符参数值
        parameterBuilder = createParameterBuilder(parameters, sqlRouteResult);
        //创建sql token，主要通过token来生成真实sql
        sqlTokens = createSQLTokens(isSingleRoute);
        //sql构建者
        sqlBuilder = new SQLBuilder(optimizedStatement.getSQLStatement().getLogicSQL(), sqlTokens);
    }
    
    ... ...
    
    private List<SQLToken> createSQLTokens(final boolean isSingleRoute) {
        List<SQLToken> result = new LinkedList<>();
        //改写SQL核心，主要根据解析后的segment生成相应类型的token，如TableTokenGenerator->TableToken
        //基础token生成引擎
        result.addAll(new BaseTokenGenerateEngine().generateSQLTokens(optimizedStatement, parameterBuilder, baseRule, isSingleRoute));
        //分库分表规则
        if (baseRule instanceof ShardingRule) {
            ShardingRule shardingRule = (ShardingRule) baseRule;
            result.addAll(new ShardingTokenGenerateEngine().generateSQLTokens(optimizedStatement, parameterBuilder, shardingRule, isSingleRoute));
            result.addAll(new EncryptTokenGenerateEngine().generateSQLTokens(optimizedStatement, parameterBuilder, shardingRule.getEncryptRule(), isSingleRoute));
        } else if (baseRule instanceof EncryptRule) {
            result.addAll(new EncryptTokenGenerateEngine().generateSQLTokens(optimizedStatement, parameterBuilder, (EncryptRule) baseRule, isSingleRoute));
        }
        //排序，这里主要根据解析后的startIndex排序，用来保证sql token的正确性
        Collections.sort(result);
        return result;
    }
    
    /**
     * Generate SQL.
     * 
     * @return sql unit
     */
    public SQLUnit generateSQL() {
        return new SQLUnit(sqlBuilder.toSQL(), parameterBuilder.getParameters());
    }
    
    /**
     * Generate SQL.
     * 
     * @param routingUnit routing unit
     * @param logicAndActualTables logic and actual tables
     * @return sql unit
     */
    public SQLUnit generateSQL(final RoutingUnit routingUnit, final Map<String, String> logicAndActualTables) {
        //封装sql单元，主要根据token index、逻辑表对应的真实表来生成sql
        return new SQLUnit(sqlBuilder.toSQL(routingUnit, logicAndActualTables), parameterBuilder.getParameters(routingUnit));
    }
}

 

3.构建SQL，SQLBuilder#toSQL

@RequiredArgsConstructor
public final class SQLBuilder {
    //逻辑sql
    private final String logicSQL;
    //sql token
    private final List<SQLToken> sqlTokens;
    
    /**
     * Convert to SQL.
     *
     * @return SQL
     */
    public String toSQL() {
        return toSQL(null, Collections.<String, String>emptyMap());
    }
    
    /**
     * Convert to SQL.
     *
     * @param routingUnit routing unit
     * @param logicAndActualTables logic and actual map
     * @return SQL
     */
    public String toSQL(final RoutingUnit routingUnit, final Map<String, String> logicAndActualTables) {
        if (sqlTokens.isEmpty()) {
            return logicSQL;
        }
        return createLogicSQL(routingUnit, logicAndActualTables);
    }
    
    private String createLogicSQL(final RoutingUnit routingUnit, final Map<String, String> logicAndActualTables) {
        StringBuilder result = new StringBuilder();
        //截取逻辑sql，从0截取到第一个token start index
        //如:SELECT i.* FROM t_order_1 o, t_order_item_1 i WHERE o.order_id = i.order_id and o.order_id = ? and o.user_id = ?
​
        //以上面sql为例，sqlTokens为:
        //[TableToken(startIndex=16,stopIndex=22,tableName=t_order), TableToken(startIndex=27,stopIndex=38,tableName=t_order_item)]
​
        result.append(logicSQL.substring(0, sqlTokens.get(0).getStartIndex())); //截取结果为select * from
        //遍历token
        for (SQLToken each : sqlTokens) {
            //以改写表为例
            //此处为根据逻辑表改写为真实表
            result.append(getSQLTokenLiterals(each, routingUnit, logicAndActualTables)); //结果为t_order_0
            //此处则是处理别名
            result.append(getConjunctionLiterals(each));//结果为 o,
        }
        return result.toString();
    }
    
    private String getSQLTokenLiterals(final SQLToken sqlToken, final RoutingUnit routingUnit, final Map<String, String> logicAndActualTables) {
        //判断token是否可变(Alterable)，调用对应token的toString方法
        //如是Alterable，返回逻辑表对应的真实表，即t_order:t_order_0，返回t_order_0
        return sqlToken instanceof Alterable ? ((Alterable) sqlToken).toString(routingUnit, logicAndActualTables) : sqlToken.toString();
    }
    
    private String getConjunctionLiterals(final SQLToken sqlToken) {
        //TableToken(startIndex=16,stopIndex=22,tableName=t_order)
        //TableToken(startIndex=27,stopIndex=38,tableName=t_order_item)
​
        //找到当前sqlToken的index
        //第一次遍历currentSQLTokenIndex为0
        int currentSQLTokenIndex = sqlTokens.indexOf(sqlToken);
        //计算需要截取的结束位置
        //第一次遍历stopIndex为27
        int stopIndex = sqlTokens.size() - 1 == currentSQLTokenIndex ? logicSQL.length() : sqlTokens.get(currentSQLTokenIndex + 1).getStartIndex();
        //计算需要截取的起始位置
        //判断当前sqlToken的起始位置是否大于逻辑sql长度，如果起始位置大于逻辑sql的长度时，则为逻辑sql长度，否则获取当前sqlToken的起始位置
        //第一次遍历 startIndex:23 stopIndex:27，截取结果为 o,
        return logicSQL.substring(getStartIndex(sqlToken) > logicSQL.length() ? logicSQL.length() : getStartIndex(sqlToken), stopIndex);
    }
    
    private int getStartIndex(final SQLToken sqlToken) {
        //判断token是否可替代，如别名
        return sqlToken instanceof Substitutable ? ((Substitutable) sqlToken).getStopIndex() + 1 : sqlToken.getStartIndex();
    }
}

 

转载于:https://my.oschina.net/u/3180962/blog/3102419