数维图资讯
文章查看
数维图SSM框架后端技术贴:多个数据源的配置
数维图
2021-10-27
在实际软件项目中经常会有这种需求,就是可能要连接多个数据库,比如有时候要与其他的业务数据对接、或者数据库采用的读写分离。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
对产品感兴趣,免费使用
热门文章
Web组态示例
查看更多的示例