-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 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: 1630
Location: Romania
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.


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: 1630
Location: Romania
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 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.