使用AbstractRoutingDataSource切换活动数据源时,如何在数据源之间共享事务？
到目前为止,没有事务,查询将在两个数据库上正确执行,但是当我开始事务时,所有事情都在同一个数据库上执行(即,我无法再切换到第二个数据库).
有任何想法吗？
@Transactional
public void crossDbTransactionTest() {
// Selects a datasource from my pool of AbstractRoutingDataSources
DbConnectionContextHolder.setDbConnectionByYear(2012);
// execute something in the first database
this.executeSomeJpaQuery("xyz");
// switch to the second database
DbConnectionContextHolder.setDbConnectionByYear(2011);
// execute something in the second database
this.executeSomeJpaQuery("xyz"); // on any errors rollback changes in both databases
}
EDIT1(添加的配置文件)：
persistence.xml：
<persistence-unit name="primarnaKonekcija" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />
<property name="hibernate.max_fetch_depth" value="1" />
<property name="hibernate.transaction.manager_lookup_class"
value="org.hibernate.transaction.JBossTransactionManagerLookup" />
</properties>
</persistence-unit>
spring-jpa.xml：
<!-- Shared DB credentials -->
<context:property-placeholder location="classpath:config.properties" />
<!-- DB connections by year -->
<bean id="parentDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" abstract="true">
<property name="driverClassName" value="${db.driver}" />
<property name="username" value="${db.user}" />
<property name="password" value="${db.password}" />
</bean>
<bean id="dataSource" class="myPackage.DbConnectionRoutingDataSource">
<!-- Placeholder that is replaced in BeanFactoryPostProcessor -->
<property name="targetDataSources">
<map key-type="int">
<entry key="0" value-ref="placeholderDs" />
</map>
</property>
<property name="defaultTargetDataSource" ref="placeholderDs" />
</bean>
<!-- EntityManager configuration -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="persistenceUnitName" value="primarnaKonekcija" />
<property name="dataSource" ref="dataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="databasePlatform" value="org.hibernate.dialect.SQLServerDialect" />
<property name="showSql" value="true" />
</bean>
</property>
</bean>
<tx:annotation-driven />
<tx:jta-transaction-manager />
编辑2：
尝试将所有内容切换到JTA和JNDI提供的数据源.
将transaction-type =“ RESOURCE_LOCAL”更改为transaction-type =“ JTA”也不起作用-JtaStatusHelper抛出NullPointerException,表示transactionManager为null.
编辑3：
在persistence.xml中添加了JBossTransactionManagerLookup,现在在切换到事务内的第二个数据源时得到“不允许添加多个最后的资源”.
编辑4：
尝试了setting JBOSS,所以我克服了该错误-数据库切换现在可以正常工作,并带有预期的警告：“多个最新资源已添加到当前事务中.这在事务上是不安全的,不应依赖.”
接下来将尝试在JBOSS中配置MSSQL XA驱动程序.
编辑5：
configuring MSSQL XA之后,一切都会按预期进行,将发布答案以及进行此设置所需的步骤.
解决方法:
除非您别无选择,否则我不建议您使用此解决方案. 2级缓存可能无法使用这样的解决方案,但这是一个(稳定)的解决方案,我不得不用它来争取时间,直到基础遗留数据库合并为一个.
首先,在JBoss standalone.xml中将数据库连接设置为XA数据源.如果使用MS SQL Server,请按照说明正确设置XA为http://msdn.microsoft.com/en-us/library/aa342335.aspx
standalone.xml
<datasources>
<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
<connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
<xa-datasource jta="true" jndi-name="java:jboss/datasources/MYDB_ONE" pool-name="MYDB_ONE" enabled="true" use-java-context="true" use-ccm="true">
<xa-datasource-property name="ServerName">
localhost
</xa-datasource-property>
<xa-datasource-property name="DatabaseName">
MYDB_ONE
</xa-datasource-property>
<xa-datasource-property name="SelectMethod">
cursor
</xa-datasource-property>
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
<driver>sqljdbc</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
</xa-pool>
<security>
<user-name>some_user</user-name>
<password>some_password</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
</validation>
</xa-datasource>
<xa-datasource jta="true" jndi-name="java:jboss/datasources/MYDB_TWO" pool-name="MYDB_TWO" enabled="true" use-java-context="true" use-ccm="true">
<xa-datasource-property name="ServerName">
localhost
</xa-datasource-property>
<xa-datasource-property name="DatabaseName">
MYDB_TWO
</xa-datasource-property>
<xa-datasource-property name="SelectMethod">
cursor
</xa-datasource-property>
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
<driver>sqljdbc</driver>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
</xa-pool>
<security>
<user-name>some_user</user-name>
<password>some_password</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
</validation>
</xa-datasource>
<drivers>
<driver name="h2" module="com.h2database.h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
<driver name="sqljdbc" module="com.microsoft.sqlserver.jdbc">
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
</driver>
<driver name="postgresql" module="org.postgresql">
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>
</drivers>
</datasources>
然后,我设置了使用我的AbstractRoutingDataSource实现作为其DataSource的entityManager bean.
这是Spring支持的JPA设置,不使用persistence.xml文件；据我所知,这是使用JBoss 7时对实体进行自动程序包扫描的唯一方法.
springJpaConfig.xml
<!-- Use @PersistenceContext annotations for injecting entity managers -->
<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
<!-- Set up JTA transaction manager -->
<tx:jta-transaction-manager />
<bean id="entityManagerFactoryMyDB" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="persistenceUnitName" value="MyDB" />
<property name="dataSource" ref="dataSourceMyDB" />
<property name="packagesToScan" value="my.package.with.jpa.entities" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="true" />
</bean>
</property>
<property name="jpaPropertyMap">
<map>
<entry key="javax.persistence.transactionType" value="jta" />
<entry key="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />
<entry key="jboss.entity.manager.factory.jndi.name" value="java:app/MyDBEntityManagerFactory" />
<entry key="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />
</map>
</property>
</bean>
<bean id="dataSourceMyDB" class="some.package.AbstractRoutingDataSourceMyDB">
<property name="lenientFallback" value="false" />
<property name="defaultTargetDataSource" value="java:jboss/datasources/ExampleDS" />
<property name="targetDataSources">
<map key-type="String">
<!-- This is a placeholder that will be filled in by BeanFactoryPostProcessor -->
</map>
</property>
</bean>
<!-- This allows us to modify Spring configuration load the list of datasources -->
<bean class="some.package.DatasourceRegisteringBeanFactoryPostProcessor" />
我使用ExampleDS作为AbstractRoutingDataSourceMyDB中的默认值,因为您必须提供defaultTargetDataSource,但我一直想手动选择一个有效的数据库,因此,如果有人尝试访问数据库而没有首先手动选择连接,他们将尝试在数据库上执行查询.不存在的ExampleDS数据库将引发异常(非常hacky,但是可以完成工作).
在BeanFactoryPostProcessor中,我现在需要填写我的数据源列表：
DatasourceRegisteringBeanFactoryPostProcessor.java
package some.package
class DatasourceRegisteringBeanFactoryPostProcessor implements BeanFactoryPostProcessor {
public void postProcessBeanFactory(ConfigurableListableBeanFactory beanFactory) {
HashMap<String, String> connectionsListMyDB = new HashMap<>();
// Load your connection list from wherever you need to, you can
// enumerate them directly from JNDI or some configuration location
connectionsListMyDB.put("db1", "java:jboss/datasources/MYDB_ONE");
connectionsListMyDB.put("db2", "java:jboss/datasources/MYDB_TWO");
if (connectionsList.isEmpty())
throw new RuntimeException("No JPA connections defined");
// Configure the dataSource bean properties
BeanDefinitionRegistry factory = (BeanDefinitionRegistry) beanFactory;
MutablePropertyValues mpv = factory.getBeanDefinition("dataSourceMyDB").getPropertyValues();
ManagedMap<String, String> mm = (ManagedMap<String, String>) mpv.getPropertyValue(
"targetDataSources").getValue();
mm.clear();
for (Entry<String, String> e : connectionsListMyDB.entrySet()) {
mm.put(e.getKey(), e.getValue());
}
}
}
这是我实现AbstractRoutingDataSource的实现,它使我可以在运行时切换连接：
AbstractRoutingDataSourceMyDB.java
public class AbstractRoutingDataSourceMyDB extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return getDbConnectionMyDB();
}
// ThreadLocal variable so that the connection gets set for the current thread
// using spring's request scope on the class instead of ThreadLocal would also work here.
private final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public void setDbConnectionMyDB(String myKey) {
Assert.notNull(myKey, "myKey cannot be null");
contextHolder.set(myKey);
String k = contextHolder.get();
}
public String getDbConnectionMyDB() {
return (String) contextHolder.get();
}
public void clearDbConnectionMyDB() {
contextHolder.remove();
}
}
请注意,在从DAO类中更改当前连接之前,必须先调用entitymanager.flush()和clear(),否则在事务提交时,将在新连接上执行该事务范围内的所有挂起操作.这是因为,就其所知,Hibernate会话忽略了连接曾经发生过的更改-它始终是同一数据库.
因此,在您的DAO中,您现在可以执行以下操作：
SomeTableDAO.java
@PersistenceContext(unitName = "MyDB")
private EntityManager em;
@Autowired
private AbstractRoutingDataSourceMyDB routingSource;
public void someMethod(int id) {
em.flush();
em.clear();
routingSource.setDbConnectionMyDB("db1");
em.remove(em.getReference(Something.class, id)); // delete something in db1
em.flush();
em.clear();
routingSource.setDbConnectionMyDB("db2");
em.remove(em.getReference(Something.class, id)); // delete something else with the same id in db2
}
这样就可以了,虽然它不漂亮-可以完成:)