民航电子数据库：Can‘t find index: AggregationProjection(xxx), please add alias for aggregate selections

已于 2024-04-30 14:35:06 修改
阅读量1.3k 收藏 24
点赞数 31
分类专栏： 第三方对接 文章标签： 数据库
于 2024-04-30 14:18:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_52116015/article/details/138343423
版权

目录

一、前言

1、对接民航电子数据库

2、框架：ShardingSphere + caedb

3、执行select时报错

二、SQL示例

注：以下示例都是基于ShardingSphere + caedb框架

示例一：使用PageHelper进行分页查询，涉及count()函数，涉及分库，不涉及跨表

Preparing: SELECT count(0) FROM t_mb_orderurlcfg ouc LEFT JOIN t_mb_config c ON ouc.originId = c.ID WHERE ouc.vdnId = ? 

Parameters: 1(Integer)

以上SQL可正常执行

在这里插入图片描述

示例二：手写SQL进行查询，涉及count()函数，涉及分库，不涉及跨表

在这里插入图片描述

Preparing: select count(1) from t_mb_message where isSend='1' and sessionType != '1' AND openId = ? AND CreateTime BETWEEN ? AND ? AND vdnId = ? 

Parameters: 1_90D5KHa3O_243fo(String), 2024-02-29 07:03:05.448(Timestamp), 2024-04-30 07:03:05.448(Timestamp), 1(String)

以上SQL执行报错

报错信息
### Error querying database.  Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
### The error may exist in file [/home/xxx/devtest/online/tomcat8-ass-cloud-web-8087/webapps/FS_WEB_ASS_WEB/WEB-INF/classes/mappings/UserDao.xml]
### The error may involve com.xxx.dao.UserDao.findSendHisMsgCount
### The error occurred while handling results
### SQL: select count(1) from t_mb_message   where isSend='1' and sessionType != '1'        AND openId = ?            AND CreateTime BETWEEN ? AND ?            AND vdnId = ?
### Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
### The error may exist in file [/home/xxx/devtest/online/tomcat8-ass-cloud-web-8087/webapps/FS_WEB_ASS_WEB/WEB-INF/classes/mappings/UserDao.xml]
### The error may involve com.xxx.dao.UserDao.findSendHisMsgCount
### The error occurred while handling results
### SQL: select count(1) from t_mb_message   where isSend='1' and sessionType != '1'        AND openId = ?            AND CreateTime BETWEEN ? AND ?            AND vdnId = ?
### Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy96.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:83)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy99.findSendHisMsgCount(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy100.findSendHisMsgCount(Unknown Source)
	at com.xxx.service.impl.UserServiceImpl.getHisMsgCount(UserServiceImpl.java:81)
	at com.xxx.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$e55ebb29.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
	at com.xxx.datasource.DataSourceAspect.around(DataSourceAspect.java:47)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.xxx.service.impl.UserServiceImpl$$EnhancerBySpringCGLIB$$2d0dda05.getHisMsgCount(<generated>)
	at com.xxx.socket.hander.WebSocketAndSocketUtil.IM_CCS_RequestHisMsg(WebSocketAndSocketUtil.java:465)
	at com.xxx.socket.hander.WebSocketAndSocketUtil.process(WebSocketAndSocketUtil.java:102)
	at com.xxx.socket.hander.WebSocketAndSocketUtil$$FastClassBySpringCGLIB$$92851630.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.xxx.socket.hander.WebSocketAndSocketUtil$$EnhancerBySpringCGLIB$$6f12877a.process(<generated>)
	at com.xxx.socket.hander.MessageSocketHandler.handleMessage(MessageSocketHandler.java:65)
	at org.springframework.web.socket.handler.WebSocketHandlerDecorator.handleMessage(WebSocketHandlerDecorator.java:75)
	at org.springframework.web.socket.handler.LoggingWebSocketHandlerDecorator.handleMessage(LoggingWebSocketHandlerDecorator.java:56)
	at org.springframework.web.socket.handler.ExceptionWebSocketHandlerDecorator.handleMessage(ExceptionWebSocketHandlerDecorator.java:58)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter.handleTextMessage(StandardWebSocketHandlerAdapter.java:113)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter.access$000(StandardWebSocketHandlerAdapter.java:42)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter$3.onMessage(StandardWebSocketHandlerAdapter.java:84)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter$3.onMessage(StandardWebSocketHandlerAdapter.java:81)
	at org.apache.tomcat.websocket.WsFrameBase.sendMessageText(WsFrameBase.java:390)
	at org.apache.tomcat.websocket.server.WsFrameServer.sendMessageText(WsFrameServer.java:130)
	at org.apache.tomcat.websocket.WsFrameBase.processDataText(WsFrameBase.java:484)
	at org.apache.tomcat.websocket.WsFrameBase.processData(WsFrameBase.java:284)
	at org.apache.tomcat.websocket.WsFrameBase.processInputBuffer(WsFrameBase.java:130)
	at org.apache.tomcat.websocket.server.WsFrameServer.onDataAvailable(WsFrameServer.java:85)
	at org.apache.tomcat.websocket.server.WsFrameServer.doOnDataAvailable(WsFrameServer.java:184)
	at org.apache.tomcat.websocket.server.WsFrameServer.notifyDataAvailable(WsFrameServer.java:164)
	at org.apache.tomcat.websocket.server.WsHttpUpgradeHandler.upgradeDispatch(WsHttpUpgradeHandler.java:152)
	at org.apache.coyote.http11.upgrade.UpgradeProcessorInternal.dispatch(UpgradeProcessorInternal.java:60)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:57)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:932)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1694)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:750)

