数维图SSM框架后端技术贴：多个数据源的配置

 在实际软件项目中经常会有这种需求，就是可能要连接多个数据库，比如有时候要与其他的业务数据对接、或者数据库采用的读写分离。SpringMVC中提供的动态数据源的配置实现这种需求。

以下以数维图开源SSM框架中实现的多数据源配置为例讲行描述。有兴趣的朋友可以下载工程进行测试。

1. 增加多个数据源的连接信息，在resource/applicationContext-jdbc.properties中添加两个数据源连接。

jdbc.user=root
jdbc.password=root
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm_base?characterEncoding=utf8

jdbc.r1.user=root
jdbc.r1.password=root
jdbc.r1.driver=com.mysql.jdbc.Driver
jdbc.r1.url=jdbc:mysql://localhost:3306/etravel?characterEncoding=utf8

上面一个是默认的数据连接，下面的是扩展的数据连接，框架中定义的写法是 jdbc.r数字，如果还要增加更多的数据源则是写 jdbc.r2    jdbc.r3...

2. 在SpringMVC的配置文件中配置多个数据源，在WEB-INF/applicationContext.xml

<context:property-placeholder location="classpath:applicationContext-jdbc.properties"/>
	
	<!-- 配置数据库连接池 c3p0   -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	 	<!-- 配置链接数据库的基本属性 -->
		<property name="user" value="${jdbc.user}" />
		<property name="password" value="${jdbc.password}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="driverClass" value="${jdbc.driver}" />
		<!-- 配置 c3p0 的数据源自身的属性 -->
		<property name="minPoolSize" value="${jdbc.minPoolSize}" />
		<property name="initialPoolSize" value="${jdbc.initialPoolSize}" />
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
		<property name="acquireIncrement" value="${jdbc.acquireIncrement}" />
		<property name="maxIdleTime" value="${jdbc.maxIdleTime}"></property>
		<property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}" />
		<property name="autoCommitOnClose" value="${jdbc.autoCommitOnClose}" />
		<property name="breakAfterAcquireFailure" value="${jdbc.breakAfterAcquireFailure}" />
		<property name="checkoutTimeout" value="${jdbc.checkoutTimeout}" />
		<property name="maxStatements" value="${jdbc.maxStatements}"></property>
		<property name="maxStatementsPerConnection" value="${jdbc.maxStatementsPerConnection}"></property>
		<property name="numHelperThreads" value="${jdbc.numHelperThreads}"></property>
	 	<property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}"></property>
	 </bean>
	<!--  -->
	<bean id="dataSourceR1" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.r1.user}" />
		<property name="password" value="${jdbc.r1.password}" />
		<property name="jdbcUrl" value="${jdbc.r1.url}" />
		<property name="driverClass" value="${jdbc.r1.driver}" />
		<property name="minPoolSize" value="${jdbc.minPoolSize}" />
		<property name="initialPoolSize" value="${jdbc.initialPoolSize}" />
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
		<property name="acquireIncrement" value="${jdbc.acquireIncrement}" />
		<property name="maxIdleTime" value="${jdbc.maxIdleTime}"></property>
		<property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}" />
		<property name="autoCommitOnClose" value="${jdbc.autoCommitOnClose}" />
		<property name="breakAfterAcquireFailure" value="${jdbc.breakAfterAcquireFailure}" />
		<property name="checkoutTimeout" value="${jdbc.checkoutTimeout}" />
		<property name="maxStatements" value="${jdbc.maxStatements}"></property>
		<property name="maxStatementsPerConnection" value="${jdbc.maxStatementsPerConnection}"></property>
		<property name="numHelperThreads" value="${jdbc.numHelperThreads}"></property>
	 	<property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}"></property>
	 </bean>

3. 增加一个数据源持有类，用来切换数据源，类：com.sovitjs.manager.base.DBContextHolder

package com.sovitjs.manager.base;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * @author 
 *
 */
public class DBContextHolder {
	
