代码之家 › 专栏 › 技术社区 › Renaud is Not Bill Gates

使用JPA标准API选择子查询作为字段

jpa-criteria jpa sql java

Renaud is Not Bill Gates · 技术社区 · 7 年前

我有以下SQL查询:

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;

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

但这对我不起作用,而是生成以下SQL查询:

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;

将引发以下SQL异常:

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

如何解决这个问题?

编辑:

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

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;

0 回复 | 直到 7 年前