CODEWITHSUNDEEP
javajpacriteria-api
John Doe
John Doe

Reputation: 1414

JPA Criteria API NOT IN Code Simplification and Optimization

I am new to JPA Criteria API.

2 Questions:

  1. How can I further simplify this method, 3rd and 4th statements both needed the class parameter?

  2. How can I translate the NOT IN operation to NOT EXISTS to improve performance?

    public List<Pilot> getPilotsExcept(final Integer... entityIds) {

  final CriteriaBuilder critBuilder = em.getCriteriaBuilder();

  final CriteriaQuery<Pilot> critQuery =
        critBuilder.createQuery(getEntityClass());

  final Root<Pilot> root = critQuery.from(getEntityClass());  // Why pass the class again?
critQuery.select(root).where(
    critBuilder.not(root.get("id").in(Arrays.asList(entityIds))));

  final TypedQuery<Pilot> typedQuery = em.createQuery(critQuery);
  return typedQuery.getResultList();
}

Upvotes: 1

Views: 605

Answers (1)

ujulu
ujulu

Reputation: 3309

Why pass the class again?

In Criteria API query components are represented by objects. So select clause and the from clause are represented by objects:

  • The select clause is represented as follows:

    CriteriaQuery<Pilot> critQuery = critBuilder.createQuery(getEntityClass());

    The Pilot class in the above line represents the return type of the query result.

  • And on the other hand the from clause of the query is defined as follows:

    final Root<Pilot> root = critQuery.from(getEntityClass());

In this example, both the Root and CriteriaQuery happen to have the same type. But this is not the case for all queries. In some queries you might want to return one or more attributes from the query (but not all attribute of the entities involved in a query). In those cases, the type you specify in the CriteriaQuery will be different from the one you use in the Root. For example, let us say you have a name attribute in your Pilot entity and you are only interested in the list of names of the Pilot entities. In this case, your CriteriaQuery will be defined as follows:

    CrtieriaQuery<String> cq = cb.createQuery(String.class);

But the definition of the Root does not change, in your example.

NOT EXISTS expression

To be able to convert your not in expresion to not exists form you have to formulate a subquery using the ids. Here is an extract from the JPA specification:

An EXISTS expression is a predicate that is true only if the result of the subquery consists of one ormore values and that is false otherwise. The syntax of an exists expression is

exists_expression::= [NOT] EXISTS (subquery) Of course, this must be translated to the Criteria API, in which case the syntax will be

not(exists(subquery)).

Here is an example for creating a subquery (pseudo code):

Subquery<Integer> sq = critQuery.subquery(Integer.class);
Root<Pilot> pilot = sq.from(Pilot.class);
sq.select(pilot.<Integer>get("id"));
sq.where(<some_condition>);

Upvotes: 1

Related Questions