As of 4.x Hibernate would allow queries to specify offset even if the underlying database had no way to support offset. How it accomplished that is a very suboptimal (from a perf spective) solution in which all rows up to the requested limit are returned in the JDBC ResultSet and then in memory we would advance the ResultSet to the requested offset.
SQL Server 2000 (SQLServerDialect) is one such database.
Add In 5.x we changed this to instead throw an exception, thereby circumventing the poor performance. In the interest of users upgrading, we should allow them to re-enable the older behavior. To this end we will add a compatibility setting (defaulting to false) that allows such users to re-enable the legacy behavior.
NOTE : that we have zero plans to "improve" the way this is handled - just allow
users to opt-in to the older behavior. At some point we will consider removing this setting as well. No modern versions of these DBs we support (that we know of) lack support for limit/offset.
As a work-around you could certainly supply a custom Dialect for the purpose of returning a "better" LimitHandler - specifically one that defines LimitHandler#supportsLimitOffset as false for SQL Server 2000. That should give you the same behavior.
----
h2. Original descripton
----
In a legacy project I have to connect to a MS SQL Server 2000. This worked well with the latest 4.x Hibernate using SQLServerDialect & jtds JDBC Driver.
Test-Case: https://github.com/ElderByte-/hibernate-setFirstResult-mssql
However, now I upgraded from Hibernate 4.x to Hibernate 5.x and pagination (EntityManager.setFirstResult() -> query.setFirstResult()) is no longer supported. Hibernate now throws the exception below.
{code}
[2016-10-21T18:34:23.732075193Z] java.lang.UnsupportedOperationException: query result offset is not supported
[2016-10-21T18:34:23.732141234Z] at org.hibernate.dialect.pagination.TopLimitHandler.processSql(TopLimitHandler.java:49) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732186954Z] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1904) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732271373Z] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732283992Z] at org.hibernate.loader.Loader.doQuery(Loader.java:932) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732373101Z] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732385606Z] at org.hibernate.loader.Loader.doList(Loader.java:2615) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732428326Z] at org.hibernate.loader.Loader.doList(Loader.java:2598) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732485046Z] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732496084Z] at org.hibernate.loader.Loader.list(Loader.java:2425) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732585796Z] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732596992Z] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732641665Z] at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732711384Z] at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1460) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732728878Z] at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732786867Z] at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.73283995Z] at org.hibernate.Query.getResultList(Query.java:417) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732853434Z] at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final]
[2016-10-21T18:34:23.732892907Z] at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:586) ~[spring-data-jpa-1.10.3.RELEASE.jar!/:na]
[2016-10-21T18:34:23.732933276Z] at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:478) ~[spring-data-jpa-1.10.3.RELEASE.jar!/:na]
{code}
|
|