Inner select using Criteria API

mr_burns · **Joined:** Thu Jan 24, 2008 11:18 am **Posts:** 1

Hello Hibernate support,

we would like to translate this SQL call:

Code:

SELECT FULLJOBNAME, TIMESTAMP, STATUS
FROM   EM_JOBSTATUS as s1
WHERE  FULLJOBNAME LIKE '/jobs/25/%' AND TIMESTAMP=(SELECT MAX(s2.TIMESTAMP)
    FROM EM_JOBSTATUS as s2
    WHERE s1.FULLJOBNAME = s2.FULLJOBNAME)

into Criteria API call.

We are starting with:

Code:

Criteria c =sessionFactory.getCurrentSession().createCriteria(PlainJobStatusBO.class);
c.add(Restrictions.like("fullJobName", jobNameBase+"%"));
c.add(Restrictions.eq("timestamp", ???));

PlainJobStatusBO mapping is:

Code:

<hibernate-mapping>
    <class name="com.service.PlainJobStatusBO" table="EM_JOBSTATUS">
        <id name="id" type="java.lang.Integer" access="field">
            <column name="id" />
            <generator class="native"></generator>
        </id>
        <version name="version" type="java.lang.Integer" access="field">
            <column name="version" />
        </version>
        <property name="fullJobName" type="java.lang.String" access="field">
            <column name="fullJobName" length="64" />
        </property>
        <property name="timestamp" type="java.util.Date" access="field">
            <column name="timestamp" />
        </property>
        <property name="status" type="java.lang.String" access="field">
            <column name="status" length="16" />
        </property>
    </class>
</hibernate-mapping>

Thanks in advance!

Hibernate version: 3.3.0.ga

Name and version of the database you are using: DB2 9.1

farzad · **Posted:** Thu Jan 24, 2008 12:05 pm

Your only way might be to use Restrictions.sqlRestriction() instread of Restrictions.eq(). Criteria API will take care of translating aliases if you need to use any.

Farzad-

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

try to avoid the subquery

Code:

select max(timestamp), fulljobname, status
where .fulljobname like ...
group by fulljobname, status

which I believe would translate into somethng like this, using Projections

Code:

List results = session.createCriteria(PlainJobStatus.class)
    .setProjection( Projections.projectionList()
        .add( Projections.max("timestamp"), "timestamp" )
        .add( Projections.groupProperty("fulljobname"), "fulljobname" )
        .add( Projections.groupProperty("status"), "status" )
    )
    .add( Restrictions. ... )
    .list();