title: Vanilla JDBC update not rolled back in spring unit test
body: <p>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 <em>not the other one</em>! 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.</p>
  
  <p>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?</p>
  
  <p>Here's my tests class:</p>
  
  <pre><code>@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);
      }
  }
  </code></pre>
  
  <p>My test-context.xml looks like this:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
  
  &lt;bean id="myDao" class="my.package.myDao"&gt;
      &lt;constructor-arg name="dataSource" ref="dataSource"/&gt;
  &lt;/bean&gt;
  
  &lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
      &lt;property name="url" value="${unit.test.db.url}"/&gt;
      &lt;property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/&gt;
      &lt;property name="username" value="${unit.test.db.user}"/&gt;
      &lt;property name="password" value="${unit.test.db.pass}"/&gt;
  &lt;/bean&gt;
  
  &lt;bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"&gt;
      &lt;property name="dataSource" ref="dataSource"/&gt;
  &lt;/bean&gt;
  
  &lt;/beans&gt;
  </code></pre>
  
  <p>The method to be tested looks roughly (removed noise on connection closing etc.) so:</p>
  
  <pre><code>  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);
   }
  </code></pre>
  
  <p><strong>Update</strong>
  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.</p>
  
  <p><strong>Update 2</strong>
  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?</p>