Announcement Announcement Module
Collapse
No announcement yet.
Integration test on a Spring transactional JDBC statement Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Integration test on a Spring transactional JDBC statement

    Hello,

    I'm using jdbc to access a MySql database.

    I'm NOT using Hibernate.

    I would need transaction support.

    So I've a jdbc method doing an insert statement.
    Code:
    	public void insert(GInnAdresse gInnAdresse) throws SesamSqlException {
    		String sql = "insert into g_inn_adresse values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, null);";
    		Connection connection = null;
    		try {
    			connection = dataSource.getConnection();
    			connection.setAutoCommit(false);
    			PreparedStatement preparedStatement = connection.prepareStatement(sql);
    			preparedStatement.setInt(1, gInnAdresse.getId());
    			preparedStatement.setString(2, gInnAdresse.getOpprettet());
    			preparedStatement.setInt(3, gInnAdresse.getAdr_id());
    			preparedStatement.setString(4, gInnAdresse.getNbr_id());
    			preparedStatement.setInt(5, gInnAdresse.getKun_kundenr());
    			preparedStatement.setInt(6, gInnAdresse.getAns_id());
    			preparedStatement.setString(7, gInnAdresse.getKde_id());
    			preparedStatement.setString(8, gInnAdresse.getKua_k_adr_type());
    			preparedStatement.setString(9, gInnAdresse.getLnd_kode());
    			preparedStatement.setString(10, gInnAdresse.getPos_postnr());
    			preparedStatement.setString(11, gInnAdresse.getAdr_adr1());
    			preparedStatement.setString(12, gInnAdresse.getAdr_adr2());
    			preparedStatement.setString(13, gInnAdresse.getAdr_tlf());
    			preparedStatement.setString(14, gInnAdresse.getAdr_fax());
    			preparedStatement.setString(15, gInnAdresse.getAdr_utl());
    			preparedStatement.setString(16, gInnAdresse.getAdr_endret_dato());
    			preparedStatement.executeUpdate();
    			logger.debug(preparedStatement.toString());
    			connection.commit();
    			preparedStatement.close();
    		} catch (SQLException e) {
    			try {
    				if (connection != null) {
    					connection.rollback();
    				}
    			} catch (SQLException rbe) {
    				rbe.printStackTrace();
    			}
    			throwCustomException(connection, e);
    		} finally {
    			if (connection != null) {
    				try {
    					connection.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    As you can see I'm touching the connection to have it with an auto commit false.

    But I'm not sure it's a good idea.

    I suspect letting Spring handle the transaction would be much better, if I knew hot to do that.

    Now, that method works fine and I'm happy with it, for now.

    But I also need to have an integration test against that method.

    And that's when it starts getting tricky.

    Here is my test class:
    Code:
    	@Test
    	@Rollback
    	public void testInsertAndFindWithId() {
    ...
    		try {
    			gInnAdresseDao.insert(gInnAdresse);
    		} catch (SesamSqlException e) {
    			fail(e.getErrorMessage());
    		}
    ...
    There is a base test class:
    Code:
    @ContextConfiguration(locations = {"classpath:integration.jdbc.xml", "classpath:dao.xml", "classpath:integration.data-source.xml" })
    public abstract class AbstractDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
    
    }
    I can see the transaction being rolled back in the console output:
    2012-03-09 13:36:24,981 DEBUG [DataSourceTransactionManager] Initiating transaction rollback
    2012-03-09 13:36:24,981 DEBUG [DataSourceTransactionManager] Rolling back JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@14e0e90]
    2012-03-09 13:36:24,982 DEBUG [DataSourceTransactionManager] Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@14e0e90] after transaction
    2012-03-09 13:36:24,983 DEBUG [DataSourceUtils] Returning JDBC Connection to DataSource
    Here is my Spring configuration:
    Code:
    	<bean id="transactionManager"
    		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    
    	<tx:annotation-driven transaction-manager="transactionManager" />
    
    	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		<property name="driverClassName">
    			<value>${dataSource.driverClassName}</value>
    		</property>
    		<property name="url">
    			<value>${dataSource.url}</value>
    		</property>
    		<property name="username">
    			<value>${dataSource.username}</value>
    		</property>
    		<property name="password">
    			<value>${dataSource.password}</value>
    		</property>
    	</bean>
    But after the test is done, the database table still contains the inserted record.

    Maybe Spring could hand me out a connection instead of me instanciating one ?

    Any idea ?

    Thanks.

    Stephane

  • #2
    I went for a

    protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    and the transaction behaves nicely now.

    I guess I was mixing two different things, handling the connection manually inside a Spring transaction was not a good idea.

    Comment

    Working...
    X