How to use the same connection for two queries in Spring?

Question

I have the following code in Spring JdbcTemplate based on dao -

getJdbcTemplate().update("Record Insert Query..."); int recordId = getJdbcTemplate().queryForInt("SELECT last_insert_id()"); 

The problem is that sometimes my update and queryForInt queries are executed using different connections from the connection pool.

This causes the record to return incorrectly, since MySql last_insert_id () must be called from the same connection that issued the insert request.

I examined the source of SingleConnectionDataSource, but I do not want to use it, since it affects the performance of the application. I want only one connection for these two queries. Not for all requests for all services.

I have two questions:

• Can I control the connection used by the template class?
• Does JdbcTemplate perform automatic transaction management? If I manually applied a transaction to my Dao method, does this mean that two transactions will be created for each request?

Hope you guys can shed some light on this topic.

Update . I tried using nwinkler and wrapped my service level in a transaction. I was surprised to see the same error appearing once again. Digging into Spring source code, I found this -

 public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) throws DataAccessException { //Lots of code Connection con = DataSourceUtils.getConnection(getDataSource()); //Lots of code } 

Thus, contrary to what I thought, not necessarily one database connection for each transaction, but one connection for each executed request. This brings me back to my problem. I want to execute two queries from the same connection .: - (

Refresh -

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${db.driver}" /> <property name="url" value="${db.jdbc.url}" /> <property name="username" value="${db.user}" /> <property name="password" value="${db.password}" /> <property name="maxActive" value="${db.max.active}" /> <property name="initialSize" value="20" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" autowire="byName"> <property name="dataSource"> <ref local="dataSource" /> </property> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <tx:advice id="transactionAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="*" propagation="REQUIRES_NEW" rollback-for="java.lang.Exception" timeout="30" /> </tx:attributes> </tx:advice> <aop:config> <aop:pointcut id="pointcut" expression="execution(* service.*.*(..))" /> <aop:pointcut id="pointcut2" expression="execution(* *.ws.*.*(..))" /> <aop:advisor pointcut-ref="pointcut" advice-ref="transactionAdvice" /> <aop:advisor pointcut-ref="pointcut2" advice-ref="transactionAdvice" /> </aop:config> 

Answer 1

Make sure your DAO is wrapped in a transaction (e.g. using Spring Interceptors for Transactions). The same connection will be used for both calls.

It would be even better to have transactions one level higher at the service level.

Documentation: http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/transaction.html

Update: If you look at the JavaDoc of the DataSourceUtils.getConnection() method that you specified in your update, you will see that it receives the connection associated with the current stream:

Knows about the appropriate connection associated with the current stream, for example, when using {@link DataSourceTransactionManager}. Binds the connection to the thread if transaction synchronization is active, for example. when working within the {@link org.springframework.transaction.jta.JtaTransactionManager JTA} transaction).

Accordingly, it should work the way you configured it. I have used this template many times and have never encountered such problems as you described ...

Please also take a look at this topic, someone has encountered similar problems: Spring Declared Jdbc transactions created, but do nothing

Answer 2

This is my approach to do this:

 namedJdbcTemplate.execute(savedQuery, map, new PreparedStatementCallback<Object>() { @Override public Object doInPreparedStatement(PreparedStatement paramPreparedStatement) throws SQLException, DataAccessException { paramPreparedStatement.execute("SET @userLogin = 'blabla123'"); paramPreparedStatement.executeUpdate(); return null; } });