Spring项目配置多数据源
Spring是支持多数据源管理的，称为数据源路由（RoutingDataSource），原理大概如下图所示
具体实现如下
DynamicDataSource：多数据源的工具类
determineCurrentLookupKey：返回当前需要切换的数据源的key
addDataSource：创建数据源，并存入数据源路由中
updateDataSource：更新数据源
removeDataSource：从路由中删除数据源
package com.jeedev.jfast.datasource;
import java.lang.reflect.Field;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.LockedAccountException;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import sun.org.mozilla.javascript.internal.Undefined;
import com.alibaba.druid.pool.DruidDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Value("${jdbc.driver}")
private String driver;
@Value("${jdbc.initialSize}")
private Integer initialSize;
@Value("${jdbc.maxActive}")
private Integer maxActive;
@Value("${jdbc.minIdle}")
private Integer minIdle;
@Value("${jdbc.maxWait}")
private Integer maxWait;
@Value("${jdbc.timeBetweenEvictionRunsMillis}")
private Integer timeBetweenEvictionRunsMillis;
@Value("${jdbc.minEvictableIdleTimeMillis}")
private Integer minEvictableIdleTimeMillis;
@Value("${jdbc.validationQuery}")
private String validationQuery;
@Value("${jdbc.testWhileIdle}")
private boolean testWhileIdle;
@Value("${jdbc.testOnBorrow}")
private boolean testOnBorrow;
@Value("${jdbc.testOnReturn}")
private boolean testOnReturn;
@Value("${jdbc.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${jdbc.maxPoolPreparedStatementPerConnectionSize}")
private Integer maxPoolPreparedStatementPerConnectionSize;
@Value("${jdbc.filters}")
private String filters;
@Value("${jdbc.url.type}")
private String urlType;
public static boolean useSession = false;
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getRouteKey();
}
/**
* 根据用户创建数据源
*/
public void addDataSource(String dataSourceId,String dbUrl,String dbUser,String dbPwd) {
if(dataSourceId == null || "".equals(dataSourceId)){
return;
}
if(dbUrl == null || "".equals(dbUrl)){
return;
}
if(dbUser == null || "".equals(dbUser)){
return;
}
if(dbPwd == null || "".equals(dbPwd)){
return;
}
try {
Field targetDataSources = AbstractRoutingDataSource.class.getDeclaredField("targetDataSources");
targetDataSources.setAccessible(true);
Map<Object, Object> dataSources = (Map<Object, Object>) targetDataSources.get(this);
if (dataSources.get(dataSourceId) != null)
return;
DruidDataSource dds = dataSource(dbUrl, dbUser, dbPwd);
dataSources.put(dataSourceId, dds);
setTargetDataSources(dataSources);
super.afterPropertiesSet();
}
catch (Exception e) {
e.printStackTrace();
throw new LockedAccountException(); //账号无效
}
}
/**
* 根据用户更新覆盖数据源
*/
public void updateDataSource(String dataSourceId,String dbUrl,String dbUser,String dbPwd) {
if(dataSourceId == null || "".equals(dataSourceId)){
return;
}
if(dbUrl == null || "".equals(dbUrl)){
return;
}
if(dbUser == null || "".equals(dbUser)){
return;
}
if(dbPwd == null || "".equals(dbPwd)){
return;
}
try {
Field targetDataSources = AbstractRoutingDataSource.class.getDeclaredField("targetDataSources");
targetDataSources.setAccessible(true);
Map<Object, Object> dataSources = (Map<Object, Object>) targetDataSources.get(this);
if (dataSources.get(dataSourceId) != null) {
removeDataSource(dataSourceId);
addDataSource(dataSourceId, dbUrl, dbUser, dbPwd);
}else{
addDataSource(dataSourceId, dbUrl, dbUser, dbPwd);
}
}
catch (Exception e) {
e.printStackTrace();
throw new LockedAccountException(); //账号无效
}
}
/**
* 删除数据源
*/
public void removeDataSource(String dataSourceId) {
if(dataSourceId == null || "".equals(dataSourceId)){
return;
}
try {
Field targetDataSources = AbstractRoutingDataSource.class.getDeclaredField("targetDataSources");
targetDataSources.setAccessible(true);
Map<Object, Object> dataSources = (Map<Object, Object>) targetDataSources.get(this);
if (dataSources.get(dataSourceId) == null)
return;
dataSources.remove(dataSourceId);
setTargetDataSources(dataSources);
super.afterPropertiesSet();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public String getDataSourceLink(String ip, String port, String exmpNm){
if(port != null && !"".equals(port)&& !"undefined".equals(port)){
port = ":" + port;
}else port = "";
if("service_name".equals(urlType)){
return "jdbc:oracle:thin:@//" + ip + port + "/" + exmpNm;
}else {
return "jdbc:oracle:thin:@" + ip + port + ":" + exmpNm;
}
}
public DruidDataSource dataSource(String dbUrl,String dbUser,String dbPwd) throws Exception {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUrl(dbUrl);
druidDataSource.setUsername(dbUser);
druidDataSource.setPassword(dbPwd);
druidDataSource.setDriverClassName(driver);//oracle.jdbc.driver.OracleDriver
druidDataSource.setInitialSize(initialSize);//1
druidDataSource.setMaxActive(maxActive);//30
druidDataSource.setMinIdle(minIdle);//0
druidDataSource.setMaxWait(maxWait);//60000
druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);//60000
druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);//300000
druidDataSource.setValidationQuery(validationQuery);//SELECT 1 FROM DUAL
druidDataSource.setTestWhileIdle(testWhileIdle);//true
druidDataSource.setTestOnBorrow(testOnBorrow);//false
druidDataSource.setTestOnReturn(testOnReturn);//false
druidDataSource.setPoolPreparedStatements(poolPreparedStatements);//true
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);//20
druidDataSource.setFilters(filters);//stat,wall
try {
if (null != druidDataSource) {
druidDataSource.setFilters("wall,stat");
druidDataSource.setUseGlobalDataSourceStat(true);
druidDataSource.setDbType("oracle");
druidDataSource.init();
}
} catch (Exception e) {
throw new RuntimeException(
"load datasource error, dbProperties is :", e);
}
return druidDataSource;
}
}DataSourceContextHolder：设置数据源得key
package com.jeedev.jfast.datasource;
public class DataSourceContextHolder {
private static ThreadLocal<String> routeKey = new ThreadLocal<String>();
/**
* 获取当前线程的数据源路由的key
*/
public static String getRouteKey() {
String key = routeKey.get();
return key;
}
/**
* 绑定当前线程数据源路由的key
* 使用完成后必须调用removeRouteKey()方法删除
*/
public static void setRouteKey(String key) {
routeKey.set(key);
}
/**
* 删除与当前线程绑定的数据源路由的key
*/
public static void removeRouteKey() {
routeKey.remove();
}
public static void main(String[] args) {
Thread t1 = new Thread(new Runnable(){
public void run(){
DataSourceContextHolder.setRouteKey("1");
System.out.println("t1=" + DataSourceContextHolder.getRouteKey());
}});
Thread t2 = new Thread(new Runnable(){
public void run(){
System.out.println("t2=" + DataSourceContextHolder.getRouteKey());
}});
t1.start();
t2.start();
}
}用的是ThreadLocal，每个线程都会去切换对应的数据源，在登录请求或filter中，设置当前请求的路由
DataSourceContextHolder.setRouteKey(session.getOrgLglpsnId());
配置文件的修改
jdbc数据源配置
<!-- 配置数据源 -->
<bean name="defaultDataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${jdbc.initialSize}" />
<property name="minIdle" value="${jdbc.minIdle}" />
<property name="maxActive" value="${jdbc.maxActive}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${jdbc.maxWait}" />
<!-- 超过时间限制是否回收 -->
<property name="removeAbandoned" value="${jdbc.removeAbandoned}" />
<!-- 超过时间限制多长； -->
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${jdbc.validationQuery}" />
<property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
<property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
<property name="testOnReturn" value="${jdbc.testOnReturn}" />
<!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="${jdbc.poolPreparedStatements}" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="${jdbc.maxPoolPreparedStatementPerConnectionSize}" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="${jdbc.filters}" />
<property name="timeBetweenLogStatsMillis" value="${jdbc.timeBetweenLogStatsMillis}" />
</bean>
<!-- 输出可执行的SQL -->
<bean id="log-filter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">
<property name="statementExecutableSqlLogEnable" value="true" />
</bean>
<!-- 多法人多数据源配置 -->
<bean id="dynamicdatasource" class="com.jeedev.jfast.datasource.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="defaultDataSource" value-ref="defaultDataSource" />
</map>
</property>
<property name="defaultTargetDataSource" ref="defaultDataSource" />
</bean>主要是数据路由（dynamicdatasource），然后配置一个默认数据源
mybatis的sqlSessionFactory引用dynamicdatasource，其他不变
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dynamicdatasource" /> 。。。
jpa也是，entityManagerFactory引用dynamicdatasource，其他不变
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <!-- 指定数据源 --> <property name="dataSource" ref="dynamicdatasource"/> 。。。
|
|
很早以前使用springMVC做得 |
|
|
项目会配置一个初始化的数据源，即为默认法人。 当用户登录后，会去默认数据库查询用户法人信息，根据法人信息查询法人对应数据库信息，然后创建数据源或get数据源，然后切换数据源，进入首页加载数据 |