Java – standard JPA 2 and 3 tables

I'm trying to create a condition to retrieve some objects from three tables (Association, update and detail) Details refer to association and update, which refers to the details list My goal is to retrieve an updated list of details with at least null values in the specified field given an association identifier It's easy to do this in jpql, but the client says it must be encoded in standard

My jpql is:

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

Can anyone help me? I searched, but I couldn't find any examples with 3 entities

Solution

Each connection can pass your type parameter from the left to the rightmost parameter Therefore, the detail connection of my code (line 2) starts from fromupdates, which is a path < update >, and creates some behind the scenes paths < detail > From here, you can build other connections 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)
);