CODEWITHSUNDEEP
jpa-2.0jpqlcriteria-api
JProgrammer
JProgrammer

Reputation: 321

jpa 2.0: how to do a sql minus of a collection with a table

I am looking for a JPQL or Criteria expression to find a subset of integers(codes) in my collection which are not in the table.

Native Query for Oracle DB would be:

SELECT column_value
FROM Table(:listOfCodes)
MINUS
SELECT code
FROM table1;

This is similar to the post here but not exactly what i want jpa 2.0 criteria api expression for sql minus

Any pointers/help would be much appreciated.

Upvotes: 0

Views: 3064

Answers (1)

JB Nizet
JB Nizet

Reputation: 692003

You could just query for the values that are in the table and in your set, and then take the complement of what the query returns:

List<Integer> valuesInTheTable = 
    (List<Integer>) em.createQuery("select a.column from Entity a where a in (:values)")
                      .setParameter("values", values)
                      .getResultList();

Set<Integer> valuesNotInTheTable = new HashSet<Integer>(values);
valuesNotInTheTable.removeAll(valuesInTheTable);

Upvotes: 1

Related Questions