Hibernate5 MSSql分页问题处理

1、前言
hibernate的分页查询，代码如下

/** * 分页查询(适用于Easyui分页) * 局限:排序仅支持单表 * @param hql HQL 如:'from MyTable o' * @param pageParams 分页参数 * @return 结果集 */ @Override @SuppressWarnings("unchecked") public List<Entity> queryByPage(String hql, PageParams pageParams) {        try {            if(StringUtils.notNull(pageParams.getOrder())&&StringUtils.notNull(pageParams.getSort())){                          hql += " order by o." + pageParams.getSort() + " " + pageParams.getOrder();            }            Query query = getQuery(hql);            query.setFirstResult((pageParams.getPage() - 1) * pageParams.getRows());            query.setMaxResults(pageParams.getRows());            return query.list();        } catch (Exception e) {            e.printStackTrace();            log.error("分页查询出现异常 hql={}",hql, e);            return null;        }    }

说明，hibernate版本为5，数据库为mssql。

2、问题描述

---

错误日志：

17/06/08 14:12:56 DEBUG spi.SqlExceptionHelper: could not extract ResultSet [n/a]com.microsoft.sqlserver.jdbc.SQLServerException: '@P0' 附近有语法错误。    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2714)    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711)    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:145)    at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)    at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)    at org.hibernate.loader.Loader.doQuery(Loader.java:919)    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)    at org.hibernate.loader.Loader.doList(Loader.java:2617)    at org.hibernate.loader.Loader.doList(Loader.java:2600)    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)    at org.hibernate.loader.Loader.list(Loader.java:2424)    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)    at com.hcepms.base.dao.BaseDaoImpl.queryByPage(BaseDaoImpl.java:131)    at com.hcepms.system.service.impl.OrganizationServiceImpl.queryOrganizationsByPage(OrganizationServiceImpl.java:66)    at com.hcepms.system.controller.OrganizationController.queryOrganizationsByPage(OrganizationController.java:70)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:498)    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:832)    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:743)    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961)    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895)    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869)    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)    at org.springframework.orm.hibernate5.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:151)    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:670)    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)    at java.lang.Thread.run(Thread.java:745)17/06/08 14:12:56 WARN spi.SqlExceptionHelper: SQL Error: 102, SQLState: S000117/06/08 14:12:56 ERROR spi.SqlExceptionHelper: '@P0' 附近有语法错误。

---

3、问题解决方法

query.setMaxResults(pageParams.getRows());

改为：

query.setFetchSize(pageParams.getRows());

修改后，数据可以正确加载。但后台还是报错。
日志：

17/06/08 14:28:13 WARN spi.SqlExceptionHelper: SQL Error: 102, SQLState: S000117/06/08 14:28:13 ERROR spi.SqlExceptionHelper: '@P0' 附近有语法错误。

4、再次处理
修改hibernate方言

#hibernate.dialect=org.hibernate.dialect.SQLServerDialecthibernate.dialect=org.hibernate.dialect.SQLServer2008DialectvalidationQuery=SELECT 1driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriverjdbc_url = jdbc\:sqlserver\://127.0.0.1\:1433;DatabaseName\=testjdbc_username = sajdbc_password = 123456hibernate.hbm2ddl.auto=nonehibernate.show_sql=falsehibernate.format_sql=falsehibernate.use_sql_comments=truehibernate.connection.release_mode=on_close

将

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

改为

hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect

这次ok,数据正确加载，后台也不再报错。

5、query.setMaxResults与query.setFetchSize方法说明
query.setFetchSize方法：缺省时，驱动程序一次从查询里获取所有的结果。这样可能对于大的数据集来说是不方便的， 因此 JDBC 驱动提供了一个方法从一个数据库游标伤抽取少数几行的 ResultSet 的方法。
setMaxRows()：是设置Resultset最多返回的行数 。
setFetchSize()设置从数据库取得多行的行数大小。