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;
}
}
GROUP BY, by definition, involves the aggregation of data, which is why Projections are necessary. Perhaps if you added more details of the tables, and the SQL query you would want Hibernate to generate, we could advise better. – Vermifuge