select distinct entities using Criteria

sb606 · **Joined:** Fri Jul 22, 2005 8:51 am **Posts:** 3

The problem is that the field that I put into the projection list is not being populated at all.

shantanu · **Joined:** Tue May 09, 2006 4:54 am **Posts:** 13

I had same problem. I added aliases to the projection list and the object started getting data.

dimitar · **Joined:** Fri Sep 15, 2006 10:29 am **Posts:** 14

Toqwaz wrote:

Code:

Criteria c = session.createCriteria(User.class)
       .createAlias("roles", "r")
       .add(Expression.or(Expression.eq("r.roleNr", new Long(410)), Expression.eq("r.roleNr", new Long(414))))
       .setProjection(Projections.distinct(Projections.projectionList().add(Projections.property("userId"), "userId").add(Projections.property("userName"), "userName")))
       .setResultTransformer(new AliasToBeanResultTransformer(User.class));
List users = c.list();
((User)users.get(0)).getUserName();

What will happen if I try:

Code:

((User)users.get(0)).getRoles().iterator().next()

In a similar case, I get only a NullPointerException.

shantanu · **Joined:** Tue May 09, 2006 4:54 am **Posts:** 13

That's right. You would expect a NullPointerException as you have not added roles in your projection list. Only those properties in your projectionList will be populated on User object.

Mik · **Posted:** Thu Nov 30, 2006 8:13 am

It's been over a year since shanonvl posted a working solution to the problem of Hibernate trying to use column aliases in the where clause, has anyone come up with a more elegant solution?

SureshV · **Joined:** Mon Apr 23, 2007 2:21 am **Posts:** 1

Hi All,

I need to set projection to the child criteria.

I need this kind of coding.

Criteria parentCriteria = HibernateUtil.createSession().createCriteria(
Cat.class);
Criteria childCriteria = parentCriteria .addCriteria("kittenlist");

childCriteria.setProjection(Projections.distinct(Projections.projectionList()
.add(Projections.property ("kittenName"), "kittenName")));

result = parentCriteria.list()

But it is showing
could not resolve property: kittenName of: Cat

Why this projection is applying to parentCriteria instead of childCriteria?

How to apply projections to the child criteria? Pls help.

Thanks in advance.

Grolubao · **Joined:** Tue Apr 24, 2007 11:47 am **Posts:** 1

Does anyone already got a fix for Toqwaz problem with the "where" clauses, that doesn't envolve the hack provided? I'm really frustrated, because otherwise I can't select distinct records with paging...

vmaurik3 · **Joined:** Tue Jun 05, 2007 10:11 am **Posts:** 1

Grolubao wrote:

Does anyone already got a fix for Toqwaz problem with the "where" clauses, that doesn't envolve the hack provided? I'm really frustrated, because otherwise I can't select distinct records with paging...

I ran into this problem as well. Here is what we did to get around it:

Note that this involves "patching" several Hibernate classes.

Download the "pacth" from http://opensource.atlassian.com/projects/hibernate/browse/HHH-817

make the required changes to add the boolean property and set it to false. Also note that you will need to override this method everywhere it occurs in the hibernate source code. I believe I ended up touching somewhere between 7-9 classes altogether.

This does work, provided you never have a need for this functionality in the future.

if I have time I will look up the exact classes we changed to implement this and give a list.

Hope that it helps.

James.Swift · **Joined:** Fri Feb 22, 2008 7:28 pm **Posts:** 2

Instead of using the .add(Expression.eq(<field>,<value>)) which causes the ugly y0_ aliases, you can use a Restrictions.sqlRestriction("<field> = ?", <value>, <type> e.g. Hibernate.INTEGER)

I used the following:

return session.createCriteria(AssessmentTestEntity.class)
.add( Restrictions.sqlRestriction("grade = ?", grade, Hibernate.INTEGER) )
.add( Restrictions.sqlRestriction("code = ?", testId, Hibernate.STRING) )
.setProjection(Projections.distinct(Projections.projectionList()
.add(Projections.property("grade"), "grade")
.add(Projections.property("code"), "code")
.add(Projections.property("obtainedScore"), "obtainedScore")
.add(Projections.property("scaledScore"), "scaledScore")
.add(Projections.property("gradeEquivalent"), "gradeEquivalent")
.add(Projections.property("nationalPercentile"), "nationalPercentile")
.add(Projections.property("normalCurveEquivalent"), "normalCurveEquivalent")
))
.setResultTransformer(new AliasToBeanResultTransformer(AssessmentTestEntity.class))
.list();

jpwinne · **Posted:** Thu Jul 10, 2008 7:42 am

By adding the following Projection as the first projection to Criteria,
you can trick Hibernate to perform a "distinct all":

Projections.sqlProjection("DISTINCT 1", new String[0], new Type[0])

nahla_ghoneim · **Joined:** Wed Nov 04, 2009 7:53 am **Posts:** 2

jpwinne- Adding DISTINCT 1 to the projections list caused Hibernate to ignore the other projections:
select DISTINCT 1 from PARENT this_ order by...
ORA-01791: not a SELECTed expression

I used Toqwaz's solution. It didn't work out for me because I had component beans
http://lesaany.blogspot.com/2009/10/hib ... st-of.html

rahul.raje · **Joined:** Mon Oct 10, 2011 6:49 pm **Posts:** 1

I found a slightly different way to resolve this problem by implementing a self join. This saves you having to create a projection list as in the earlier examples. May work for some....

Begin by creating a Detached criteria for the complex query. Add a distinct clause to the query to uniquely identify the @Id values. Next create a criteria that uses this detached criteria to extract the required.

Code:

DetachedCriteria dc = DetachedCriteria.forClass(Employee.class);
dc.createAlias("location", "location");
dc.createAlias("location.dept", "department");
dc.add(
    Restrictions.or(
        Restrictions.eq("location.id", locationId),
        Restrictions.eq("department.name", departmentName)));
dc.setProjection(Projections.distinct(Property.forName("id"))); 

Code:

Criteria criteria = session().createCriteria(Employee.class);
criteria.add(Property.forName("id").in(dc)); 
criteria.setMaxResults(maxLength);
criteria.setFirstResult((int)rowNum);

Yaser23 · **Joined:** Fri Sep 19, 2014 4:18 am **Posts:** 1

@Shenoval : where to call the buildProjection method u hav written???