Some random JPA pitfalls

There are some custom errors that are usually made by even people who are not quite novice in building JPA 2.0 queries.

One of the most common mistake is the use of isNull() and isNotNull() functions.
The other day I spent a considerable amount of time for debugging a code that contained the following:

...
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> criteriaQuery = cb.createQuery(Foo.class);
Root<Foo> root = criteriaQuery.from(Foo.class);
Predicate predicate = cb.conjunction();
predicate=cb.and(predicate, cb.isNull(root.get(Foo_.amount)));
...
cb.where(predicate);
...

In this case, the generated SQL did not include the part "AND foo.amount = null". This is because this expression should be used in other cases, such as a CASE expression, and preferably contains constants.

The correct form of the predicate is this:

predicate=cb.and(predicate, root.get(Foo_.amount).isNull());

The resulting SQL now contains the foo.amount=null part, so the query finally works. The hard part is it is not so trivial (at least for me) from looking at just the source code, but if the debug is available, and the generated SQL can be tracked, then this is not a big deal to point out the mistake.

This applies to isNotNull() and in(Collection<e>) functions as well. 

Another common pitfall is to try to make two different queries with the same predicate, in a way that one creates a predicate for a query, executes the query, and tries to do another select with the previously created predicate instance. A typical use-case is a count and a select query, where the same filters are applied.

Unfortunately this cannot be achieved for predicates that have different root instances.

The third is not quite a mistake, but can easily be one. This is listed here basically for prevention.

What is the difference in the following queries (which can go wrong):

TypedQuery<Long> randomSelectForSingleResult = em.createQuery(cq);

int result = randomSelectForSingleResult.getSingleResult();

TypedQuery<Long> randomSelectForSingleResult = em.createQuery(criteriaQuery);

List<Long> resultList = randomSelectForSingleResult.getResultList();

int result = 0;

if(resultList.size() != 0)

    result = resultList.get(0);

The problem is that if the first type of selection does not contain EXACTLY one element, then it throws an exception. Although this may not happen if the selection is a count, otherwise it may occur, so if there is the tiniest, slightest chance that the query returns more or less than one results, the second attempt should be used.