spring 多数据库（数据源） JTA

原创 spring 多数据库（数据源） JTA 事务2 收藏

注：本文引自http://malaqu.com/?p=542

最近一个项目要跨多数据，配多数据源的，其中就用到了事务，毫无疑问我选择的是Spring的声明式JTA事务。我的环境是JBOSS+ORACLE 9I
自己私下做了些实验，不过还是成功了
实验一：MySQL 5.0
采用atomikos的jta事务（要感谢 http://andyao.iteye.com/）

view plaincopy to clipboardprint?

1. <?xml version=”1.0″ encoding=”UTF-8″?>
2. <beans xmlns=”http://www.springframework.org/schema/beans”
3. xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:aop=”http://www.springframework.org/schema/aop”
4. xmlns:tx=”http://www.springframework.org/schema/tx”
5. xsi:schemaLocation=”http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
6. http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
7. http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd”>
8.
9. <bean id=”dataSource” class=”com.atomikos.jdbc.SimpleDataSourceBean”
10. init-method=”init” destroy-method=”close”>
11. <property name=”uniqueResourceName”>
12. <value>mysql/main</value>
13. </property>
14. <property name=”xaDataSourceClassName”>
15. <!–使用Mysql XADataSource(mysql>=5.0, Connector/J>=5.0才可以支持XADatasource)–>
16. <value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value>
17. </property>
18. <property name=”xaDataSourceProperties”>
19. <value>URL=jdbc:mysql://localhost:3306/crm?useUnicode=true&characterEncoding=UTF-8;user=root;password=root</value>
20. </property>
21. <property name=”exclusiveConnectionMode”>
22. <value>true</value>
23. </property>
24. <property name=”connectionPoolSize”>
25. <value>3</value>
26. </property>
27. <property name=”validatingQuery”>
28. <value>SELECT 1</value>
29. </property>
30. </bean>
31. <!– 第二个数据库 –>
32. <bean id=”dataSourceB” class=”com.atomikos.jdbc.SimpleDataSourceBean”
33. init-method=”init” destroy-method=”close”>
34. <property name=”uniqueResourceName”>
35. <value>mysql/news</value>
36. </property>
37. <property name=”xaDataSourceClassName”>
38. <!–
39. 使用Mysql XADataSource(mysql>=5.0, Connector/J>=5.0才可以支持XADatasource)
40. –>
41. <value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value>
42. </property>
43. <property name=”xaDataSourceProperties”>
44. <value>URL=jdbc:mysql://localhost:3306/crm2?useUnicode=true&characterEncoding=UTF-8;user=root;password=root</value>
45. </property>
46. <property name=”exclusiveConnectionMode”>
47. <value>true</value>
48. </property>
49. <property name=”connectionPoolSize”>
50. <value>3</value>
51. </property>
52. <property name=”validatingQuery”>
53. <value>SELECT 1</value>
54. </property>
55. </bean>
56.
57. <bean id=”lobHandler” class=”org.springframework.jdbc.support.lob.DefaultLobHandler” />
58.
59. <!– 第一个数据库的sqlMapClient –>
60. <bean id=”sqlMapClient1″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”>
61. <property name=”configLocation”>
62. <!– 包含第一个数据库表的map –>
63. <value>classpath:SqlMapConfig.xml</value>
64. </property>
65. <property name=”dataSource” ref=”dataSource” />
66. <property name=”lobHandler” ref=”lobHandler” />
67. </bean>
68. <!– 第二个数据库的sqlMapClient –>
69. <bean id=”sqlMapClient2″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”>
70. <property name=”configLocation”>
71. <!– 包含第一个数据库表的map –>
72. <value>classpath:SqlMapConfig2.xml</value>
73. </property>
74. <property name=”dataSource” ref=”dataSourceB” />
75. <property name=”lobHandler” ref=”lobHandler” />
76. </bean>
77.
78. <!– Optional: add a log administrator –>
79. <bean id=”localLogAdministrator”
80. class=”com.atomikos.icatch.admin.imp.LocalLogAdministrator”/>
81.
82. <bean id=”userTransactionService”
83. class=”com.atomikos.icatch.config.UserTransactionServiceImp”
84. init-method=”init” destroy-method=”shutdownForce”>
85. <constructor-arg>
86. <!– IMPORTANT: specify all Atomikos properties here –>
87. <props>
88. <prop key=”com.atomikos.icatch.service”>com.atomikos.icatch.standalone.UserTransactionServiceFactory</prop>
89. </props>
90. </constructor-arg>
91. <property name=”initialLogAdministrators”>
92. <list>
93. <ref bean=”localLogAdministrator”/>
94. </list>
95. </property>
96. </bean>
97. <!–Construct Atomikos UserTransactionManager,needed to configure Spring –>
98. <bean id=”AtomikosTransactionManager”
99. class=”com.atomikos.icatch.jta.UserTransactionManager”
100. init-method=”init” destroy-method=”close”
101. depends-on=”userTransactionService”>
102. <!–when close is called,should we force transactions to terminate or not?–>
103. <property name=”forceShutdown” value=”false” />
104. </bean>
105. <!–Also use Atomikos UserTransactionImp, needed to configure Spring–>
106. <bean id=”AtomikosUserTransaction”
107. class=”com.atomikos.icatch.jta.UserTransactionImp”
108. depends-on=”userTransactionService”>
109. <property name=”transactionTimeout” value=”300″ />
110. </bean>
111. <!– Configure the Spring framework to use JTA transactions from Atomikos –>
112. <bean id=”JtaTransactionManager”
113. class=”org.springframework.transaction.jta.JtaTransactionManager”
114. depends-on=”userTransactionService”>
115. <property name=”transactionManager” ref=”AtomikosTransactionManager” />
116. <property name=”userTransaction” ref=”AtomikosUserTransaction” />
117. </bean>
118.
119. <bean id=”user1Dao” class=”com.crm.code.dao.impl.User1DaoImpl”>
120. <property name=”sqlMapClient”>
121. <ref bean=”sqlMapClient1″/>
122. </property>
123. </bean>
124. <bean id=”user2Dao” class=”com.crm.code.dao.impl.User2DaoImpl”>
125. <property name=”sqlMapClient”>
126. <ref bean=”sqlMapClient2″/>
127. </property>
128. </bean>
129. <bean id=”user12Service” class=”com.crm.code.service.impl.User12ServiceImpl”>
130. <property name=”user1Dao”>
131. <ref bean=”user1Dao” />
132. </property>
133. <property name=”user2Dao”>
134. <ref bean=”user2Dao” />
135. </property>
136. </bean>
137.
138. </beans>

