PageHelper+sql server 2014

Question

当sql里面有with（nolock）和定义了order by（PageInfo pageInfo = fixtureService.getAllFixtureByPage(1, 10,"ChangedOn desc");）就会出现这个异常，单@Test测试是能通过的 数据也能准确返回，这个异常求解决

net.sf.jsqlparser.JSQLParserException
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:56)
at com.github.pagehelper.parser.OrderByParser.converToOrderBySql(OrderByParser.java:51)
at com.github.pagehelper.dialect.AbstractHelperDialect.getPageSql(AbstractHelperDialect.java:176)
at com.github.pagehelper.PageHelper.getPageSql(PageHelper.java:97)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:129)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy26.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy19.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy20.getAllFixture(Unknown Source)
at com.artesyn.reports.fixture.service.FixtureService.getAllFixtureByPage(FixtureService.java:24)
at com.artesyn.reports.fixture.service.FixtureService$$FastClassBySpringCGLIB$$f264224e.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
at com.artesyn.reports.fixture.service.FixtureService$$EnhancerBySpringCGLIB$$681a5e90.getAllFixtureByPage()
at TestMyBatis.getDataSource(TestMyBatis.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "WITH" "WITH "" at line 3, column 38.
Was expecting one of:

"AS" ...
"DO" ...
"ANY" ...
"KEY" ...
"PERCENT" ...
"END" ...
"JOIN" ...
"LEFT" ...
"CROSS" ...
"OPEN" ...
"FULL" ...
"TABLE" ...
"WHERE" ...
"FOR" ...
"PIVOT" ...
"XML" ...
"UNION" ...
"GROUP" ...
"INNER" ...
"ORDER" ...
"RIGHT" ...
"VALUE" ...
"HAVING" ...
"INSERT" ...
"VALUES" ...
"NATURAL" ...
"REPLACE" ...
"TRUNCATE" ...
"INTERSECT" ...
"CAST" ...
"EXCEPT" ...
"MINUS" ...
"OVER" ...
"PARTITION" ...
"EXTRACT" ...
"MATERIALIZED" ...
"START" ...
"CONNECT" ...
"PRIOR" ...
"SIBLINGS" ...
"COLUMN" ...
"NULLS" ...
"FIRST" ...
"LAST" ...
"ROWS" ...
"RANGE" ...
"FOLLOWING" ...
"ROW" ...
"COMMIT" ...
"SEPARATOR" ...
"CASCADE" ...
"NO" ...
"ACTION" ...
...
"PRECISION" ...
";" ...
...
...
"," ...
"ORDER" ...
"ORDER" ...

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:16869)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:16722)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:91)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:54)
... 62 more

Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered " "WITH" "WITH "" at line 3, column 38.
Was expecting one of:

Answer 1

解决办法： 自己重写一个自定义的Dialect

 /**
 * 分页查询，pageHelper转换SQL时报错with(nolock)不识别的问题，
 * 解决办法是自定义一个Dialect，继承SqlServerDialect，
 * 并且重写父类AbstractHelperDialect.getPageSql转换出错的方法。
 * 1. this.replaceSql.replace(sql);先转换成假的表名
 * 2. 然后进行SQL转换
 * 3. this.replaceSql.restore(sql);最后再恢复成真的with(nolock)
 * 需要配置以下属性，以指定到自定义的Dialect：
 * pagehelper.helperDialect = sqlservercustom
 * pagehelper.dialectAlias = sqlservercustom=com.m5173.integral.common.tkmybatis.SqlServerCustomDialect
 */
public class SqlServerCustomDialect extends SqlServerDialect {

    @Override
    public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
        String sql = boundSql.getSql();
        Page page = this.getLocalPage();
        String orderBy = page.getOrderBy();
        if (StringUtil.isNotEmpty(orderBy)) {
            pageKey.update(orderBy);
            sql = this.replaceSql.replace(sql);
            sql = OrderByParser.converToOrderBySql(sql, orderBy);
            sql = this.replaceSql.restore(sql);
        }

        return page.isOrderByOnly() ? sql : this.getPageSql(sql, page, pageKey);
    }
}

并且配置如下：

 pagehelper.helperDialect = sqlservercustom
pagehelper.dialectAlias = sqlservercustom=com.m5173.integral.common.tkmybatis.SqlServerCustomDialect

终极办法： 建议作者修改SqlServerDialect，并且重写父类AbstractHelperDialect.getPageSql方法如上。