Hibernate Criteria with Projections.groupProperty cannot return full hibernate object (ClassCastException)

Question

I am relatively new to Hibernate, and I have a problem when adding a "distinct" restriction on my hibernate class.

@Entity
public class TaggedOffer {
   private Long tagged_offers_id;
   private String brand;
   private Long cid;
   private Date created_date;
   //Getter and Setter and more fields
}

Previously, we were creating a hibernate query as follows:

public DetachedCriteria build(final TaggedOfferRequest request) {

        DetachedCriteria criteria = DetachedCriteria.forClass(TaggedOffer.class);

        criteria.add(Restrictions.eq("brand", request.getBrand()));
        criteria.add(Restrictions.in("cid", request.getCids()));

        // sort by date
        criteria.addOrder(Property.forName("createdDate").desc());

        return criteria;
}

This would create the following (working) SQL query:

select
        this_.tagged_offers_id as tagged1_2_3_,
        this_.brand as brand2_3_,
        this_.cid as cid2_3_,
        this_.created_date as created6_2_3_
    from
        site.tagged_offers this_ 
    where
        this_.brand=? 
        and this_.country_code=? 
        and this_.cid in (
            ?, ?
        ) 
    order by
        this_.created_date desc limit ?

Here comes the tricky part. We now need to ensure that the results that are returned are distinct on the cid field. Meaning, return as many results as possible, providing each record has a unique cid associated with it.

I looked into this in SQL, and it seems that the easiest way to do this is just to have a group by cid in the query. In terms of the hibernate criteria, this is basically what I've been trying:

public DetachedCriteria build(final TaggedOfferRequest request) {

        DetachedCriteria criteria = DetachedCriteria.forClass(TaggedOffer.class);

        criteria.add(Restrictions.eq("brand", request.getBrand()));
        criteria.add(Restrictions.in("cid", request.getCids()));

        // sort by date
        criteria.addOrder(Property.forName("createdDate").desc());

        // ** new ** distinct criteria
        criteria.setProjection(Projections.groupProperty("cid"));

        return criteria;
}

This almost creates the SQL that I am looking for, but it later throws a class cast exception (as it's just selecting the cid field as opposed to the entire object).

select
    this_.cid as y0_ 
from
    site.tagged_offers this_ 
where
    this_.brand=? 
    and this_.country_code=? 
    and this_.cid in (
        ?, ?
    ) 
    and tagtype1_.tag_type=? 
group by
    this_.cid 
order by
    this_.created_date desc limit ?

And the exception:

java.lang.ClassCastException: java.lang.Long cannot be cast to com.mycompany.site.taggedoffers.dao.model.TaggedOffer

Any idea how I can use projections to do what I want?

Thanks for your help.

Answer 1

Add projections for all columns which you need.

    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.groupProperty("cid"));
    projectionList.add(Projections.property("tagged_offers_id"));
    ...
    criteria.setProjection(projectionList);