Sharding-jdbc连接kingbasev8r6跨表分页

最新推荐文章于 2024-06-06 18:05:40 发布

idto315

最新推荐文章于 2024-06-06 18:05:40 发布

阅读量1.8k

点赞数 1

文章标签： java mybatis spring boot

本文链接：https://blog.csdn.net/idto315/article/details/126776394

版权

背景：

最近有个分库分表项目需要适配国产数据库，如人大金仓、达梦数据库，记录下对接人大金仓过程中遇到的一个问题；

maven配置：

        <!--sharding-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>

错误描述：

由于业务量比较大，所有业务表数使用了按天分表的策略，在适配人大金仓的过程中，发现页面分页存证一个现象（mysql、达梦、sqlServer数据库正常）：若查询条件跨天（即跨表查询）统计日志会出现异常堆栈，但页面只查询一天的数据（即不跨表）统计正常，异常日志如下：

### Error querying database.  Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(*), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
### The error may exist in class path resource [mapping/TransMapper.xml]
### The error may involve cn.***.dao.mapper.TransMapper.countByExample
### The error occurred while handling results
### SQL: select count(*) from f_trans                    WHERE (  create_time between ? and ? )
### Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(*), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	at sun.reflect.GeneratedMethodAccessor154.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	... 97 common frames omitted
Caused by: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(*), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
	at com.google.common.base.Preconditions.checkState(Preconditions.java:534)
	at org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.setIndexForAggregationProjection(SelectStatementContext.java:128)
	at org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.setIndexes(SelectStatementContext.java:121)
	at org.apache.shardingsphere.sharding.merge.dql.ShardingDQLResultMerger.merge(ShardingDQLResultMerger.java:62)
	at org.apache.shardingsphere.underlying.merge.MergeEntry.merge(MergeEntry.java:85)
	at org.apache.shardingsphere.underlying.merge.MergeEntry.process(MergeEntry.java:75)
	at org.apache.shardingsphere.underlying.pluggble.merge.MergeEngine.merge(MergeEngine.java:61)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.mergeQuery(ShardingPreparedStatement.java:190)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.getResultSet(ShardingPreparedStatement.java:158)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:238)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:188)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
	... 104 common frames omitted

错误分析：

日志关键信息：

Caused by: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(*), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections

sharding-jdbc源码：

private void setIndexForAggregationProjection(Map<String, Integer> columnLabelIndexMap) {
	Iterator var2 = this.projectionsContext.getAggregationProjections().iterator();

	while(var2.hasNext()) {
		AggregationProjection each = (AggregationProjection)var2.next();
		// 检查含聚合函数的SQL中解析的列标签里是否包含遍历的列标签，没有则提示需要给聚合函数添加别名
		Preconditions.checkState(columnLabelIndexMap.containsKey(each.getColumnLabel()), "Can't find index: %s, please add alias for aggregate selections", new Object[]{each});
		each.setIndex((Integer)columnLabelIndexMap.get(each.getColumnLabel()));
		Iterator var4 = each.getDerivedAggregationProjections().iterator();

		while(var4.hasNext()) {
			AggregationProjection derived = (AggregationProjection)var4.next();
			Preconditions.checkState(columnLabelIndexMap.containsKey(derived.getColumnLabel()), "Can't find index: %s", new Object[]{derived});
			derived.setIndex((Integer)columnLabelIndexMap.get(derived.getColumnLabel()));
		}
	}

}

根据关系信息配合sharding-jdbc源码查询的得知，sharding-jdbc跨表进行聚合SQL操作时需要对聚合的记过进行别名设置，否则就会报这个错误；

问题解决测试：

既然提示需要给聚合函数设置别名，就按照要求来修正；

PS:为了防止和部分数据库的关键字撞衫，SQL中的字段名、别名尽量不要用通用的单词（已经踩过坑了）

  <select id="countByExample" parameterType="cn.org.bjca.preserve.model.common.TransExample" resultType="java.lang.Long">
    select count(id) as totalSize from f_trans
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>

启动服务测试跨表分页，日志正常，页面也有数据

2022-09-08 17:02:55,049 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] org.mybatis.spring.SqlSessionUtils       : Creating a new SqlSession
2022-09-08 17:02:55,049 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] org.mybatis.spring.SqlSessionUtils       : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1320a097] was not registered for synchronization because synchronization is not active
2022-09-08 17:02:55,051 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
2022-09-08 17:02:55,051 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] o.m.s.t.SpringManagedTransaction         : JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@26fcc33a] will not be managed by Spring
2022-09-08 17:02:55,051 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] c.o.b.p.d.m.TransMapper.countByExample   : ==>  Preparing: select count(id) from f_trans WHERE ( create_time between ? and ? )
2022-09-08 17:02:55,051 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] c.o.b.p.d.m.TransMapper.countByExample   : ==> Parameters: 2022-09-07 00:00:00.0(Timestamp), 2022-09-08 23:59:59.0(Timestamp)
2022-09-08 17:02:55,052 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] b.p.c.s.DayHintDataBaseShardingAlgorithm : ["db_2022"]
2022-09-08 17:02:55,053 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] .o.b.p.c.s.DayHintTableShardingAlgorithm : ["f_trans_20220907","f_trans_20220908"]
2022-09-08 17:02:55,056 DEBUG 808708 --- [http-nio-8051-exec-6] [d873ca8114f043fba92abfa62a71a3d5] org.mybatis.spring.SqlSessionUtils       : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1320a097]