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.
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.
How can I convert my existing criteria query (which has joins using createAlias()) to use a nested select instead?
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
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.
criteria.setProjection(Projections.distinct(Projections.id()));
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:
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.
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.
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.
The solution:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
works very well.
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:
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.
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.
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.
A slight improvement building on FishBoy's suggestion.
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.
Simply use a DetachedCriteria with an id projection as a subquery, and then add paging values on 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();
NullPointerException in some cases!
Without criteria.setProjection(Projections.distinct(Projections.property("id")))
all query goes well!
This solution is bad!
Another way is use SQLQuery. In my case 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();
Distinction is done in data base! In opposite to:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
where distinction is done in memory, after load entities!
The solution:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
works very well.
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
NullPointerException in some cases!
Without criteria.setProjection(Projections.distinct(Projections.property("id")))
all query goes well!
This solution is bad!
Another way is use SQLQuery. In my case 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();
Distinction is done in data base! In opposite to:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
where distinction is done in memory, after load entities!
I am using this one with my codes.
Simply add this to your criteria:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
that code will be like the select distinct * from table of the native sql. Hope this one helps.
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"))
)
);
Below is the way we can do Multiple projection 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();
}
}
Sample Usage:
String propertyNames = "titleName;titleDescr;titleVersion"
criteria countCriteria = ....
countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames);
Referenced from https://forum.hibernate.org/viewtopic.php?t=964506
A slight improvement building on FishBoy's suggestion.
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.
Simply use a DetachedCriteria with an id projection as a subquery, and then add paging values on 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();
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.
criteria.setProjection(Projections.distinct(Projections.id()));
I am using this one with my codes.
Simply add this to your criteria:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
that code will be like the select distinct * from table of the native sql. Hope this one helps.
Below is the way we can do Multiple projection 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();
}
}
Sample Usage:
String propertyNames = "titleName;titleDescr;titleVersion"
criteria countCriteria = ....
countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames);
Referenced from https://forum.hibernate.org/viewtopic.php?t=964506
Source: Stackoverflow.com