Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 758
imkepo Avatar asked Oct 25 '18 12:10

imkepo


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

like image 132
SadmirD Avatar answered Oct 11 '22 05:10

SadmirD


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.

like image 21
Simon Martinelli Avatar answered Oct 11 '22 03:10

Simon Martinelli