标准JPA 2有3个表 [英] Criteria JPA 2 with 3 tables

问题描述

我正在尝试创建一个标准来从3个表中检索一些对象（关联，更新和详细信息）。详细信息引用了关联和更新，更新引用了详细信息列表。我的目标是在给定Associate id的情况下检索在指定字段中至少具有null值的Detail的更新列表。在JPQL很容易做，但客户说这必须用标准编码。

I'm trying to create a criteria to retrieve some objects from 3 tables (Associate, Update and Detail). A Detail has reference to Associate and Update, and an Update has reference to a list of Details. My objective is to retrieve a list of Updates that has at least a Detail with null value in a specified field, given an Associate id. In JPQL was easy to do but the client said that this must be coded with criteria.

我的JPQL是：

public List<Update> getUpdates(long associateId) {
    TypedQuery<Update> query = em.createQuery("select distinct u from Update u, Detail dt, Associate a "
        + "where dt.update = u and dt.associate = a and a.associateId = :id and "
        + "dt.ack_date is null", Update.class);
    query.setParameter("id", associateId);
    return query.getResultList();
}

我尝试了以下内容，但它只返回了数据库中的所有更新：

I tried the following, but it just returned all updates in the database:

public List<Update> getUpdates(long associateId) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Update> query = builder.createQuery(Update.class);

    Root<Update> fromUpdates = query.from(Update.class);
    Root<Associate> fromAssociate = query.from(Associate.class);
    Root<Detail> fromDetail = query.from(Detail.class);

    Join<Detail, Associate> associateJoin = fromDetail.join("associate");
    Join<Detail, Update> updateJoin = fromDetail.join("update");

    TypedQuery<Update> typedQuery = em.createQuery(query

            .select(fromUpdates)
            .where(builder.and(
                    builder.equal(fromAssociate.get("associateId"), associateId),
                    builder.equal(fromDetail.get("associate"), associateJoin),
                    builder.equal(fromDetail.get("update"), updateJoin),
                    builder.isNull(fromDetail.get("ack_date"))
            ))

            .orderBy(builder.asc(fromUpdates.get("updateId")))
            .distinct(true)
    );

    return typedQuery.getResultList();
}

任何人都可以帮助我吗？我搜索但找不到任何有3个实体的例子。

Can anyone help me? I searched but can't find any example with 3 entities.

推荐答案

每个连接都会从左边的类型参数转到右边的一。所以，详细信息加入我的代码（第二行）从 fromUpdates 开始，即路径<更新> ，并在幕后创建路径< Detail> 。从那以后，您可以构建其他联接。试试这个（代码未测试）：

Each join takes you from the leftish type parameter to the rightish one. So, the details join of my code (second line) starts from fromUpdates, that is a Path<Update>, and creates something which is behind the scenes also a Path<Detail>. From that, you can build other joins. Try this (code not tested):

Root<Update> fromUpdates = query.from(Update.class);
Join<Update, Detail> details = fromUpdates.join("details");
Join<Detail, Associate> associate = details.join("associate");
List<Predicate> conditions = new ArrayList();
conditions.add(builder.equal(associate.get("associateId"), associateId));
conditions.add(builder.isNull(details.get("ack_date")));

TypedQuery<Update> typedQuery = em.createQuery(query
        .select(fromUpdates)
        .where(conditions.toArray(new Predicate[] {}))
        .orderBy(builder.asc(fromUpdates.get("updateId")))
        .distinct(true)
);

这篇关于标准JPA 2有3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！