Simple Query help using session.createCriteria

itsmeprash · **Joined:** Thu Jun 01, 2006 8:51 am **Posts:** 6

Hi All,

I need help in writing a simple query. Just not able to do it. I am breaking my head and just don't know how to do this using Criteria.
I have a Project and this project has multiple tasks. I have defined a set for the task.

Code:

<set name="tasks"
   cascade="all-delete-orphan"
   lazy="true"
   inverse="true"
   access="field" order-by="CREATED_DATE_ desc">
   <key>
      <column name="PROJID_" not-null="true"/>
   </key>
   <one-to-many class="Task"/>
</set>

If it was a sql query then i would have done.

select
project.ID_,
project.NAME_,
project.STARTDATE_,
max(task.DUEDATE_)
from
PROJECT project, TASK task
where
project.id_ = task.PROJID_
group by project.ID_, project.name_, project.startdate_

I am using hibernate 3.1.3. I could do this with a named query but there is no resulttransformer for a Query in 3.1.3 and it is in 3.2. But since 3.2 is still not production ready i would like to do it using a Criteria in version 3.1.3.

Thanks in advance
Prashanth

CWitherow · **Posted:** Wed Aug 23, 2006 12:54 pm

How about a criteria like this:

Code:

List results =
    session
        .createCriteria(Project.class, "proj")
        .createAlias("proj.tasks", "t")
        .setProjection(
                Projections.projectionList()
                    .add(Projections.groupProperty("id"), "projId")
                    .add(Projections.groupProperty("name"), "projName" 
                    .add(Projections.groupProperty("startDate"), "projStartDate")
                    .add(Projections.max("t.dueDate"), "projMaxDate"))
        .setResultTransformer(new AliasToBeanResultTransformer(ResultsBean.class))
        .list

You need a class to store the transformed results in:

Code:

public class ResultsBean implements Serializable {
    private Long projId;
    private String projName;
    private Date projStartDate;
    private Date projMaxDate;

// Need setters/getters ...
}

NOTE that I used different property names in ResultsBean than in source objects -- I've gotten bad SQL if they were the same.

I had this running using v3.1.3

Good luck
Curtis ...

itsmeprash · **Joined:** Thu Jun 01, 2006 8:51 am **Posts:** 6

Bless you. I did not know that we could do a

Code:

createAlias("proj.tasks", "t") on a set.

Thanks
Prashanth

itsmeprash · **Joined:** Thu Jun 01, 2006 8:51 am **Posts:** 6

Thanks Curtis for the reply.

You mentioned you used a different name (alias for the column names)other than the one from the Project Entitiy itself.

What i usually do is

Code:

session.createCriteria(Project.class, "project")
    .createAlias("project.tasks", "task", Criteria.LEFT_JOIN)
    .setProjection(Projections.projectionList()
       .add(Projections.groupProperty("project.id"), "id")
       .add(Projections.groupProperty("project.name"), "name")
       .add(Projections.groupProperty("project.startDate"), "startDate")
       .add(Projections.max("task.dueDate"), "estmtdEndDate")
    )
    .setResultTransformer(new AliasToBeanResultTransformer(Project.class))
    .addOrder(Order.asc("startDate"))
    .list();

This works just fine. But does this mess up my Hibernate Cache that caches the objects. Like in this case my Project cache.

Thanks
Prashanth