Criteria Query on Sakila Database

Question

I am new to Hibernate and Criteria Query. I want to perform operations on sakila database. I want a Criteria query for the following SQL query:

select c.city, cr.country 
from city c 
left join country cr 
on c.country_id = cr.country_id;

Answer 1

Let's say you have OneToMany uni-directional mapping between your Country and City like this:

@Entity
public class Country {

    @OneToMany(cascade=CascadeType.ALL)
    @JoinColumn(name = "country_id")
    private Set<City> cities = new HashSet<City>();
}

Then you can create Criteria query like this:

    Criteria criteria = getSession().createCriteria(Country.class);
    criteria.createAlias("cities","c",JoinType.LEFT_OUTER_JOIN);

    // This line removes the duplicate results of root entity - `Country`
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

    List<Country> countries = criteria.list();