<?xml version=”1.0″ encoding=”UTF-8″?> <beans xmlns=”http://www.springframework.org/schema/beans” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:aop=”http://www.springframework.org/schema/aop” xmlns:tx=”http://www.springframework.org/schema/tx” xsi:schemaLocation=”http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd”> <bean id=”dataSource” class=”com.atomikos.jdbc.SimpleDataSourceBean” init-method=”init” destroy-method=”close”> <property name=”uniqueResourceName”> <value>mysql/main</value> </property> <property name=”xaDataSourceClassName”> <!–使用Mysql XADataSource(mysql>=5.0, Connector/J>=5.0才可以支持XADatasource)–> <value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value> </property> <property name=”xaDataSourceProperties”> <value>URL=jdbc:mysql://localhost:3306/crm?useUnicode=true&characterEncoding=UTF-8;user=root;password=root</value> </property> <property name=”exclusiveConnectionMode”> <value>true</value> </property> <property name=”connectionPoolSize”> <value>3</value> </property> <property name=”validatingQuery”> <value>SELECT 1</value> </property> </bean> <!– 第二个数据库 –> <bean id=”dataSourceB” class=”com.atomikos.jdbc.SimpleDataSourceBean” init-method=”init” destroy-method=”close”> <property name=”uniqueResourceName”> <value>mysql/news</value> </property> <property name=”xaDataSourceClassName”> <!– 使用Mysql XADataSource(mysql>=5.0, Connector/J>=5.0才可以支持XADatasource) –> <value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value> </property> <property name=”xaDataSourceProperties”> <value>URL=jdbc:mysql://localhost:3306/crm2?useUnicode=true&characterEncoding=UTF-8;user=root;password=root</value> </property> <property name=”exclusiveConnectionMode”> <value>true</value> </property> <property name=”connectionPoolSize”> <value>3</value> </property> <property name=”validatingQuery”> <value>SELECT 1</value> </property> </bean> <bean id=”lobHandler” class=”org.springframework.jdbc.support.lob.DefaultLobHandler” /> <!– 第一个数据库的sqlMapClient –> <bean id=”sqlMapClient1″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”> <property name=”configLocation”> <!– 包含第一个数据库表的map –> <value>classpath:SqlMapConfig.xml</value> </property> <property name=”dataSource” ref=”dataSource” /> <property name=”lobHandler” ref=”lobHandler” /> </bean> <!– 第二个数据库的sqlMapClient –> <bean id=”sqlMapClient2″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”> <property name=”configLocation”> <!– 包含第一个数据库表的map –> <value>classpath:SqlMapConfig2.xml</value> </property> <property name=”dataSource” ref=”dataSourceB” /> <property name=”lobHandler” ref=”lobHandler” /> </bean> <!– Optional: add a log administrator –> <bean id=”localLogAdministrator” class=”com.atomikos.icatch.admin.imp.LocalLogAdministrator”/> <bean id=”userTransactionService” class=”com.atomikos.icatch.config.UserTransactionServiceImp” init-method=”init” destroy-method=”shutdownForce”> <constructor-arg> <!– IMPORTANT: specify all Atomikos properties here –> <props> <prop key=”com.atomikos.icatch.service”>com.atomikos.icatch.standalone.UserTransactionServiceFactory</prop> </props> </constructor-arg> <property name=”initialLogAdministrators”> <list> <ref bean=”localLogAdministrator”/> </list> </property> </bean> <!–Construct Atomikos UserTransactionManager,needed to configure Spring –> <bean id=”AtomikosTransactionManager” class=”com.atomikos.icatch.jta.UserTransactionManager” init-method=”init” destroy-method=”close” depends-on=”userTransactionService”> <!–when close is called,should we force transactions to terminate or not?–> <property name=”forceShutdown” value=”false” /> </bean> <!–Also use Atomikos UserTransactionImp, needed to configure Spring–> <bean id=”AtomikosUserTransaction” class=”com.atomikos.icatch.jta.UserTransactionImp” depends-on=”userTransactionService”> <property name=”transactionTimeout” value=”300″ /> </bean> <!– Configure the Spring framework to use JTA transactions from Atomikos –> <bean id=”JtaTransactionManager” class=”org.springframework.transaction.jta.JtaTransactionManager” depends-on=”userTransactionService”> <property name=”transactionManager” ref=”AtomikosTransactionManager” /> <property name=”userTransaction” ref=”AtomikosUserTransaction” /> </bean> <bean id=”user1Dao” class=”com.crm.code.dao.impl.User1DaoImpl”> <property name=”sqlMapClient”> <ref bean=”sqlMapClient1″/> </property> </bean> <bean id=”user2Dao” class=”com.crm.code.dao.impl.User2DaoImpl”> <property name=”sqlMapClient”> <ref bean=”sqlMapClient2″/> </property> </bean> <bean id=”user12Service” class=”com.crm.code.service.impl.User12ServiceImpl”> <property name=”user1Dao”> <ref bean=”user1Dao” /> </property> <property name=”user2Dao”> <ref bean=”user2Dao” /> </property> </bean> </beans>

