JPA 2.0 Criteria Query/Builder DELETE : Typesafe DELETE queries

Removed

JPA 2.0 don't provide a dynamic way to write delete queries. In fact we can only write a type safe queries for the SELECT but not for the DELETE and Update.

JPA 2.1 provides this possibility using  CriteriaUpdate and CriteriaDelete. You can have more details in this link.


But actually, as I am obliged to use JPA 2.0 which comes with JBoss eap 6, and waiting for JPA 2.1, I should use the native queries.

So the problem here is that I have multiple lists that I should use in the query and that may be null.

Query query = entityManager.createNativeQuery("DELETE FROM person_record pr WHERE pr.person.person_id IN (select person_id from person p WHERE p.age IN ?1 AND p.job IN  ?2 ");

query.setParameter(1, ageList);
query.setParameter(2, jobList);

Here I am using a SELECT subquery. In fact if I use this query,

DELETE.... Where pr.person.age IN ?1

Hibernate will not accept it and throw an exception.

Problem:

The problem with the first query is that when ageList or the jobList is null, nothing will be returned. So we should put the parameter only if it is not null. We can so create a StringBuilder and put the first part of the request and according to what parameter we have, we create the rest of the query.

Solution: 

But a more safe think to do is to use the Criteria Builder in order to select the rows to delete, then delete the rows using entityManager.remove(entity). This is also very useful if we need to do a Cascade DELETE.

So the first think is to do like this:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery(PersonRecord.class);
Root root = query.from(PersonRecord.class);

List predicateList = new ArrayList();

if(ageList != null && !ageList .isEmpty()){
predicateList.add(builder.isTrue(root.get("person").get("age").in(ageList )));
}
if(jobList!= null && !jobList.isEmpty()){
predicateList.add(builder.isTrue(modelTypeRoot.get("person").get("job").in(jobList)));
}

query.where(builder.and(predicateList.toArray(new Predicate[predicateList.size()])));

List personToDelete= entityManager.createQuery(query).getResultList();

for(PersonRecord item:personToDelete){
       // Make the entity attached again
entityManager.merge(item);
      // Here a CASCADE remove will be done easily
entityManager.remove(item);
}

This is only a temporarely solution till we will get the JBoss EAP with JPA 2.1