英文:
Spring disable transactions on datasource
问题
我在一个Spring Boot应用程序中有一个方法，它接受一个SQLite数据库，并将其内容插入到PostgreSQL数据库中。这些内容必须全部插入，否则不插入任何内容，因此该方法带有@Transactional注解。
由于SQLite数据库可能很大并且缺少索引，因此在读取之前必须添加这些索引以获得一些性能。然而，我仍希原始数据库保持完好无损，所以文件首先会被复制到临时文件夹中，并在操作完成时进行删除。
// 使用自动配置的PostgreSQL数据源的Repository和JdbcTemplate。
@Autowired MyRepository myRepository;
@Transactional
public void insert(Path file) {
Path tempFile = copyToTempDir(file);
try {
// 设置连接到SQLite数据库
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) {
// 处理异常...
} finally {
deleteCopy(tempFile);
}
}
问题出现在finally子句中删除SQLite副本时。由于我们仍然处于事务内部，数据库连接没有关闭，操作系统（至少在Windows上）拒绝删除该文件。如果我移除@Transactional注解，一切都正常运作。
我尝试使用TransactionTemplate包装与sqlite JdbcTemplate 交互的方法。将其传播行为设置为NEVER确实会导致异常，就像我期望的那样，但是使用NOT_SUPPORTED或REQUIRES_NEW仍然会锁定文件。
@Autowired DataSourceTransactionManager transactionManager;
private MyData readData(JdbcTemplate sqlite) {
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);
transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_NOT_SUPPORTED);
return transactionTemplate.execute(status -> {
// 读取数据...
});
}
那么我是否可以通过某种形式的配置使SQLite文件与事务管理隔离开，或者是否必须使用原始的JDBC来确保Spring不会介入？
英文:
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?
答案1
得分: 1
我最终创建了一个继承自 JdbcTemplate 的类，并重写了与 DataSourceUtils 交互的五个方法，以便自行创建连接。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论