Delete statement while soft deletion is active

Support
#1

I have the following delete statement:

EntityManager em = persistence.getEntityManager();
em.createQuery("delete from erp$DemandPlan e where e.plant.id = :plantId", DemandPlan.class) .setParameter("plantId", plant.getId()) .executeUpdate();

I get the following error. I want to keep my soft deletion mode on, but wondering what’s the mistake!
UnsupportedOperationException: Delete queries are not supported with enabled soft deletion. Use 'cuba.enableDeleteStatementInSoftDeleteMode' application property to roll back to legacy behavior.

#2

Hi Mortoza,

Such batch operation is disabled by default since 6.9 because it is confusing: one can decide that if he executes batch delete in soft delete mode the entities will be marked as deleted. But actually the operation will completely delete instances that are not yet marked as deleted.

So the question is, what do you want to do with this statement?

#3

Hi Konstantin
In this particular case I want them to be deleted permanently. But in many other cases, I would like to do soft delete. Thanks for any thoughts how we can deal with both in the same application.

Mortoza

#4

Then if you want to delete something permanently, use

EntityManager em = persistence.getEntityManager();
em.setSoftDeletion(false);
em.createQuery("delete from erp$DemandPlan e where e.plant.id = :plantId") 
.setParameter("plantId", plant.getId()) .executeUpdate();
em.setSoftDeletion(true); // if you continue working in this transaction

If you want to mark as deleted, use update instead of delete:

EntityManager em = persistence.getEntityManager();
em.createQuery("update erp$DemandPlan e set e.deleteTs = :deleteTs, e.deletedBy = :deletedBy where e.plant.id = :plantId")
    .setParameter("deleteTs", timeSource.currentTimestamp())
    .setParameter("deletedBy", userSessionSource.getUserSession().getUser().getLoginLowerCase())
    .setParameter("plantId", plant.getId())
    .executeUpdate();
1 Like
#5

Thank you Konstantin for those codes, it helped.

I have a suggestion to keep this simple. Instead of using two different SQL (delete vs Update), delete statement should always delete what is the global setup i.e. by default it is soft delete. When the user wants to use exceptional option i,e, in this case hard delete then we just use “em.setSoftDeletion(false/true);”

what do you think?

#6

Yes, but it will require an automatic replacement of the delete statement with update. We’ll think about it later.