当前使用版本(mybatis-plus:3.4.2 mybatis-plus-join-boot-starter:1.4.5)
数据库：SQLServer
异常描述：使用分页插件进行分页查询并去重。生成的SQL语句异常，DISTINCT 应拼接在 SELECT 关键字后，而不应该拼接在查询字段中（查看以下SQL3）。
Java 代码：
分页：
Page<SysUser> sysUserPage = sysUserDao.getBaseMapper()
.selectJoinPage(
new Page<SysUser>(pageable.getIndex(), pageable.getSize()).setOptimizeCountSql(false),
SysUser.class,
hrbpManagerStaffQueryWrapper()
.distinct()
.select(SysUser::getUserId, SysUser::getEmail, SysUser::getStaffDisName, SysUser::getNickName, SysUser::getDeptName, SysUser::getDeptCnName, SysUser::getJobTitle, SysUser::getJobCnTitle)
.eq(StringUtils.isNotBlank(condition.getStaffCode()), SysUser::getUserId, condition.getStaffCode())
.eq(StringUtils.isNotBlank(condition.getEmail()), SysUser::getEmail, condition.getEmail())
.eq(SysPmmHrbpMetrics::getStaffCode, loginUser.getUserId())
.eq(SysUser::getStatus, "0")
.ne(SysUser::getStaffGroup, "P")
);
查询通用条件：
private static MPJLambdaWrapper<SysUser> hrbpManagerStaffQueryWrapper() {
return new MPJLambdaWrapper<SysUser>().innerJoin(SysPmmHrbpMetrics.class, on -> on
.eq(SysUser::getFuncCode, SysPmmHrbpMetrics::getFuncCode)
.eq(SysUser::getDeptCode, SysPmmHrbpMetrics::getDeptCode)
.eq(SysUser::getTeamCode, SysPmmHrbpMetrics::getTeamCode)
.eq(SysUser::getRegionCode, SysPmmHrbpMetrics::getRegionCode)
.eq(SysUser::getLocationName, SysPmmHrbpMetrics::getLocationCode)
.eq(SysUser::getServiceLineCode, SysPmmHrbpMetrics::getServiceLineCode)
);
}
生成的SQL
生成的SQL1 - 正常：
SELECT DISTINCT
t.user_id ,
t.email ,
t.STAFF_DIS_NAME,
t.nick_name ,
t.DEPT_NAME ,
t.DEPT_CN_NAME ,
t.JOB_TITLE ,
t.JOB_CN_TITLE
FROM
sys_user t
INNER JOIN
sys_pmm_hrbp_metrics t1
ON
(
t.FUNC_CODE = t1.FUNC_CODE
AND t.DEPT_CODE = t1.DEPT_CODE
AND t.TEAM_CODE = t1.TEAM_CODE
AND t.REGION_CODE = t1.REGION_CODE
AND t.LOCATION_NAME = t1.LOCATION_CODE
AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE)
WHERE
t1.DEL_FLAG='0'
AND (
t1.STAFF_CODE = ?
AND t.status = ?
AND t.STAFF_GROUP <> ?)
生成的SQL2 - 正常：
SELECT
COUNT(*)
FROM
(
SELECT DISTINCT
t.user_id ,
t.email ,
t.STAFF_DIS_NAME,
t.nick_name ,
t.DEPT_NAME ,
t.DEPT_CN_NAME ,
t.JOB_TITLE ,
t.JOB_CN_TITLE
FROM
sys_user t
INNER JOIN
sys_pmm_hrbp_metrics t1
ON
(
t.FUNC_CODE = t1.FUNC_CODE
AND t.DEPT_CODE = t1.DEPT_CODE
AND t.TEAM_CODE = t1.TEAM_CODE
AND t.REGION_CODE = t1.REGION_CODE
AND t.LOCATION_NAME = t1.LOCATION_CODE
AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE)
WHERE
t1.DEL_FLAG = '0'
AND (
t1.STAFF_CODE = ?
AND t.status = ?
AND t.STAFF_GROUP <> ?)) TOTAL
生成的_SQL3 - 异常_：
WITH selectTemp AS
(
SELECT
TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__,
DISTINCT t.user_id ,
t.email ,
t.STAFF_DIS_NAME ,
t.nick_name ,
t.DEPT_NAME ,
t.DEPT_CN_NAME ,
t.JOB_TITLE ,
t.JOB_CN_TITLE
FROM
sys_user t
INNER JOIN
sys_pmm_hrbp_metrics t1
ON
(
t.FUNC_CODE = t1.FUNC_CODE
AND t.DEPT_CODE = t1.DEPT_CODE
AND t.TEAM_CODE = t1.TEAM_CODE
AND t.REGION_CODE = t1.REGION_CODE
AND t.LOCATION_NAME = t1.LOCATION_CODE
AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE)
WHERE
t1.DEL_FLAG='0'
AND (
t1.STAFF_CODE = ?
AND t.status = ?
AND t.STAFF_GROUP <> ?))
SELECT
*
FROM
selectTemp
WHERE
__row_number__ BETWEEN 1 AND 20
ORDER BY
__row_number__
报错信息：
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e433553] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1622937882 wrapping ConnectionID:1 ClientConnectionId: d031f14a-cf0b-4c20-984e-fcf89f04cfd6] will not be managed by Spring
JsqlParserCountOptimize sql=SELECT DISTINCT
t.user_id,t.email,t.STAFF_DIS_NAME,t.nick_name,t.DEPT_NAME,t.DEPT_CN_NAME,t.JOB_TITLE,t.JOB_CN_TITLE
FROM sys_user t INNER JOIN sys_pmm_hrbp_metrics t1 ON (t.FUNC_CODE = t1.FUNC_CODE AND t.DEPT_CODE = t1.DEPT_CODE AND t.TEAM_CODE = t1.TEAM_CODE AND t.REGION_CODE = t1.REGION_CODE AND t.LOCATION_NAME = t1.LOCATION_CODE AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE)
WHERE t1.DEL_FLAG='0'
AND
(t1.STAFF_CODE = ? AND t.status = ? AND t.STAFF_GROUP <> ?)
==> Preparing: SELECT COUNT(*) FROM (SELECT DISTINCT t.user_id, t.email, t.STAFF_DIS_NAME, t.nick_name, t.DEPT_NAME, t.DEPT_CN_NAME, t.JOB_TITLE, t.JOB_CN_TITLE FROM sys_user t INNER JOIN sys_pmm_hrbp_metrics t1 ON (t.FUNC_CODE = t1.FUNC_CODE AND t.DEPT_CODE = t1.DEPT_CODE AND t.TEAM_CODE = t1.TEAM_CODE AND t.REGION_CODE = t1.REGION_CODE AND t.LOCATION_NAME = t1.LOCATION_CODE AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE) WHERE t1.DEL_FLAG = '0' AND (t1.STAFF_CODE = ? AND t.status = ? AND t.STAFF_GROUP <> ?)) TOTAL
==> Parameters: 133331(Long), 0(String), P(String)
<== Columns:
<== Row: 26
==> Preparing: WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, DISTINCT t.user_id,t.email,t.STAFF_DIS_NAME,t.nick_name,t.DEPT_NAME,t.DEPT_CN_NAME,t.JOB_TITLE,t.JOB_CN_TITLE FROM sys_user t INNER JOIN sys_pmm_hrbp_metrics t1 ON (t.FUNC_CODE = t1.FUNC_CODE AND t.DEPT_CODE = t1.DEPT_CODE AND t.TEAM_CODE = t1.TEAM_CODE AND t.REGION_CODE = t1.REGION_CODE AND t.LOCATION_NAME = t1.LOCATION_CODE AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE) WHERE t1.DEL_FLAG='0' AND (t1.STAFF_CODE = ? AND t.status = ? AND t.STAFF_GROUP <> ?)) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 20 ORDER BY __row_number__
==> Parameters: 133331(Long), 0(String), P(String)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e433553]
2023-06-09 15:38:04.315 ERROR 17076 --- [nio-8080-exec-1] c.d.o.exception.GlobalExceptionHandler : 全局异常信息 ex=
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'DISTINCT'.
### The error may exist in com/deloitte/osp/system/mapper/SysUserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, DISTINCT t.user_id,t.email,t.STAFF_DIS_NAME,t.nick_name,t.DEPT_NAME,t.DEPT_CN_NAME,t.JOB_TITLE,t.JOB_CN_TITLE FROM sys_user t INNER JOIN sys_pmm_hrbp_metrics t1 ON (t.FUNC_CODE = t1.FUNC_CODE AND t.DEPT_CODE = t1.DEPT_CODE AND t.TEAM_CODE = t1.TEAM_CODE AND t.REGION_CODE = t1.REGION_CODE AND t.LOCATION_NAME = t1.LOCATION_CODE AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE) WHERE t1.DEL_FLAG='0' AND (t1.STAFF_CODE = ? AND t.status = ? AND t.STAFF_GROUP <> ?)) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 20 ORDER BY __row_number__
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'DISTINCT'.
; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'DISTINCT'.
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'DISTINCT'.
### The error may exist in com/deloitte/osp/system/mapper/SysUserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, DISTINCT t.user_id,t.email,t.STAFF_DIS_NAME,t.nick_name,t.DEPT_NAME,t.DEPT_CN_NAME,t.JOB_TITLE,t.JOB_CN_TITLE FROM sys_user t INNER JOIN sys_pmm_hrbp_metrics t1 ON (t.FUNC_CODE = t1.FUNC_CODE AND t.DEPT_CODE = t1.DEPT_CODE AND t.TEAM_CODE = t1.TEAM_CODE AND t.REGION_CODE = t1.REGION_CODE AND t.LOCATION_NAME = t1.LOCATION_CODE AND t.SERVICE_LINE_CODE = t1.SERVICE_LINE_CODE) WHERE t1.DEL_FLAG='0' AND (t1.STAFF_CODE = ? AND t.status = ? AND t.STAFF_GROUP <> ?)) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 20 ORDER BY __row_number__
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'DISTINCT'.
; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'DISTINCT'.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
at com.sun.proxy.$Proxy150.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:122)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:86)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy172.selectJoinPage(Unknown Source)
at com.deloitte.osp.system.service.impl.SysUserServiceImpl.doFindByHrbpRole(SysUserServiceImpl.java:309)
at com.deloitte.osp.system.service.impl.SysUserServiceImpl.findByHrbpRole(SysUserServiceImpl.java:287)
at com.deloitte.osp.system.SystemUserController.findByHrbpRole(SystemUserController.java:58)
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.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:665)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at com.deloitte.framework.xss.XssFilter.doFilter(XssFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at com.deloitte.aad.oauth.filter.AuthFilter.doFilter(AuthFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at com.deloitte.aad.oauth.filter.RequestFilter.doFilter(RequestFilter.java:26)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
Comment From: hesixian123
已解决：在 Page 中添加排序条件，会触发 TOP 关键字和 distinct 关键字拼接顺序正常。
Page<SysUser> sysUserPage = sysUserDao.getBaseMapper()
.selectJoinPage(
new Page<SysUser>(pageable.getIndex(), pageable.getSize()).addOrder(OrderItem.asc("user_id")),
SysUser.class,
new MPJLambdaWrapper<SysUser>().innerJoin(SysPmmHrbpMetrics.class, on -> on
.eq(SysUser::getFuncCode, SysPmmHrbpMetrics::getFuncCode)
.eq(SysUser::getDeptCode, SysPmmHrbpMetrics::getDeptCode)
.eq(SysUser::getTeamCode, SysPmmHrbpMetrics::getTeamCode)
.eq(SysUser::getRegionCode, SysPmmHrbpMetrics::getRegionCode)
.eq(SysUser::getLocationName, SysPmmHrbpMetrics::getLocationCode)
.eq(SysUser::getServiceLineCode, SysPmmHrbpMetrics::getServiceLineCode)
)
.distinct()
.select(SysUser::getUserId, SysUser::getEmail, SysUser::getStaffDisName, SysUser::getNickName, SysUser::getDeptName, SysUser::getDeptCnName, SysUser::getJobTitle, SysUser::getJobCnTitle)
.eq(StringUtils.isNotBlank(condition.getStaffCode()), SysUser::getUserId, condition.getStaffCode())
.eq(StringUtils.isNotBlank(condition.getEmail()), SysUser::getEmail, condition.getEmail())
.eq(SysPmmHrbpMetrics::getStaffCode, loginUser.getUserId())
.eq(SysUser::getStatus, "0")
.ne(SysUser::getStaffGroup, "P")
);