Hibernate criteria: Joining table without a mapped association

Question

I'd like to use Hibernate's criteria api to formulate a particular query that joins two entities. Let's say I have two entities, Pet and Owner with a owner having many pets, but crucially that association is not mapped in the Java annotations or xml.

With hql, I could select owners that have a pet called 'fido' by specifying the join in the query (rather than adding a set of pets to the owner class).

Can the same be done using hibernate criteria? If so how?

Thanks, J

Pierre Pretorius · Answer 1 · 2012-12-21T07:03:20.830

79

This is indeed possible with criteria:

DetachedCriteria ownerCriteria = DetachedCriteria.forClass(Owner.class);
ownerCriteria.setProjection(Property.forName("id"));
ownerCriteria.add(Restrictions.eq("ownername", "bob"));

Criteria criteria = getSession().createCriteria(Pet.class);
criteria.add(Property.forName("ownerId").in(ownerCriteria));

Update: This actually performs a sub-query instead of a join but it allows you to use Criteria on two entities that do not have a hibernate relationship defined.

edited Dec 21 '12 at 07:03

answered Jan 20 '10 at 14:39

Pierre Pretorius

2,789
18
21

2

why there are so many up-votes? It still two different criteria right? – Reddy Mar 10 '11 at 09:04
37

Problem is this is not a join, but subquery - that means that you can't order your results by a column from first criteria. – Martin Lazar May 11 '11 at 08:13
1

I guess it becomes sub-query instead of a join – Reddy Jul 12 '11 at 13:12
I had to do a query between a table and a view and there was no explicit association. This worked fine. – Angela Jul 10 '12 at 04:07
How do i retrieve the result? Can i get it as List> ? – Trind Jun 18 '13 at 21:07
Agree with Jonas Stensved. – Ankit Sep 05 '14 at 06:08
I think @Pierre is right, although we should ALL be careful when using detached criterias or subqueries. Sometimes, we only need to join those tables in order to filter the results and not to retrieve the joined entity. Check the question. Fortyrunner asked how to filter owners with restrictions over unmapped relationships, not how to make an inner join. – coya Nov 23 '16 at 01:58
If owner and pet is mapped in java, are these codes doing join query? – Jiayi Zhou Dec 06 '16 at 01:49

score 62 · Accepted Answer · answered Apr 06 '09 at 10:20

62

My understanding is that if you do this using HQL, you are creating a Cartesian join with a filter, rather than an inner join. Criteria queries do not support doing this.

answered Apr 06 '09 at 10:20

David M

68,374
13
151
181

David is correct on this, you cannot do this with a Criteria you can do it with HSQL – Gareth Davis Apr 06 '09 at 17:05
1

Does anyone know if it is still impossible to use criteria queries to join unmapped entities? – Peter Kirby Jun 12 '14 at 14:07
I think it is possible using HQL `with ` clause – Suganthan Madhavan Pillai Jan 19 '15 at 15:57
@Suganthan No it's not possible, just tested at work as we had similar issue. – Tarik Aug 21 '15 at 20:12
@DavidM an upvote because this is clear and correct, and because I really don't understand why the downvote! – Tarik Aug 21 '15 at 20:14
@Tarik, its fine, but long back I had a similiar experience where I can able to solve the issue using `with` clause – Suganthan Madhavan Pillai Aug 22 '15 at 08:57
@Suganthan yes, functionally. But it still generate a CROSS JOIN... anyway that was long time ago for you to remember :) – Tarik Aug 22 '15 at 15:16

score 1 · Answer 3 · answered Apr 15 '09 at 13:40

In NHibernate you can use subqueries which are defined as DetachedCriteria. Not sure if it works the same in Java, most probably it is the same:

DetachedCriteria pets = DetachedCriteria.For<Pet>("pet")
  .SetProjection(Projections.Property("pet.ownername"))
  .Add(/* some filters */ );

session.CreateCriteria(typeof(Owner))
  .Add(Subqueries.PropertyIn("name", pets);

Assumed that it is joined using the name of the owner.

score 0 · Answer 4 · answered May 20 '15 at 20:13

0

Criterion ownerCriterion = Restrictions.sqlRestriction(SELECT ownerId FROM   Owner WHERE ownerName ='bob');
Criteria criteria = getSession().createCriteria(Pet.class);
criteria.createCriteria("ownerId").add(ownerCriterion);

answered May 20 '15 at 20:13

javasmith

11
2

score -2 · Answer 5 · edited Mar 01 '16 at 13:46

-2

There's a SQLCriterion, which you can give arbitrary SQL, and add to your Criteria. In the SQL string, the token {alias} "will be replaced by the alias of the root entity."

edited Mar 01 '16 at 13:46

Tom11

2,199
6
26
50

answered Apr 06 '09 at 11:38

tpdi

32,745
10
74
117

2

how to do this? any example? – iPhoneJavaDev Sep 03 '14 at 08:51
No example or (at least) link provided. – n3k0 Jul 24 '15 at 21:29
SQLCriterion has protected constructor – AndreyT Aug 12 '15 at 12:57

Hibernate criteria: Joining table without a mapped association

5 Answers5

Linked

Related