Batch deletion for Spring data JPA

Question

I have around 20k records to be deleted from Spring data JPA query , query is related to deleting all records before some particular date.

I am using below query

dao.deleteByCreationDateBefore(new Date());

I think this query hits database for each row deletion.

Please let me know is there any way I can use batch deletion here?

Regards

score 0 · Accepted Answer · edited Jun 27 '18 at 06:26

0

spting data doen't support batch operation.

try to do it with simple delete if it's possible , like : it be very fast operation (faster than butch delete)

delete from SOME_ENTITY/TABLE where CreationDate < new Date()/curentDate

if your dao method delete record by record : But you can do it with hibernate/jpa in dao level , like (example from site with persist) from Hibernate/JPA Batch Insert and Batch Update Example:

    em.getTransaction().begin();
    for (int i = 0; i < 100; i++){
        Book book = new Book(i, "Hibernate/JPA Batch Insert Example: " + i);
        em.persist(book);

        if (i % batchSize == 0 && i > 0) {
            em.flush();
            em.clear();
        }
    }
    em.getTransaction().commit();

for hibernate : and here is article How to batch INSERT and UPDATE statements with Hibernate , read about 'Configuring hibernate.jdbc.batch_size'. And Hibernate JDBC and Connection Properties options for hibernate for hibernate.jdbc.fetch_size and hibernate.jdbc.batch_size

edited Jun 27 '18 at 06:26

buræquete

14,226
4
44
89

answered Jun 28 '17 at 09:20

xyz

5,228
2
26
35

Thanks for the comment, i am already using batch for insertion . I wanted to know if there is any batch delete option exist otherwise i use plain hql or sql query. – Khushi Jun 30 '17 at 08:54
if you can , use plain hql or sql query! batch delete is slower than query as you should iterate and delete ,even in batch , and flush it into db. In you have choise batch or query ALWAYS use delete , update wherever you can – xyz Jul 01 '17 at 13:27
sbjavateam, i have used HQL to delete all records in one go . It is quite faster. thanks for suggesstion – Khushi Jul 03 '17 at 04:30

Batch deletion for Spring data JPA

1 Answers1