这篇文章主要介绍“sharding-jdbc中SQL改写用法”,在日常操作中,相信很多人在sharding-jdbc中SQL改写用法问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”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(); } }
到此,关于“sharding-jdbc中SQL改写用法”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。