示例三：手写SQL进行查询，涉及count()函数，涉及分库，涉及跨表

在这里插入图片描述

在这里插入图片描述

以上SQL执行报错

报错信息
2024-04-29 21:26:05.372 - [DEBUG] - [http-nio-8087-exec-10] - [c.xxx.dao.UserDao.findHisMsgCountByIReportNew - line:159]: ==>  Preparing: select count(1) from t_mb_message4rpt where sessionType = 1 AND openId = ? AND subTableDate BETWEEN ? AND ? AND vdnId = ? 
2024-04-29 21:26:05.372 - [DEBUG] - [http-nio-8087-exec-10] - [c.xxx.dao.UserDao.findHisMsgCountByIReportNew - line:159]: ==> Parameters: 1_90D5KHa3O_243fo(String), 2024-02-29 05:26:03.243(Timestamp), 2024-04-30 05:26:03.243(Timestamp), 1(Integer)
2024-04-29 21:26:05.373 - [INFO ] - [http-nio-8087-exec-10] - [c.f.d.algorithm.ModuloShardingDatabaseAlgorithm - line:20]: databases collection is [[ireport0, ireport1]], precise sharding value is [PreciseShardingValue(logicTableName=t_mb_message4rpt, columnName=vdnId, value=1)]
2024-04-29 21:26:05.373 - [INFO ] - [http-nio-8087-exec-10] - [c.f.d.algorithm.ModuloShardingDatabaseAlgorithm - line:25]: use databases name is [ireport1]
2024-04-29 21:26:05.373 - [INFO ] - [http-nio-8087-exec-10] - [c.f.d.algorithm.DateTimeShardingTableAlgorithm - line:46]: range table collection is [[t_mb_message4rpt_202401, t_mb_message4rpt_202402, t_mb_message4rpt_202403, t_mb_message4rpt_202404, t_mb_message4rpt_202405, t_mb_message4rpt_202406, t_mb_message4rpt_202407, t_mb_message4rpt_202408, t_mb_message4rpt_202409, t_mb_message4rpt_202410, t_mb_message4rpt_202411, t_mb_message4rpt_202412]], range sharding value is [RangeShardingValue(logicTableName=t_mb_message4rpt, columnName=subTableDate, valueRange=[2024-02-29 05:26:03.243..2024-04-30 05:26:03.243])]
2024-04-29 21:26:05.374 - [INFO ] - [http-nio-8087-exec-10] - [c.f.d.algorithm.DateTimeShardingTableAlgorithm - line:67]: use table name is [[t_mb_message4rpt_202402, t_mb_message4rpt_202403, t_mb_message4rpt_202404]]
2024-04-29 21:26:05.379 - [ERROR] - [http-nio-8087-exec-10] - [com.xxx.service.impl.UserServiceImpl - line:128]: 查询访客历史会话聊天记录出现异常(从报表库中获取)，详情为：nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
### The error may exist in file [/home/xxx/devtest/online/tomcat8-ass-cloud-web-8087/webapps/FS_WEB_ASS_WEB/WEB-INF/classes/mappings/UserDao.xml]
### The error may involve com.xxx.dao.UserDao.findHisMsgCountByIReportNew
### The error occurred while handling results
### SQL: select count(1) from t_mb_message4rpt      where sessionType = 1        AND openId = ?            AND subTableDate BETWEEN ? AND ?       AND vdnId = ?
### Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
### The error may exist in file [/home/xxx/devtest/online/tomcat8-ass-cloud-web-8087/webapps/FS_WEB_ASS_WEB/WEB-INF/classes/mappings/UserDao.xml]
### The error may involve com.xxx.dao.UserDao.findHisMsgCountByIReportNew
### The error occurred while handling results
### SQL: select count(1) from t_mb_message4rpt      where sessionType = 1        AND openId = ?            AND subTableDate BETWEEN ? AND ?       AND vdnId = ?
### Cause: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(1), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy96.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:83)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy99.findHisMsgCountByIReportNew(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy100.findHisMsgCountByIReportNew(Unknown Source)
	at com.xxx.service.impl.UserServiceImpl.getHisMsgCountByIReportNew(UserServiceImpl.java:126)
	at com.xxx.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$e55ebb29.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
	at com.xxx.datasource.DataSourceAspect.around(DataSourceAspect.java:47)
	at sun.reflect.GeneratedMethodAccessor157.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.xxx.service.impl.UserServiceImpl$$EnhancerBySpringCGLIB$$b1a4e816.getHisMsgCountByIReportNew(<generated>)
	at com.xxx.socket.hander.WebSocketAndSocketUtil.IM_CCS_RequestHisMsg(WebSocketAndSocketUtil.java:471)
	at com.xxx.socket.hander.WebSocketAndSocketUtil.process(WebSocketAndSocketUtil.java:102)
	at com.xxx.socket.hander.WebSocketAndSocketUtil$$FastClassBySpringCGLIB$$92851630.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.xxx.socket.hander.WebSocketAndSocketUtil$$EnhancerBySpringCGLIB$$f3a9958b.process(<generated>)
	at com.xxx.socket.hander.MessageSocketHandler.handleMessage(MessageSocketHandler.java:65)
	at org.springframework.web.socket.handler.WebSocketHandlerDecorator.handleMessage(WebSocketHandlerDecorator.java:75)
	at org.springframework.web.socket.handler.LoggingWebSocketHandlerDecorator.handleMessage(LoggingWebSocketHandlerDecorator.java:56)
	at org.springframework.web.socket.handler.ExceptionWebSocketHandlerDecorator.handleMessage(ExceptionWebSocketHandlerDecorator.java:58)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter.handleTextMessage(StandardWebSocketHandlerAdapter.java:113)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter.access$000(StandardWebSocketHandlerAdapter.java:42)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter$3.onMessage(StandardWebSocketHandlerAdapter.java:84)
	at org.springframework.web.socket.adapter.standard.StandardWebSocketHandlerAdapter$3.onMessage(StandardWebSocketHandlerAdapter.java:81)
	at org.apache.tomcat.websocket.WsFrameBase.sendMessageText(WsFrameBase.java:390)
	at org.apache.tomcat.websocket.server.WsFrameServer.sendMessageText(WsFrameServer.java:130)
	at org.apache.tomcat.websocket.WsFrameBase.processDataText(WsFrameBase.java:484)
	at org.apache.tomcat.websocket.WsFrameBase.processData(WsFrameBase.java:284)
	at org.apache.tomcat.websocket.WsFrameBase.processInputBuffer(WsFrameBase.java:130)
	at org.apache.tomcat.websocket.server.WsFrameServer.onDataAvailable(WsFrameServer.java:85)
	at org.apache.tomcat.websocket.server.WsFrameServer.doOnDataAvailable(WsFrameServer.java:184)
	at org.apache.tomcat.websocket.server.WsFrameServer.notifyDataAvailable(WsFrameServer.java:164)
	at org.apache.tomcat.websocket.server.WsHttpUpgradeHandler.upgradeDispatch(WsHttpUpgradeHandler.java:152)
	at org.apache.coyote.http11.upgrade.UpgradeProcessorInternal.dispatch(UpgradeProcessorInternal.java:60)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:57)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:932)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1694)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:750)

