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.

score 32 · Answer 1 · edited Jul 11 '17 at 09:50

32

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));

edited Jul 11 '17 at 09:50

Steve Chambers

37,270
24
156
208

answered Jun 14 '13 at 10:34

SJuan76

24,532
6
47
87

5

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](https://stackoverflow.com/questions/670980#671023)). – Steve Chambers Jul 11 '17 at 09:58
19

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)? – Jakob Apr 06 '18 at 08:48
can you also please expand ..., to select different columns from two tables Link and Training – vijay Oct 12 '19 at 15:42
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: https://stackoverflow.com/questions/71320879/left-outer-join-of-unrelated-entities-with-criteria – T3rm1 Jul 29 '22 at 07:51
Nice but this doesn't work with PostGreSQL which requires an 'ON' clause :-( – Christophe Moine Dec 26 '22 at 09:18

Vlad Mihalcea · Answer 2 · 2023-02-23T13:38:27.653

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.

score 4 · Answer 3 · answered Mar 18 '16 at 15:04

4

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

answered Mar 18 '16 at 15:04

anthavio

2,518
1
18
10

2

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? – slartidan Nov 26 '19 at 11:47

score 0 · Answer 4 · answered Sep 21 '19 at 06:15

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.

score 0 · Answer 5 · answered Jun 03 '21 at 07:43

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

score 0 · Answer 6 · answered Jan 06 '23 at 10:43

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.

How to join unrelated entities with the JPA Criteria API

6 Answers6

The solution

Linked