1

I have

@Entity
public class Entity0 implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;
    @OneToOne
    private Entity1 entity1;
    ...
}

@Entity
public class Entity1 implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;
    @OneToMany
    private List<Entity2> entity2s;
    ...
}

@Entity
public class Entity2 implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;
    @ManyToOne
    private Entity3 entity3;
    ...
}

@Entity
public class Entity3 implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;
    @Basic
    private String property0;
    ...
}

(where ... stands for parameterless constructor, constructor with all arguments as well getter and setters for all properties) for which I have the following working query which works fine with Hibernate 5.2.12.Final (JPA 2.1):

Entity3 entity3 = new Entity3(1l, "some property value");
TypedQuery<Long> query = entityManager.createQuery(
        "SELECT COUNT(e0) FROM Entity0 e0 LEFT JOIN e0.entity1 e1 "
                + "WHERE e0.entity1 != null "
                + "AND NOT EXISTS (SELECT e2 FROM e1.entity2s e2 LEFT OUTER JOIN e2.entity3 e3 WHERE e3.id = :someId)",
        Long.class)
        .setParameter("someId", entity3.getId());
    //join is necessary in order to be able to access
    //e0.entity1.entity2s because JPA isn't capable of
    //accessing a reference of a reference
long retValue = query.getSingleResult();

Now, I want to transform the query to be executed through the JPA Criteria API, but I don't seem to figure out how to perform the join in the subquery which involves attributes as I only find Root.join and a Root always refers to an entity represented by a class. This results in the following attempt:

EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("richtercloud_jpa-subquery-attribute-join_jar_1.0-SNAPSHOTPU" //persistenceUnitName
);
EntityManager entityManager = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<Entity0> entity0Root = cq.from(Entity0.class);
Join<Entity0, Entity1> entity0Join = entity0Root.join(Entity0_.entity1,
        JoinType.LEFT);
Root<Entity2> entity2JoinRoot = cq.from(Entity2.class);
    //can only specify a Class here while selecting from a join is
    //possible outside criteria api as well

//        Join<Entity2, Entity3> entity2Join = cq.join(Entity1_.entity2s)
//                .get(Entity1_.entity2s)
//                .join(Entity2_.entity3);

Subquery<Entity2> entity2Subquery = cq.subquery(Entity2.class);
Root<Entity2> entity2SubqueryRoot = entity2Subquery.from(Entity2.class);
entity2Subquery.where(cb.equal(entity2SubqueryRoot.get(Entity2_.entity3), entity3));
cq.where(cb.and(cb.notEqual(entity0Root.get(Entity0_.entity1), cb.nullLiteral(Entity1.class))),
        cb.not(cb.exists(entity2Subquery)));
TypedQuery<Long> criteriaQuery = entityManager.createQuery(cq);
long retValue = criteriaQuery.getSingleResult();

(only the subquery needs to be fixed).

According to some posts like JPA/Metamodel: Strange (inconsistent ?) example in Sun Docs (which explains that the Oracle authors were so thoughtful to list impossible examples in their tutorials, but that's another issue), there's CriteriaQuery.join, but that's simply not the case afaik.

I'd be interested if JPA 2.2 or (2.3 if there're any plans for it already) would solve problems in case there're any besides me not finding a solution.

Since this is an exercise for me to deepen my understanding (the JPQL query already works), I'm looking for a quotable reference why this is not possible because of a limitation in the criteria API if that's the case.

If you care to investigate further, a SSCCE can be found at https://gitlab.com/krichter/jpa-subquery-attribute-join which saves you the time to create the entity classes.

MWiesner
  • 7,913
  • 11
  • 31
  • 66
Karl Richter
  • 6,271
  • 17
  • 57
  • 120

0 Answers0