如何在Spring中对两个查询使用相同的连接？ [英] How to use same connection for two queries in Spring?

问题描述

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

  getJdbcTemplate ..）; 
 int recordId = getJdbcTemplate（）。queryForInt（SELECT last_insert_id（））;问题是，我有时我的更新和queryForInt查询使用连接池中的不同连接执行。
  

$ b <

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

我考虑了SingleConnectionDataSource，但不想使用它，因为它降低了应用程序的性能。我只想要单个连接这两个查询。

所以我有两个问题：

1. 我可以管理模板类使用的连接吗？


2. JdbcTemplate是否执行自动事务管理？如果我手动应用事务到我的Dao方法，这意味着每个查询将创建两个事务？

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

  public< T& $（$ PreparedStatementCreator psc，PreparedStatementCallback< T>动作）
 throws DataAccessException {
 //许多代码
 Connection con = DataSourceUtils.getConnection（getDataSource（））; 
 //很多代码
} 
  

每个事务不一定有一个数据库连接，而是每个查询执行一个连接。
这让我回到我的问题。我想从同一连接执行两个查询。 ： - （

更新 -

 < bean id =dataSourceclass =org.apache.commons.dbcp.BasicDataSource
 destroy-method =close> 
< property name =driverClassNamevalue = $ {db.driver}/> 
< property name =urlvalue =$ {db.jdbc.url}/> 
< property name =usernamevalue =$ {db.user}/> 
< property name =passwordvalue =$ {db.password}/> 
< property name =maxActivevalue =$ {db.max.active}/> 
< property name =initialSizevalue =20/> 
< / bean> 
 $ b b< bean id =jdbcTemplateclass =org.springframework.jdbc.core.JdbcTemplate
 autowire =byName> 
< property name =dataSource> 
< ref local =dataSource/> 
< / property> 
< / bean> 
 
 
& 
 class =org.springframework.jdbc.datasource.DataSourceTransactionManager> 
< property name =dataSourceref =dataSource/> 
< / bean> 
 
< tx：advice id =transactionAdvicetransaction-manager =transactionManager> 
< tx：attributes> 
< tx：method name =*propagation =REQUIRES_NEWrollback-for =java.lang.Exceptiontimeout =30/> 
< / tx：attributes> 
< / tx：advice> 
< aop：config> 
< aop：pointcut id =pointcutexpression =execution（* service。*。*（..））/> 
< aop：pointcut id =pointcut2expression =execution（* * .ws。*。*（..））/> 
 
< aop：advisor pointcut-ref =pointcutadvice-ref =transactionAdvice/> 
< aop：advisor pointcut-ref =pointcut2advice-ref =transactionAdvice/> 
< / aop：config> 
  

解决方案

确保您的DAO包裹在一个事务中通过使用Spring的拦截器进行事务）。

更好的办法是让服务层的事务处理级别更高一级。

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

Update：
如果您查看更新中引用的 DataSourceUtils.getConnection（）方法的JavaDoc，看到它获得与当前线程相关联的连接：

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

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

请也看看这个线程，有人在处理类似问题：创建Spring Jdbc声明式事务但不执行任何操作< a>

I have the following code in a Spring JdbcTemplate based 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.

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.

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.

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?

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 -

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>

解决方案

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.

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

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:

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

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中对两个查询使用相同的连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！