Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: JPA Criteria Query multiple roots - optimize cross join
PostPosted: Mon Oct 16, 2017 4:20 pm 
Newbie

Joined: Wed Nov 26, 2014 5:56 am
Posts: 12
Hi

Consider the following entities (pseudocode to make is more concise)

Quote:
class A
{
@Id String id;
@ManyToOne B b; // Unidirectional association.

// Other fields ...
}

class B
{
@Id String id;

// Some more fields.
}

Suppose we want to select a B if (i) there exists an A that is associated to B and (ii) if A satisfies some other predicates (e.g. some field of A equals some value).

The way I'm currently expressing this as a JPA criteria query is as follows:

Code:
List<Predicate> ps = new ArrayList<>();
CriteriaQuery<B> q = builder.createQuery(B.class);
Root<A> rootA = q.from(A.class);
Root<B> rootB = q.from(B.class); // Second root creates cross join (cartesian product).
// Make cross join an inner join by adding a corresponding predicate on PKs.
ps.add(b.equal(rootA.get(A_.b).get(B_.id), rootB.get(B_.id)));
TypedQuery<B> tq = em.createQuery(
   q.select(rootB).where(ps.toArray(new Predicate[ps.size()]))
);
List<B> bs = tq.getResultList();

As far as I have tested it, the query can only be created starting from the cross join. This is because we want to select Bs, but joining A and B must start from A since the Criteria API allows to create joins only in the direction of associations (and we do not have an association from B to A). (Another point is that we want to create additional predicates over A.) The cross join of A and B is implicit in the creation of the two roots (as clearly documented in the AbstractQuery.from(..) method). Looking at the SQL query created by Hibernate I verified that there is indeed a cross join. It should be clear that we actually can and should use an inner join over A and B. This is achieved indirectly here by creating an "equals" predicate as shown in the code.

While I think that a database's query optimizer should be able to optimize the cross join into an inner join based on the additional predicate, I'm wondering whether this use case really has to be programmed that way or whether there exists a possibility to directly express an inner join. In fact, I came up with the following alternative that appears consistent to me but which resulted in runtime exceptions thrown by Hibernate (I'm Using Hibernate 5.2.10):

Code:
List<Predicate> ps = new ArrayList<>();
CriteriaQuery<B> q = builder.createQuery(B.class);
Root<A> rootA = q.from(A.class);
From<A,B> fromB = rootA.join(A_.b);
// add predicates ...
TypedQuery<B> tq = em.createQuery(
   q.select(fromB).where(ps.toArray(new Predicate[ps.size()]))
);
List<B> bs = tq.getResultList();

The exception thrown with this code is "org.hibernate.hql.internal.ast.ErrorCounter Invalid path: 'generatedAlias5.name'".


Top
 Profile  
 
 Post subject: Re: JPA Criteria Query multiple roots - optimize cross join
PostPosted: Tue Oct 17, 2017 1:10 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
You can easily do that with HQL which supports joining unrelated entities.

JPA Criteria does not support this yet. I guess this has to be addressed at JPA spec level first.

As for performance, you're right. The database engine optimizer will transform the CROSS JOIN + WHERE clause into an INNER JOIN.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: JPA Criteria Query multiple roots - optimize cross join
PostPosted: Tue Oct 17, 2017 6:10 am 
Newbie

Joined: Wed Nov 26, 2014 5:56 am
Posts: 12
Thanks Vlad for essentially confirming all my guesswork.

vlad wrote:

Yes I know. I want to use Criteria API because the query is pretty dynamic, actually: predicates are added or not depending on user input. If I'm not missing something, using HQL I would either need to (tediously) piece together the query or have a bunch of similar queries for each combination of the predicates.


Top
 Profile  
 
 Post subject: Re: JPA Criteria Query multiple roots - optimize cross join
PostPosted: Tue Oct 17, 2017 9:02 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
Using multiple HQL is probably better than concatenating String because you don't risk SQL injection attacks.

As long as the number of possible HQL variants is low, this is not a bad choice. However, if there are many possibilities, it's better to use jOOQ to build the native SQL and just pass it to Hibernate to fetch entities.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.