Java 如何通过连接和基于行的限制（分页）在休眠中获得不同的结果？

How to get distinct results in hibernate with joins and row-based limiting (paging)?

提问by Daniel Alexiuc

I'm trying to implement paging using row-based limiting (for example: setFirstResult(5)and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables.

我正在尝试在具有连接到其他表的 Hibernate Criteria 查询上使用基于行的限制（例如：setFirstResult(5)and setMaxResults(10)）来实现分页。

Understandably, data is getting cut off randomly; and the reason for that is explained here.

可以理解的是，数据被随机切断；原因在这里解释。

As a solution, the page suggests using a "second sql select" instead of a join.

作为解决方案，该页面建议使用“第二个 sql 选择”而不是连接。

How can I convert my existing criteria query (which has joins using createAlias()) to use a nested select instead?

如何将我现有的条件查询（使用 连接createAlias()）转换为使用嵌套选择？

采纳答案by Daniel Alexiuc

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

您可以通过请求不同 id 的列表而不是不同的水合对象列表来实现所需的结果。

Simply add this to your criteria:

只需将此添加到您的标准中：

criteria.setProjection(Projections.distinct(Projections.property("id")));

Now you'll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check as part ofthe sql query, instead of what a ResultTransformer does which is to filter the results for distinctness afterthe sql query has been performed.

现在，您将根据基于行的限制获得正确数量的结果。这样做的原因是因为投影将执行差异性检查作为sql 查询的一部分，而不是 ResultTransformer 所做的，即在执行 sql 查询后过滤结果的差异性。

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

值得注意的是，您现在将获得一个 id 列表，而不是获取对象列表，稍后您可以使用它来从休眠状态中水合对象。

回答by grayshop

I am using this one with my codes.

我在我的代码中使用了这个。

Simply add this to your criteria:

只需将此添加到您的标准中：

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

标准.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

that code will be like the select distinct * from table of the native sql. Hope this one helps.

该代码将类似于本机 sql 的 select distinct * from 表。希望这个有帮助。

回答by JJ.

The solution:

解决方案：

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

works very well.

效果很好。

回答by Krzysztof Barczyński

NullPointerExceptionin some cases! Without criteria.setProjection(Projections.distinct(Projections.property("id")))all query goes well! This solution is bad!

NullPointerException在某些情况下！没有criteria.setProjection(Projections.distinct(Projections.property("id")))所有的查询顺利！这个解决方案不好！

Another way is use SQLQuery. In my case following code works fine:

另一种方法是使用 SQLQuery。在我的情况下，以下代码工作正常：

List result = getSession().createSQLQuery(
"SELECT distinct u.id as usrId, b.currentBillingAccountType as oldUser_type,"
+ " r.accountTypeWhenRegister as newUser_type, count(r.accountTypeWhenRegister) as numOfRegUsers"
+ " FROM recommendations r, users u, billing_accounts b WHERE "
+ " r.user_fk = u.id and"
+ " b.user_fk = u.id and"
+ " r.activated = true and"
+ " r.audit_CD > :monthAgo and"
+ " r.bonusExceeded is null and"
+ " group by u.id, r.accountTypeWhenRegister")
.addScalar("usrId", Hibernate.LONG)
.addScalar("oldUser_type", Hibernate.INTEGER)
.addScalar("newUser_type", Hibernate.INTEGER)
.addScalar("numOfRegUsers", Hibernate.BIG_INTEGER)
.setParameter("monthAgo", monthAgo)
.setMaxResults(20)
.list();

Distinction is done in data base!In opposite to:

区分是在数据库中完成的！与：

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

where distinction is done in memory, after load entities!

加载实体后，在内存中进行区分！

回答by nikita

A small improvement to @FishBoy's suggestion is to use the id projection, so you don't have to hard-code the identifier property name.

@FishBoy 建议的一个小改进是使用 id 投影，因此您不必对标识符属性名称进行硬编码。

criteria.setProjection(Projections.distinct(Projections.id()));

回答by Daniel Alexiuc

A slight improvement building on FishBoy's suggestion.

基于 FishBoy 的建议略有改进。

It is possible to do this kind of query in one hit, rather than in two separate stages. i.e. the single query below will page distinct results correctly, and also return entities instead of just IDs.

可以一次完成这种查询，而不是分两个单独的阶段。即下面的单个查询将正确分页不同的结果，并且还返回实体而不仅仅是 ID。

Simply use a DetachedCriteria with an id projection as a subquery, and then add paging values on the main Criteria object.

只需使用带有 id 投影的 DetachedCriteria 作为子查询，然后在主 Criteria 对象上添加分页值。

It will look something like this:

它看起来像这样：

DetachedCriteria idsOnlyCriteria = DetachedCriteria.forClass(MyClass.class);
//add other joins and query params here
idsOnlyCriteria.setProjection(Projections.distinct(Projections.id()));

Criteria criteria = getSession().createCriteria(myClass);
criteria.add(Subqueries.propertyIn("id", idsOnlyCriteria));
criteria.setFirstResult(0).setMaxResults(50);
return criteria.list();

回答by Andreas Hartmann-schneevoigt

