1

I have a Task entity, has a Site

Entity

public class Task {
    // ....

    @Column(name = "START_DATE")
    @Temporal(value = TemporalType.TIMESTAMP)
    public Date getStartDate() {
        return startDate;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "SITE_NO")
    public Site getSite() {
        return site;
    }

}

HQL

select id from Task main
where exists (
    select site.id, MAX(startDate) as latest_date from Task sub
    group by site.id
    having main.startDate = MAX(startDate)
)

The hql above is working fine. Result set is all tasks GROUP BY site and MAX(startDate).
But Criteria is prefered.
I have tried the following code, but can't convert the having clause to criteria.

Criteria

DetachedCriteria detachedCriteria = service.createDetachedCriteria();
detachedCriteria.setProjection(Projections.projectionList()
        .add(Projections.groupProperty("site.no"))
        .add(Projections.max("startDate"))
        // [having clause]
    );

Criteria criteria = service.createCriteria();
criteria.add(Subqueries.exists(detachedCriteria));
Lakshmi
  • 2,204
  • 3
  • 29
  • 49
user3156403
  • 11
  • 3
  • I hope you're practicing with the criteria API. In most cases the HQL approach is much more flexible, readable and maintainable. Criteria API is generally used when you need to enforce restrictions based on user input. Since your query is static I don't see any benefit in converting. – Bart Jan 03 '14 at 09:56

1 Answers1

1

We can add having function by adding subquery. I added the max(startdate) as inner query to workaround. 

DetachedCriteria detachedCriteria = service.createDetachedCriteria();
detachedCriteria.setProjection(Projections.projectionList()
    .add(Projections.groupProperty("site.no"))
    .add(Projections.max("startDate"))
    // [having clause]
    );

Criteria criteria = service.createCriteria();
criteria.add(Subqueries.exists(detachedCriteria));
criteria.add(Subqueries.eq("startDate", innerQuery));
Sureshkumar Panneerselvan
  • 749
  • 6
  • 22