Spring动态切换多数据源解决方案

<!-- 数据源相同的内容 -->
	<bean class="com.p6spy.engine.spy.P6DataSource">
		<constructor-arg ref="parentDataSource"></constructor-arg>
	</bean>  
	<bean id="parentDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="minPoolSize" value="${jdbc.miniPoolSize}" />
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
		<property name="initialPoolSize" value="${jdbc.initialPoolSize}" />
		<property name="maxIdleTime" value="${jdbc.maxIdleTime}" />
		<property name="acquireIncrement" value="${jdbc.acquireIncrement}" />
		<property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}" />
		<property name="acquireRetryDelay" value="${jdbc.acquireRetryDelay}" />
		<property name="checkoutTimeout" value="${jdbc.checkoutTimeout}" />
	</bean>
	
	<!-- 以下配置各个数据源的特性 -->  
	<bean parent="parentDataSource" id="mysqlDataSource">
		<property name="driverClass" value="${mysql.jdbc.driverClassName}" />
		<property name="jdbcUrl" value="${mysql.jdbc.url}" />
		<property name="user" value="${mysql.jdbc.username}" />
		<property name="password" value="${mysql.jdbc.password}" />  
	</bean>   
	<bean parent="parentDataSource" id="oracleDataSource">
		<property name="driverClass" value="${oracle.jdbc.driverClassName}" />
		<property name="jdbcUrl" value="${oracle.jdbc.url}" />
		<property name="user" value="${oracle.jdbc.username}" />
		<property name="password" value="${oracle.jdbc.password}" />  
	</bean>   
	<!-- end 配置各个数据源的特性 -->  
	
	<!-- 动态数据源 -->
	<bean class="com.fable.workflow.business.util.DynamicDataSource" id="dataSource">  
	    <property name="targetDataSources">   
	       <map key-type="java.lang.String">   
	           <entry value-ref="mysqlDataSource" key="mysql"></entry>  
	           <entry value-ref="oracleDataSource" key="oracle"></entry>  
	       </map>   
	    </property>   
	    <property name="defaultTargetDataSource" ref="mysqlDataSource" ></property>  
	</bean>

#################################oracle######################################
oracle.jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
oracle.jdbc.url=jdbc:oracle:thin:@192.168.3.223:1521:ORCL
oracle.jdbc.username=nt_yxt
oracle.jdbc.password=nt_yxt

#################################mysql######################################
mysql.jdbc.driverClassName=com.mysql.jdbc.Driver
mysql.jdbc.url=jdbc:mysql://192.168.20.201:3306/itsm_1217?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&tinyInt1isBit=false&allowMultiQueries=true
mysql.jdbc.username=root
mysql.jdbc.password=root123

jdbc.miniPoolSize = 1
jdbc.maxPoolSize = 20
jdbc.initialPoolSize = 1
jdbc.maxIdleTime = 25000
jdbc.acquireIncrement = 1
jdbc.acquireRetryAttempts = 30
jdbc.acquireRetryDelay = 1000
jdbc.checkoutTimeout=3000

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
	private static final ThreadLocal<String> threadLocal = new ThreadLocal<String>(); // 线程本地环境

	// 设置数据源
	public static void setDataSource(String dataSource) {
		threadLocal.set(dataSource);
	}

	@Override
	protected Object determineCurrentLookupKey() {
		return threadLocal.get();
	}

}

public List<MenuBean> queryGrantedMenuByType(String type) throws Exception {
		DynamicDataSource.setDataSource(DataSourceConst.ORACLE);
		List<MenuBean> list = menuBeanMapper.queryByTypeII(type,
				UserSession.getUserId());
		String[] menuIds = new String[list.size()];
		for (int i = 0; i < menuIds.length; i++) {
			menuIds[i] = list.get(i).getId();
		}
		List<MenuBean> menus = menuIds.length > 0 ? menuBeanMapper
				.queryByIds(menuIds) : null;
		System.out.println(menus);
		DynamicDataSource.setDataSource(DataSourceConst.MYSQL);
		list = menuBeanMapper.queryByTypeII(type, UserSession.getUserId());
		menuIds = new String[list.size()];
		for (int i = 0; i < menuIds.length; i++) {
			menuIds[i] = list.get(i).getId();
		}
		menus = menuIds.length > 0 ? menuBeanMapper.queryByIds(menuIds) : null;
		System.out.println(menus);
		return menus;
	}

public interface DataSourceConst {
	String MYSQL = "mysql";

	String ORACLE = "oracle";
}

注意：1、该配置不支持事务，即使如下配置都不行：

<tx:method name="query*" read-only="true" propagation="NOT_SUPPORTED" />

2、为防止除defaultTargetDataSource外的某个数据源对应的数据库实例挂掉而影响系统正常启动，不要在parentDataSource的定义中加init-method="init"配置：

<bean id="parentDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">

参考： http://blog.csdn.net/llhwin2010/article/details/11695781