Hibernate Criteria join query one to many

Question

I have a Cat class and a Owner class. A cat has one owner but an owner can have many cats. What I want to query is get all owners who have a cat with blue eyes.

class Cat {
    Owner owner; //referenced from Owner.id
    String eyeColor;
}

class Owner {
    List<Cat> catList;
}

I tried some codes but I really don't know what to do.

Criteria criteria = getCurrentSession().createCriteria(cat.getClass(), "cat");
criteria.createAlias("cat.owner", "owner");    
criteria.add(Restrictions.eq("cat.eyeColor", "blue");

Is the association bidirectional? Why not use HQL for such a static query? — JB Nizet, Jul 17 '13 at 13:35
Is the association bidirectional? Why don't you have the choice? That's like a carpenter not allowed to use a hammer! — JB Nizet, Jul 17 '13 at 13:43
can't use HQL in case query is too much complex, its better to write criteria query, also it handles all the type conversions like converting localDate type to timezone format , etc. Writing HQL is a pain in the ass especially when you have 100s of filters in the query for where clause, code is a managed way of writing long queries and easily maintainable in the long run. — user1735921, May 23 '18 at 20:15

score 48 · Accepted Answer · answered Jul 17 '13 at 14:03

48

Criteria can only select projections, or the root entity. Not some joined entity. Some queries are thus impossible to express with Criteria (which is one more good reason to use HQL, in addition to much better readability and conciseness).

All is not lost here, though, because your association is bidirectional. So you just need the equivalent of the HQL query

select distinct owner from Owner owner 
join owner.cats cat 
where cat.eyeColor = 'blue'

Which is

Criteria c = session.createCriteria(Owner.class, "owner");
c.createAlias("owner.cats", "cat");
c.add(Restrictions.eq("cat.eyeColor", "blue");
c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

answered Jul 17 '13 at 14:03

JB Nizet

678,734
91
1,224
1,255

In my case, the cat knows the owner, but owner doesn't know about his cats. How would the criteriaquery look like? – Kayser Dec 11 '14 at 14:33
As said in my answer, AFAIK (unless they made it possible in newer releases), it's not possible using criteria. Use HQL, or use the JPA2 criteria API. – JB Nizet Dec 11 '14 at 15:06
This does in-memory "distinct" operation. If one owner has one million blu-eyed cats, it will fetch them all from the database, just to find the single owner (the example isn't probably the askers real case, you don't know the real cardinality). See my answer as how it could be done in database. – Oliv Mar 12 '15 at 07:11
its in memory but it does the trick, but I hope it could have been done via query without using HQL, but looks like hibernate doesn't support it – user1735921 May 23 '18 at 20:17

Oliv · Answer 2 · 2018-05-24T06:43:08.933

4

Try this:

DetachedCriteria dc = DetachedCriteria.forClass(Cat.class, "inner")
    .add(Restrictions.eq("eyeColor", "blue"))
    .add(Restrictions.eqProperty("inner.owner", "outer.id"));

session.createCriteria(Owner.class, "outer")
    .add(Subqueries.exists(dc))
    .list();

This can use index in the database and won't do an in-memory distinct operation as in the @JB Nizet's version (see my comment there). The index will be:

CREATE INDEX idx_cat_owner_eyecolor ON Cat(fkOwner, eyeColor)

Think of distinct operation (either in SQL or in memory) as of a code smell. It is rarely used and many novice programmers use it to fix the problem "why do I have this row twice". It can almost always be rewritten such as in this case. Use cases, when it is necessary, are few.

edited May 24 '18 at 06:43

answered Mar 12 '15 at 07:10

Oliv

10,221
3
55
76

in outer.pk, what is pk? – user1735921 May 23 '18 at 19:44
this query doesn't work and throws null pointer exception.. JBNizet's query is working good. although I know its not exactly a query is code base distinct not query based distinct but atleast it works. – user1735921 May 23 '18 at 19:50
@user1735921, could you share the NPE's stack trace? I believe you did some other mistake. `outer.pk` is the primary key, maybe I should have used `outer.id`, I edited – Oliv May 24 '18 at 06:41

Hibernate Criteria join query one to many

2 Answers2