	/*
	 * 程threadlocal 
    */  
	private static ThreadLocal<String> contextHolder = new ThreadLocal<String>(); 
	
	public static String DB_TYPE_W = "dataSourceKey";
	public static String DB_TYPE_R1 = "dataSourceKeyR1";
	
	public static boolean DB_TYPE_W_VALID = true;
	public static boolean DB_TYPE_R1_VALID = false;
	
	public static final Map<String,String> mysqlIpMap = new HashMap<String,String>(){{
		put(DB_TYPE_W,"192.168.1.109");
		//put(DB_TYPE_R1,"192.168.1.108");
	}};
	
	public static String getDbType() {
		String db = contextHolder.get();
		if(db == null) {
			db = DB_TYPE_W;
		}
		return db;
	}
	
	public static void setDbType(String str) {  
        contextHolder.set(str);  
    }
	
	public static void setDbType(int wrType) {
		if(wrType == 1) {
			contextHolder.set(DBContextHolder.DB_TYPE_W);
		}else if(wrType == 2) {
			Map<String,String> rMap = new HashMap<String,String>();
			System.out.println("-------------------DB_TYPE_R1_VALID:"+DB_TYPE_R1_VALID);
			List<String> idList = new ArrayList<String>();
			if(DB_TYPE_R1_VALID == true) {
				rMap.put("1", DBContextHolder.DB_TYPE_R1);
				idList.add("1");
			}
			System.out.println("-------------------DB_TYPE_W_VALID:"+DB_TYPE_W_VALID);
			if(DB_TYPE_W_VALID == true) {
				rMap.put("2", DBContextHolder.DB_TYPE_W);
				idList.add("2");
			}
			String[] ids = (String[])idList.toArray(new String[0]);
			Random rnd = new Random();
			Integer startNum = rnd.nextInt(ids.length);
			String theId = ids[startNum];
			System.out.println("-------------------rMap.get(theId):"+rMap.get(theId));
			contextHolder.set(rMap.get(theId));
		}
	}
	
	public static void clearDBType() {
		contextHolder.remove();
	}
}

4. 增加数据源路由类，类：com.sovitjs.manager.base.DynamicDataSource

import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;

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

/**
 * @author c
 *
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

	/* (non-Javadoc)
	 * @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()
	 */
	@Override
	protected Object determineCurrentLookupKey() {
		// TODO Auto-generated method stub
		 return DBContextHolder.getDbType();
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

}

设置动态数据源和事务配置

<!-- 动态数据源 -->  
   <bean id="dynamicDataSource" class="com.sovitjs.manager.base.DynamicDataSource">  
       <!-- 通过key-value关联数据源 -->  
       <property name="targetDataSources">  
           <map>  
               <entry value-ref="dataSource" key="dataSourceKey"></entry>  
               <entry value-ref="dataSourceR1" key="dataSourceKeyR1"></entry>
           </map>  
       </property>  
       <property name="defaultTargetDataSource" ref="dataSource" />      
   </bean>
	
	<!-- 开启事务支持 -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" p:dataSource-ref="dynamicDataSource"/>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 
		<!--dataSource属性指定要用到的连接池--> 
		<property name="dataSource" ref="dynamicDataSource"/>
		
		<property name="mapperLocations" value="classpath:com.sovitjs.**.xml"  />
		<!--configLocation属性指定mybatis的核心配置文件 -->
		<property name="configLocation" value="classpath:Configuration.xml" /> 
	</bean>

在服务类中设置使用某个数据源：

//设置用哪个数据源
DBContextHolder.setDbType(DBContextHolder.DB_TYPE_W);
DBContextHolder.setDbType(DBContextHolder.DB_TYPE_R1);

//设置随机使用数据源
DBContextHolder.setDbType(2);//1为默认数据源

具体请参考工程中的源代码。

如有疑问请进QQ群：762443014

对产品感兴趣，免费使用

免费使用SovitJS

热门文章