示例四：使用PageHelper进行分页查询，涉及count()函数，涉及分库，涉及跨表

报错信息与示例三一直，这里就省略掉了

三、原因

select涉及函数时，未给字段结果定义别名

示例三、示例四的具体原因应该是ShardingSphere进行跨表查询后，需要合并结果，由于字段没有起别名，导致合并的时候报错

至于示例二，原因不明

四、解决

select查询时如果涉及函数，未其加上别名即可

1、如果是手写的SQL直接加上别名即可

2、如果是PageHelper，可自定义PageHelper方言

1、编写方言类CustomDialect和CustomCountSqlParser

CustomCountSqlParser类
package com.xxx.dialect;

import com.github.pagehelper.parser.CountSqlParser;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.*;

import java.util.ArrayList;
import java.util.List;

/**
 * @description: 自定义CountSql
 **/
public class CustomCountSqlParser extends CountSqlParser {
    private static final Alias TABLE_ALIAS = new Alias("table_count");


    /**
     * 重写sqlToCount
     * @param select
     * @param name
     */
    @Override
    public void sqlToCount(Select select, String name) {
        SelectBody selectBody = select.getSelectBody();
        List<SelectItem> COUNT_ITEM = new ArrayList();
        // 增加别名total
        COUNT_ITEM.add(new SelectExpressionItem(new Column("count(" + name + ") as total")));
        if (selectBody instanceof PlainSelect && this.isSimpleCount((PlainSelect)selectBody)) {
            ((PlainSelect)selectBody).setSelectItems(COUNT_ITEM);
        } else {
            PlainSelect plainSelect = new PlainSelect();
            SubSelect subSelect = new SubSelect();
            subSelect.setSelectBody(selectBody);
            subSelect.setAlias(TABLE_ALIAS);
            plainSelect.setFromItem(subSelect);
            plainSelect.setSelectItems(COUNT_ITEM);
            select.setSelectBody(plainSelect);
        }
    }
}
CustomDialect类
package com.xxx.dialect;

