spring多数据源

最新推荐文章于 2022-08-30 11:31:59 发布

原创最新推荐文章于 2022-08-30 11:31:59 发布 · 142 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Spring #Hibernate #Bean #MySQL #JDBC

Spring 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何通过Atomikos和Spring框架配置跨库事务，并提供具体的XML配置示例及测试代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先查看Mysql存储引擎运行show engines

发现以下：

MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO

FEDERATED NO Federated MySQL storage engine

MyISAM YES Default engine as of MySQL 3.23 with great performance NO NO NO

BLACKHOLE YES /dev/null storage engine (anything you write to it disappears) NO NO NO

MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO

CSV YES CSV storage engine NO NO NO

ARCHIVE YES Archive storage engine NO NO NO

InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys YES YES YES

除了InnoDB之外都不支持事务操作。所以要想支持跨库事务首先表是InnoDB类型的存储

运行mysql当前默认的存储引擎:show variables like '%storage_engine%';

显示：

storage_engine InnoDB

运行具体某一个表的详细信息 show create table hc_info ；

显示：

hc_info

CREATE TABLE `hc_info` (

`id` int(11) NOT NULL AUTO_INCREMENT, ........省略

) ENGINE=InnoDB AUTO_INCREMENT=22872 DEFAULT CHARSET=utf8

好了，这就开始下一步操作了。

2：跨库事务只有JTA支持，EJB也是用JTA做到的跨库支持，其实Spring3.0较EJB 在跨库事务更方便。

先用JDBC方式实现：

application.xml内容：