这样是成功的 可是切换oracle9i时悲剧发生了
— Cause: com.atomikos.datasource.ResourceException: resume for XID oracle.jdbc.xa.OracleXid@145f939 raised -3: the XA resource detected an internal error
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
— The error occurred in ibatis/Product1.xml.
— The error occurred while executing update.
— Check the insert into boss_product (PROD_ID, PARENT_ID, APP_ID, PROD_NAME, PROD_CODE, DEFAULT_VER_PROD_ID, DATA_PATH, GMT_CREATED, GMT_MODIFIED, CREATOR, MODIFIER, IS_DELETED) values (seq_boss_product.nextval, 1, 88, ?, ?, 10, ‘aaa’, sysdate, sysdate, ‘aavv’, ‘aacb’, ‘n’)

官方说oracle连接问题 哎。。。无语了
换了一种JTA事务机制 通过JOTM

view plaincopy to clipboardprint?

1. <?xml version=”1.0″ encoding=”UTF-8″?>
2. <beans xmlns=”http://www.springframework.org/schema/beans”
3. xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:aop=”http://www.springframework.org/schema/aop”
4. xmlns:tx=”http://www.springframework.org/schema/tx”
5. xsi:schemaLocation=”http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
6. http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
7. http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd”>
8.
9. <bean id=”jotm” class=”org.springframework.transaction.jta.JotmFactoryBean”/>
10. <bean id=”txManager” class=”org.springframework.transaction.jta.JtaTransactionManager”>
11. <property name=”userTransaction” ref=”jotm”/>
12. </bean>
13.
14. <bean id=”dataSourceA” class=”org.enhydra.jdbc.pool.StandardXAPoolDataSource”
15. destroy-method=”shutdown”>
16. <property name=”dataSource”>
17. <bean class=”org.enhydra.jdbc.standard.StandardXADataSource” destroy-method=”shutdown”>
18. <property name=”transactionManager” ref=”jotm”/>
19. <property name=”driverName” value=”oracle.jdbc.driver.OracleDriver”/>
20. <property name=”url” value=”jdbc:oracle:thin:@10.2.224.44:1521:trade”/>
21. </bean>
22. </property>
23. <property name=”user” value=”crm_aep”/>
24. <property name=”password” value=”crm_aep”/>
25. </bean>
26.
27. <bean id=”dataSourceB” class=”org.enhydra.jdbc.pool.StandardXAPoolDataSource”
28. destroy-method=”shutdown”>
29. <property name=”dataSource”>
30. <bean class=”org.enhydra.jdbc.standard.StandardXADataSource” destroy-method=”shutdown”>
31. <property name=”transactionManager” ref=”jotm”/>
32. <property name=”driverName” value=”oracle.jdbc.driver.OracleDriver”/>
33. <property name=”url” value=”jdbc:oracle:thin:@10.2.226.24:1521:voucher”/>
34. </bean>
35. </property>
36. <property name=”user” value=”boss”/>
37. <property name=”password” value=”boss”/>
38. </bean>
39.
40. <tx:annotation-driven transaction-manager=”txManager” proxy-target-class=”true” />
41.
42. <!– 第一个数据库的sqlMapClient –>
43. <bean id=”sqlMapClient1″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”>
44. <property name=”configLocation”>
45. <!– 包含第一个数据库表的map –>
46. <value>classpath:SqlMapConfig_ora1.xml</value>
47. </property>
48. <property name=”dataSource” ref=”dataSourceA” />
49. </bean>
50. <!– 第二个数据库的sqlMapClient –>
51. <bean id=”sqlMapClient2″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”>
52. <property name=”configLocation”>
53. <!– 包含第一个数据库表的map –>
54. <value>classpath:SqlMapConfig_ora2.xml</value>
55. </property>
56. <property name=”dataSource” ref=”dataSourceB” />
57. </bean>
58.
59. <bean id=”product1Dao” class=”com.crm.code.dao.impl.Product1DaoImpl”>
60. <property name=”sqlMapClient”>
61. <ref bean=”sqlMapClient1″/>
62. </property>
63. </bean>
64. <bean id=”product2Dao” class=”com.crm.code.dao.impl.Product2DaoImpl”>
65. <property name=”sqlMapClient”>
66. <ref bean=”sqlMapClient2″/>
67. </property>
68. </bean>
69. <bean id=”product12Service” class=”com.crm.code.service.impl.Product12ServiceImpl”>
70. <property name=”product1Dao”>
71. <ref bean=”product1Dao” />
72. </property>
73. <property name=”product2Dao”>
74. <ref bean=”product2Dao” />
75. </property>
76. </bean>
77. </beans>

