Is it possible to use "TRUNCATE" in Spring Data JPA using JpaRepository? Or a more effective method than the standard deleteAll()?

user_10557411 0
Viewed 22116

Im currently using the standard jparepository method repository.deleteAll() to clean my table before adding new info. The table consists of 8300 rows with 5 columns each. It's currently taking about 80 sec to get them all removed, while it takes 1-3 sec to put them in using the standard repository.saveAll(list). Is there a more effecient way to do this? Deleting the data manually in sql with DELETE FROM table takes 0,1 sec. Using MySQL database. log from putting in data log from deletion.

spring-data-jpa spring mysql sql java
2 Answers

Example, in your service interface:

public interface MyTableInterface {
    //...

    void truncateMyTable();
}

In your service implementation (with @Autowired myTableRepository):

public class MyTableImpl implements MyTableService {

    // other methods, @Autowiring, etc

    @Override
    @Transactional
    public void truncateMyTable() {
        myTableRepository.truncateMyTable();
    }
}

In your repository;

public interface MyTableRepository extends JpaRepository<MyTable, Long> {
    //....

    @Modifying
    @Query(
            value = "truncate table myTable",
            nativeQuery = true
    )
    void truncateMyTable();
}

EDIT: Also notice the @Transactional on service implemntation layer, instead of placing it on DAO/Repository layer

user_87587850

deleteAll is fetching all entities and then removes one by one:

// Code from SimpleJpaRepository

@Transactional
public void deleteAll() {

    for (T element : findAll()) {
        delete(element);
    }
}

I would suggest that you create your own delete method in the repo like:

@Modifying
@Transactional
@Query("delete from MyEntity m")
void deleteAllWithQuery();

This will create only one SQL DELETE statement.

user_10451420
Related