CODEWITHSUNDEEP
javaspring-boottransactionsspring-jdbcspring-transactions
aznan
aznan

Reputation: 399

Spring disable transactions on datasource

I have a method in a Spring Boot application that takes an SQLite database and inserts its contents into a PostgreSQL database. The contents must be inserted all or nothing, so the method is annotated with @Transactional.

The SQLite database may be large and missing indexes, so those will have to be added before it's read in order to get some performance. I still want the original intact though, so the file is first copied into a temp folder and deleted when the operation is complete.

// Repository using a JdbcTemplate with an autoconfigured PostgreSQL datasource.
@Autowired MyRepository myRepositry;

@Transactional
public void insert(Path file) {
  Path tempFile = copyToTempDir(file);
  try {
    // Setup connection to SQLite database
    SQLiteDataSource dataSource = new SQLiteDataSource();
    dataSource.setUrl("jdbc:sqlite:" + tempFile);
    JdbcTemplate sqlite = new JdbcTemplate(dataSource);

    addIndex(sqlite);

    while(hasMoreData(sqlite)) {
      MyData data = readData(sqlite);
      myRepository.insert(data);
    }
    
  } catch(Exception ex) {
    // handle it..

  } finally {
    deleteCopy(tempFile);
  }
}

The problem comes with deleting the SQLite copy in the finally clause. Since we are still inside a transaction, the database connection is not closed and the OS (Windows, anyway) refuses to delete the file. If I remove the @Transactional annotation everything works fine.

I tried wrapping the methods that interact with the sqlite JdbcTemplate in a TransactionTemplate. Setting its propagation behavior to NEVER does result in an exception, like I would expect, but using NOT_SUPPORTED or REQUIRES_NEW still locks the file.

@Autowired DataSourceTransactionManager transactionManager;

private MyData readData(JdbcTemplate sqlite) {
  TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
  transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_NOT_SUPPORTED);
  return transactionTemplate.execute(status -> {
    // read data...
  });
}

So can I somehow isolate the SQLite file from transaction management via some sort of configuration, or will I have to use raw JDBC to ensure Spring doesn't get involved?

Upvotes: 1

Views: 867

Answers (1)

aznan
aznan

Reputation: 399

I ended up creating a class that extends JdbcTemplate and overrides the five methods that interact with DataSourceUtils to create connections on their own instead.

Upvotes: 1

Related Questions