CODEWITHSUNDEEP
sqldatabasespringjakarta-eejpa
user1912130
user1912130

Reputation: 31

Questions about query several tables through JPA

Recently, I need to use JPA in my project. However, I encountered some difficulties. JPA seemes to be harder than I thought. So, I post my problem here, hoping that, some guys may help me.

There are two tables operation and analysis, the sql is like the following:

select op_id from operation where flag = 1 
union
select an_id from analysis where on_type = "processed";

op_id and an_id are two column names actually means the same.

Can anyone help me writing a JPA version of this? I'm very grateful of that. Thank you very much.

Upvotes: 1

Views: 98

Answers (1)

gaborsch
gaborsch

Reputation: 15758

JPQL does not support unions.

You have four options:

  1. You can create a View in your DB that would do the unioning, then map to that view
  2. Write a native SQL that does the union (since you only need the ID, this is a valid option)
  3. If the IDs are distinct, you can run two queries, and concatenate the result list (if not distinct, you could use Sets to filter duplications)
  4. If you are using the latest (at least >=2.4) EclipseLink as a JPA provider, you can use UNION

See this question here.

Upvotes: 1

Related Questions