Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA Criteria API: LEFT JOIN for optional relationships

I'm using the Criteria API basically the first time. It's about abstracting queries for a generic builder:

public TypedQuery<T> newQuery( Manager<?,T> manager )
{
    CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();

    Class<T> genericClass = ( Class<T> ) ( ( ParameterizedType ) manager.getClass().getGenericSuperclass() ).getActualTypeArguments()[1];

    CriteriaQuery<T> criteriaQuery = builder.createQuery( genericClass );
    Root<T> root = criteriaQuery.from( genericClass );

    ...
}

The call criteriaQuery.from( genericClass ); generates SQL INNER JOIN for all relationships found on the entity by default. This is a problem, because every relationship being null (DB NULL or a DB that doesn't use foreign keys and has an invalid reference) those entities will be missing in the result list, effectively producing wrong search results.

An example can be found here: JPA Criteria query Path.get left join is it possibile

What I'd like to happen for queries instantiated by this class/method is that all relationships on the entity, here genericClass, that are mapped as optional = true

@ManyToOne( FetchType.EAGER, optional = true )
@JoinColumn( name = "CLOSE_USER_ID", referencedColumnName = "USER_ID" )
private User              closer;

to generate an SQL LEFT (OUTER) JOIN instead of INNER JOIN.

Question:

Is there standard JPQ way to get this done? If so, how?

PS: there's generally no way to know the concrete type beforehand, so the only way I might be able to achieve what I need is to use the metamodel of some sort and generate the joins manually (which I'd like to avoid).


We are using EclipseLink 2.3

like image 573
Kawu Avatar asked Sep 03 '13 13:09

Kawu


People also ask

What is JPA Criteria API?

Java Prime Pack The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax. Similar to JPQL it follows abstract schema (easy to edit schema) and embedded objects.

How join works in JPA?

First of all, JPA only creates an implicit inner join when we specify a path expression. For example, when we want to select only the Employees that have a Department, and we don't use a path expression like e. department, we should use the JOIN keyword in our query.

What is a CriteriaQuery root?

For a particular CriteriaQuery object, the root entity of the query, from which all navigation originates, is called the query root. It is similar to the FROM clause in a JPQL query. Create the query root by calling the from method on the CriteriaQuery instance.

What is predicate in JPA?

The type of a simple or compound predicate: a conjunction or disjunction of restrictions. A simple predicate is considered to be a conjunction with a single conjunct. Since: Java Persistence 2.0.


2 Answers

.from(class) does not use an INNER join for all relationships, it only queries the class.

A relationship will only be queried if you use the join() or fetch() API, to use an outer join use join() with a JoinType.LEFT.

https://en.wikibooks.org/wiki/Java_Persistence/Criteria#Join

I'm not sure why you are seeing joins if you are not calling join(). Some JPA providers automatically join fetch all EAGER relationships, this may be what you are seeing. I have always though this odd, perhaps your JPA provider has a away to be configured not to do this, or you can make the relationships LAZY.

like image 185
James Avatar answered Sep 18 '22 21:09

James


I had the same issue... After investigation the conclusion is That you hâve to handle this with left join in your jpql See that: http://www.objectdb.com/java/jpa/query/jpql/path#Navigation_through_a_NULL_value_

like image 22
Steph Avatar answered Sep 17 '22 21:09

Steph