9

I am trying to convert a native SQL query to use the Criteria API in JPA 2.0. I have found a lot of Criteria API examples on Google, but I am having a really hard time putting all of the pieces together. I'm hoping that a more experienced person will be able to help me out. The native query looks like this:

select 
    sum(amount) from firstTable, secondTable 
        where firstTable.id = secondTable.id 
            and amount <> 0 
            and firstTable.id = ?
union all
select 
    sum(amount) from firstTable, thirdTable 
        where firstTable.id = thirdTable.id 
            and amount <> 0 
            and firstTable.id = ?

The original query result set was returning a List of BigDecimal objects.

Thank you!

user1148956
  • 303
  • 1
  • 3
  • 9
  • 1
    I had the same problem to solve, with this I could make the union https://stackoverflow.com/a/55302501/5486945 – yOshi Mar 22 '19 at 15:13
  • You can produce the effect of union with JPA/JPQL but at the cost of poor performance with subqueries and OR. https://stackoverflow.com/a/25135559/1627524 – Alex Jun 30 '20 at 10:13

4 Answers4

18

JPA does not support UNION, either use a native SQL query, or execute two queries.

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

Sorry the below example is not union, it is rather a join.

I would consider using query with multiple roots.

Here is an exerpt from Hiberante developer guide, the code is JPA 2.0-compatible.

Criteria queries may define multiple roots, the effect of which is to create a cartesian product between the newly added root and the others. Here is an example matching all single men and all single women: 

CriteriaQuery query = builder.createQuery();
Root<Person> men = query.from( Person.class );
Root<Person> women = query.from( Person.class );
Predicate menRestriction = builder.and(
        builder.equal( men.get( Person_.gender ), Gender.MALE ),
        builder.equal( men.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
Predicate womenRestriction = builder.and(
        builder.equal( women.get( Person_.gender ), Gender.FEMALE ),
        builder.equal( women.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
query.where( builder.and( menRestriction, womenRestriction ) )
Alex
  • 185
  • 7
  • How does select clause looks here? I am using criteriaBuilder.construct to fetch selected columns. – user613114 Aug 08 '16 at 15:06
  • to @user613114: I haven't used ORM and Java for several years, yet I'll try to answer. The above example will give you all possible combinations of single man and single woman. The SQL query will look like: select a.name b.name from PERSONS a, PERSONS b where a.gender = 'male' and b.gender = 'female' and a.status = single and b.status = single – Alex Feb 28 '17 at 17:31
0

If you need Union All, then two entities are candidates to apply inheritance to them in my mind.

Marcin Cinik
  • 105
  • 5
0

As Marcin noted it is possible with inheritance, at least in special cases.

If you can make queried entities inherit one from another using TABLE_PER_CLASS inheritance strategy (provided optionally, possible in hibernate, but has limitations there - you cannot use IDENTITY and AUTO) and if the field to be present in the query has the same name in both entities then you can unite both entities by querying the parent one.

For instance, if entity class Child extends from entity class Parent both having field "name", query would be :

select p.name from Parent p

To select only names of Parent entities add condition with TYPE.

select p.name from Parent p where TYPE(p) = Parent

If modifying entities to inherit one from another is inappropriate, you can create special ones (with inheritance, of cause) for this query, since it is possible to create several entities for the same table. But you likely don't find that effort reasonable.

Alex
  • 185
  • 7