Traversing queries that use UNION in JPA
So, I am migrating some services from an old application that used its own queries to fetch data, and I found some problems and would like to discuss what would be the best way to solve them.
The main problem is that this service uses its own request, which includes several other requests related to the instruction UNION
. Something like that:
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 since it JPA
doesn't support the operator UNION
, the only solution I thought of was to execute these queries separately and then remove duplicates, ordering and paging in Java code.
Possibly adding results to a set to remove duplicates and then applying Comparator
to order the returned objects. BUT the other problem is that I also need pagination because any of these queries could return from 10 to maybe 1000 results, leading to server timeout issues.
Has anyone dealt with something similar?
source to share
I think you could replace these UNION statements with subqueries and use JPQL / HQL to achieve persistence support 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
source to share