How to join unrelated entities with the JPA Criteria API

Question

Two database tables have a foreign key relationship.

They are mapped to two entities A and B by JPA, but the join columns are manually removed from the entities, so in JPA world classes A and B are not related and you cannot navigate from one to the other through a field/property.

Using the JPA Criteria API, is it possible to create a query which joins the two tables?

All examples I found on internet uses the join column to achieve the goal, but, as stated above, it was removed from the code because most time I'm not interested in the relationship between A and B and I'm afraid about possible overhead.

Annecy

Answer 1

First: Foreign key relationship are not only for navigating. They mainly serve to ensure that no spurious values are introduced in the relationship. They also may help the database for query optimization. I would advise you to reconsider that.

Anyway, for creating a query that uses several unrelated entities, you need to put them as from (root) entities (as you would do in SQL or JPQL)

SELECT .... FROM Link l, Training t WHERE l.attribute = t.attribute;

Root<Link> rootLink = criteriaQuery.from(Link.class);
Root<Training> rootTraining = criteriaQuery.from(Training.class);
...
criteriaQuery.where(
    criteriaBuilder.equal(rootLink.get(link_.linkAttribute), trainingLink));

Frankfort

Worth noting that using multiple roots creates a cartesian product (i.e. a CROSS JOIN) between the entities. With the WHERE condition, this behaves the same as an INNER JOIN (including same performance as noted here). – Clerkly

I don't understand this answer. Where does trainingLink come from? Wouldn't I want something like criteriaBuilder.equal(rootLink.get(link_.linkAttribute), rootTraining.get(training_.linkAttribute (which doesn't work with criteria api)? – Egression

can you also please expand ..., to select different columns from two tables Link and Training – Potiche

How can I do an outer join with unrelated entities? This might be required if the other table has some optional data and I still want a result even if the data is not there. See this question: #71321379 – Larina

Nice but this doesn't work with PostGreSQL which requires an 'ON' clause :-( – Highhanded

Answer 2

Since Hibernate 5.1, you can join unrelated entities when using JPQL and HQL:

Tuple postViewCount = entityManager.createQuery("""
    select p as post, count(pv) as page_views
    from Post p
    left join PageView pv on p.slug = pv.slug
    where p.title = :title
    group by p
    """, Tuple.class)
.setParameter("title", "Presentations")
.getSingleResult();

However, this feature is not available in Criteria API, as that would require an API extension.

The solution

While you could try to use two Root objects and emulate the JOIN via WHERE clause predicates, the resulting SQL is not the best way to solve this issue.

You should consider using jOOQ as, besides giving you the ability to join tables in any possible way, you can also fetch entities if you pass the generated SQL query to the JPA createNativeQuery method.

Bihar

Answer 3

Join on unrelated entities is not covered by latest JPA spec (2.1)

However Hibernate 5.1.0+ and EclipseLink 2.4.0+ support ad hoc joins. http://blog.anthavio.net/2016/03/join-unrelated-entities-in-jpa.html

Another possibility is native query http://www.oracle.com/technetwork/articles/vasiliev-jpql-087123.html

Overlying

The first link is broken. Could you please provide an alternative link, or (better) give us a summary of what the link told us to do? – Berns

Answer 4

You could use the JPA Criteria implementation of Blaze-persistence that translates to JPQL/HQL and offers that feature as extension to the JPA Criteria API.

Look for "Blaze-Persistence JPA-Criteria module dependencies" in the readme: https://github.com/Blazebit/blaze-persistence/blob/master/README.md

Then use BlazeCriteriaBuilder cb = BlazeCriteria.get(criteriaBuilderFactory) and finally blazeCriteriaQuery.getCriteriaBuilder(entityManager) to render the JPA Criteria Query object.

Ancelin

Answer 5

It's possible to create view for join-query and create entity for this view and JpaRepository can be utilized similarly as for usual entity for table. And if you need to build dynamically where-clause, it's still feasible to build it on top of this view-entity

Monanthous

Answer 6

Better use good old JPQL/native query when faced with complex situation such as above. Criteria queries tend to get super complicated and inefficient as soon as someone talks about joins or composite primary keys and and so does jpa entity mappings with all the JoinColumns, JoinTable, PrimaryKeyJoinColumn etc etc.

Cradling