Help converting HQL to Criteria

Hanan · **Posted:** Thu Nov 29, 2007 8:17 am

Hibernate version: 3.0.2
Name and version of the database you are using: MySQL 5

I need help converting the below HQL to Criteria:

Code:

from BalanceItem i
where i.accountItem = :account and
      i.date = (select max(i2.date)
                from BalanceItem i2
                where i2.accountItem = :account and
                i2.date < :date)

Thanks in advance,

Hanan · **Posted:** Sat Dec 01, 2007 5:13 am

I found the answer and I am posting it here in case someone else will need it:

Code:

    DetachedCriteria tMaxDate = DetachedCriteria.forClass(BalanceItem.class).setProjection(Property.forName("date").max()).
            add(Restrictions.eq("accountItem", pAccountItem)).add(Restrictions.lt("date", pDate));
    return (BalanceItem)getCurrentSession().createCriteria(BalanceItem.class).add(Restrictions.eq("accountItem", pAccountItem)).
            add(Property.forName("date").eq(tMaxDate)).uniqueResult();