Can JdbcTemplate Share a Common Transaction?

Question

So I originally posted a question here regarding an issue I was having with mixing JDBC Templates/JPA.

However I'm wondering now if it's even possible to share a common transaction between JDBC Template operations?

Example will update table 'test' in separate transactions.

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void storeDataInSingleTransaction(List<Test> testEntries) {
        namedParameterJdbcTemplate.update("DELETE FROM test", new HashMap<>());
        namedParameterJdbcTemplate.update("alter table test auto_increment = 1", new HashMap<>());

        String insertTestSQL = "INSERT INTO test VALUES (:id, :name, :value)";
        SqlParameterSource[] testBatch = SqlParameterSourceUtils.createBatch(testEntries.toArray());
        namedParameterJdbcTemplate.batchUpdate(insertTestSQL, testBatch);
    }

EDIT 1: I have tried creating the template/data source manually, with Transactional annotation, but has not been successful for me.

    SingleConnectionDataSource dataSource = new SingleConnectionDataSource();

    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl(url);
    dataSource.setUsername(user);
    dataSource.setPassword(password);

    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

You need to alter table in a real application flow? Why would alter multiple times in real time? Is it a test? — Ori Marko, Jul 28 '19 at 04:55
The example is a test but I do have a need for the above in a real use case. Data in a table needs to be completely refreshed during a user operation. The table in question can still be searched on, so the refresh needs to be in a single transaction — Ph33ly, Jul 28 '19 at 18:05

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Normally in Spring, when you call your method from @Transactional method it should execute as a single connection

It is not sufficient to tell you simply to annotate your classes with the @Transactional annotation, add @EnableTransactionManagement to your configuration

you should apply the @Transactional annotation only to methods with public visibility

Also, to use same connection you can set datasorce in context as SingleConnectionDataSource

wraps a single JDBC Connection which is not closed after use.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 29 '19 at 03:10

Ori Marko

56,308
23
131
233

I tried adding the Transactional annotation to the method and manually setting a SingleConnectionDataSource to a JDBC Template, but unfortunately couldn't get it to work properly. Is there something I'm missing in my implementation possibly? I posted the code in the edit. – Ph33ly Jul 29 '19 at 15:14
@GetOtterHere the issue probably because `alter table` is DDL statement that will commit, see https://stackoverflow.com/questions/730621/do-ddl-statements-always-give-you-an-implicit-commit-or-can-you-get-an-implicit also *It is not sufficient to tell you simply to annotate your classes with the @Transactional annotation, add @EnableTransactionManagement to your configuration* – Ori Marko Jul 29 '19 at 15:17
Ok yea looks like `alter table` was the issue in this case. Removing it fixes the issue I was seeing and keeps everything in a single transaction. Thanks! – Ph33ly Jul 29 '19 at 16:05

Can JdbcTemplate Share a Common Transaction?

1 Answers1