CODEWITHSUNDEEP
javasqljpaunion-all
Kevin Rave
Kevin Rave

Reputation: 14436

Union in JPA query - from the same table

I have a requirement where I need to restrict number of records returned from a table for a particular flag and all records for the other flag value.

For example: contact-history table has an element called IorM_flg with possible values 'm' and 'o'.

I need to return only 10 records of 'o' and all records of 'm'.

I was about to write a query with union for this. Something like this:

select ch from contact_history where ch.rownum <= 10 and ch.IorM_flg = 'o'
Union All
select ch from contact_history where ch.IorM_flg != 'o'

Is this possible? Note that its a JPA query. (contact_history is object name)

Any other better suggestions welcome!

Upvotes: 4

Views: 23125

Answers (3)

user3869979
user3869979

Reputation: 21

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

Upvotes: 0

James
James

Reputation: 18379

JPA does not support UNION, but if you use EclipseLink, UNION is supported in JPQL. Also the COLUMN operator can be used to access special columns like rownum.

See, http://java-persistence-performance.blogspot.com/2012/05/jpql-vs-sql-have-both-with-eclipselink.html

Upvotes: 2

Mikko Maunu
Mikko Maunu

Reputation: 42094

No, this is not possible with JPQL, because it does not have UNION (ALL). Additionally there is no way to limit amount of rows returned in query string itself with rownum, but that is done via setMaxResults.

In many situations

  • executing two queries,
  • limiting number of results in first one with setMaxResults, and
  • discarding duplicates and combining results of both queries in Java

is viable solution.

Upvotes: 1

Related Questions