Criteria JPA 2 with 3 tables

Question

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.

My JPQL was:

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 can't find any example with 3 entities.

Answer 1

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