Hibernate5 MSSql分页问题处理

最新推荐文章于 2021-06-16 14:24:51 发布

蔚蓝色天空sky

最新推荐文章于 2021-06-16 14:24:51 发布

阅读量1.9k

点赞数

分类专栏： hibernate 文章标签： hibernate setMaxRows 分页

本文链接：https://blog.csdn.net/ytangdigl/article/details/72917117

版权

hibernate 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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()设置从数据库取得多行的行数大小。

蔚蓝色天空sky

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
Hibernate5 MSSql分页问题处理

1、前言 hibernate的分页查询，代码如下/** * 分页查询(适用于Easyui分页) * 局限:排序仅支持单表 * @param hql HQL 如:'from MyTable o' * @param pageParams 分页参数 * @return 结果集 */ @Override @SuppressWarnings("unchecked") public List
复制链接

扫一扫