import com.github.pagehelper.dialect.helper.MySqlDialect;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.RowBounds;

/**
 * @description: 自定义方言  兼容国产数据库
 **/
public class CustomDialect  extends MySqlDialect {

    public CustomDialect() {
        this.countSqlParser = new CustomCountSqlParser();
    }

    @Override
    public String getCountSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey countKey) {
        return this.countSqlParser.getSmartCountSql(boundSql.getSql());
    }
}
application配置方言（yml）
pagehelper:
  helper-dialect: 全限定类名.CustomDialect

六、结果

示例二

在这里插入图片描述

示例三

在这里插入图片描述

天黑请​闭眼
关注
  • 31
    点赞
  • 踩
  • 24
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
DynSketch: a spatio-temporal aggregate index for moving objects in road networks
02-20
DynSketch: a spatio-temporal aggregate index for moving objects in road networks
Oracle 9i&10g编程艺术：深入数据库体系结构 电子版及源代码
08-22
《Oracle 9i&10g编程艺术：深入数据库体系结构》是一本专注于Oracle数据库系统核心技术的专著，旨在帮助读者深入理解Oracle数据库的工作原理，掌握高效编程与优化技巧。这本书的内容涵盖了数据库设计、SQL查询优化、...
最近使用Sharding-JDBC在PageHelper分页报错 Can‘t find index: AggregationSelectItem
javamendou的博客
06-19 628
最近使用Sharding-JDBC在PageHelper分页报错 Can‘t find index: AggregationSelectItem
【Mysql】用Shardingsphere分库分表出现的问题
winrh的博客
07-24 7015
我的sql包含了count、left join、group by，然后报了错： Caused by: java.lang.IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(s.id), alias=Optional.of(countNum), derivedAggregationProjections=[], index=-1), please add alias f.
Sharding-JDBC 源码分析 —— 结果归并
weixin_42073629的博客
06-13 827
1. 概述 本文分享查询结果归并的源码实现。 正如前文《SQL 执行》提到的**“分表分库，需要执行的 SQL 数量从单条变成了多条”，多个SQL执行**结果必然需要进行合并，例如： SELECT * FROM t_order ORDER BY create_time 在各分片排序完后，Sharding-JDBC 获取到结果后，仍然需要再进一步排序。目前有分页、分组、排序、聚合列、迭代五种场景需要做进一步处理。当然，如果单分片SQL执行结果是无需合并的。在《SQL 执行》不知不觉已经分享了插..
Sharding-JDBC（五）PageHelper分页报错 Can‘t find index: AggregationSelectItem
ACGkaka的博客
01-26 2626
PageHelper分页报错 Can't find index: AggregationSelectItem
Sharding-jdbc连接kingbasev8r6跨表分页
idto315的博客
09-09 1816
Sharding-jdbc连接kingbasev8r6跨表分页异常Can't find index: AggregationProjection
ShardingSphere查询报错：Can‘t find index: AggregationProjection(xxx), please add alias for aggregate sele
最新发布
weixin_52116015的博客
04-22 597
IllegalStateException: Can't find index: AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
理解数据库：掌握关系代数.pptx
11-02
【关系代数】是数据库理论中的一个重要概念，它是一种形式化的查询语言，用于描述对关系数据库的操作。关系代数的操作主要基于集合论，它提供了一种抽象的方式来表达对数据库的查询，而无需考虑具体的实现细节。在...
数据库应用技术：SQL函数.pptx
03-24
数据库应用技术：SQL函数 数据库应用技术中，SQL函数是非常重要的一部分。SQL函数可以帮助我们更方便地操作和处理数据库中的数据。在本节中，我们将学习 Aggregate 计算函数和 Scalar 函数两种类型的 SQL 函数。 ...
数据库管理系统：ch02 Relational Model.ppt
06-09
【数据库管理系统：关系模型】 关系模型是数据库管理系统中的核心概念，它是现代数据库系统中最常见和广泛使用的数据模型。本章“Relational Model”主要探讨了关系模型的结构、基本操作以及扩展操作，还涉及到了...
shardingsphere 支持达梦数据库
jiejiegua的博客
03-29 7461
1、项目中分表分库插件用的是 shardingsphere，现在需要从mysql 数据转移到达梦数据库。 2、但是shardingsphere并不支持达梦数据库，所以查看了源码。基于shardingsphere（4.1.1版本）源码优化了支持达梦数据库大部分sql查询能力，（复合查询需要自己拆解）。 1、第一步 首先找到 DataSourceMetaData 接口实现该接口 这里需要注意java 包路径 org.apache.shardingsphere.underlying.common.
数据库中间件 Sharding-JDBC 源码分析 —— 结果归并
weixin_34232744的博客
10-29 211
摘要: 原创出处 www.iocoder.cn/Sharding-JD… 「芋道源码」欢迎转载，保留摘要，谢谢！ 本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. MergeEngine 2.1 SelectStatement#setIndexForItems() 2.2 ResultSetMerger 2.2.1 AbstractStreamResultSet...
shardingjdbc (七)-结果合并
bohu83的博客
06-10 4856
一 序          单分片的SQL查询不需要合并，多分片的情况在各分片排序完后，Sharding-JDBC 获取到结果后，仍然需要再进一步排序。目前有 分页、分组、排序、聚合列、迭代 五种场景需要做进一步处理。 public ResultSet executeQuery() throws SQLException { ResultSet result; ...
sharding 的遇到的相关坑
Angus
02-23 6179
org.springframework.dao.InvalidDataAccessApiUsageException: Can't find index: AggregationSelectItem 这个问题是用sharding ，要对聚合函数起一个别名。看官网给的解释： 第二个问题： io.shardingsphere.core.parsing.parser...
elasticsearch aggregation的入门案例
yfy的博客
12-22 304
1. 数据的准备 我们的数据结构为： https://github.com/elastic/elasticsearch/blob/master/docs/src/test/resources/accounts.json {"index":{"_id":"1"}} {"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes La
PostgreSQL aggregate function 2 : Aggregate Functions for Statistics
postgrechina的博客
10-14 575
前面一篇对PostgreSQL支持的一般聚合函数做了一些实例讲解,  http://blog.163.com/digoal@126/blog/static/16387704020152223539859/ 本文将讲解一下统计类的聚合函数. 在此前我分别以相关性函数corr和计算总体/样本 方差, 标准方差的函数variance, var_pop, var_samp, stddev
aws athena 建hive表命令不对，向表中插入数据时引发错误
finalcj的博客
03-10 2087
EMR集群，向hive表中插入数据时，引发错误：org.apache.spark.sql.AnalysisException: Can not create a Path from an empty string 网上搜索不到解决方案，最终偶然发现了问题所在，记录如下： 这是有问题的表，导出DDL是如下的： 实际我的建表命了，并没有指定任何参数，上面看到的参数是默认产生的： CREATE EXTERNAL TABLE `dwt_ob_video_all_user` ( .
数据库关键字详解：MySQL、Oracle与SQL Server对比
本文档是一份全面的数据库关键字一览表，主要针对MySQL、Oracle和SQL Server这三个主流数据库系统，帮助数据库设计师和开发者了解和避免在设计表结构时使用系统保留关键字。以下是详细的内容概览： 1. **数据库...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值