Java 如何在 Spring 中为两个查询使用相同的连接？

How to use same connection for two queries in Spring?

提问by Monika Michael

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

我在基于 Spring JdbcTemplate 的 dao 中有以下代码 -

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

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

问题是有时我的 update 和 queryForInt 查询是使用连接池中的不同连接执行的。

This results in an incorrect recordId being returned since MySql last_insert_id() is supposed to be called from the same connection that issued insert query.

这会导致返回不正确的 recordId，因为 MySql last_insert_id() 应该从发出插入查询的同一连接调用。

I have considered the SingleConnectionDataSource but do not want to use it since it degrades the application performance. I only want single connection for these two queries. Not for all the requests for all the services.

我已经考虑过 SingleConnectionDataSource 但不想使用它，因为它会降低应用程序的性能。我只想要这两个查询的单一连接。并非针对所有服务的所有请求。

So I have two questions:

所以我有两个问题：

1. Can I manage the connection used by the template class?
2. Does JdbcTemplate perform automatic transaction management? If i manually apply a transaction to my Dao method does that mean two transactions will be created per query?

1. 我可以管理模板类使用的连接吗？
2. JdbcTemplate 是否执行自动事务管理？如果我手动将事务应用到我的 Dao 方法，这是否意味着每个查询将创建两个事务？

Hoping that you guys can shed some light on the topic.

希望大家能对这个话题有所了解。

Update- I tried nwinkler's approach and wrapped my service layer in a transaction. I was surprised to see the same bug pop up again after sometime. Digging into the Spring source code i found this -

更新- 我尝试了 nwinkler 的方法并将我的服务层包装在一个事务中。我很惊讶地看到同样的错误在一段时间后再次出现。深入研究 Spring 源代码，我发现了这个 -

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

So contrary to what I thought, there isn't necessarily one database connection per transaction, but one connection for each query executed. Which brings me back to my problem. I want to execute two queries from the same connection. :-(

因此与我的想法相反，每个事务不一定有一个数据库连接，但每个执行的查询都有一个连接。这让我回到我的问题。我想从同一个连接执行两个查询。:-(

Update-

更新-

<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>

采纳答案by nwinkler

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

确保您的 DAO 包含在事务中（例如，通过使用 Spring 的 Interceptors for Transactions）。然后，两个呼叫将使用相同的连接。

Even better would be to have the transactions one level higher, at the service layer.

更好的做法是在服务层将事务提高一级。

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

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

Update:If you take a look at the JavaDoc of the DataSourceUtils.getConnection()method that you referenced in your update, you will see that it obtains the connection associated with the current thread:

更新：如果您查看DataSourceUtils.getConnection()您在更新中引用的方法的 JavaDoc ，您将看到它获取与当前线程关联的连接：

Is aware of a corresponding Connection bound to the current thread, for example when using {@link DataSourceTransactionManager}. Will bind a Connection to the thread if transaction synchronization is active, e.g. when running within a {@link org.springframework.transaction.jta.JtaTransactionManager JTA} transaction).

知道绑定到当前线程的相应连接，例如在使用 {@link DataSourceTransactionManager} 时。如果事务同步处于活动状态，则将连接绑定到线程，例如在 {@link org.springframework.transaction.jta.JtaTransactionManager JTA} 事务中运行时）。

According to this, it should work like you have set it up. I have used this pattern plenty of times, and never ran into any issues like you described...

据此，它应该像您设置的那样工作。我已经多次使用这种模式，从来没有遇到过你描述的任何问题......

Please also take a look at this thread, someone was dealing with similar issues there: Spring Jdbc declarative transactions created but not doing anything

也请看一下这个线程，有人在那里处理类似的问题：Spring Jdbc declarative transactions created but not doing anything

回答by Alexey Sviridov

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;
            }
        });