Wednesday, September 16, 2009

Hibernate subquery join using Criteria

hibernate
I recently needed to create max query in hibernate returning an object instead of the maximal value of the field and I wanted to do this using Hibernate Criteria in our JPA environment.
A simple example of what I wanted:
select *
from user
where userid = (select max(userid)
                from user
                where company = 'aCompanyName')

The way to program this in JPA/Hibernate using Criterias, DetachedCriteria and Subqueries. Make sure to use Subqueries.propertyEq instead of Subqueries.eq if you want to join on a field:

public User getMaxUserOfCompany(String companyName) {
Session session = (Session) em.getDelegate();
DetachedCriteria subCriteria = DetachedCriteria.forClass(User.class);
subCriteria.add(Restrictions.eq("company", companyName));
subCriteria.setProjection(Projections.max("userid") );
Criteria criteria = session.createCriteria(User.class);
criteria.add(Subqueries.propertyEq("userid", subCriteria));
return (User) criteria.uniqueResult();
}

4 comments:

  1. Thank you very much

    ReplyDelete
  2. Thanks a lot friend........U r just a rock star. U helped me a lot and saved much time.
    Keep it up. Thanks again.

    ReplyDelete
  3. select FS.supplier_id,VSF.supplier_name from FRA_SUPPLIER FS,VENDOR_SUPPLIERS_FRANCE VSF
    where fs.supplier_id=vsf.supplier_id and vsf.vendor_office_id=? and Fs.supplier_name=? and ...

    Pls told how we make this

    ReplyDelete