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

来源：互联网发布：网络摄像头uid密码大全编辑：程序博客网时间：2024/06/07 20:45

<!-- 数据源相同的内容 --><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.OracleDriveroracle.jdbc.url=jdbc:oracle:thin:@192.168.3.223:1521:ORCLoracle.jdbc.username=nt_yxtoracle.jdbc.password=nt_yxt#################################mysql######################################mysql.jdbc.driverClassName=com.mysql.jdbc.Drivermysql.jdbc.url=jdbc:mysql://192.168.20.201:3306/itsm_1217?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&tinyInt1isBit=false&allowMultiQueries=truemysql.jdbc.username=rootmysql.jdbc.password=root123jdbc.miniPoolSize = 1jdbc.maxPoolSize = 20jdbc.initialPoolSize = 1jdbc.maxIdleTime = 25000jdbc.acquireIncrement = 1jdbc.acquireRetryAttempts = 30jdbc.acquireRetryDelay = 1000jdbc.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);}@Overrideprotected 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

0 0