CODEWITHSUNDEEP
spring-data-jpajdbctemplatespring-transactions
MelleD
MelleD

Reputation: 791

Transaction handling with spring data and JDBC Template

I would like to do a batch delete without first making a Select for each object. I didn't find a way with spring data jpa and the entity manager to do this.For this reason I try the JDBC template. However, I get problems with the pageable. This code workes fine and all deletes are executed by batching. Unfortunately with an unnecessary select for each entity.

@Transactional
public void deleteFoos() {

   Page<Foo> page = repository.findAll(PageRequest.of(0,50);
   Pageable pageable = null;
   do {
      if ( pageable != null ) {
         page = repository.findAll( pageable );
      }
      repository.deleteAll( page.getContent() );
   } while ( (pageable = page.nextPageable()) != Pageable.unpaged() );
}

What I have tried now is to implement the whole thing directly with the JDBC template.

@Transactional
    public void deleteFoos() {
    
       Page<Foo> page = repository.findAll(PageRequest.of(0,50);
       Pageable pageable = null;
       do {
          if ( pageable != null ) {
             page = repository.findAll( pageable );
          }
          //repository.deleteAll( page.getContent() );
 jdbcTemplate.batchUpdate(
         "DELETE FROM foo WHERE (id = ?)",
         page.getContent(),
         page.getTotalItems(),
         ( preparedStatement, argument ) -> {
            preparedStatement.setObject( 1, argument.getId() );
         } );
       } while ( (pageable = page.nextPageable()) != Pageable.unpaged() );
    }

However, the third and fourth page is always empty. It seems the delete is executed directly. Auto commit is off, what else could be wrong?

EDIT: Solution from Jens works fine: "You can simply repeatedly request the first page and delete it."

Upvotes: 0

Views: 963

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81862

The JdbcTemplate does simply execute SQL statements, so yes, they get immediately executed. There is no caching involved, as with JPA operations. But even JPA operations get flushed to the database before a query gets executed, so the difference isn't that big.

You can simply repeatedly request the first page and delete it.

But using pagination with deletes doesn't make much sense, you could just delete all rows from the table and be done with it in a single roundtrip to the database:

DELETE FROM foo

If you want to delete in batches you could still do this without loading anything in your application. The details might be database dependent, but something similar to this should work nicely:

DELETE FROM foo
LIMIT 100;

Upvotes: 1

Related Questions