Hibernate Criteria with group by getting error

Question

I have these two Entity java class which is referring the tables (jos_content & jos_article_section).

@Entity
@Table(name="jos_content", 
uniqueConstraints={@UniqueConstraint(columnNames="id")})
public class ContentEntity implements Serializable {
private int id; 
private Set<ArticleSectionEntity> josArticleSection;    
private String sefUrl;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="id", unique = true, nullable = false)
public int getId() {
    return id;
}   
public void setId(int id) {
    this.id = id;
}

@Column(name="sef_url")
public String getSefUrl() {
    return sefUrl;
}
public void setSefUrl(String sefUrl) {
    this.sefUrl = sefUrl;
}

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name="article_id")
public Set<ArticleSectionEntity> getJosArticleSection() {
    return josArticleSection;
}
public void setJosArticleSection(Set<ArticleSectionEntity> josArticleSection) {
    this.josArticleSection = josArticleSection;
}
}


@Entity
@Table(name="jos_article_section", uniqueConstraints=    {@UniqueConstraint(columnNames="id")})
public class ArticleSectionEntity implements Serializable {
private int id; 
private int articleId;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="id", nullable=false, length=10)
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}

@Column(name="article_id", nullable=false)
public int getArticleId() {
    return articleId;
}
}

I'm using Criteria interface with group by like this:

Criteria contentCriteria = session.createCriteria(ContentEntity.class, "content");
// Join two tables (jos_content, jos_article_section) on  jos_content.id =     jos_acticle_section.article_id
contentCriteria = contentCriteria.setFetchMode("josArticleSection", FetchMode.JOIN);
contentCriteria = contentCriteria.add(Restrictions.eq("content.id", storyId));
contentCriteria = contentCriteria.add(Restrictions.eq("content.state",1));          

//Start Group By Clause
PropertyProjection propProjection = Projections.groupProperty("content.id");
contentCriteria = contentCriteria.setProjection(propProjection);
//End Group By

When I running my application, i'm getting this output with error as:

contentCriteria >>>> CriteriaImpl(com.itgd.entity.ContentEntity:content[][content.id=374821, content.state=1]content.id)
14:24:39,649 INFO  [STDOUT] Hibernate: select this_.id as y0_ from jos_content this_  where this_.id=? and this_.state=? group by this_.id
14:24:39,659 ERROR [STDERR] java.lang.ClassCastException: java.lang.Integer cannot be cast to com.itgd.entity.ContentEntity

How do I have to resolve this error and is it possible to keep all the fields with group by, using Criteria interface? Please let know, if anyone guide me.

Answer 1

We can use projections, to narrow the list of selected columns, but then we have to decide

• will we work with a result represented as object[]
• will we transform result into DTO (if possible to original entity)

If we would go with transformation, we have to help the transformer with column names. It is done with passing alias

PropertyProjection propProjection = Projections
   .groupProperty("content.id")
       .as("id") // the alias
   ;

contentCriteria
    .setProjection(propProjection)
    .setResultTransformer(Transformers.aliasToBean(ContentEntity.clas‌​‌​s));

List<ContentEntity> groupedEntities = contentCriteria.list();

then the result would be list of ContentEntity which would have filled only id. See:

• 17.9. Projections, aggregation and grouping

The alias() and as() methods simply wrap a projection instance in another, aliased, instance of Projection.

or we can expect the result as object[]

Object[] results = contentCriteria.list();

EXTEND:

If we would like to get list of route entities we can convert the current query into DetachedCriteria

DetachedCriteria grouped = DetachedCriteria.forClass(ContentEntity.class, "grouped")
    // Filter the Subquery
    .add(...
    // SELECT The User Id  
   .setProjection(propProjection)

So, the above will return just IDs we like, and the main query will be filtered by them, while returning the full object (no need to transform)

Criteria query = session.createCriteria(ContentEntity.class, "content")
    .add( Subqueries.propertyIn("content.id", grouped) );

Check similar stuff here: Hibernate Criteria for "in subselect"