CODEWITHSUNDEEP
javasqloracle-databasejpaorm
slash3584
slash3584

Reputation: 93

Getting around queries that use UNION in JPA

So I'm migrating some services from and old application that used native queries to retrieve data and I've found some issues and would like to discuss what would be the best way to solve them.

The main problem is that this service uses a native query that encloses several other queries that are connected via UNION statement. Something like this:

WITH TMP_QUERY AS (
select p.id,p.name from PROCESS p inner join TABLE_B on ...
UNION
select p.id,p.name from PROCESS p inner join TABLE_C on ...
UNION
select p.id,p.name from PROCESS p inner join TABLE_A on ...)
select p.name from PROCESS p inner join TMP_QUERY tmp on p.id = tmp.id order 
by 1

And because JPA does not support the UNION statement the only solution I've thought about is to execute these queries separately and then do the duplicate removal, ordering and pagination in java code.

Maybe adding the results to a set in order to remove the duplicates and then apply a Comparator to order the returned entities. BUT the other issue is that I also require pagination because any of those queries could return from 10 to maybe 1000s of results which will lead to server timeout problems.

Has anyone dealt with something similar?

Upvotes: 4

Views: 9503

Answers (2)

user3869979
user3869979

Reputation: 21

Union/Union All will work if you change it to a 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

Upvotes: 0

Maciej Kowalski
Maciej Kowalski

Reputation: 26522

I think you could replace those UNION statements with subqueries and use JPQL / HQL to achieve the usage of persistence provider orm support:

select p.name as name 
from Process p  
where (p.id, p.name) in
    (
        select pp.id,pp.name from Process pp inner join pp.tableB b ...
    )   
    or (p.id, p.name) in
    (
        select pp.id,pp.name from Process pp inner join pp.tableC c ...
    )   
    or (p.id, p.name) in
    (
        select pp.id,pp.name from Process pp inner join pp.tableA a ...
    )

order by name

Upvotes: 3

Related Questions