SSM+C3P0配置多个数据源

SSM+C3P0配置多个数据源

基于公司的ssm框架，通过配置文件配置可操作的多个数据库办法

一、在sping配置文件applicationCpontext.xml全文如下：

注意：1、数据源配置完成后，将事务管理中的内容改为动态数据源的id
2、在AOP切入的地方增加执行顺序 order="2"
3、动态DataSource配置中，需要配置继承AbstractRoutingDataSource的类
4、为了方便阅读，我讲数据库的配置直接写到了这里，原来是从.properties中读取的

<?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:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd"
	default-lazy-init="false">
	<description>Spring公共配置文件 </description>
	<bean id="environmentVariablesConfiguration" class="org.jasypt.encryption.pbe.config.EnvironmentStringPBEConfig">
		<property name="algorithm" value="${enc.algorithm}" />
		<property name="password" value="${enc.password}" />
	</bean>

	<bean id="configurationEncryptor" class="org.jasypt.encryption.pbe.StandardPBEStringEncryptor">
		<property name="config" ref="environmentVariablesConfiguration" />
	</bean>

	<bean id="encryptedPropertyConfigurer" class="org.jasypt.spring31.properties.EncryptablePropertyPlaceholderConfigurer">
		<constructor-arg ref="configurationEncryptor" />
		<property name="ignoreUnresolvablePlaceholders" value="true" />
		<property name="locations">
			<list>
				<value>classpath*:spring/application.properties</value>
				<!-- <value>classpath*:redis/redis-client-config.properties</value> -->
			</list>
		</property>
	</bean>
	<bean class="com.hzhy.framework.common.SpringContextHolder" lazy-init="false" />
	<bean id="webConstant" class="com.hzhy.web.constant.Constants">
		<property name="ROOT_PATH" value="${file_upload_path}" />
		<property name="TEST_ROOT_PATH" value="${file_upload_path}" />
	</bean>

	<context:property-placeholder
		ignore-unresolvable="true" location="classpath*:/spring/application.properties,classpath*:system.properties" />

	*<!-- 第一个数据源配置 -->
	<bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass">
			<value>com.p6spy.engine.spy.P6SpyDriver</value>
		</property>
		<property name="jdbcUrl">
			<value>jdbc:p6spy:mysql://192.168.2.117:3306/hy_manage?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true</value>
		</property>
		<property name="user">
			<value>user01</value>
		</property>
		<property name="password">
			<value>123456</value>
		</property>
		<property name="maxPoolSize" value="50" />
		<property name="minPoolSize" value="0" />
		<property name="initialPoolSize" value="1" />
		<property name="maxIdleTime" value="1800" />
		<property name="testConnectionOnCheckout" value="true" />
		<property name="acquireIncrement" value="5" />
		<property name="maxStatements" value="0" />
		<property name="idleConnectionTestPeriod" value="120" />
		<property name="acquireRetryAttempts" value="30" />
	</bean>
	<!-- 第二个数据源配置开始 -->
	<bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		  destroy-method="close">
		<property name="driverClass">
			<value>com.p6spy.engine.spy.P6SpyDriver</value>
		</property>
		<property name="jdbcUrl">
			<value>jdbc:p6spy:mysql://192.168.2.140:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true</value>
		</property>
		<property name="user">
			<value>root</value>
		</property>
		<property name="password">
			<value>root</value>
		</property>
		<property name="maxPoolSize" value="50" />
		<property name="minPoolSize" value="0" />
		<property name="initialPoolSize" value="1" />
		<property name="maxIdleTime" value="1800" />
		<property name="testConnectionOnCheckout" value="true" />
		<property name="acquireIncrement" value="5" />
		<property name="maxStatements" value="0" />
		<property name="idleConnectionTestPeriod" value="120" />
		<property name="acquireRetryAttempts" value="30" />
	</bean>
	<!-- 动态DataSource配置 -->
	<bean id="dynamicDataSource" class="com.hzhy.manager.dataSource.DynamicDataSource">
		<property name="targetDataSources">
			<map key-type="java.lang.String">
				<entry key="dataSource1" value-ref="dataSource1"/>
				<entry key="dataSource2" value-ref="dataSource2"/>
			</map>
		</property>
		<property name="defaultTargetDataSource" ref="dataSource1"/>
	</bean>*

	<!-- 事物管理器 -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dynamicDataSource" />
	</bean>
	<!-- 事物管理模板 -->
	<bean id="transactionTemplate"
		class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager" />
	</bean>
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dynamicDataSource" />
		<property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" />

		<!-- <property name="typeAliasesPackage" value="${typeAliasesPackage}" />
 		<property name="mapperLocations" value="classpath*:com/hzhy/**/*.xml" />   -->
		<property name="mapperLocations">
			<array>
				<value>classpath*:/com/hzhy/**/domain/*.xml</value>
			</array>
		</property>
	</bean>

	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
	
	<!--扫描com.包下包含spring 注解的类，自动注入 -->
	<context:component-scan base-package="com.hzhy" />

	<!-- 使用annotation定义事务 -->
	<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />

	<tx:advice id="hzhyTxAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="*save*" propagation="REQUIRED" rollback-for="java.lang.Exception" />
			<tx:method name="*delete*" propagation="REQUIRED" rollback-for="java.lang.Exception" />
			<tx:method name="*update*" propagation="REQUIRED" rollback-for="java.lang.Exception" />
			<tx:method name="*remove*" propagation="REQUIRED" rollback-for="java.lang.Exception" />
		</tx:attributes>
	</tx:advice>
	<aop:config>
		<aop:pointcut id="hzhyAop"
			expression="execution(* com.hzhy.framework.core.service.IBaseService+.*(..))" />
		<aop:advisor pointcut-ref="hzhyAop" advice-ref="hzhyTxAdvice" order="2"/>
	</aop:config>
</beans>

二、继承类AbstractRoutingDataSource并重写方法：

注意：类的上面增加排序@Order(1)

import org.springframework.core.annotation.Order;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
@Order(1)
public class DynamicDataSource extends AbstractRoutingDataSource {
    /** * 取得当前使用那个数据源。 */

    @Override
    protected Object determineCurrentLookupKey() {
        return DbContextHolder.getDbType();
    }
}

三、增加切换数据源的方法：

public class DbContextHolder {
    private static final ThreadLocal contextHolder = new ThreadLocal();

    /** * 设置当前数据库。 * @param dbType */
    public static void setDbType(String dbType) {
        contextHolder.set(dbType);
    }

    /** * 取得当前数据源。 * @return */
    public static String getDbType() {
        String str = (String) contextHolder.get();
        return str;
    }

    /** * 清除设置的数据源 */
    public static void clearDbType() {
        contextHolder.remove();
    }

}

四、测试

public void test(){
        BaseStaffDomain staffDomain = new BaseStaffDomain();
        staffDomain.setName("孙六");
        try {
            DbContextHolder.setDbType("dataSource2");// 切换数据源
            baseStaffService.save(staffDomain);
            DbContextHolder.clearDbType();//清除数据源
        }catch (Exception e){
            e.printStackTrace();
        }
    }