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: S0001 17/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: S0001 17/06/08 14:28:13 ERROR spi.SqlExceptionHelper: '@P0' 附近有语法错误。4、再次处理 修改hibernate方言
#hibernate.dialect=org.hibernate.dialect.SQLServerDialect hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect validationQuery=SELECT 1 driverClassName = com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc_url = jdbc\:sqlserver\://127.0.0.1\:1433;DatabaseName\=test jdbc_username = sa jdbc_password = 123456 hibernate.hbm2ddl.auto=none hibernate.show_sql=false hibernate.format_sql=false hibernate.use_sql_comments=true hibernate.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()设置从数据库取得多行的行数大小。
