How does one construct multi-column "WHERE ... IN" expressions using JPA's criteria API?

Question

For an Entity with a multi-column ID, we need to persist all those objects in a given List that aren't already present in the DB. Since the number of objects to check is large, and the number of objects in storage can be extremely large, the idea is to select the existing objects from the List using their multi-column private key and a "WHERE ... IN (...)" type statement that is constructed using the criteria API, so that they can be removed from the List before persisting.

Here's the code that attempts to do this:

    public void persistUnique(List<CdrEntity> cdrs) {
        // find all the CDRs in the collection that are already in the DB
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<CdrEntity> query = criteriaBuilder.createQuery(CdrEntity.class);
        Root<CdrEntity> cdrRoot = query.from(CdrEntity.class);
        query.select(cdrRoot).where(cdrRoot.in(cdrs));
        List<CdrEntity> nonUnique = entityManager.createQuery(query).getResultList();
        // remove nonUnique elements from crds and persist
        ...
    }

However, at least using EclipseLink 2.4.1, this is what's actually sent to the DB (some output elided for readability):

[EL Fine]: sql:...--SELECT SUBINDEX, ... FROM CDRS WHERE ((?, ?, ?, ?) IN ((?, ?, ?, ?), (?, ?, ?, ?), ...))
bind => [null, null, null, null, 2, 1362400759, 19415, 176, ...]

Essentially, where the appropriate column names for the primary key columns should be, a number of parameters are added and later bound (with the value null). Apart from that, the query is fine, and if the first four ?s are replaced by the actual column names, executes with the desired result.

It appears however that calling .in(...) directly on a Root does not have the desired result. If using the Entities directly is not possible, I would expect there to be some sort of Expression that can represent multiple columns which could then be the receiver for a call to .in(...), but I haven't been able to find any.

So, the question is: how does one do this properly? Or is it not possible at all with JPA? Or is there simply a bug in EclipseLink?

Answer 1

Interesting attempt, and odd SQL that was generated, the SQL looks like it got the left side correct, but not the right. Please log a bug for this, it is something that could be supported. (also try it in the 2.5 dev build, it may work).

The JPA Criteria API does not support nested arrays, so anything this is beyond the JPA spec.

You could create a list of path expressions for the id fields for the left side and use the CriteriaBuilder literal() expression for this, it might work.

The JPA standard way to do this would be to iterate over the list of objects and or() in an expression for each object's id comparison.