All geek questions in one place

Union on JPA request - from the same table

I have a requirement when I need to limit the number of records returned from a table for a certain flag and all records for a different flag value.

For example: the contact-history table has an element named IorM_flg with possible values ​​of "m" and "o".

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

I was going to write a join query for this. Something like that:

 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 it possible? Please note that this is a JPA request. (contact_history - object name)

Any other best offers are welcome!

+4
source share
2 answers

No, this is not possible with JPQL because it does not have UNION (ALL). In addition, there is no way to limit the number of rows returned in a query string using rownum, but this is done using setMaxResults .

In many situations

  • two queries
  • limiting the number of results in the first using setMaxResults and
  • dropping duplicates and combining the results of both queries in Java

is a viable solution.

+1
source

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

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

+2
source

Source: https://habr.com/ru/post/1485666/

More articles:


All Articles