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?

Kulturkampf

possible duplicate of Union All and Sum with JPA CriteriaBuilder – Myeshamyhre

Answer 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.

Inflectional

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

Answer 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'
)

Tenfold

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

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. – Tenfold

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 :( – Beguin

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

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

Answer 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.

Worst

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

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 – Burglarious

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

I disagree. @Kulturkampf 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. – Cuenca

but we cannot do pagination. – Risarise

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. – Homage

@Homage 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. – Worst

@Worst 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. – Homage

@Homage 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. – Worst

@Worst 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. – Homage

@Risarise 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. – Worst

Answer 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

Roaring

Can i return an pageable result like a Page<MyCustomDataDto>? – Disengage

Answer 5

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

Spelt

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

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

Answer 6

There is no direct union for JPA, what I did was to build two specifications.

Specification<Task> specification = Specification.where(null);
Specification<Task> specification2 = Specification.where(null;

They belong to a single table but return different values

specification = specification.and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.equal(criteriaBuilder.function(MONTH, Integer.class, root.get("deliveryExpirationDate")), month));
        specification2 = specification2.and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.lessThan(criteriaBuilder.function(MONTH, Integer.class, root.get("deliveryExpirationDate")), month))
            .and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.equal(root.get("enable"), true));

for this example it is a table of tasks that in the first specification I need the tasks of the current month enabled and disabled, and in the second specification I only need the tasks enabled of the previous months.

Specification<Task> specificationFullJoin = Specification.where(specification).or(specification2);

Esto es muy útil para que la lista de tareas devueltas tenga paginación.

taskRepository.findAll(specificationFullJoin, pageable).map(TaskResponse::new); //Here you can continue adding filters, sort or search.

It helps me a lot, I hope it is what they are looking for or that it serves them something.

Overture

Answer 7

I have solved this in my project.

Union/Union All will work if you change it to native query and use like below

//In Your Entity class
@NamedNativeQuery(name="EntityClassName.functionName", 
query="your_native_query")

//In your Repository class
@Query(native=true)
List<Result> functionName();

Below method of defining Native query in JPA repository will not solve this problem

@Query(value="your_native_query", native=true)

will not

Haeckel

Answer 8

You can directly use UNION in your query. The following query returns id of friends from FRIENDSHIP table.

Eg:

TypedQuery<String> queryFriend = em.createQuery("SELECT f.Id FROM Friendship f WHERE f.frStuId = :stuId UNION "
                                 + "SELECT f.frStuId FROM Friendship f WHERE f.FriendId = :stuId", String.class);
queryFriend.setParameter("stuId", stuId);

List<String> futureFriends = queryFriend.getResultList();

Booklover

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. – Douma