Vanilla JDBC update not rolled back in spring unit test

Question

I have a spring unit test for a DAO method that inserts in two different tables. After the test is done one of the inserts is rolled back as expected, but not the other one! I really can't figure out what's happening. I've debugged the test several times so that I can see when the (uncommitted) changes appear in the database, but then only one disappears.

The only difference that I can see is the in one case the insert is done with raw JDBC and in the second case with Sping's JdbcTemplate. But shouldn't both be in the same transaction which is then rolled back?

Here's my tests class:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring/test-context.xml"})
@Rollback
@Transactional(transactionManager = "txManager")
public class MyDaoIntegrationTest {
    @Autowired
    private DataSource dataSource;
    @Autowired
    private MyDao myDao;

    @Test
    public void createMyObject_test() throws Exception {
        MyObject myObject = new MyObject();

        myDao.createMyObject(myObject, 123L);
    }
}

My test-context.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="
 http://www.springframework.org/schema/beans 
 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

<bean id="myDao" class="my.package.myDao">
    <constructor-arg name="dataSource" ref="dataSource"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="url" value="${unit.test.db.url}"/>
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="username" value="${unit.test.db.user}"/>
    <property name="password" value="${unit.test.db.pass}"/>
</bean>

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>

</beans>

The method to be tested looks roughly (removed noise on connection closing etc.) so:

  public void createMyObject(MyObject vo, long refId) throws SQLException {

    Connection cn = dataSource.getConnection();
    PreparedStatement ps = cn.prepareStatement("insert into MY_OBJECT (COL1, COL, ...)) values (?,?,...)");
    ps.setInt(1, vo.getCol1());
    ps.setInt(2, vo.getCol2());
         ...
    ps.executeUpdate();

    MyObjectEventVO event = new MyObjectEventVO();
    createMyObjectEvent(event);
}

public void createMyObjectEvent(MyObjectEventVO vo) throws DataAccessException {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String updateSql =
            "insert into MY_OBJECT_EVENT(COL1, COL2, ...) "
                    + "values (?, ?, ...)";
    Object[] params = {vo.getCol1(), vo.getCol2(), ... };
    int[] types = {Types.INTEGER, Types.VARCHAR, ...};

    jdbcTemplate.update(updateSql, params, types);
 }

Update I've tried commenting out the call to createMyObjectEvent(event) so that only one inserts happens. The end result is the same: the first insert is not rolled back. So there seems to be a problem with the transaction for the first insert.

Update 2 I've refactored the first insert to also use JdbcTemplate and then everything works fine! So the question could be rephrased as: how to you get vanilla JDBC code to roll back in a spring unit test?

Is it possible that createMyObjectEvent has an own transaction? E.g. is myDao annotated this way? — Uwe Allner, May 05 '17 at 09:09
There are no annotations on MyDao. All configuration outside of the unit test comes from the XML. — Nicola Ambrosetti, May 05 '17 at 09:12
Possible duplicate of [Transaction rollback on Spring JDBC tests](http://stackoverflow.com/questions/4048340/transaction-rollback-on-spring-jdbc-tests) — Nicola Ambrosetti, May 05 '17 at 22:15

score 1 · Accepted Answer · answered May 05 '17 at 09:47

1

@Rollback need spring managed transaction to work. To participate spring managed transaction easiest way is to use JDBCTemplate or DataSourceUtils. Both contain code needed to use spring managed transaction.

answered May 05 '17 at 09:47

ikettu

1,203
12
17

You're right! The raw jdbc connection doesn't participate in the transaction unless I create it with DataSourceUtils.getConnection(dataSource). That's what's internally used by JdbcTemplate. See also http://stackoverflow.com/questions/4048340/transaction-rollback-on-spring-jdbc-tests – Nicola Ambrosetti May 05 '17 at 22:17

Vanilla JDBC update not rolled back in spring unit test

1 Answers1