Hibernate（/ JPA）服务器端分页和MS SQL Server [英] Hibernate (/JPA) server-side paging and MS SQL Server

问题描述

使用 hibernate / JPA实现服务器端数据分页，底层数据库为 MS SQL Server 2008。

SQL是这样生成的：

  criteria.setFirstResult（pagingParams.getDisplayStart（ ））
 .setMaxResults（pagingParams.getDisplayLength（））; 
  

（主要工作在于创建适当的过滤器/排序，但这与此无关） p>

我在观察的是以下SQL：

  page（0 -20）：
 select top 20 this_.id as id11_9 _，... 
 
 page（20-40）：
 select top 40 this_.id as id11_9 _，.. 。
 
 page（40-60）：
 select top 60 this_.id as id11_9 _，... 
  

...等等。 （b）显然，如果底层结果集太大而且（b）与分页关系不大，那么（a）会遇到严重问题： - （

有同样问题的任何人？

更新：好像NHibernate （Hibernate的.NET实现）利用 Row_Number（） T-SQL的函数。可惜Hibernate没有...

解决方案

有一点迟到的反应，但它可能是有用的，所以我会发布它。有完全相同的问题，并且头痛地跟踪它。解决方案是使用 org.hibernate.dialect.SQLServer2012Dialect 它包含在Hibernate 4.3.0中，生成的查询变成了（粘贴真正的Hibernate转储，没有列名和别名）：

  WITH查询
 AS（SELECT inner_query。*，
 Row_numb er（）
 OVER（
 ORDER BY CURRENT_TIMESTAMP）AS __hibernate_row_nr__ 
 FROM（SELECT TOP（？）< COLUMN_NAMES> AS< ALIASES> 
 FROM< TABLE_NAME> 
）inner_query）
 SELECT< ALIASES> 
 FROM查询
 WHERE __hibernate_row_nr__> =？ 
 AND __hibernate_row_nr__< ？ 
  

注意内部查询的用法和 Row_number（）


I implementing a server-side data paging with hibernate / JPA, the underlying database is MS SQL Server 2008.

The SQL is generated like this:

criteria.setFirstResult(pagingParams.getDisplayStart())
.setMaxResults(pagingParams.getDisplayLength());

(The main work lays in creating the appropriate filter / sorting, but that's not relevant here)

What I'm observing is the following SQL:

page (0-20):
select top 20 this_.id as id11_9_,...

page (20-40):
select top 40 this_.id as id11_9_,... 

page (40-60):
select top 60 this_.id as id11_9_,... 

... and so on.

Obviously this (a) will run into serious issues if the underlying resultset gets too big and (b) hasn't much to do with paging at all :-(

Anyone who had the same issue?

Update: It seems as if NHibernate (the .NET implementation of Hibernate) takes advantage of the Row_Number() function of T-SQL. Pity that Hibernate doesn't...

解决方案

A little bit late response, but it can be helpful so I will post it. Had the exactly same problem and a headache to track it down. Solution is to use org.hibernate.dialect.SQLServer2012Dialect which is included in Hibernate 4.3.0. Generated query becomes (pasting real Hibernate dump without column names and aliases):

WITH query 
     AS (SELECT inner_query.*, 
                Row_number() 
                  OVER ( 
                    ORDER BY CURRENT_TIMESTAMP) AS __hibernate_row_nr__ 
         FROM   (SELECT TOP(?) <COLUMN_NAMES> AS <ALIASES>
FROM <TABLE_NAME>
) inner_query) 
SELECT <ALIASES>
FROM   query 
WHERE  __hibernate_row_nr__ >= ? 
       AND __hibernate_row_nr__ < ?

Notice the usage of inner query and Row_number() function. They finally resolved it!

这篇关于Hibernate（/ JPA）服务器端分页和MS SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！