Spring DatasourceTransaction Manager Problem

Question

final DataSource ds = DataSourceLocator.getInstance()
    .getDataSource(sg.cmpl.starhub.lprs.Constants.APP_KEY);
final DataSourceTransactionManager txManager = new DataSourceTransactionManager();
txManager.setDataSource(ds);

final DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
final TransactionStatus status = txManager.getTransaction(def);

Connection conn = null;
PreparedStatement ps = null;

try {

    /***************************************************************************/
    conn = DataSourceUtils.getConnection(ds);
    ps = conn.prepareStatement(sql);
    ps.execute();
    /***************************************************************************/

    txManager.commit(status);

} catch (Exception e) {
    txManager.rollback(status);
}

Is there something wrong with my transaction manager logic? It looks like unstable. When I insert new data, First time it seems to save and later I can't find the data in mysql database. Please help. Thanks a lot.

Answer 1

Yes, there is something wrong. This is not the Spring way. You should not be putting commit/rollback logic in code like this. The advantage comes when you can do it declaratively, in configuration.

Have a look at the Spring transaction reference docs.

Answer 2

Assuming there's a special reason you want to do programmatic connection and transaction management I suggest taking a look at Spring's JdbcTemplate and wrap it's usage in a TransactionTemplate.

However as stated in the previous comment, you should try to avoid programmatic transaction management as much as possible and use annotations (@Transactional) or XML configuration (TransactionProxyFactoryBean or <tx:advice/>) instead.