一、引入MySql数据库驱动包和PostgreSql数据库驱动包,引入Druid数据库连接池jar包,在构建Spring时不要忘了加入spring-jdbc依赖包。
二、自定义动态数据库切换类:
package com.xxx.xxx.common.dynamicdatasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 动态切换数据源
*
* @author dyw
* @date 2019年6月14日
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
/** mysql数据库标识 */
public static final String mysqlDataSource = "mysqlDataSource";
/** oracle数据库标识 */
public static final String oracleDataSource = "oracleDataSource";
/** postgresql数据库标识 */
public static final String postgresqlDataSource = "postgresqlDataSource";
/** sqlserver数据库标识 */
public static final String sqlserverDataSource = "sqlserverDataSource";
/** 当前线程的上下文 */
public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**
* 设置数据源类型
*
* @param customerBDType 用户数据类型
*/
public static void setCustomerDBType(String customerBDType) {
contextHolder.set(customerBDType);
}
/**
* 获取数据源类型
*
* @return
*/
public static String getCustomerDBType() {
return contextHolder.get();
}
/**
* 移除数据源类型
*/
public static void clearCustomerDBType() {
contextHolder.remove();
}
@Override
protected Object determineCurrentLookupKey() {
return getCustomerDBType();
}
}
三、在spring-mybatis.xml中配置:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<description>Spring-MyBatis整合配置文件</description>
<!--##### 引入配置文件 ##### -->
<context:property-placeholder
location="classpath:config.properties" />
<!--##### 数据库连接池配置 ##### -->
<bean id="dataSource"
class="com.xxx.xxx.common.dynamicdatasource.DynamicDataSource">
<property name="defaultTargetDataSource" ref="mysqlDS" /><!--设置默认数据源 -->
<property name="targetDataSources"><!--设置多个数据源 -->
<map>
<entry key="mysqlDataSource" value-ref="mysqlDS" />
<entry key="postgresqlDataSource" value-ref="postgresqlDS" />
</map>
</property>
</bean>
<!-- mysql数据源 -->
<bean name="mysqlDS"
class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
destroy-method="close">
<property name="url" value="${mysql.url}" />
<property name="username" value="${mysql.username}" />
<property name="password" value="${mysql.password}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="${initialSize}" />
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="${maxActive}" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="${minIdle}" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="${maxWait}" />
<property name="validationQuery" value="${validationQuery}" />
<property name="testOnBorrow" value="${testOnBorrow}" />
<property name="testOnReturn" value="${testOnReturn}" />
<property name="testWhileIdle" value="${testWhileIdle}" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis"
value="${timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis"
value="${minEvictableIdleTimeMillis}" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="${removeAbandoned}" />
<!-- 7200秒,也就是2小时 -->
<property name="removeAbandonedTimeout"
value="${removeAbandonedTimeout}" />
<!-- 不关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="${logAbandoned}" />
<!-- 监控数据库 -->
<property name="filters" value="${filters}" />
</bean>
<!-- postgresql数据源 -->
<bean name="postgresqlDS"
class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
destroy-method="close">
<property name="url" value="${postgresql.url}" />
<property name="username" value="${postgresql.username}" />
<property name="password" value="${postgresql.password}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="${initialSize}" />
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="${maxActive}" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="${minIdle}" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="${maxWait}" />
<property name="validationQuery" value="${validationQuery}" />
<property name="testOnBorrow" value="${testOnBorrow}" />
<property name="testOnReturn" value="${testOnReturn}" />
<property name="testWhileIdle" value="${testWhileIdle}" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis"
value="${timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis"
value="${minEvictableIdleTimeMillis}" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="${removeAbandoned}" />
<!-- 7200秒,也就是2小时 -->
<property name="removeAbandonedTimeout"
value="${removeAbandonedTimeout}" />
<!-- 不关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="${logAbandoned}" />
<!-- 监控数据库 -->
<property name="filters" value="${filters}" />
</bean>
<!--其他配置省略-->
</beans>
四、怎样动态切换数据库:
package com.xxx.xxx.controller;
import org.springframework.stereotype.Controller;
import com.xxx.xxx.common.dynamicdatasource.DynamicDataSource;
@Controller
public class TestController {
public String test(String time) {
//一通操作......
//切换数据库
DynamicDataSource.clearCustomerDBType();
DynamicDataSource.setCustomerDBType(DynamicDataSource.postgresqlDataSource);
//又一通操作......
return null;
}
}
附:config.properties
#MySql数据库配置
mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/postgres
mysql.username=root
mysql.password=123456
#mysql.password=
#PostgreSQL数据库配置
postgresql.driver=org.postgresql.Driver
postgresql.url=jdbc:postgresql://localhost:5432/postgres
postgresql.username=postgres
postgresql.password=postgres
#Druid数据库连接池配置
validationQuery=SELECT 1
initialSize=10
maxActive=150
minIdle=1
maxWait=60000
testOnBorrow=false
testOnReturn=false
testWhileIdle=true
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=25200000
removeAbandoned=false
removeAbandonedTimeout=7200
logAbandoned=true
filters=mergeStat