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

6 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
  4. Hi,
    Thanks for the example, pretty simple when you join one field but how about a join on two columns with the subquery ?

    ReplyDelete
  5. For exemple

    I have two classes :

    a super class Rent :

    class Rent {
    Long idRent
    Date dateRent
    ... }

    and a subclass CarRent :

    class CarRent extends Rent {
    Car car
    String typeCar
    ....
    }

    class Car {
    Long idCar
    ....
    }

    I would like to write this SQL request using criteria (in the sub query I look for the latest rent date for each car, and then I look for rent information related to the retrieved couple (idCar, dateRent)

    the SQL request would be

    select * from CarRent a, Rent b,
    (select b.idCar, max(a.dateRent) as dateRent
    from Rent a, CarRent b
    where a.idRent = b.idRent
    group by (idCar)
    )c
    where a.idRent = b.idRent
    and b.idCar = c.idCar
    and a.dateRent = c.dateRent

    in criteria, here it is my code

    DetachedCriteria inner = DetachedCriteria.forClass(CarRent.class, "inner")
    .setProjection(Projections.projectionList().add(Projections.max("inner.dateRent")).add(Projections.groupProperty("inner.car")));

    and

    Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(CarRent.getClass()).add(example);
    criteria.createAlias("car", "car",Criteria.INNER_JOIN,Subqueries.eq("rentDate", inner));

    Any ideas how to perform a inner join between the criteria and inner (DetachedCriteria) on the two columns (idCar,rentDate) ?

    Thanks for your help

    ReplyDelete