Spring @Transational no rollback for jdbctemplate.update(PreparedStatementCreator， KeyHolder）

Question

I'm newbie with spring framework. I used spring.xml to define the datasouce and DataSourceTransactionManager, so that I could insert data with jdbctemplate object.
And now I want to add the rollback to the transaction.
Unfortunately this rollback only works for JdbcTemplate.updata (String SQL), not for JdbcTemplate.update(PreparedStatementCreator, Keyholder), which I used to get the generated ID by insert.

@Override
@Transactional("txManagerTest")
public SQLQueryObjectIF process(SQLQueryObjectIF queryObj) {

    KeyHolder keyHolder = new GeneratedKeyHolder();
    for (final String query : queryObj.getQueries()) {
        System.out.println(query);

        // Rollback works fine for the "update" below. 
        //jdbcTemplate.update(query);


        // Rollback doesn't work for the "update" below. Don't why...
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                jdbcTemplate.getDataSource().getConnection().setAutoCommit(false);
                PreparedStatement ps = jdbcTemplate.getDataSource().getConnection().prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
                return ps;
            }
        }, keyHolder);


        //log.info(keyHolder.getKeys().toString());
    }

        //just for rollback test
    if (keyHolder.toString().length()>-1){
        throw new RuntimeException("Test Error");
    }
    return queryObj;
}  

Answer 1

That code should be used like this (you need to use the connection given as parameter), otherwise with your code you will get a connection that Spring doesn't know about, by directly accessing the DataSource instance (if Spring doesn't know about it, it will not know to rollback in case of exception):

public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
     PreparedStatement ps = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
     return ps;
}