UNION to JPA Query

Question

Is it possible to query "UNION" in JPA and even "Criteria Builder"?

I'm looking for examples, but so far i got no result.

Does anyone have any examples of how to use it?

Or would that be with native sql?

Solution 1

SQL supports UNION, but JPA 2.0 JPQL does not. Most unions can be done in terms of joins, but some can not, and some are more difficult to express using joins.

EclipseLink supports UNION.

Solution 2

Depending on the case, one could use sub queries, something like:

select e
from Entity e
where e.id in
(
  select e.id
  from Entity2 e2
       join e2.entity e
  where e2.someProperty = 'value'
)
      or e.id in
(
  select e.id
  from Entity3 e3
       join e3.entity e
  where e3.someProperty = 'value2'
)

Solution 3

One thing just comes to my mind (searching for the exact same problem):

Perform two different JPA queries on the same entity mapping and simply add the objects of the second result to the list (or set to avoid duplicates) of the first result.

That way you get the same effect as with a UNION, the difference being that you use two SQL statements instead of one. But actually, I would expect that to perform just as good as issueing one UNION statement.

Solution 4

write native query (set it true , default its false) - ex.

String findQuery = "select xyz from abc union select abc from def"
@Query(value = findQuery, nativeQuery = true)
//method

Solution 5

using EntityManager.createNativeQuery(...); It's allow you use UNION

View more solutions

Comments

[danizmax]

possible duplicate of Union All and Sum with JPA CriteriaBuilder

[Chris]

wiki.eclipse.org/EclipseLink/UserGuide/JPA/… link to EL docs with examples

[user613114]

How to handle scenario if in subquery has more than 1000 results?

[szczepanpp]

For MS SQL it's over 2000 actually, which doesn't change the fact it's not going to work. You could fetch all those id's and then partition resulting list by the limit you want to set and then execute query for all sublists and join results. It's not that bad considering the way JPA works but then, as always, it depends on your use case.

[Ali Saeed]

genius!!! should be considered the best answer until JPQL supports unions

[kopelitsa]

Well, this is one of the obvious workarounds, but I also want to order the results all together for example and i prefer to do this in sql than in java

[takacsot]

Not even mentioning that certain usage of UNION is about avoiding OR (when tuning SQL performance). I personally have a sub-expression with OR what is very slow. After rewriting to use union it become magnitude faster. Now I have to find a solution to build it up with CriteriaBuilder :(

[cslotty]

But that was not the question, kopelitsa, and you can always use SQL and make views.

[webyildirim]

This 'or' solution might cause to block usage of some indexes unfortunatelly

[chaserb]

I disagree. @Giovane asked if UNIONS could be performed in JPA, and the fact that a UNION could be accomplished in a SQL view is beside the point. Not all JPA use cases have the latitude to require database owners to create views in their databases.

[Admin]

Errm nope. JPQL has no "UNION" as all answers tell you. The only way that a JPA provider will accept that is if it provides a VENDOR EXTENSION. And has the OP said what JPA provider is used? Nope.

[Sml004]

but we cannot do pagination.

[Thomas]

The problem with over 1000 values (can be configured) does not apply with subqueries in Oracle. It only occurs when using parameters.

[Motolola]

This is a solution I had considered, but I guess there will be multiple database connections which will mean lesser performance, but with UNION ALL, you only throw you SQL query once.

[Motolola]

True, I don't know why this is voted down, but it is a very valid option, especially in terms of performance.

[cslotty]

@Motolola That's right, but on the other hand can a UNION ALL be very expensive in database systems. The connection shouldn't be a problem as long as you use connection pools.

[Motolola]

@cslotty I haven't put both methods in a comparative tests, but my guess is one big call with "UNION ALL" should perform better than multiple smaller calls (considering multiple connections as well) on the overall app-database system.

[cslotty]

@Motolola It really depends on the individual database system and the regarding context, and needs to be compared. Any assumption on the outcome is speculative as long as it can't be compared using JPA. I can only say from my experience, that UNION ALL could even be more costly than joining arrays in code, when using a connection pool.

[Motolola]

@cslotty definitely speculative, I used a "UNION ALL" on a certain project that requires more than 1000 lists to be sent to Oracle, although the overall consensus from my team was to avoid multiple calls when possible, but i 'll admit we never put it to a benchmark test, so it is a speculation as you have mentioned.

[Nikolai Shevchenko]

@Motolola maybe it's because the Answer is about JPA and not about Native queries?

[cslotty]

@Sml004 Right - for pagination, again, you would have to make a proper view and then query the view. This is always an option, and it always depends on the use case, which solution you choose.