JPA bulk delete with multi columns primary key

Question

Given the following entity :

@Entity(name = "Object")
@Table(name = "OBJECT_TABLE")
public class ObjectEntity {
    @EmbeddedId
    private ObjectEntityPk id;

    @Column(name = "SOME_ATTIBUTE")
    private String someAttribute;

    ... Getters/Setters
}

And the following primary key object :

@Embeddable
public class ObjectEntityPk {
    @Column(name = "id1")
    private String id1;

    @Column(name = "id2)
    private Long id2;

    ... Getters/Setters
}

I have 50 ObjectEntity to delete and I want to avoid to loop on it and execute 50 delete requests. So I wrote the following code in my dao to generate a delete query like DELETE FROM OBJECT_TABLE WHERE (id1, id2) IN ((someId1, someId2), (someOtherId1, someOtherId2), ...)

public void deleteObjects(final List<ObjectEntity> objects) {
    CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
    CriteriaDelete<ObjectEntity> deleteCriteria = criteriaBuilder.createCriteriaDelete(ObjectEntity.class);
    Root<ObjectEntity> rootObject= deleteCriteria.from(ObjectEntity.class);

    Expression<ObjectEntityPk> expression = rootObject.get("id");
    Predicate predicate = expression.in(objects.stream().map(ObjectEntity::getId).collect(Collectors.toList()));
    deleteCriteria.where(predicate);
    this.entityManager.createQuery(deleteCriteria).executeUpdate();
}

Except I have a problem with the generated query. The form is correct, but it doesn't bind correctly column names. See the query I get.

 DELETE FROM OBJECT_TABLE WHERE ((?, ?) IN ((?, ?), (?, ?), (?, ?), (?, ?), ...)) 
 bind => [null, null, 0d287cacc63ff01b5222bf0bb2b2c794, 8105793672, 222a1399065f41b10814d88690c32bcf, 8105793874, 0b0c2f8e73475759872eb97a96f942a3, 8105794177, ...]

I don't understand why my column names are not binded. Did I miss something on the Entity or Pk ?

Thank's in advance.

PS : Generated queries are executed on Oracle database

EDIT -> SOLVED USING @Guenther SOLUTION

The problem is solved by constructing the same request with multiple predicates.

public void deleteObjects(final List<ObjectsEntity> objects) {
    CriteriaDelete<ObjectEntity> deleteCriteria = this.entityManager.getCriteriaBuilder()
                    .createCriteriaDelete(ObjectEntity.class);

    Root<ObjectEntity> rootObject = deleteCriteria.from(ObjectEntity.class);

    List<Predicate> predicates = objects.stream().map(n -> generateAndPredicate(n, rootObject))
                    .collect(Collectors.toList());

    Predicate predicate = this.entityManager.getCriteriaBuilder().or(predicates.toArray(new Predicate[predicates.size()]));

    deleteCriteria.where(predicate);

    this.entityManager.createQuery(deleteCriteria).executeUpdate();
}

private Predicate generateAndPredicate(final ObjectEntity object,
    final Root<ObjectEntity> rootObject) {
    Predicate id1Equal = this.entityManager.getCriteriaBuilder().equal(rootObject.get("id").get("id1"),
                    object.getId().getId1());
    Predicate id2Equal = this.entityManager.getCriteriaBuilder().equal(rootObject.get("id").get("id2"),
                    object.getId().getId2());
    return this.entityManager.getCriteriaBuilder().and(id1Equal, id2Equal);
}

The final request look like DELETE FROM OBJECT_TABLE WHERE ((ID1 = someId1 AND ID2 = someId2) OR (ID1 = someOtherId1 AND ID2 = someOtherId2) OR ...)

Guenther · Accepted Answer · 2017-09-27T10:28:35.033

0

The generated SQL is wrong. It's not possible to bind column names. This is due to the fact, that JPA doesn't support multiple columns for an in statement. More details can be found here JPA query for select which multiple values in the "IN" clause

You need to create a list of id.id1 = x and id.id2 = y predicates and or them together.

As an alternative, you could use a native query, as also mentioned in the above quoted answer.

edited Sep 27 '17 at 10:28

answered Sep 27 '17 at 09:31

Guenther

2,035
2
15
20

If I understood correctly your answer. I should build a request like **DELETE FROM OBJECT_TABLE WHERE ID1 IN (?, ?, ?, ...) AND ID2 IN (?, ?, ?, ...)** which is not the same request. – Maxime André Sep 27 '17 at 09:43
You are right, that isn't the same. My mistake. I'll edit the answer. You need to add the `and` for each value pair. – Guenther Sep 27 '17 at 09:53

JPA bulk delete with multi columns primary key

1 Answers1