2

my question is how i perform UNION ALL clause in JPA ? is it possible to use @NamedQuery? this query using 2 tables at a time in each SELECT statement and at the end it is using ORDER BY 

    SELECT 0 as intorder, u.id, q.order_type
      FROM test_union u, test_query q
     WHERE u.id     = q.id
       AND u.type   is NULL
       AND q.field1 = 'DEFAULTS'
UNION ALL
  SELECT 1 as intorder, u.id, q.order_type
      FROM test_union u, test_query q
     WHERE u.id     = q.id
       AND u.type   is NOT NULL
       AND q.field1 = 'TESTING'
 UNION ALL
  SELECT 2 as intorder, u.id, q.order_type
      FROM test_union u, test_query q
     WHERE u.id     = q.id
       AND u.type   is NULL
       AND q.field1 = 'DEFAULTS'
ORDER BY intorder, q.order_type;
Sangram Badi
  • 4,054
  • 9
  • 45
  • 78

2 Answers2

3

Since Jpql and Hql (one is the subset of the other, and JPA uses Jpql) are not supporting UNION, you would need to use native queries suited to your database query language. An implementation could look like this:

@Entity
@NamedNativeQuery(
    name="EntityClassName.functionName"
    , query="SELECT .. "
        + "UNION ALL "
        + "SELECT .."
    , resultClass=YourResultClass.class`
)

directly on one of your entities and in your repository interface:

public interface EntityClassNameRepository extends JpaRepository<EntityClassName, Long> {

    @Query(nativeQuery = true)
    List<YourResultClass> functionName(parameter list)

When I was doing something equal YourResultClass needed to be an entity.

Another solution would be to extract the UNION out of the database into the JVM, where multiple query results enhance a list. Afterward the list also could be sorted (How to sort a ArrayList in Java?).

Community
  • 1
  • 1
KLHauser
  • 856
  • 5
  • 11
0

Use each query separately. like 

SELECT 0 as intorder, u.id, q.order_type
      FROM test_union u, test_query q
     WHERE u.id     = q.id
       AND u.type   is NULL
       AND q.field1 = 'DEFAULTS'

then you can pass each queries separately, then you can merge these result in to one result.