1、ResultSet结果集合并
在单库架构下，我们不需要考虑数据结果集合并问题，但是在分库情况下，我们就需要关注数据合并问题，比如查询两个数据库，那么给用户返回的数据应该是两个DB结果集合，下面主要分许sharding-JDBC是如何处理结集，主要分为有resultSet返回和无resultSet返回两种方式
2、无ResultSet返回
对于update等操作是没有resultSet返回，返回具体影响行数或者是boolean值， 通过getResultSet方法获取结果集，此类结果集处理比较简单。首先看下在sharding内部接口定义,选取部分
@Override
public int executeUpdate(final String sql) throws SQLException {
try {
clearPrevious();
shard(sql);
initStatementExecutor();
return statementExecutor.executeUpdate();
} finally {
currentResultSet = null;
}
}
@Override
public int executeUpdate(final String sql, final int autoGeneratedKeys) throws SQLException {
if (RETURN_GENERATED_KEYS == autoGeneratedKeys) {
returnGeneratedKeys = true;
}
try {
clearPrevious();
shard(sql);
initStatementExecutor();
return statementExecutor.executeUpdate(autoGeneratedKeys);
} finally {
currentResultSet = null;
}
}
@Override
public int executeUpdate(final String sql, final int[] columnIndexes) throws SQLException {
returnGeneratedKeys = true;
try {
clearPrevious();
shard(sql);
initStatementExecutor();
return statementExecutor.executeUpdate(columnIndexes);
} finally {
currentResultSet = null;
}
}
@Override
public int executeUpdate(final String sql, final String[] columnNames) throws SQLException {
returnGeneratedKeys = true;
try {
clearPrevious();
shard(sql);
initStatementExecutor();
return statementExecutor.executeUpdate(columnNames);
} finally {
currentResultSet = null;
}
}
@Override
public boolean execute(final String sql) throws SQLException {
try {
clearPrevious();
shard(sql);
initStatementExecutor();
return statementExecutor.execute();
} finally {
currentResultSet = null;
}
}
.............
executeUpdate方法内部对结果集进行了处理
public int executeUpdate() throws SQLException {
return executeUpdate(new Updater() {
@Override
public int executeUpdate(final Statement statement, final String sql) throws SQLException {
return statement.executeUpdate(sql);
}
});
}
executeUpdate方法参数是一个Updater函数接口，该接口定义如下
private interface Updater {
int executeUpdate(Statement statement, String sql) throws SQLException;
}
executeUpdate方法
private int executeUpdate(final Updater updater) throws SQLException {
final boolean isExceptionThrown = ExecutorExceptionHandler.isExceptionThrown();
//抽象类SQLExecuteCallback主要是在SQL执行逻辑中添加其他操作，真正的SQL执行方法executeSQL作为抽象方法，使用者实现
SQLExecuteCallback<Integer> executeCallback = new SQLExecuteCallback<Integer>(getDatabaseType(), isExceptionThrown) {
@Override
protected Integer executeSQL(final RouteUnit routeUnit, final Statement statement, final ConnectionMode connectionMode) throws SQLException {
return updater.executeUpdate(statement, routeUnit.getSqlUnit().getSql());
}
};
//执行executeCallback方法，得到结果集
List<Integer> results = executeCallback(executeCallback);
//判断是否需要结果集合并，如果是广播表，则不需要结果集合并
if (isAccumulate()) {
//内部是对每个数据库返回的数据集进行累加
return accumulate(results);
} else {
//没有返回resultSet时直接返回0
return null == results.get(0) ? 0 : results.get(0);
}
}
SQLExecuteCallback模型如下
/**
* @author Qi.qingshan
* @date 2020/7/29
*/
public abstract class SQLExecuteCallback {
public void execute() {
//doBefore
execute0();
//doAfter
}
abstract void execute0();
}
3、ResultSet返回值
典型的就是executeQuery方法
public ResultSet executeQuery(final String sql) throws SQLException {
ResultSet result;
try {
clearPrevious();
shard(sql);
initStatementExecutor();
//结果集合并引擎
MergeEngine mergeEngine = MergeEngineFactory.newInstance(connection.getRuntimeContext().getDatabaseType(),
connection.getRuntimeContext().getRule(), sqlRouteResult, connection.getRuntimeContext().getMetaData().getTable(), statementExecutor.executeQuery());
//获取包装后的resultSet
result = getResultSet(mergeEngine);
} finally {
currentResultSet = null;
}
currentResultSet = result;
return result;
}
resultSet合并引擎主要有三个实现
其中TransparentMergeEngine代表是流式结果集合并，不需要对结果集做处理，TransparentMergeEngine 处理的是TransparentOptimizedStatement类型的语句，TransparentOptimizedStatement通过工厂创建
public static OptimizeEngine newInstance(final SQLStatement sqlStatement) {
if (sqlStatement instanceof SelectStatement) {
return new ShardingSelectOptimizeEngine();
}
if (sqlStatement instanceof InsertStatement) {
return new ShardingInsertOptimizeEngine();
}
if (sqlStatement instanceof UpdateStatement) {
return new ShardingUpdateOptimizeEngine();
}
if (sqlStatement instanceof DeleteStatement) {
return new ShardingDeleteOptimizeEngine();
}
if (sqlStatement instanceof DropIndexStatement) {
return new ShardingDropIndexOptimizeEngine();
}
//1、除了上述几种类型（select、insert、update、delete、dropIndex）其他都用TransparentOptimizeEngine处理，比showTables查看表结构，仅返回一个节点的就可以
//另外一点是与代理模式Proxy组合使用时，也使用此引擎
return new TransparentOptimizeEngine();
}
sharding-JDBC对ResultSet重新包装为QueryResult，定义接口如下
public interface QueryResult {
/**
* iterate next data.
*
* @return has next data
* @throws SQLException SQL Exception
*/
boolean next() throws SQLException;
/**
* Get column count.
*
* @return column count
* @throws SQLException SQL Exception
*/
int getColumnCount() throws SQLException;
/**
* Get column label.
*
* @param columnIndex column index
* @return column label
* @throws SQLException SQL Exception
*/
String getColumnLabel(int columnIndex) throws SQLException;
/**
* Get data value.
*
* @param columnIndex column index
* @param type class type of data value
* @return data value
* @throws SQLException SQL Exception
*/
Object getValue(int columnIndex, Class<?> type) throws SQLException;
/**
* Get data value.
*
* @param columnLabel column label
* @param type class type of data value
* @return data value
* @throws SQLException SQL Exception
*/
Object getValue(String columnLabel, Class<?> type) throws SQLException;
/**
* Get calendar value.
*
* @param columnIndex column index
* @param type class type of data value
* @param calendar calendar
* @return calendar value
* @throws SQLException SQL Exception
*/
Object getCalendarValue(int columnIndex, Class<?> type, Calendar calendar) throws SQLException;
/**
* Get calendar value.
*
* @param columnLabel column label
* @param type class type of data value
* @param calendar calendar
* @return calendar value
* @throws SQLException SQL Exception
*/
Object getCalendarValue(String columnLabel, Class<?> type, Calendar calendar) throws SQLException;
/**
* Get InputStream.
*
* @param columnIndex column index
* @param type class type of data value
* @return InputStream
* @throws SQLException SQL Exception
*/
InputStream getInputStream(int columnIndex, String type) throws SQLException;
/**
* Get InputStream.
*
* @param columnLabel column label
* @param type class type of data value
* @return InputStream
* @throws SQLException SQL Exception
*/
InputStream getInputStream(String columnLabel, String type) throws SQLException;
/**
* Judge ResultSet is null or not.
*
* @return ResultSet is null or not
* @throws SQLException SQL Exception
*/
boolean wasNull() throws SQLException;
/**
* Whether the column value is case sensitive.
*
* @param columnIndex column index
* @return true if column is case sensitive, otherwise false
* @throws SQLException SQL Exception
*/
boolean isCaseSensitive(int columnIndex) throws SQLException;
/**
* Get QueryResultMetaData.
*
* @return QueryResultMetaData
*/
QueryResultMetaData getQueryResultMetaData();
}
QueryResult实现有以下几种
- DistinctQueryResult 处理查询中包含distinct关键字语句
- MemoryQueryResult 在内存中进行结果集合并，处理那种多节点查询，需要对结果集重新处理的场景
- StreamQueryResult 流式，对结果集无需处理，将多个resultSet结果集直接返回
采用流式还是内存主要通过每个查询允许的连接数确定
public enum ConnectionMode {
MEMORY_STRICTLY, CONNECTION_STRICTLY
}
//当每个查询允许最大连接数小于需要执行的sql个数时，采用CONNECTION_STRICTLY，否则采用 MEMORY_STRICTLY
ConnectionMode connectionMode = maxConnectionsSizePerQuery < sqlUnits.size() ? ConnectionMode.CONNECTION_STRICTLY : ConnectionMode.MEMORY_STRICTLY;
获取resultSet
private QueryResult getQueryResult(final RouteUnit routeUnit, final Statement statement, final ConnectionMode connectionMode) throws SQLException {
ResultSet resultSet = statement.executeQuery(routeUnit.getSqlUnit().getSql());
ShardingRule shardingRule = getConnection().getRuntimeContext().getRule();
getResultSets().add(resultSet);
//根据上边的赋值，获取对应类型的resultSet
return ConnectionMode.MEMORY_STRICTLY == connectionMode ? new StreamQueryResult(resultSet, shardingRule)
: new MemoryQueryResult(resultSet, shardingRule);
}
getResultSet方法
private ShardingResultSet getResultSet(final MergeEngine mergeEngine) throws SQLException {
return getCurrentResultSet(statementExecutor.getResultSets(), mergeEngine);
}
//返回包装后的 ShardingResultSet，
private ShardingResultSet getCurrentResultSet(final List<ResultSet> resultSets, final MergeEngine mergeEngine) throws SQLException {
return new ShardingResultSet(resultSets, mergeEngine.merge(), this, sqlRouteResult);
}
主要分析mergeEngine.merge()方法，获取合并引擎，选取DQLMergeEngine分析
@Override
public MergedResult merge() throws SQLException {
//当查询结果中只有一个resultSet，则无需对结果集处理，直接使用流式结果集处理，返回结果
if (1 == queryResults.size()) {
return new IteratorStreamMergedResult(queryResults);
}
optimizedStatement.setIndexForItems(columnLabelIndexMap);
//decorate对sql中存在分页进行包装
return decorate(build());
}
private MergedResult build() throws SQLException {
//SQL中含有groupby或者聚合函数关键字，则使用GroupByMergedResult
if (!optimizedStatement.getGroupBy().getItems().isEmpty() || !optimizedStatement.getSelectItems().getAggregationSelectItems().isEmpty()) {
return getGroupByMergedResult();
}
//仅包含orderby的sql采用流式合并
if (!optimizedStatement.getOrderBy().getItems().isEmpty()) {
return new OrderByStreamMergedResult(queryResults, optimizedStatement.getOrderBy().getItems());
}
return new IteratorStreamMergedResult(queryResults);
}
getGroupByMergedResult方法
private MergedResult getGroupByMergedResult() throws SQLException {
//如果group by 列与排序列相同则使用流式合并，否则采用内存合并方法
//内存合并意思是将各数据库中的数据获取到以后重新保存在内存中，然后对内存中的数据进行处理，然后返回
return optimizedStatement.isSameGroupByAndOrderByItems()
? new GroupByStreamMergedResult(columnLabelIndexMap, queryResults, optimizedStatement) : new GroupByMemoryMergedResult(columnLabelIndexMap, queryResults, optimizedStatement);
}
