使用JPA（Hibernate）查找包含给定记录的页面 [英] Finding out the page containing a given record using JPA (Hibernate)

问题描述

我如何知道JPA查询中记录的位置？

我有一个服务返回分页结果，更少的方法与此签名：

  List< Record> getRecordsPage（long page，int pageSize）; 
  

当这个被调用时，我只是创建一个查询并像这样配置：

  TypedQuery< Record> query = entityManager.createQuery（criteriaQuery）; 
 query.setFirstResult（page * pageSize）; 
 query.setMaxResults（pageSize）; 
  

此页面显示结果。这是按预期工作，而且很简单。

问题

我的另一个要求是实现一个方法，该方法将检索包含特定记录的页面。使用以下签名实现方法：

  List< Record> getRecordsPage（Record record，int pageSize）; 
  

此方法需要产生记录所在的右页。例如，针对 getRecordsPage（RECORD4,2）调用，考虑数据库状态：

  1。 RECORD1 
 2. RECORD2 
 3. RECORD3 
 4. RECORD4 
 5. RECORD5 
  

返回的页面应该是2，包含 [RECORD3，RECORD4] 。

ORDER BY 参数始终设置，并且可能超过一个字段。

/ strong>

到目前为止，我有一些解决方案是：

1. 不是很好，但它解决了问题：
使用提供的查询我只选择了id而没有分页和执行只是一个 indexOf 为了找到它的位置和基于位置我可以找出页面的记录，然后执行常规过程使用 getRecordsPage（long page，int pageSize）已经实现。
$ b


1. 与数据库高度结合：



当我使用mySQL时，我可以执行一个sql如： select r from（select rownum r，id from t order by x，y）z where z.id =：id ，会返回记录，我可以使用它来调用 getRecordsPage（长页面，int页面大小）。








要求：





1. 必须支持多个字段的顺序;


2. 在给定查询的情况下，它将返回记录位置或包含的记录页面偏移量;



一个好的解决方案是：





1. 纯属JPA;
   如果在数据库中执行一个额外的查询以查找记录位置，则li>
   
2. 为 Ok ;
   
3. 为 Ok 如果在某些时候使用hibernate（因为Hibernate在这种情况下是JPA的后面）。
div>

为了确保我理解正确：您正在显示记录并且想要显示所有记录的分页列表是否预先选择了包含您的项目的页面？



首先，您必须知道关系数据库不提供数据库中任何记录的隐式排序。虽然它们似乎是从第一个到最后一个排序，但这不是可移植的和可靠的。





因此，您的分页列表/网格必须按某列明确排序。为了简单起见，你的网格按 id 排序。您知道当前显示的记录的标识（例如： X ）。你首先需要弄清楚你的记录在这个排序顺序中的位置：



  SELECT COUNT（r ）
 FROM记录r 
 WHERE r.id< ：X 
  

该查询将返回记录之前的记录数。现在很简单：



  int page = count / pageSize 
  

页面是基于0的。





排序列不是唯一的，这可能不适用于所有情况。但是，如果列不是唯一的，排序本身并不稳定（具有相同值的记录可能以随机顺序出现），所以请考虑使用额外的唯一列进行排序：



  ... 
 ORDER BY r.sex，r.id 
  

在这种情况下，记录首先按 sex （大量重复项）和id排序。在当前记录之前计数记录的解决方案仍然有效。


How can I know the position of a record in a JPA query?

I have a service that returns paged results implementing more or less a method with this signature:

List<Record> getRecordsPage(long page, int pageSize);

When this is invoked I just create a query and configure like this:

TypedQuery<Record> query = entityManager.createQuery(criteriaQuery);
query.setFirstResult(page * pageSize);
query.setMaxResults(pageSize);

This pages the result. And this is working as expected, and is quite simple.

The Problem

Another requirement I have is to implement a method that would retrieve the page that contains a specific record. Implementing a method with the following signature:

List<Record> getRecordsPage(Record record, int pageSize);

This method needs to result the right page in which the record is. For instance, for getRecordsPage(RECORD4, 2) invocation, considering database state:

1. RECORD1 
2. RECORD2 
3. RECORD3 
4. RECORD4 
5. RECORD5 

The returned page should be 2 containing [RECORD3, RECORD4].

The ORDER BY parameter is always set, and could be more than one field.

Solution Until Now

Until now I have a few solutions that are:

1. Not good at all but it solves the problem:

Using the query provided I select just the id without paging and perform just a indexOf in order to find its position and based on the position I can find out the page the record is and then perform the regular process using getRecordsPage(long page, int pageSize) already implemented.

1. Not so good because is high coupled with database:

As I'm using mySQL, I could perform an sql like : select r from (select rownum r, id from t order by x,y) z where z.id = :id, what would return the position of the record and I could use it in order to invoke getRecordsPage(long page, int pageSize).

Good Solution

Requirements:

1. shall support order by multiple fields;
2. given a query, it will return the record position or the containing record page offset;

A good solution would:

1. be purely JPA;
2. be Ok if one additional query is performed in database just to find out the record position;
3. be Ok if hibernate is used in some point (as Hibernate is behind JPA in this case).

解决方案

To be sure I understand correctly: you are displaying a Record and want to display a paged list of all records preselecting the page containing your item?

First of all you must know that relational databases do not offer any implicit sorting of records in the database. Although they seem to be sorted from first to last added, this is not portable and reliable.

So your paged list/grid must be sorted explicitly by some column. For simplicity say your grid is sorted by id. You know the id of the record being currently displayed (say: X). You first need to figure out at which position in your table your record is with regards to this sorting order:

SELECT COUNT(r)
FROM Record r
WHERE r.id < :X

This query will return the number of records before your record. Now it's simple:

int page = count / pageSize

page is 0-based.

Unfortunately if your sort column is not unique, this might not work in all cases. But if the column is not unique, the sorting itself is not stable (records having the same value might appear in random order), so consider ordering by extra unique column:

...
ORDER BY r.sex, r.id

In this case the records are first sorted by sex (lots of duplicates) and by id. The solution with counting records before current record will still work.

这篇关于使用JPA（Hibernate）查找包含给定记录的页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！