为什么我的 ORDER BY create_time ASC 变成了 order by ASC(PageHelper的一些坑)
1.场景
在开发的过程中遇到了一件诡异的事,两条一毛一样的sql为啥分页的sql执行没问题,不分页的sql执行就有问题。
1-1.场景准备
测试场景用到的表结构以及数据。
CREATE TABLE `test_page_or_not` (
`id` bigint(20) NOT NULL COMMENT '主键编号',
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
`create_time` datetime(3) NOT NULL COMMENT '创建时间',
`create_by` bigint(20) NOT NULL COMMENT '创建人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='测试分页与不分页';
INSERT INTO `test_page_or_not` (`id`, `name`, `create_time`, `create_by`) VALUES (1, 'name1', '2022-02-17 22:25:04.000', 0);
INSERT INTO `test_page_or_not` (`id`, `name`, `create_time`, `create_by`) VALUES (2, 'name2', '2022-02-17 22:25:04.000', 0);
INSERT INTO `test_page_or_not` (`id`, `name`, `create_time`, `create_by`) VALUES (3, 'name3', '2022-02-18 22:25:04.000', 0);
INSERT INTO `test_page_or_not` (`id`, `name`, `create_time`, `create_by`) VALUES (4, 'name4', '2022-02-19 22:25:04.000', 0);
一些Java类文件。
public class TestWithPageDTO extends PageDTO {
private static final long serialVersionUID = -970059691509424681L;
private String name;
private String orderBy;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
@Override
public String toString() {
return "TestWithPageDTO{" +
"name='" + name + '\'' +
", orderBy='" + orderBy + '\'' +
'}';
}
}
public class PageDTO extends BaseDTO {
private static final long serialVersionUID = 2572899663737669356L;
private Integer pageSize = 10;
private Integer pageNum = 1;
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getPageNum() {
return pageNum;
}
public void setPageNum(Integer pageNum) {
this.pageNum = pageNum;
}
@Override
public String toString() {
return "PageDTO{" +
"pageSize=" + pageSize +
", pageNum=" + pageNum +
'}';
}
}
public class BaseDTO implements Serializable {
private static final long serialVersionUID = 9055050419546393543L;
}
public class TestWithoutPageDTO extends BaseDTO {
private static final long serialVersionUID = 7862152821778815456L;
private String name;
private String orderBy;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
@Override
public String toString() {
return "TestWithoutPageDTO{" +
"name='" + name + '\'' +
", orderBy='" + orderBy + '\'' +
'}';
}
}
1-2.分页场景
先来看看分页场景的参数、代码和日志打印,从日志可以看出SQL的最后执行是没问题的。
请求参数:
{
"name": "name",
"orderBy": "ASC"
}
sql文件:
<!--分页查询数据-->
<select id="selectWithPage" parameterType="com.peng.java_study.api.dto.TestWithPageDTO" resultMap="BaseResultMap">
SELECT * FROM test_page_or_not tpon
WHERE
tpon.name LIKE CONCAT("%", #{name, jdbcType=VARCHAR}, "%")
<if test="orderBy != null and orderBy != ''">
ORDER BY tpon.create_time ${orderBy}
</if>
</select>
日志:
2022-02-18 16:39:09.217 DEBUG 18036 --- [nio-8080-exec-3] c.p.j.c.m.T.selectWithPage_COUNT : ==> Preparing: SELECT count(0) FROM test_page_or_not tpon WHERE tpon.name LIKE CONCAT("%", ?, "%")
2022-02-18 16:39:09.218 DEBUG 18036 --- [nio-8080-exec-3] c.p.j.c.m.T.selectWithPage_COUNT : ==> Parameters: name(String)
2022-02-18 16:39:09.234 DEBUG 18036 --- [nio-8080-exec-3] c.p.j.c.m.T.selectWithPage_COUNT : <== Total: 1
2022-02-18 16:39:09.236 DEBUG 18036 --- [nio-8080-exec-3] c.p.j.c.m.T.selectWithPage : ==> Preparing: SELECT * FROM test_page_or_not tpon WHERE tpon.name LIKE CONCAT("%", ?, "%") ORDER BY tpon.create_time ASC LIMIT ?
2022-02-18 16:39:09.236 DEBUG 18036 --- [nio-8080-exec-3] c.p.j.c.m.T.selectWithPage : ==> Parameters: name(String), 10(Integer)
2022-02-18 16:39:09.238 DEBUG 18036 --- [nio-8080-exec-3] c.p.j.c.m.T.selectWithPage : <== Total: 4
1-3.不分页场景
再来看看正常场景的参数、代码和日志打印,从日志可以看出SQL的执行报错了,因为SQL从 ORDER BY tpon.create_time ASC 变成了 order by ASC。
请求参数:
{
"name": "name",
"orderBy": "ASC"
}
sql文件:
<!--不分页查询数据-->
<select id="selectWithoutPage" parameterType="com.peng.java_study.api.dto.TestWithoutPageDTO" resultMap="BaseResultMap">
SELECT * FROM test_page_or_not tpon
WHERE
tpon.name LIKE CONCAT("%", #{name, jdbcType=VARCHAR}, "%")
<if test="orderBy != null and orderBy != ''">
ORDER BY tpon.create_time ${orderBy}
</if>
</select>
日志:
2022-02-18 16:52:24.684 DEBUG 18036 --- [nio-8080-exec-5] c.p.j.c.m.T.selectWithoutPage : ==> Preparing: SELECT * FROM test_page_or_not tpon WHERE tpon.name LIKE CONCAT("%", ?, "%") order by ASC
2022-02-18 16:52:24.684 DEBUG 18036 --- [nio-8080-exec-5] c.p.j.c.m.T.selectWithoutPage : ==> Parameters: name(String)
2022-02-18 16:52:24.687 ERROR 18036 --- [nio-8080-exec-5] c.p.java_study.rest.test.TestController : TestController.list(TestWithoutPageDTO) exception:
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1
### The error may exist in file [D:\file\project\1\java-study\java-study-provider\target\classes\mapper\TestPageOrNotMapper.xml]
### The error may involve com.peng.java_study.core.mapper.TestPageOrNotMapper.selectWithoutPage-Inline
### The error occurred while setting parameters
### SQL: SELECT * FROM test_page_or_not tpon WHERE tpon.name LIKE CONCAT("%", ?, "%") order by ASC
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) ~[mybatis-spring-2.0.6.jar:2.0.6]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.6.jar:2.0.6]
at com.sun.proxy.$Proxy92.selectList(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.6.jar:2.0.6]
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) ~[mybatis-3.5.6.jar:3.5.6]
at com.sun.proxy.$Proxy93.selectWithoutPage(Unknown Source) ~[na:na]
at com.peng.java_study.core.manager.impl.TestPageOrNotManagerImpl.selectWithoutPage(TestPageOrNotManagerImpl.java:36) ~[classes/:na]
at com.peng.java_study.rest.test.TestController.list(TestController.java:150) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at com.peng.java_study.config.LanguageFilter.doFilter(LanguageFilter.java:39) ~[classes/:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.31.jar:9.0.31]
at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.23.jar:8.0.23]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.23.jar:8.0.23]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.23.jar:8.0.23]
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.23.jar:8.0.23]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.4.2.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.6.jar:3.5.6]
at com.sun.proxy.$Proxy132.execute(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.6.jar:3.5.6]
at com.github.pagehelper.util.ExecutorUtil.pageQuery(ExecutorUtil.java:177) ~[pagehelper-5.1.11.jar:na]
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:104) ~[pagehelper-5.1.11.jar:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar:3.5.6]
at com.sun.proxy.$Proxy130.query(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.6.jar:3.5.6]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.6.jar:3.5.6]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
... 62 common frames omitted
2.分析
接下来仔细分析下为啥会出现这种情况。
2-1.分析两场景的区别
这两个sql的语句和入参已经是一毛一样的,唯一的区别在于一个使用了分页,另一个没有使用分页。因此我们来仔细分析下报错日志,看看为啥sql被修改了。
2-2.分析具体的代码调用链
在日志的99行,我们发现有一个PageHelper的调用栈。
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:104) ~[pagehelper-5.1.11.jar:na]
进入该行代码,发现它进入了分页查询,明明我们使用的对象是 TestWithoutPageDTO.class,调用的sql也是也是 selectWithoutPage,为啥还是会进入分页查询呢?让我们来一探究竟。
看下图的1号红色圈,判断当前sql是否需要进行分页,本应该走else的分支(不分页,3号绿色圈)的,但最后进了if分支(分页,2号红色圈)。
继续进入skip方法看看,按照该代码的逻辑,4号红色圈的page应该为null,才不会分页。既然分页了,那就说明page不为null。
继续进入getPage方法,经过调试发现,代码进入了5号红色圈位置,这说明,要么参数对象实现了IPage接口,要么supportMethodsArguments参数为true。从上面sql中我们可以知道参数是
TestWithoutPageDTO.class的实例,它的类继承图如下,可以看出,该类并没有继承或实现IPage接口。因此肯定是supportMethodsArguments参数为true。
进入PageHelper官网(https://pagehelper.github.io/docs/howtouse/),查看supportMethodsArguments参数介绍,它可以根据参数对象中的属性来自动识别是否需要分页。然后再查看当前项目的配置,发现当前项目确实配置了该参数,既然它确实可以简化一些操作,那这个属性还不能改动。
supportMethodsArguments:支持通过 Mapper 接口参数来传递分页参数,默认值false,分页插件会从查询方法的参数值中,自动根据上面 params 配置的字段中取值,查找到合适的值时就会自动分页。 使用方法可以参考测试代码中的 com.github.pagehelper.test.basic 包下的 ArgumentsMapTest 和 ArgumentsObjTest。
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
那就继续进入5号红色圈内部的getPageFromObject方法,该方法比较长,分两个页面截图左边的106行对应右边的106行。该方法中有6~12号红色圈的内容可以返回。然后重点看一下13号黄色圈,这个圈使用反射的方式去获取参数对象的orderBy属性。很明显,我们的为了排序在实体中增加了orderBy属性,因此hasOrderBy肯定为true。然后肯定会进入9号红色圈的代码块,创建了一个分页的对象,这就导致后面PageHelper插件对该sql语句作为分页查询来对待,然后就把我们的 ORDER BY create_time ASC 语句偷梁换柱了。
至于后面的偷梁换柱是如何做的,就不用细看了。
3.解决
3-1.解决方案1
将supportMethodsArguments属性改为false,但这个改动很有可能会对现有的sql查询语句造成一定的影响,因此不推荐。
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: false
params: count=countSql
3-2.解决方案2
将实体类中的orderBy改为另一个变量名,比如orderByCreateTime,这样就可以避免PageHelper认错分页参数了。
public class TestWithoutPageDTO extends BaseDTO {
private static final long serialVersionUID = 7862152821778815456L;
private String name;
private String orderByCreateTime;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getOrderByCreateTime() {
return orderByCreateTime;
}
public void setOrderByCreateTime(String orderByCreateTime) {
this.orderByCreateTime = orderByCreateTime;
}
@Override
public String toString() {
return "TestWithoutPageDTO{" +
"name='" + name + '\'' +
", orderByCreateTime='" + orderByCreateTime + '\'' +
'}';
}
}
<!--不分页查询数据-->
<select id="selectWithoutPage" parameterType="com.peng.java_study.api.dto.TestWithoutPageDTO" resultMap="BaseResultMap">
SELECT * FROM test_page_or_not tpon
WHERE
tpon.name LIKE CONCAT("%", #{name, jdbcType=VARCHAR}, "%")
<if test="orderByCreateTime != null and orderByCreateTime != ''">
ORDER BY tpon.create_time ${orderByCreateTime}
</if>
</select>
修改后的日志,无异常产生:
2022-02-18 21:00:58.928 DEBUG 9768 --- [nio-8080-exec-1] c.p.j.c.m.T.selectWithoutPage : ==> Preparing: SELECT * FROM test_page_or_not tpon WHERE tpon.name LIKE CONCAT("%", ?, "%") ORDER BY tpon.create_time ASC
2022-02-18 21:00:58.944 DEBUG 9768 --- [nio-8080-exec-1] c.p.j.c.m.T.selectWithoutPage : ==> Parameters: name(String)
2022-02-18 21:00:58.960 DEBUG 9768 --- [nio-8080-exec-1] c.p.j.c.m.T.selectWithoutPage : <== Total: 4