4

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!

Kevin Rave
  • 13,876
  • 35
  • 109
  • 173
  • If it's possible I would recommend not to use `JPA`, since it's very limited and applicable only to quite simple `O/R` mappings. To have more flexibility I would recommend using `Apache dbUtils` or Spring's `JdbcTemplate` which are similar. Also, there is `JOOQ` which looks like good alternative but I haven't tried it deeper. – Yuriy Nakonechnyy Nov 19 '13 at 15:50

3 Answers3

2

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

James
  • 17,965
  • 11
  • 91
  • 146
1

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.

Mikko Maunu
  • 41,366
  • 10
  • 132
  • 135
0

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

user3869979
  • 21
  • 1
  • 6