spring多数据源

程序员文章站 2022-07-15 10:08:20

...

首先查看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中的配置

上一篇：多数据源---SessionFactory

下一篇： spring boot mybatis plus 使用

spring多数据源

西南大学全国排名2021-西南大学录取分数线2020（含四川、云南多省）

为什么我推荐企业做seo 原来好处那么多

java 使用memcached以及spring 配置memcached完整实例代码

深入理解spring boot异步调用方式@Async

Java Web使用Html5 FormData实现多文件上传功能

spring学习之参数传递与检验详解

Spring设计模式_工厂模式

最容易养的多肉排名这7种最适合新手，你选对了吗

Spring+MyBatis实现数据读写分离的实例代码

Spring boot 和Vue开发中CORS跨域问题解决

spring多数据源

西南大学全国排名2021-西南大学录取分数线2020（含四川、云南多省）

为什么我推荐企业做seo 原来好处那么多

java 使用memcached以及spring 配置memcached完整实例代码

深入理解spring boot异步调用方式@Async

Java Web使用Html5 FormData实现多文件上传功能

spring学习之参数传递与检验详解

Spring设计模式_工厂模式

最容易养的多肉排名 这7种最适合新手，你选对了吗

Spring+MyBatis实现数据读写分离的实例代码

Spring boot 和Vue开发中CORS跨域问题解决

最容易养的多肉排名这7种最适合新手，你选对了吗