分页异常:Mybatis"/>
PaginationInterceptor分页异常:Mybatis
记一次开发过程中Mybatis Plus PaginationInterceptor分页功能异常,找了很多博客,都没有分析为什么导致异常,大多数是自定义count查询,这样要多写很多代码,本篇文章对异常进行了源码分析,定位到了问题所在,有用请点赞支持!!!
先说明结论
当数据源是SQLServer & 使用PaginationInterceptor分页插件 & 使用order by时三种情况并存的情况,如果SQL中出现with(nolock),会导致SQL无法被分页插件解析,从而出现在执行count查询时报错。
异常如下:
com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
异常复现:
PaginationInterceptor存在分页异常bug:当我们数据源是SQLServer时,因为SQLServer在增删改操作时是表锁,SQLServer会阻止脏读,导致查询效率变低,所以通常我们写SQL查询的时候会加上with(nolock),允许查询语句脏读,从而提升查询效率,正常写SQL是没有问题的,但是在使用PaginationInterceptor分页插件分页查询 & 使用order by时,如下:
<select id="selectProductOrderReport" resultType="com.topsun.centric.model.vo.ProductOrderReportVO">select mx.ddbh, mx.ddxh, dd.khbh, mx.dhsl as xsl, cast(mx.wbdj * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsdj, cast(mx.wbje * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsje, mx.lrl, mx.lrlnew, '美元' as bzmc, mx.qfsj, case when month(mx.qfsj) >= 7 then (year(mx.qfsj) + 1) else year(mx.qfsj) end as ddjyjfrom ddmxb as mx with(nolock)left join xsddb as dd with(nolock) on dd.ddbh = mx.ddbhwhere mx.qfsj > '2019-07-01' and mx.qfbj = 1order by dd.ddbh desc
</select>
会报如下异常:
2023-03-13 09:07:35.735 ERROR 11728 [1084764685762138112] druid.sql.Statement : {conn-10001, pstmt-20001} execute error. SELECT COUNT(1) FROM ( select mx.ddbh, mx.ddxh, dd.khbh, mx.dhsl as xsl, cast(mx.wbdj * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsdj, cast(mx.wbje * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsje, mx.lrl, mx.lrlnew, '美元' as bzmc, mx.qfsj, case when month(mx.qfsj) >= 7 then (year(mx.qfsj) + 1) else year(mx.qfsj) end as ddjyjfrom ddmxb as mx with(nolock)left join xsddb as dd with(nolock) on dd.ddbh = mx.ddbhwhere mx.qfsj > '2019-07-01' and mx.qfbj = 1order by dd.ddbh desc ) TOTAL
com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3240)at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)......
可以看到在执行count查询的时候就已经报错,执行的SQL如下:
SELECT COUNT(1)FROM (SELECT mx.ddbh, mx.ddxh, dd.khbh, mx.dhsl as xsl , cast(mx.wbdj * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsdj , cast(mx.wbje * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsje , mx.lrl, mx.lrlnew, '美元' as bzmc, mx.qfsj , case when month(mx.qfsj) >= 7 then (year(mx.qfsj) + 1) else year(mx.qfsj) end as ddjyjFROM ddmxb as mx with(nolock)LEFT JOIN xsddb as dd with(nolock) ON dd.ddbh = mx.ddbhWHERE mx.qfsj > '2019-07-01' and mx.qfbj = 1 order by dd.ddbh desc ) TOTAL;
这种语法在SQLServer中执行是不通过的,可以看到其SQL并不是select count(1) from … order by格式,而是把原来的SQL整体括了起来,正常的应该是如下:
SELECT COUNT(1)
FROM ddmxb as mx with(nolock)
LEFT JOIN xsddb as dd with(nolock) ON dd.ddbh = mx.ddbh
WHERE mx.qfsj > '2019-07-01' and mx.qfbj = 1;
异常排查:
所以就需要排查为什么没有解析并替换掉原SQL,这里分析过程就不再给了,篇幅太长,直接给出定位问题的位置和结果:
首先问题肯定出现在PaginationInterceptor分页插件中,我通过对分页插件打断点一行行排查,在PaginationInterceptor类下的intercept()方法下的SqlParserUtils.getOptimizeCountSql()方法下的COUNT_SQL_PARSER.parser()方法发现SQL解析结果就是以上的错误SQL:
然后进入到parser实现方法,找到JsqlParserCountOptimize类下的parser()方法,发现在执行(Select) CCJSqlParserUtil.parse(sql)时报异常,被try catch捕获,然后在catch中提示直接使用原SQL
所以可以确定问题就是出在了这里,既然无法解析原SQL,就说明原SQL有不能被解析的单词,然后我去掉了一些SQL代码后不断重试,最终发现在去掉with(nolock)后,SQL解析正常。所以问题可以确定就是:在使用SQLServer数据源的时候,with(nolock)会导致SQL解析异常,count()查询解析结果会是一个错误的SQL。
解决方法:
- 原SQL中不要出现with(nolock)(不建议使用:表锁的情况下会导致查询等待,使效率变低)
- 使用PageHelper分页插件,弃用PaginationInterceptor,(可以使用:分页插件其实就是起到一个解析并拼接SQL的作用,哪个好用就用哪个)
- PageHelper + PaginationInterceptor(推荐:在SQLServer数据源时使用PageHelper插件,其他数据源使用PaginationInterceptor插件,因为PaginationInterceptor可以少写一行代码)
总结:
当数据源是SQLServer & 使用PaginationInterceptor分页插件 & 使用order by时三种情况并存的情况,如果SQL中出现with(nolock),会导致SQL无法被分页插件解析,从而出现在执行count查询时报错。
更多推荐
PaginationInterceptor分页异常:Mybatis
发布评论