关于javascript:为什么我的-ORDER-BY-createtime-ASC-变成了-order-by-ASC

32次阅读

共计 19730 个字符，预计需要花费 50 分钟才能阅读完成。

开发是一件“降级打怪”的事，在开发各个环境下，妖怪频出，战术一直降级。程序员想要抛开迷雾，窥得假相，须得先参透个中招式，而后逐个解决。

本期文章的内容“就地取材”，起源自 LigaAI 工作中常见的开发问题，并通过「场景测试—剖析区别—提出解决方案」的办法帮咱们躲避和尽早辨认这种陷阱~ 话不多说，一起来看看吧！

遇到的问题：

“两条一毛一样的 sql，分页的 sql 执行没问题，不分页的 sql 执行就有问题？”

01、场景筹备

>> 测试场景 

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 + '\'' +
                '}';
    }
}

>> 分页场景

先来看看分页场景的参数、代码和日志打印，从日志能够看出 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

>> 不分页场景

再来看看失常场景的参数、代码和日志打印：从日志能够看出 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

02、剖析

两个 sql 的语句和入参是一毛一样的，惟一的区别在于一个应用了分页，另一个没有应用分页。因而咱们来仔细分析下报错日志，查找 sql 被批改的起因。

>> 剖析具体的代码调用链

在日志的 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 官网，查看 supportMethodsArguments 参数介绍：依据参数对象中的属性自动识别是否须要分页。

再查看以后我的项目的配置，发现以后我的项目的确配置了该参数，既然它的确能够简化一些操作，那这个属性还不能改变。


supportMethodsArguments：反对通过 Mapper 接口参数来传递分页参数，默认值 false，分页插件会从查询方法的参数值中，主动依据下面 params 配置的字段中取值，查找到适合的值时就会主动分页。应用办法能够参考测试代码中的 com.github.pagehelper.test.basic 包下的 ArgumentsMapTest 和 ArgumentsObjTest。
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

持续进入 5 号红色圈外部的 getPageFromObject 办法，重点看一下 13 号黄色圈，这个圈应用反射的形式去获取参数对象的 orderBy 属性。

很快，问题找到了：

笔者为了排序在实体中减少了 orderBy 属性，因而 hasOrderBy 为 true；进入 9 号红色圈的代码块后，创立了分页的对象，导致 PageHelper 插件把该 sql 语句作为分页查问来看待，天然也就把 ORDER BY create_time ASC 语句移花接木了。

找出问题所在，接下来就是解决了。

03、解决

>> 计划一：

将 supportMethodsArguments 属性改为 false，但这个改变很有可能会对现有的 sql 查问语句造成肯定的影响，因而不举荐。

pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: false
  params: count=countSql

>> 计划二：

将实体类中的 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

LigaAI 新一代智能研发合作平台 让 AI 为您的研发团队提供个性化、智能化的我的项目合作体验，化繁就简，帮忙开发者专一、高效的创作！LigaAI 会继续分享更多与麻利开发、项目管理相干的文章~

本文作者：rookie0peng

正文完
javascript
发表至： javascript
2022-02-28
 0
关于javascript:Vue30升级以及部分新特性
原型链
关于javascript:js高手必知api-使用数组方法reduce简化代码
2019-前端秋季社招面试经历总结二年多经验