CODEWITHSUNDEEP
javasql-serverhibernatehqlcriteria
user3156403
user3156403

Reputation: 11

hibernate : convert complicated hql to criteria

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));

Upvotes: 1

Views: 1470

Answers (1)

Sureshkumar Panneerselvan
Sureshkumar Panneerselvan

Reputation: 590

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));

Upvotes: 1

Related Questions