当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:
解决办法： 自己重写一个自定义的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方法如上。
你这样写解决了with(nolock)问题，但是又把分页方式按照sqlserver2005的方式进行了，能不能保留分页方式使用sqlserver2012那种fetch....next。。。的方式啊
如下配置dialct 属性试试
--------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
--- 省略
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="dialect" value="com.github.pagehelper.dialect.helper.SqlServerDialect"/>
</plugin>
</plugins>
</configuration>