-1

I want to update a date field in my table using CriteriaUpdate query.

I'm making this update based on the set of IDs filtered in where in clause.

This set of IDs can be greater than 1000, so in order to avoid ORA-01795(1000 limit in in clause), I'm partitioning the IDs list based into sub-lists of 999.

Here is my code:

@Modifying
public void updateDate(List<Long> ids, Date date) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaUpdate<MyTable> update = builder.createCriteriaUpdate(MyTable.class);
    Root root = update.from(MyTable.class);
    update.set("dateField", date);  // dateField present in MyTable entity.

    List<List<Long>> partitionedList = Lists.partition(ids, 999);
    List<Predicate> orPredicates = new ArrayList<>();
    partitionedList.forEach(partition -> {
        Predicate predicate = builder.or(root.get("id").in(partition));     // Id of MyTable entity
        orPredicates.add(predicate);
    });
    update.where(orPredicates.toArray(new Predicate[]{}));
    entityManager.createQuery(update).executeUpdate();
}

I was expecting generated query to be like:

update myTable set dateField=? where (id in (1, 2, 3.....9999)) OR ((id in (1000, 1001....))

But the generated query has AND instead of OR.

update myTable set dateField=? where (id in (1, 2, 3.....9999)) AND ((id in (1000, 1001....))

What am I missing?

sonic boom
  • 764
  • 4
  • 11
  • 26
  • 1
    Why do you believe that [`update.where(Predicate... restrictions)`](https://docs.oracle.com/javaee/7/api/javax/persistence/criteria/CriteriaUpdate.html#where-javax.persistence.criteria.Predicate...-) uses `OR` between the predicates? Javadoc says *"according to the **conjunction** of the specified restriction predicates"*, and "[conjunction](https://en.wikipedia.org/wiki/Logical_conjunction)" means `AND`. – Andreas Oct 04 '20 at 11:34
  • @Andreas, you're right. I missed this. Any suggestions on a suitable alternative? – sonic boom Oct 04 '20 at 11:38
  • What is the point of calling `builder.or()` with a single predicate? Telling the builder "here is a set of 1 predicate, please put `OR` between them (it) for me" seems rather ... meaningless. --- Seems you should remove `builder.or()` from inside the `forEach`, then apply it to the `orPredicates` array, and give that single predicate in the `where()` call. – Andreas Oct 04 '20 at 11:45

1 Answers1

3

Since you're using Java 8 stream logic, do it this way:

update.where(builder.or(
        Lists.partition(ids, 999).stream()
                .map(partition -> root.get("id").in(partition))
                .toArray()
        ));
Andreas
  • 154,647
  • 11
  • 152
  • 247