JPA 2 criteria query, where on property list class

Question

I'd like to achieve the query below using criteria typed query:

SELECT
    a.*
FROM
    table_a a
    INNER JOIN table_b b ON a.id= b.id
WHERE
    b.url = 'value.org';

Criteria query:

    final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    final CriteriaQuery<A> criteriaQuery = criteriaBuilder.createQuery(A.class);
    final Root<A> root = criteriaQuery.from(A.class);
    root.fetch(A.domains); // domains is a List<B>
    criteriaQuery
            .select(root);

Entity A:

  @OneToMany
  List<B> domains;

Entity B:

  @Column(name="URL")
  String url;

My issue is how to reproduce where, I've tried something like:

 .where(criteriaBuilder.equal(root.get(A.domains).. ??))

but I don't know how to continue, because proposal clause are as(), in(..), etc..

Any hints?

Please go through https://stackoverflow.com/questions/29145958/jpa-criteria-builder-onetomany-restrictions — Vivek, Feb 12 '20 at 17:31

snieguu · Accepted Answer · 2020-02-13T09:05:21.623

First of all, You don't need to fetch as you only want to get a. Second, You are almost good only important missing part is join. Here is code that should work for You:

public A loadAForValueOrg() {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<A> query = builder.createQuery(A.class);
    Root<A> a = query.from(A.class);
    Join<A, B> b = a.join("domains");
    query.select(A);
    query.where(builder.equal(b.get("url"), "'value.org'"));
    TypedQuery<A> typedQuery = entityManager.createQuery(query);
    typedQuery.setMaxResults(1);
    List<A> result = typedQuery.getResultList();
    return result.isEmpty() ? null : result.get(0);
}

Few considerations:

setMaxResults is not need, only good practice for performance
If no result we return null so it is good candidate for Java 8 Optional
Also I recommended use metamodel to not operate on strings(its are error-prone)

about Java8 Optional, with JPA 2.2 you can also use .getResultStream(). So the code would be entityManager.createQuery(criteriaQuery) .getResultStream() .findAny() .orElse(null); — Fabrizio Stellato, Feb 13 '20 at 09:02

JPA 2 criteria query, where on property list class

1 Answers1