Add a Group by to a Hibernate Criteria-Query without Projection

Question

I have a Criteria-Query, which joins a second table B, to select entities from table A. The problem is, that this query returns some entities from table A multiple times. But I need the results to be distinct.

Using Criteria.DISTINCT_ROOT_ENTITY is useless, becaus this filters out the multiple occurences after the SQL-Query was executed. So, when I limit my results to 20 hits, I end up with only 4, though there are more entries, that match my query.

In pure SQL I simply can add a "GROUP BY ID" to the query and everything is fine, because the join of table B is only used, to select the entities from table A. But with the Criteria-API I cannot do this. The only way to add a "GROUP BY" is by using Projections. But then, I end up with scalar values, not with a real instance of my class. Using a SQL-restriction does not work either, because hibernate adds a bogous "1=1" after my "GROUP BY"-clause. :(

Any ideas?

Solution 1

Have you tried to use something like this?

    ICriteria criteria = dc.GetExecutableCriteria(RepositoryInstance.Session)
            .SetProjection(Projections.distinct(Projections.projectionList()
                    .add(Projections.property("Prop1"), "Prop1")
                    .add(Projections.property("Prop2"), "Prop2")
                    .add(Projections.property("Prop3"), "Prop3")
                    .add(Projections.property("Prop4"), "Prop4")));
    result = criteria.List();

You can dynamically add properties through reflection of the class.

This creates SQl like this: select distinct prop1,prop2,prop3,prop4 from yourClass

I did not include DetachedCriteria dc since that is irrelevant.

Solution 2

GROUP BY WITHOUT PROJECTION: Its not possible as it make sense, in many answers you may found, But most people don't want to use projection, because it require them to project each and every attribute, but requirement is that a bean must be projected. (and returned as a result). In example below I have tried to project the required bean as resultant object.

I have achieved the same result with a little bit of trick I believe, First I was trying to apply group by without projection but I have found no solution, so I have to rely on Projection.

Here is what I wanted to achieve

select p.* FROM parent p INNER JOIN child c ON p.id_parent=c.id_father
WHERE c.child_name like '%?%' AND p.parent_name like '%?%' 
group by p.id_parent

In Java code I wanted p.* to be a Parent class which is my entity bean and I wanted it be unique, one way is get the result list in a Set, but i dont like this way due many reasons :)

So I created a Criteria from Child.class instead of Parent.class, and this trick worked for me.

Criteria c = session.createCriteria(Child.class,"c");// starting from Child
    c.add(Restrictions.like("childName",   "abc", MatchMode.ANYWHERE));
    c.createAlias("parent", "p"); //remember parent is an attribute in Child.class
    c.add(Restrictions.like("p.parentName",   "xyz", MatchMode.ANYWHERE));
    c.setProjection( Projections.projectionList().add(Projections.groupProperty("parent"))); //projecting parent which is an attribute of Child.class

    List<Parent> result = c.list(); //get the result
    for (Parent p: result) {
        System.out.println(p);
    }

If you still haven't got the idea here are my mapped Entity Bean classes.

package com.mazhar.beans;

import static javax.persistence.GenerationType.IDENTITY;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "parent")
public class Parent {
    private Integer idParent;
    private String parentName;
    private List<Child> childs;

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id_parent")
    public Integer getIdParent() {
        return idParent;
    }
    public void setIdParent(Integer idParent) {
        this.idParent = idParent;
    }

    @Column(name = "parent_name")
    public String getParentName() {
        return parentName;
    }
    public void setParentName(String parentName) {
        this.parentName = parentName;
    }

    @OneToMany(fetch=FetchType.LAZY, mappedBy="parent", cascade=CascadeType.ALL)
    public List<Child> getChilds() {
        return childs;
    }
    public void setChilds(List<Child> childs) {
        this.childs = childs;
    }

}

and my child class

package com.mazhar.beans;

import static javax.persistence.GenerationType.IDENTITY;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "child")
public class Child {
    private Integer idChild;
    private String childName;
    private Parent parent; //this actually we projected in criteria query.

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id_city", unique = true, nullable = false)
    public Integer getIdChild() {
        return idChild;
    }

    public void setIdChild(Integer idChild) {
        this.idChild = idChild;
    }

    @Column(name = "city_name", nullable = false)
    public String getChildName() {
        return childName;
    }

    public void setChildName(String cName) {
        this.childName = cName;
    }

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "id_father")
    public Parent getParent() {
        return parent;
    }

    public void setParent(Parent parent) {
        this.parent = parent;
    }
}

Comments

[Kai Moritz]

Thanks for your answer :) but I am not using HQL. I am using the Criteria-API and I am bound to it, because the query has to be compiled dynamically! Hence, I need to find a way to tell Hibernate to group the results by the id-property, without using Projections, because I need real instances of my classes as reslut, not scalar values.

[alasdairg]

Its also possible to specify a native SQL query programmatically rather than as a named query as shown above - although not by using the criteria API.

[Andrey Doloka]

This is the only legal way to do this, but changing the query like this may cause adding unwanted joins or extra time of refactoring of consuming methods. Additionally, we could just get projection property of root instance by id and make a get() call by providing a list of ids. But those are workarounds due to missing method in criteria builder which loads the root entity as a property() projection into the resulting map.