I'm trying to do bulk delete in my entities, and the best solution will be to go with CriteriaDelete. But CriteriaDelete does not cascade (at least not for me).
So, it seems like the only solution which I have is to do select first and delete each element separately. Which does not seems wrong to me.
Is anyone have a better idea of how to do bulk delete? Is it actually a better way?
If it helps I'm using EclipseLink 2.5.2.
The options are:
A JPA CriteriaDelete statement generates a JPQL bulk delete statement, that's parsed to an SQL bulk delete statement.
So, the following JPA CriteriaDelete statement:
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaDelete<PostComment> delete = builder.createCriteriaDelete(PostComment.class);
Root<T> root = delete.from(PostComment.class);
int daysValidityThreshold = 3;
delete.where(
builder.and(
builder.equal(
root.get("status"),
PostStatus.SPAM
),
builder.lessThanOrEqualTo(
root.get("updatedOn"),
Timestamp.valueOf(
LocalDateTime
.now()
.minusDays(daysValidityThreshold)
)
)
)
);
int deleteCount = entityManager.createQuery(delete).executeUpdate();
generates this SQL delete query:
DELETE FROM
post_comment
WHERE
status = 2 AND
updated_on <= '2020-08-06 10:50:43.115'
So, there is no entity-level cascade since the delete is done using the SQL statement, not via the EntityManager.
To enable cascading when executing bulk delete, you need to use DDL-level cascade when declaring the FK constraints.
ALTER TABLE post_comment
ADD CONSTRAINT FK_POST_COMMENT_POST_ID
FOREIGN KEY (post_id) REFERENCES post
ON DELETE CASCADE
Now, when executing the following bulk delete statement:
DELETE FROM
post
WHERE
status = 2 AND
updated_on <= '2020-08-02 10:50:43.109'
The DB will delete the post_comment records referencing the post rows that got deleted.
The best way to execute DDL is via an automatic schema migration tool, like Flyway, so the Foreign Key definition should reside in a migration script.
If you are generating the migration scripts using the HBM2DLL tool, then, in the PostComment class, you can use the following mapping to generate the aforementioned DDL statement:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(foreignKey = @ForeignKey(name = "FK_POST_COMMENT_POST_ID"))
@OnDelete(action = OnDeleteAction.CASCADE)
private Post post;
If you really care about the time it takes to perform this bulk delete, I suggest you use JPQL to delete your entities. When you issue a DELETE JPQL query, it will directly issue a delete on those entities without retrieving them in the first place.
int deletedCount = entityManager.createQuery("DELETE FROM Country").executeUpdate();
You can even do conditional deletes based on some parameters on those entities using Query API like below
Query query = entityManager.createQuery("DELETE FROM Country c
WHERE c.population < :p");
int deletedCount = query.setParameter(p, 100000).executeUpdate();
executeUpdate will return the number of deleted rows once the operation is complete.
If you've proper cascading type in place in your entities like CascadeType.ALL (or) CascadeType.REMOVE, then the above query will do the trick for you.
@Entity
class Employee {
@OneToOne(cascade=CascadeType.REMOVE)
private Address address;
}
JPQL BULK DELETE (whether using string-based JPQL or using Criteria JPQL) is not intended to cascade (i.e follow the cascade type settings for fields). If you want cascading then you either set up the datastore to use real FOREIGN KEYs, or you pull back the objects to delete and call EntityManager.remove().
