Wednesday, 11 July 2012

Hibernate nested query using criteria

Heres an example to show how to do a nested query in Hibernate using the criteria api.

Say you have 2 domain objects, that look like this.

class Car{
    public Integer showRoomFk;  //<-- refers to the showRoom 
    public Integer regYear;
    public String make;
    public String model; 

    //getters and setters;
}


class ShowRoom{
    public Integer showRoomPk; //<-- showRoomPk 
    public String location;

    //getter and setters
}

Here a car can be displayed in a given showroom. We want to find show rooms which contain cars registered in the year 2010.

Step 1:
Create detached criteria that finds all cars that have the regYear of 2010. Make the criteria return only the showRoomFk for cars found.

DetachedCriteria carCriteria = DetachedCriteria.forClass(Car.class);
carCriteria.add(Restrictions.eq("regYear", "2010"));
carCriteria.setProjection(
Projections.projectionList().add( Projections.property("showRoomFk")));

Step 2:
Create a criteria that gets all showRoomFk's returned from the criteria above and use them to
return show room objects.

Criteria criteria = aSession.createCriteria(ShowRoom.class, "showRoom");
criteria.add(Subqueries.propertyIn("showRoom.showRoomPk", carCriteria));
return criteria.list();

No comments:

Post a Comment