<?xml version=”1.0″ encoding=”UTF-8″?> <beans xmlns=”http://www.springframework.org/schema/beans” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:aop=”http://www.springframework.org/schema/aop” xmlns:tx=”http://www.springframework.org/schema/tx” xsi:schemaLocation=”http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd”> <bean id=”jotm” class=”org.springframework.transaction.jta.JotmFactoryBean”/> <bean id=”txManager” class=”org.springframework.transaction.jta.JtaTransactionManager”> <property name=”userTransaction” ref=”jotm”/> </bean> <bean id=”dataSourceA” class=”org.enhydra.jdbc.pool.StandardXAPoolDataSource” destroy-method=”shutdown”> <property name=”dataSource”> <bean class=”org.enhydra.jdbc.standard.StandardXADataSource” destroy-method=”shutdown”> <property name=”transactionManager” ref=”jotm”/> <property name=”driverName” value=”oracle.jdbc.driver.OracleDriver”/> <property name=”url” value=”jdbc:oracle:thin:@10.2.224.44:1521:trade”/> </bean> </property> <property name=”user” value=”crm_aep”/> <property name=”password” value=”crm_aep”/> </bean> <bean id=”dataSourceB” class=”org.enhydra.jdbc.pool.StandardXAPoolDataSource” destroy-method=”shutdown”> <property name=”dataSource”> <bean class=”org.enhydra.jdbc.standard.StandardXADataSource” destroy-method=”shutdown”> <property name=”transactionManager” ref=”jotm”/> <property name=”driverName” value=”oracle.jdbc.driver.OracleDriver”/> <property name=”url” value=”jdbc:oracle:thin:@10.2.226.24:1521:voucher”/> </bean> </property> <property name=”user” value=”boss”/> <property name=”password” value=”boss”/> </bean> <tx:annotation-driven transaction-manager=”txManager” proxy-target-class=”true” /> <!– 第一个数据库的sqlMapClient –> <bean id=”sqlMapClient1″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”> <property name=”configLocation”> <!– 包含第一个数据库表的map –> <value>classpath:SqlMapConfig_ora1.xml</value> </property> <property name=”dataSource” ref=”dataSourceA” /> </bean> <!– 第二个数据库的sqlMapClient –> <bean id=”sqlMapClient2″ class=”org.springframework.orm.ibatis.SqlMapClientFactoryBean”> <property name=”configLocation”> <!– 包含第一个数据库表的map –> <value>classpath:SqlMapConfig_ora2.xml</value> </property> <property name=”dataSource” ref=”dataSourceB” /> </bean> <bean id=”product1Dao” class=”com.crm.code.dao.impl.Product1DaoImpl”> <property name=”sqlMapClient”> <ref bean=”sqlMapClient1″/> </property> </bean> <bean id=”product2Dao” class=”com.crm.code.dao.impl.Product2DaoImpl”> <property name=”sqlMapClient”> <ref bean=”sqlMapClient2″/> </property> </bean> <bean id=”product12Service” class=”com.crm.code.service.impl.Product12ServiceImpl”> <property name=”product1Dao”> <ref bean=”product1Dao” /> </property> <property name=”product2Dao”> <ref bean=”product2Dao” /> </property> </bean> </beans>

成功了。。。
很好很好 哈哈哈

发表于 @ 2010年05月05日　12:06:00 | 评论( 0 ) | 编辑| 举报| 收藏
旧一篇:spring 多数据源 JTA 事务 | 新一篇:禁止页面的选择和复制功能