Union All and Sum with JPA CriteriaBuilder

Question

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!

Answer 1

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

Answer 2

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 ) )