7

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?

Daniel
  • 75
  • 4

1 Answers1

2

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.

James
  • 17,965
  • 11
  • 91
  • 146
  • Thanks for the quick response. Unfortunately, using the `CriteriaBuilder.literal()` method does not seem to do the trick; in fact, it produces SQL that's even worse: – Daniel Mar 05 '13 at 09:38
  • `WHERE (((?, ?, ?, ?) IN (?, ?, ?, ?, ...)` - then it proceeds to bind the actual path and entity objects. The `Root.in()` method was almost doing the right thing, and it intuitively seems like this should be what `Root.in()` should do, after all, a Root always represents an Entity, right? Unfortunately, EclipseLink 2.5 is not an option for this project, as we're close to release (just trying to cleanly deal with a duplicate record problem here), but I'll give it a spin and see if it does the trick some time. If not, I'll file a bug as suggested. – Daniel Mar 05 '13 at 09:49
  • I ended up solving this by iterating over the list and adding a long series of ORed expressions containing ANDed comparisons for the various PK columns - exactly what I was hoping to avoid, really. It's pretty ugly and produces the largest amount of parentheses I've ever seen in one place, but it works. Since you suggested this as the "standard way", I'm marking your answer as accepted. – Daniel Mar 05 '13 at 17:57