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