Hibernate transform SQL to Criteria

Question

Hello,

I 'd like to use Criteria for my SQL query. I have 3 tables "home", "person" and a third table "liveIn" for correspondance between home and person.

My sql query is "select home.id from home, person, liveIn where home.country = 'Japan' and person.id = '15' and liveIn.Homeid = home.id and liveIn.PersonId = person.id"

A little help anybody ?

Answer 1

Assuming you have the tables mapped as entities Home, Person and LiveIn then something like this might work :

          session.createCriteria(Home.class)
                .add(Restrictions.eq("country", "Japan"))
                .createAlias("person", "p")
                .add(Restrictions.eq("p.id", "15"))
                .list();

Answer 2

If you have the object reference of the person object, you could use that in your criteria query instead of having to search for the id of the person.

For example:

public List<Home> getHomesForPerson(Person thePerson){

    List<Home> homes = session.createCriteria(Home.class)
                          .add(Restrictions.eq("country", "Japan")
                          .add(Restrictions.eq("person", thePerson)
                          .list();
    return homes;
}