<bean id="dataSource" class="com.atomikos.jdbc.SimpleDataSourceBean"> 
		<property name="uniqueResourceName"><value>dataSource</value></property> 
		<property name="xaDataSourceClassName"><value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value></property> 
		<property name="xaDataSourceProperties">
		<value>URL=jdbc:mysql://192.168.0.229:3306/huisou;user=root;password=123654</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="dataSource2" class="com.atomikos.jdbc.SimpleDataSourceBean"> 
		<property name="uniqueResourceName"><value>dataSource2</value></property> 
		<property name="xaDataSourceClassName"><value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value></property> 
		<property name="xaDataSourceProperties">
		<value>URL=jdbc:mysql://192.168.0.229:3306/huisou2;user=root;password=123654</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> 
	<!--date source 3  -->
	<bean id="dataSource3" class="com.atomikos.jdbc.SimpleDataSourceBean"> 
		<property name="uniqueResourceName"><value>dataSource3</value></property> 
		<property name="xaDataSourceClassName"><value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value></property> 
		<property name="xaDataSourceProperties">
		<value>URL=jdbc:mysql://192.168.0.229:3306/huisou2_enterprise;user=root;password=123654</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> 
	
		<!--date source 4  -->
	<bean id="dataSource4" class="com.atomikos.jdbc.SimpleDataSourceBean"> 
		<property name="uniqueResourceName"><value>dataSource4</value></property> 
		<property name="xaDataSourceClassName"><value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value></property> 
		<property name="xaDataSourceProperties">
		<value>URL=jdbc:mysql://192.168.0.229:3306/huisou2_sale;user=root;password=123654</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> 
	<!-- ============================sessionFactory============================ -->
	
	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
		<!-- ========动态读取实体映射文件===== -->
			<property name="mappingLocations">
			<list>
				<value>
					classpath:/com/huisou/auxiliary/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/business/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/core/entity/*.hbm.xml
				</value>
			</list>
			</property>
		<property name="hibernateProperties">
			<value>
			hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
			hibernate.show_sql=true
			hibernate.format_sql=false
			hibernate.query.substitutions=true 1, false 0
			hibernate.jdbc.batch_size=20
			hibernate.connections.autocommit=true;
			<!--  hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
			hibernate.cache.provider_configuration_file_resource_path=/ehcache-hibernate.xml-->
			</value>
		</property>
		<property name="entityInterceptor">
			<ref local="treeInterceptor" />
		</property>
	</bean>
	<bean id="sessionFactory2"
		class="com.huisou.common.hibernate3.HibernateSessionFactory">
		<property name="dataSource">
			<ref bean="dataSource2" />
		</property>
		<!-- ========动态读取实体映射文件===== -->
			<property name="mappingLocations">
			<list>
				<value>
					classpath:/com/huisou/auxiliary/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/business/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/core/entity/*.hbm.xml
				</value>
			</list>
			</property>
		<property name="hibernateProperties">
			<value>
			hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
			hibernate.show_sql=true
			hibernate.format_sql=false
			hibernate.query.substitutions=true 1, false 0
			hibernate.jdbc.batch_size=20
			hibernate.connections.autocommit=true;
			<!--  hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
			hibernate.cache.provider_configuration_file_resource_path=/ehcache-hibernate.xml-->
			</value>
		</property>
		<property name="entityInterceptor">
			<ref local="treeInterceptor" />
		</property>
	</bean>
	 
	 
	 <bean id="sessionFactory3"
		class="com.huisou.common.hibernate3.HibernateSessionFactory">
		<property name="dataSource">
			<ref bean="dataSource3" />
		</property>
		<!-- ========动态读取实体映射文件===== -->
			<property name="mappingLocations">
			<list>
				<value>
					classpath:/com/huisou/auxiliary/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/business/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/core/entity/*.hbm.xml
				</value>
			</list>
			</property>
		<property name="hibernateProperties">
			<value>
			hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
			hibernate.show_sql=true
			hibernate.format_sql=false
			hibernate.query.substitutions=true 1, false 0
			hibernate.jdbc.batch_size=20
			hibernate.connections.autocommit=true;
			<!--  hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
			hibernate.cache.provider_configuration_file_resource_path=/ehcache-hibernate.xml-->
			</value>
		</property>
		<property name="entityInterceptor">
			<ref local="treeInterceptor" />
		</property>
	</bean>
	 
	 
	  <bean id="sessionFactory4"
		class="com.huisou.common.hibernate3.HibernateSessionFactory">
		<property name="dataSource">
			<ref bean="dataSource4" />
		</property>
		<!-- ========动态读取实体映射文件===== -->
			<property name="mappingLocations">
			<list>
				<value>
					classpath:/com/huisou/auxiliary/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/business/entity/*.hbm.xml
				</value>
				<value>
					classpath:/com/huisou/core/entity/*.hbm.xml
				</value>
			</list>
			</property>
		<property name="hibernateProperties">
			<value>
			hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
			hibernate.show_sql=true
			hibernate.format_sql=false
			hibernate.query.substitutions=true 1, false 0
			hibernate.jdbc.batch_size=20
			hibernate.connections.autocommit=true;
			<!--  hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
			hibernate.cache.provider_configuration_file_resource_path=/ehcache-hibernate.xml-->
			</value>
		</property>
		<property name="entityInterceptor">
			<ref local="treeInterceptor" />
		</property>
	</bean>
	<!-- ==============================声明式事务======================================= -->
	<!--JDBC事务管理器,根据你的情况使用不同的事务管理器,如果工程中有Hibernate,就用Hibernate的事务管理器 -->
	<bean id="transactionManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
	<!-- 哈哈 -->
	<bean id="transactionManager2"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory2" />
	</bean>
	
	
	<bean id="transactionManager3"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory3" />
	</bean>
	
	<bean id="transactionManager4"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory4" />
	</bean>
	
	<!-- start 实现分布式事务(跨库事务同步) -->
	<bean id="atomikosTransactionManager" class="com.atomikos.icatch.jta.UserTransactionManager" init-method="init" destroy-method="close">
		<property name="forceShutdown"><value>true</value></property>
	</bean>
	<bean id="atomikosUserTransaction" class="com.atomikos.icatch.jta.UserTransactionImp">
		<property name="transactionTimeout"><value>300</value></property>
	</bean>
	<!-- jta -->
	<bean id="hibernateTransactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
		<property name="transactionManager">
		<ref local="atomikosTransactionManager"/>
		</property>
		<property name="userTransaction">
		<ref local="atomikosUserTransaction"/>
		</property>
	</bean>
	<bean id="hibernateTransactionManager2" class="org.springframework.transaction.jta.JtaTransactionManager">
		<property name="transactionManager2">
		<ref local="atomikosTransactionManager"/>
		</property>
		<property name="userTransaction">
		<ref local="atomikosUserTransaction"/>
		</property>
	</bean>
	<bean id="hibernateTransactionManager3" class="org.springframework.transaction.jta.JtaTransactionManager">
		<property name="transactionManager3">
		<ref local="atomikosTransactionManager"/>
		</property>
		<property name="userTransaction">
		<ref local="atomikosUserTransaction"/>
		</property>
	</bean>
	<bean id="hibernateTransactionManager4" class="org.springframework.transaction.jta.JtaTransactionManager">
		<property name="transactionManager4">
		<ref local="atomikosTransactionManager"/>
		</property>
		<property name="userTransaction">
		<ref local="atomikosUserTransaction"/>
		</property>
	</bean>
	<!-- end 实现分布式事务(跨库事务同步)jta -->
	<!-- 注解多事务管理 -->
	<tx:annotation-driven transaction-manager="transactionManager" />
	<tx:annotation-driven transaction-manager="transactionManager2" />
	<tx:annotation-driven transaction-manager="transactionManager3" />
	<tx:annotation-driven transaction-manager="transactionManager4" />
	
	
	<bean id="treeInterceptor"
		class="com.huisou.common.hibernate3.TreeIntercptor" />
	<context:component-scan base-package="com.huisou" />

测试代码：

Connection conection  = null;
		WebApplicationContext webApplicationContext = org.springframework.web.context.support.WebApplicationContextUtils
		.getRequiredWebApplicationContext(ServletActionContext.getRequest().getSession().getServletContext());
		DataSource dataSource = (DataSource)webApplicationContext.getBean("dataSource2");
		System.out.println("test");
		//SpObserver.putSp("dataSource");
		try {
		 conection = dataSource.getConnection();
		 ResultSet rs = (ResultSet)conection.prepareStatement("select id ,subject from hc_info").executeQuery();
		  while(rs.next()){     
              System.out.println("数据库2  " + rs.getInt(1)+ "\t");     
              System.out.println("数据库2  " + rs.getString(2)+ "\t");     
          }     
		  dataSource = (DataSource)webApplicationContext.getBean("dataSource");
		 conection = dataSource.getConnection();
		 rs = (ResultSet)conection.prepareStatement("select id ,subject from hc_info limit 20").executeQuery();
		  while(rs.next()){     
             System.out.println("数据库1  " + rs.getInt(1)+ "\t");     
             System.out.println("数据库1  " + rs.getString(2)+ "\t");     
         }     
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

下面是JOTM在Spring中的配置