Subquery SELECT as a field using JPA Criteria API

Question

I have the following SQL query:

select
        A.A_ID,
        B.Lib,
        A.Lib,
        C.Lib,
        (SELECT count(*) FROM X WHERE A.A_ID = X.A_ID) AS countX,
        (SELECT count(*) FROM Y WHERE A.A_ID = Y.A_ID) AS countY,
        (SELECT count(*) FROM Z WHERE A.A_ID = Z.A_ID) AS countZ
    from
        A
    left outer join
        C
            on A.C_ID=C.C_ID
    left outer join
        B
            on A.B_ID=B.B_ID;

I want to create this query using the JPA Criteria API, so I did as following :

final CriteriaBuilder builder = getCriteriaBuilder();
final CriteriaQuery<A_DTO> criteriaQuery = builder.createQuery(A_DTO.class);
final Root<A> aRoot = criteriaQuery.from(A.class);

// LEFT OUTER JOIN B
Join<A, B> bJoin = aRoot.join(A_.bID, JoinType.LEFT);
// LEFT OUTER JOIN C
Join<A, C> cJoin = aRoot.join(A_.cID, JoinType.LEFT);

// (SELECT count(*) FROM X WHERE A.A_ID = X.A_ID) AS countX
final Subquery<X> xSubquery = criteriaQuery.subquery(X.class);
final Root<X> xRoot = xSubquery.from(X.class);
xRoot.alias("countX");
xSubquery.select(xRoot);
xSubquery.where(builder.equal(xRoot.get(X_.a).get(A_.aID), aRoot.get(A_.aID)));

// (SELECT count(*) FROM Y WHERE A.A_ID = Y.A_ID) AS countY
final Subquery<Y> ySubquery = criteriaQuery.subquery(Y.class);
final Root<Y> yRoot = ySubquery.from(Y.class);
yRoot.alias("countY");
ySubquery.select(yRoot);
ySubquery.where(builder.equal(yRoot.get(Y_.a).get(A_.aID), aRoot.get(A_.aID)));

// (SELECT count(*) FROM Z WHERE A.A_ID = Z.A_ID) AS countZ
final Subquery<Z> zSubquery = criteriaQuery.subquery(Z.class);
final Root<Z> zRoot = zSubquery.from(Z.class);
zRoot.alias("countZ");
zSubquery.select(zRoot);
zSubquery.where(builder.equal(zRoot.get(Z_.a).get(A_.aID), aRoot.get(A_.aID)));

// Selection
criteriaQuery.multiselect(aRoot.get(A_.aID),
        bJoin.get(B_.lib),
        aRoot.get(A_.lib),
        cJoin.get(C_.lib),
        builder.count(xRoot),
        builder.count(yRoot),
        builder.count(zRoot));

return getEntityManager().createQuery(criteriaQuery);

But this didn't work for me, and this generates the following SQL query instead:

select
    a0_.aID as col_0_0_,
    b2_.Lib as col_1_0_,
    a0_.Lib as col_3_0_,
    c1_.Lib as col_4_0_,
    count(countX) as col_5_0_,
    count(countY) as col_6_0_,
    count(countZ) as col_7_0_
from
    A a0_
left outer join
    C c1_
        on a0_.cID=c1_.cID
left outer join
    B b2_
        on a0_.bID=b2_.bID;

Which will throw the following SQL exception:

WARN o.h.e.j.s.SqlExceptionHelper - SQL Error: 904, SQLState: 42000

ERROR o.h.e.j.s.SqlExceptionHelper - ORA-00904: "countZ": invalid identifier

How can I solve this issue ?

Edit:

I have resolved this using the following code :

final CriteriaBuilder builder = getCriteriaBuilder();
final CriteriaQuery<A_DTO> criteriaQuery = builder.createQuery(A_DTO.class);
final Root<A> aRoot = criteriaQuery.from(A.class);

// LEFT OUTER JOIN B
Join<A, B> bJoin = aRoot.join(A_.bID, JoinType.LEFT);
// LEFT OUTER JOIN C
Join<A, C> cJoin = aRoot.join(A_.cID, JoinType.LEFT);

// (SELECT count(*) FROM X WHERE A.A_ID = X.A_ID) AS countX
final Subquery<Long> xSubquery = criteriaQuery.subquery(Long.class);
final Root<X> xRoot = xSubquery.from(X.class);
final Expression<Long> xCount = builder.count(xRoot);
xSubquery.select(xCount);
xSubquery.where(builder.equal(xRoot.get(X_.a).get(A_.aID), aRoot.get(A_.aID)));

// (SELECT count(*) FROM Y WHERE A.A_ID = Y.A_ID) AS countY
final Subquery<Long> ySubquery = criteriaQuery.subquery(Long.class);
final Root<Y> yRoot = ySubquery.from(Y.class);
final Expression<Long> yCount = builder.count(yRoot);
ySubquery.select(yCount);
ySubquery.where(builder.equal(yRoot.get(Y_.a).get(A_.aID), aRoot.get(A_.aID)));

// (SELECT count(*) FROM Z WHERE A.A_ID = Z.A_ID) AS countZ
final Subquery<Long> zSubquery = criteriaQuery.subquery(Long.class);
final Root<Z> zRoot = zSubquery.from(Z.class);
final Expression<Long> zCount = builder.count(zRoot);
zSubquery.select(zCount);
zSubquery.where(builder.equal(zRoot.get(Z_.a).get(A_.aID), aRoot.get(A_.aID)));

// Selection
criteriaQuery.multiselect(aRoot.get(A_.aID),
        bJoin.get(B_.lib),
        aRoot.get(A_.lib),
        cJoin.get(C_.lib),
        xSubquery.getSelection(),
        ySubquery.getSelection(),
        zSubquery.getSelection());

return getEntityManager().createQuery(criteriaQuery);

But when I wanted to select results depending on the count value or sort the result using it, I had to change the query as following:

WITH cte AS ( select
        A.A_ID,
        B.Lib,
        A.Lib,
        C.Lib,
        (SELECT count(*) FROM X WHERE A.A_ID = X.A_ID) AS countX,
        (SELECT count(*) FROM Y WHERE A.A_ID = Y.A_ID) AS countY,
        (SELECT count(*) FROM Z WHERE A.A_ID = Z.A_ID) AS countZ
    from
        A
    left outer join
        C
            on A.C_ID=C.C_ID
    left outer join
        B
            on A.B_ID=B.B_ID)
    SELECT * FROM cte 
    WHERE countX > 2 
    ORDER BY countY, countZ DESC;

Now I have no idea how to create this Common Table Expression in JPA Criteria API.

What JPA implementation were you using? – Filou Aug 27 '21 at 05:30 — Filou, Aug 27 '21 at 05:30

Subquery SELECT as a field using JPA Criteria API

Edit:

0 Answers0