Hibernate Criteria query on multiple tables

Question

I have 3 tables named School, SchoolType and Country. You can see the relationship between School and SchoolType. What I want to query is;

Select countries which has same id with Schooltypes that has at least one school named "blabla".

My School class;

@JoinColumn(name = "school_type_ref", referencedColumnName = "school")
@ManyToOne(cascade = CascadeType.ALL, optional = false, fetch = FetchType.EAGER)
private SchoolType schooltype;

My SchoolType Class;

@LazyCollection(LazyCollectionOption.FALSE)
@OneToMany(cascade = CascadeType.ALL, mappedBy = "schooltype")
private List<School> schoolList;

private String id;

My Country Class;

private String id;

You want criteria query for: SELECT * FROM countries WHERE id IN (SELECT school_type_ref FROM schools WHERE name = 'blabla') ? Is Country related with SchoolType? — paulek, Sep 06 '13 at 09:06
@paulek I think this is the correct version: SELECT * FROM countries WHERE id IN (select type.id from SchoolType type join School scl on type.school = scl.schooltype and scl.name = 'blabla') — hellzone, Sep 06 '13 at 12:03

score 1 · Accepted Answer · edited May 23 '17 at 12:20

Check this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Country> c = cb.createQuery(Country.class);
Root<Country> countries = c.from(Country.class);

Subquery<Integer> sq = c.subquery(Integer.class);
Root<SchoolType> schoolTypes = sq.from(SchoolType.class);

sq.select(schoolTypes.<Integer>get("id")).where(
    cb.equal(schoolTypes.get("schoolList").get("name"), "blabla"));

c.select(countries).where(cb.in(countries.<Integer>get("id")).value(sq));

TypedQuery<Country> q = entityManager.createQuery(c);
List<Country> result = q.getResultList();

Also instead of schoolTypes.<Integer>get("id") you can try use schoolTypes.get(SchoolType_.id) but it never works for me.

My answer is basing on JPA 2.0, Criteria API, Subqueries, In Expressions

Hibernate Criteria query on multiple tables

1 Answers1