背景:
最近有个分库分表项目需要适配国产数据库,如人大金仓、达梦数据库,记录下对接人大金仓过程中遇到的一个问题;
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]