JPA Hibernate Specification

Question

I am having following sql that needs to be transitioned to JPA specs:

select 
* from  TableA a
    cross join TableB b
    cross join (
        select distinct refAId from TableC c where c.name like 'Your_NAME'
        ) as T
where
T.refAId = a.id

How can I form the cross join T to the root TableA here in terms of entities? So basically in the specification implementation I get CriteriaBuilder as Root root, CriteriaQuery<?> cq, CriteriaBuilder cb. Now how should i proceed ahead to map the root to the Query defined table T mentioned above?

Above query very efficient than the following:

select 
* from  TableA a
    cross join TableB b
where
a.id in (
select refAId from TableC c where c.name like 'Your_NAME'
)

Why it is more efficient because the scan c.name like 'Your_NAME' happenes only once in the first query.

crizzis crizzis · Accepted Answer · 2021-03-10T16:11:45

The query can be rewritten in the following way:

SELECT * 
FROM TableA a, TableB b, TableC c
WHERE c.name LIKE 'Your_NAME' 
AND c.refAId = a.id

The Criteria query then becomes:

var tableA = cq.from(TableA.class);
var tableB = cq.from(TableB.class);
var tableC = cq.from(TableC.class);
cq.where(cb.and(
    cb.equal(tableA_.id, tableC_.refAId), 
    cb.equal(tableC_.name, "YOUR_NAME")
));
cq.multiselect(...);

Is there no association between TableA and TableC entities, though? If there was one, you could simply do:

var tableC = cq.from(TableC.class);
var tableB = cq.from(TableB.class);
var tableA = tableC.join(TableC_.refA);
...

JPA Hibernate Specification

1 Answers