在Hibernate中用连接表分页 [英] Pagination in Hibernate with joined tables

问题描述

有很多关于它的问题，但我找不到具体的答案。
我是Hibernate的新手，并试图实现分页。
假设我有两个实体：Parent和Child，它们的定义如下：

pre $ @Entity
@Table（name =Parents）
public class Parent {

@Id
@Column（name =id，length = 255）
private String ID;

@Column（name =name，length = 255）
保护字符串名称;

@OneToMany
@JoinTable（name =parents_children，joinColumns = @JoinColumn（name =parent_id），inverseJoinColumns = @JoinColumn（name =child_id））
@LazyCollection（LazyCollectionOption.FALSE）
protected List< Child>儿童;


@Entity
@Table（name =children）
public class Child {

@Id
@Column（name =id，length = 255）
保护字符串ID;

@Column（name =name，length = 255）
保护字符串名称;

$ b}


每个属性也有getter和setter as

我希望获得父母 按名称排序的第一页 ，其中每个页面有10个结果。

因此，我从以下内容开始：

  Session session = HibernateUtil.getSessionFactory（）。openSession（）; 
标准c = session.createCriteria（Parent.class，p）; 
 c.createAlias（q.children，ch）; 
 c.setResultTransformer（CriteriaSpecification.DISTINCT_ROOT_ENTITY）; 
 c.addOrder（Order.desc（name））; 
 c.setMaxResults（10）; 
 c.setFirstResult（0）; 
列表< Parent> result = c.list（）; 
 session.close（）; 
  

此代码无法按预期工作，因为setMaxResults在连接的表上执行，而不是父母名单（根据我的意愿）。

我的问题是查询应该是在家长列表上而不是在连接表上进行分页？ p>

解决方案

分页不适用于连接的集合，因为它计算所有满足的行，其中谓词（Hibernate与此无关，它是数据库的工作方式，例如 Oracle rownum ）。 解决这个问题的方法是使用子查询，以便将 rownum （或所用数据库中的等价物）应用于仅一个表（或连接的表在一对一的关系中）。

在HQL中：

 从父p中选择p是p（从c中选择c.parent ...）
  

可以在标准等效中构建类似的方式。

There are a lot of questions about it but I couldn't find a concrete answer. I'm new to Hibernate and trying to implement pagination. Let's say I have two entities: Parent and Child which are defined as follows:

@Entity
@Table(name="Parents")
public class Parent{

   @Id
   @Column(name="id", length=255)
   private String id;

   @Column(name="name", length=255)
   protected String name;

   @OneToMany
   @JoinTable(name="parents_children", joinColumns = @JoinColumn( name="parent_id"), inverseJoinColumns = @JoinColumn( name="child_id"))
   @LazyCollection(LazyCollectionOption.FALSE)
   protected List<Child> children;
}

@Entity
@Table(name="children")
public class Child {

   @Id
   @Column(name="id", length=255)
   protected String id;

   @Column(name="name", length=255)
   protected String name;


  }

Each property also has getter and setter as required.

I want to get the first page of Parents ordered by name, where each page is 10 results.

So I started from:

    Session session = HibernateUtil.getSessionFactory().openSession();
    Criteria c = session.createCriteria(Parent.class, "p");
    c.createAlias("q.children", "ch"); 
    c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    c.addOrder(Order.desc("name"));
    c.setMaxResults(10);
    c.setFirstResult(0);
    List<Parent> result = c.list();
    session.close();

This code doesn't work as expected because the setMaxResults is performed on the joined table and not on the parents list (as I want it to be).

My question is what should be the query to get paging on the Parents list and not on the joined table?

解决方案

Pagination does not work with joined collections because it counts all the rows that have satisfied the where predicate (Hibernate has nothing to do with this, it is how databases work, for example Oracle rownum).

The usual way to overcome this is to use subqueries, so that rownum (or the equivalent in the database used) is applied to the selected rows of only one table (or joined tables which are in to-one relationships).

In HQL:

select p from Parent p were p in (select c.parent from Child c where ...)

The criteria equivalent can be built in a similar way.

这篇关于在Hibernate中用连接表分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！