I will now explain a different solution, where you can use the normal query and pagination method without having the problem of possibly duplicates or suppressed items.

我现在将解释一个不同的解决方案，您可以使用正常的查询和分页方法，而不会出现可能重复或隐藏项目的问题。

This Solution has the advance that it is:

此解决方案具有以下优点：

• faster than the PK id solution mentioned in this article
• preserves the Ordering and don't use the 'in clause' on a possibly large Dataset of PK's

• 比本文提到的PK id解决方案更快
• 保留排序并且不要在可能很大的 PK 数据集上使用“in 子句”

The complete Article can be found on my blog

完整的文章可以在我的博客上找到

Hibernate gives the possibility to define the association fetching method not only at design time but also at runtime by a query execution. So we use this aproach in conjunction with a simple relfection stuff and can also automate the process of changing the query property fetching algorithm only for collection properties.

Hibernate 提供了不仅在设计时而且在运行时通过查询执行定义关联获取方法的可能性。因此，我们将这种方法与简单的关联内容结合使用，并且还可以自动更改仅针对集合属性的查询属性获取算法的过程。

First we create a method which resolves all collection properties from the Entity Class:

首先，我们创建一个方法来解析实体类中的所有集合属性：

public static List<String> resolveCollectionProperties(Class<?> type) {
  List<String> ret = new ArrayList<String>();
  try {
   BeanInfo beanInfo = Introspector.getBeanInfo(type);
   for (PropertyDescriptor pd : beanInfo.getPropertyDescriptors()) {
     if (Collection.class.isAssignableFrom(pd.getPropertyType()))
     ret.add(pd.getName());
   }
  } catch (IntrospectionException e) {
    e.printStackTrace();
  }
  return ret;
}

After doing that you can use this little helper method do advise your criteria object to change the FetchMode to SELECT on that query.

这样做之后，您可以使用这个小助手方法来建议您的标准对象在该查询上将 FetchMode 更改为 SELECT。

Criteria criteria = …

//    … add your expression here  …

// set fetchmode for every Collection Property to SELECT
for (String property : ReflectUtil.resolveCollectionProperties(YourEntity.class)) {
  criteria.setFetchMode(property, org.hibernate.FetchMode.SELECT);
}
criteria.setFirstResult(firstResult);
criteria.setMaxResults(maxResults);
criteria.list();

Doing that is different from define the FetchMode of your entities at design time. So you can use the normal join association fetching on paging algorithms in you UI, because this is most of the time not the critical part and it is more important to have your results as quick as possible.

这样做与在设计时定义实体的 FetchMode 不同。因此，您可以在 UI 中的分页算法上使用正常的连接关联获取，因为这在大多数情况下不是关键部分，并且尽可能快地获得结果更为重要。

回答by Yashpal Singla

Below is the way we can do Multiple projection to perform Distinct

下面是我们可以通过多重投影来执行 Distinct 的方法

    package org.hibernate.criterion;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.type.Type;

/**
* A count for style :  count (distinct (a || b || c))
*/
public class MultipleCountProjection extends AggregateProjection {

   private boolean distinct;

   protected MultipleCountProjection(String prop) {
      super("count", prop);
   }

   public String toString() {
      if(distinct) {
         return "distinct " + super.toString();
      } else {
         return super.toString();
      }
   }

   public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      return new Type[] { Hibernate.INTEGER };
   }

   public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      StringBuffer buf = new StringBuffer();
      buf.append("count(");
      if (distinct) buf.append("distinct ");
        String[] properties = propertyName.split(";");
        for (int i = 0; i < properties.length; i++) {
           buf.append( criteriaQuery.getColumn(criteria, properties[i]) );
             if(i != properties.length - 1) 
                buf.append(" || ");
        }
        buf.append(") as y");
        buf.append(position);
        buf.append('_');
        return buf.toString();
   }

   public MultipleCountProjection setDistinct() {
      distinct = true;
      return this;
   }

}

ExtraProjections.java

额外投影.java

package org.hibernate.criterion; 

public final class ExtraProjections
{ 
    public static MultipleCountProjection countMultipleDistinct(String propertyNames) {
        return new MultipleCountProjection(propertyNames).setDistinct();
    }
}

Sample Usage:

示例用法：

String propertyNames = "titleName;titleDescr;titleVersion"

criteria countCriteria = ....

countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames);

Referenced from https://forum.hibernate.org/viewtopic.php?t=964506

引用自https://forum.hibernate.org/viewtopic.php?t=964506

回答by Andrew

session = (Session) getEntityManager().getDelegate();
Criteria criteria = session.createCriteria(ComputedProdDaily.class);
ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("user.id"), "userid");
projList.add(Projections.property("loanState"), "state");
criteria.setProjection(Projections.distinct(projList));
criteria.add(Restrictions.isNotNull("this.loanState"));
criteria.setResultTransformer(Transformers.aliasToBean(UserStateTransformer.class));

This helped me :D

这对我有帮助：D

回答by rekinyz

if you want to use ORDER BY, just add:

如果你想使用 ORDER BY，只需添加：

criteria.setProjection(
    Projections.distinct(
        Projections.projectionList()
        .add(Projections.id())
        .add(Projections.property("the property that you want to ordered by"))
    )
);