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();
}
Posts
Thanks very much
ReplyDeleteThank you very much
ReplyDeleteThanks a lot friend........U r just a rock star. U helped me a lot and saved much time.
ReplyDeleteKeep it up. Thanks again.
select FS.supplier_id,VSF.supplier_name from FRA_SUPPLIER FS,VENDOR_SUPPLIERS_FRANCE VSF
ReplyDeletewhere fs.supplier_id=vsf.supplier_id and vsf.vendor_office_id=? and Fs.supplier_name=? and ...
Pls told how we make this
Hi,
ReplyDeleteThanks for the example, pretty simple when you join one field but how about a join on two columns with the subquery ?
For exemple
ReplyDeleteI 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
Thanks a lot!
ReplyDelete