How to get great results in sleep mode with line-based joins and restrictions (paging)?

Question

I am trying to implement paging using row restrictions (for example: setFirstResult(5) and setMaxResults(10) ) in a hibernation criterion request that has connections to other tables.

It is clear that data is truncated randomly; and the reason for this is explained here .

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

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

Answer 1

You can achieve the desired result by requesting a list of different identifiers instead of a list of individual hydrated objects.

Just add this according to your criteria:

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

You will now get the right amount of results according to your string-based delimiter. The reason for this is because the projection will perform a parity check as part of the sql query, instead of what ResultTransformer does, which should filter the results for distinctness after the sql query has been executed.

It should be noted that instead of getting a list of objects, now you will get a list of identifiers that you can use to hydrate objects from sleep mode later.

Answer 2

I use this code with my codes.

Just add this according to your criteria:

criteria.setResultTransformer (Criteria.DISTINCT_ROOT_ENTITY);

this code will look like a highlighted report * from a native sql table. Hope this helps.

Answer 3

A slight improvement as suggested by FishBoy.

You can make such a request in one stroke, and not in two separate stages. that is, one query below will correctly display various results, as well as return objects, not just identifiers.

Just use DetachedCriteria with the id projection as a subquery, and then add the paging values ​​to the main Criteria object.

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(); 

Answer 4

A slight improvement to @FishBoy's suggestion is to use the id projection, so you don't need to hardcode the identifier property name.

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

Answer 5

Decision:

 criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); 

works very well.

Answer 6

 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)); 

It helped me: D

Answer 7

Now I will explain another solution in which you can use a regular query and pagination method without having problems with possible duplicates or suppressed elements.

This solution has the advantage of:

• faster than PK id solution mentioned in this article.
• saves the order and does not use the "in" parameter on the largest possible data set PK

The full article can be found on my blog.

Hibernate provides the ability to define an association selection method not only at design time, but also at run time by executing a query. Thus, we use this aproach in combination with simple interception tools and can also automate the process of changing the query properties selection algorithm for collection properties only.

First, we create a method that resolves all the properties of the collection 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 that, you can use this small helper method, advising your criteria object to change the FetchMode to SELECT in this query.

 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 this is different from defining the FetchMode of your objects at design time. That way, you can use the usual associative binding using swap algorithms in the user interface, because in most cases this is not a critical part, and it is more important that your results are as fast as possible.

Answer 8

if you want to use ORDER BY just add:

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

Answer 9

NullPointerException in some cases! Without criteria.setProjection(Projections.distinct(Projections.property("id"))) whole query goes well! This decision is bad!

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

 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(); 

The difference is in the database! In contrast:

 criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); 

where the distinction is made in memory, after loading objects!

Answer 10

Below we can make several projections to perform 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

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

Usage example:

 String propertyNames = "titleName;titleDescr;titleVersion" criteria countCriteria = .... countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames); 

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