这篇文章主要介绍“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改写用法”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：https://my.oschina.net/u/3180962/blog/3102419
