第一步
定义 相关配置。不多说 上代码
实体类,用于数据库DB 操作
package com.framework.conf.dynamic_db;
import lombok.Data;
@Data
public class DataSource {
String datasourceId;
String url;
String userName;
String passWord;
String code;
String databasetype;
}
注解定义
package com.framework.conf.dynamic_db;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 切换数据
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({
ElementType.METHOD,ElementType.PARAMETER
})
public @interface DBChange {
}
数据源方法调用
package com.framework.conf.dynamic_db;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class DBContextHolder {
private final static Logger log = LoggerFactory.getLogger(DBContextHolder.class);
// 对当前线程的操作-线程安全的
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
// 调用此方法,切换数据源
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
log.info("已切换到数据源:{}",dataSource);
}
// 获取数据源
public static String getDataSource() {
return contextHolder.get();
}
// 删除数据源
public static void clearDataSource() {
contextHolder.remove();
log.info("已切换到主数据源");
}
}
连接池配置 ,此处没有封装出来。意味着每次切换都需要 装配,建议小伙伴自己搞一下
package com.framework.conf.dynamic_db;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* 连接池配置
*/
@Configuration
@EnableTransactionManagement
public class DruidDBConfig {
private final Logger log = LoggerFactory.getLogger(getClass());
// adi数据库连接信息
@Value("${spring.datasource.master.url}")
private String dbUrl;
@Value("${spring.datasource.master.username}")
private String username;
@Value("${spring.datasource.master.password}")
private String password;
@Value("${spring.datasource.master.driver-class-name}")
private String driverClassName;
// 连接池连接信息
@Value("${spring.datasource.master.initialSize}")
private int initialSize;
@Value("${spring.datasource.master.minIdle}")
private int minIdle;
@Value("${spring.datasource.master.maxActive}")
private int maxActive;
@Value("${spring.datasource.master.maxWait}")
private int maxWait;
@Bean // 声明其为Bean实例
@Primary // 在同样的DataSource中,首先使用被标注的DataSource
@Qualifier("mainDataSource")
public DataSource dataSource() throws SQLException {
DruidDataSource datasource = new DruidDataSource();
// 基础连接信息
datasource.setUrl(dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
// 连接池连接信息
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setPoolPreparedStatements(true); //是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
datasource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");//对于耗时长的查询sql,会受限于ReadTimeout的控制,单位毫秒
datasource.setTestOnBorrow(true); //申请连接时执行validationQuery检测连接是否有效,这里建议配置为TRUE,防止取到的连接不可用
datasource.setTestWhileIdle(true);//建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
String validationQuery = "select 1 from dual";
datasource.setValidationQuery(validationQuery); //用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
// datasource.setFilters("stat,wall");//属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有:监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
datasource.setTimeBetweenEvictionRunsMillis(60000); //配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
datasource.setMinEvictableIdleTimeMillis(180000); //配置一个连接在池中最小生存的时间,单位是毫秒,这里配置为3分钟180000
datasource.setKeepAlive(true); //打开druid.keepAlive之后,当连接池空闲时,池中的minIdle数量以内的连接,空闲时间超过minEvictableIdleTimeMillis,则会执行keepAlive操作,即执行druid.validationQuery指定的查询SQL,一般为select * from dual,只要minEvictableIdleTimeMillis设置的小于防火墙切断连接时间,就可以保证当连接空闲时自动做保活检测,不会被防火墙切断
datasource.setRemoveAbandoned(true); //是否移除泄露的连接/超过时间限制是否回收。
datasource.setRemoveAbandonedTimeout(3600); //泄露连接的定义时间(要超过最大事务的处理时间);单位为秒。这里配置为1小时
datasource.setLogAbandoned(true);
return datasource;
}
@Bean(name = "dynamicDataSource")
@Qualifier("dynamicDataSource")
public DynamicDataSource dynamicDataSource() throws SQLException {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setDebug(false);
//配置缺省的数据源
// 默认数据源配置 DefaultTargetDataSource
dynamicDataSource.setDefaultTargetDataSource(dataSource());
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
//额外数据源配置 TargetDataSources
targetDataSources.put("mainDataSource", dataSource());
dynamicDataSource.setTargetDataSources(targetDataSources);
return dynamicDataSource;
}
@Bean
public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
return new DataSourceTransactionManager(dynamicDataSource);
}
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dynamicDataSource());
//解决手动创建数据源后字段到bean属性名驼峰命名转换失效的问题
sqlSessionFactoryBean.setConfiguration(configuration());
// 设置mybatis的主配置文件
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
//手动配置mybatis的mapper.xml资源路径,如果单纯使用注解方式,不需要配置该行
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:/mapper/*/*.xml"));
return sqlSessionFactoryBean.getObject();
}
/**
* 读取驼峰命名设置
*
* @return
*/
@Bean
@ConfigurationProperties(prefix = "mybatis.configuration")
public org.apache.ibatis.session.Configuration configuration() {
return new org.apache.ibatis.session.Configuration();
}
}
此处是 动态切换的核心 (重写了AbstractRoutingDataSource 父类的方法 )
package com.framework.conf.dynamic_db;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.stat.DruidDataSourceStatManager;
import com.framework.utils.DBUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.util.StringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Map;
import java.util.Set;
public class DynamicDataSource extends AbstractRoutingDataSource {
private boolean debug = true;
private final Logger log = LoggerFactory.getLogger(getClass());
private Map<Object, Object> dynamicTargetDataSources;
private Object dynamicDefaultTargetDataSource;
@Override
protected Object determineCurrentLookupKey() {
String datasource = DBContextHolder.getDataSource();
if (!StringUtils.isEmpty(datasource)) {
Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
if (dynamicTargetDataSources2.containsKey(datasource)) {
log.info("---当前数据源:" + datasource + "---");
} else {
log.info("不存在的数据源:");
return null;
}
} else {
log.info("---当前数据源:默认数据源---");
}
return datasource;
}
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
this.dynamicTargetDataSources = targetDataSources;
}
// 创建数据源
public boolean createDataSource(String key, String driveClass, String url, String username, String password, String databasetype) {
try {
try { // 排除连接不上的错误
Class.forName(driveClass);
DriverManager.getConnection(url, username, password);// 相当于连接数据库
} catch (Exception e) {
return false;
}
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setName(key);
druidDataSource.setDriverClassName(driveClass);
druidDataSource.setUrl(url);
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.setInitialSize(1); //初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时
druidDataSource.setMaxActive(20); //最大连接池数量
druidDataSource.setMaxWait(60000); //获取连接时最大等待时间,单位毫秒。当链接数已经达到了最大链接数的时候,应用如果还要获取链接就会出现等待的现象,等待链接释放并回到链接池,如果等待的时间过长就应该踢掉这个等待,不然应用很可能出现雪崩现象
druidDataSource.setMinIdle(5); //最小连接池数量
String validationQuery = "select 1 from dual";
if("mysql".equalsIgnoreCase(databasetype)) {
driveClass = DBUtil.mysqldriver;
validationQuery = "select 1";
} else if("oracle".equalsIgnoreCase(databasetype)){
driveClass = DBUtil.oracledriver;
druidDataSource.setPoolPreparedStatements(true); //是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(50);
int sqlQueryTimeout = 5000;
druidDataSource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout="+sqlQueryTimeout);//对于耗时长的查询sql,会受限于ReadTimeout的控制,单位毫秒
} else if("dm".equalsIgnoreCase(databasetype)){
driveClass = DBUtil.dm;
} else if("sqlserver".equalsIgnoreCase(databasetype)){
driveClass = DBUtil.sqlserverdriver;
validationQuery = "select 1";
}
druidDataSource.setTestOnBorrow(true); //申请连接时执行validationQuery检测连接是否有效,这里建议配置为TRUE,防止取到的连接不可用
druidDataSource.setTestWhileIdle(true);//建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
druidDataSource.setValidationQuery(validationQuery); //用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
druidDataSource.setFilters("stat");//属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有:监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
druidDataSource.setTimeBetweenEvictionRunsMillis(60000); //配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
druidDataSource.setMinEvictableIdleTimeMillis(180000); //配置一个连接在池中最小生存的时间,单位是毫秒,这里配置为3分钟180000
druidDataSource.setKeepAlive(true); //打开druid.keepAlive之后,当连接池空闲时,池中的minIdle数量以内的连接,空闲时间超过minEvictableIdleTimeMillis,则会执行keepAlive操作,即执行druid.validationQuery指定的查询SQL,一般为select * from dual,只要minEvictableIdleTimeMillis设置的小于防火墙切断连接时间,就可以保证当连接空闲时自动做保活检测,不会被防火墙切断
druidDataSource.setRemoveAbandoned(true); //是否移除泄露的连接/超过时间限制是否回收。
druidDataSource.setRemoveAbandonedTimeout(3600); //泄露连接的定义时间(要超过最大事务的处理时间);单位为秒。这里配置为1小时
druidDataSource.setLogAbandoned(true);
druidDataSource.init();
this.dynamicTargetDataSources.put(key, druidDataSource);
setTargetDataSources(this.dynamicTargetDataSources);// 将map赋值给父类的TargetDataSources
super.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
log.info(key+"数据源初始化成功");
return true;
} catch (Exception e) {
log.error(e + "");
return false;
}
}
// 删除数据源
public boolean delDatasources(String datasourceid) {
Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
if (dynamicTargetDataSources2.containsKey(datasourceid)) {
Set<DruidDataSource> druidDataSourceInstances = DruidDataSourceStatManager.getDruidDataSourceInstances();
for (DruidDataSource l : druidDataSourceInstances) {
if (datasourceid.equals(l.getName())) {
dynamicTargetDataSources2.remove(datasourceid);
DruidDataSourceStatManager.removeDataSource(l);
setTargetDataSources(dynamicTargetDataSources2);// 将map赋值给父类的TargetDataSources
super.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
return true;
}
}
return false;
} else {
return false;
}
}
// 测试数据源连接是否有效
public boolean testDatasource(String key, String driveClass, String url, String username, String password) {
try {
Class.forName(driveClass);
DriverManager.getConnection(url, username, password);
return true;
} catch (Exception e) {
return false;
}
}
@Override
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
this.dynamicDefaultTargetDataSource = defaultTargetDataSource;
}
/**
* @param debug
* the debug to set
*/
public void setDebug(boolean debug) {
this.debug = debug;
}
/**
* @return the debug
*/
public boolean isDebug() {
return debug;
}
/**
* @return the dynamicTargetDataSources
*/
public Map<Object, Object> getDynamicTargetDataSources() {
return dynamicTargetDataSources;
}
/**
* @param dynamicTargetDataSources
* the dynamicTargetDataSources to set
*/
public void setDynamicTargetDataSources(Map<Object, Object> dynamicTargetDataSources) {
this.dynamicTargetDataSources = dynamicTargetDataSources;
}
/**
* @return the dynamicDefaultTargetDataSource
*/
public Object getDynamicDefaultTargetDataSource() {
return dynamicDefaultTargetDataSource;
}
/**
* @param dynamicDefaultTargetDataSource
* the dynamicDefaultTargetDataSource to set
*/
public void setDynamicDefaultTargetDataSource(Object dynamicDefaultTargetDataSource) {
this.dynamicDefaultTargetDataSource = dynamicDefaultTargetDataSource;
}
public void createDataSourceWithCheck(DataSource dataSource) throws Exception {
String datasourceId = dataSource.getDatasourceId();
log.info("正在检查数据源:"+datasourceId);
Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
if (dynamicTargetDataSources2.containsKey(datasourceId)) {
log.info("数据源"+datasourceId+"之前已经创建,准备测试数据源是否正常...");
DruidDataSource druidDataSource = (DruidDataSource) dynamicTargetDataSources2.get(datasourceId);
boolean rightFlag = true;
Connection connection = null;
try {
log.info(datasourceId+"数据源的概况->当前闲置连接数:"+druidDataSource.getPoolingCount());
long activeCount = druidDataSource.getActiveCount();
log.info(datasourceId+"数据源的概况->当前活动连接数:"+activeCount);
if(activeCount > 0) {
log.info(datasourceId+"数据源的概况->活跃连接堆栈信息:"+druidDataSource.getActiveConnectionStackTrace());
}
log.info("准备获取数据库连接...");
connection = druidDataSource.getConnection();
log.info("数据源"+datasourceId+"正常");
} catch (Exception e) {
log.error(e.getMessage(),e); //把异常信息打印到日志文件
rightFlag = false;
log.info("缓存数据源"+datasourceId+"已失效,准备删除...");
if(delDatasources(datasourceId)) {
log.info("缓存数据源删除成功");
} else {
log.info("缓存数据源删除失败");
}
} finally {
if(null != connection) {
connection.close();
}
}
if(rightFlag) {
log.info("不需要重新创建数据源");
return;
} else {
log.info("准备重新创建数据源...");
createDataSource(dataSource);
log.info("重新创建数据源完成");
}
} else {
createDataSource(dataSource);
}
}
private void createDataSource(DataSource dataSource) throws Exception {
String datasourceId = dataSource.getDatasourceId();
log.info("准备创建数据源"+datasourceId);
String databasetype = dataSource.getDatabasetype();
String username = dataSource.getUserName();
String password = dataSource.getPassWord();
String url = dataSource.getUrl();
String driveClass = "";
if("mysql".equalsIgnoreCase(databasetype)) {
driveClass = DBUtil.mysqldriver;
} else if("oracle".equalsIgnoreCase(databasetype)){
driveClass = DBUtil.oracledriver;
} else if("sqlserver".equalsIgnoreCase(databasetype)){
driveClass = DBUtil.sqlserverdriver;
} else if("dm".equalsIgnoreCase(databasetype)){
driveClass = DBUtil.dm;
}
if(testDatasource(datasourceId,driveClass,url,username,password)) {
boolean result = this.createDataSource(datasourceId, driveClass, url, username, password, databasetype);
if(!result) {
log.error("数据源"+datasourceId+"配置正确,但是创建失败");
}
} else {
log.error("数据源配置有错误");
}
}
}
AOP根据注解调用
package com.framework.conf.dynamic_db;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
@Aspect
@Component
public class DynamicDataSourceAspect {
@Pointcut("@annotation(com.framework.conf.dynamic_db.DBChange)")
private void pointcut() {
}
@Resource
private DynamicDataSource dynamicDataSource;
@Before("pointcut()")
public void beforeSwitchDS(JoinPoint point){
//获得当前访问的class
Class<?> className = point.getTarget().getClass();
//获得访问的方法名
String methodName = point.getSignature().getName();
Object[] objects=point.getArgs();
//得到方法的参数的类型
Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
String dataSource = null;
try {
// 得到访问的方法对象
Method method = className.getMethod(methodName, argClass);
Parameter[] parameters = method.getParameters();
// 判断是否存在注解
for (int i = 0; i <parameters.length; i++) {
Parameter parameter = parameters[i];
if (parameter.isAnnotationPresent(DBChange.class)) {
DataSource dataSource1=(DataSource) objects[i];
dynamicDataSource.createDataSourceWithCheck(dataSource1);
dataSource =dataSource1.getDatasourceId();
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBContextHolder.setDataSource(dataSource);
}
}
@After("pointcut()")
public void afterSwitchDS(JoinPoint point){
DBContextHolder.clearDataSource();
}
}
多种数据库切换
package com.framework.utils;
import lombok.extern.log4j.Log4j2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
@Log4j2
public class DBUtil {
private DatabaseType databaseType = null;
private String username;
private String password;
private String url;
public static final String mysqldriver = "com.mysql.jdbc.Driver"; // mysql数据库的驱动类
public static final String oracledriver = "oracle.jdbc.OracleDriver"; // oracles数据库的驱动类
public static final String sqlserverdriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // sqlserver数据库的驱动类
public static final String dm = "dm.jdbc.driver.DmDriver"; // 达梦数据库的驱动类
public DBUtil(DatabaseType databaseType, String username,
String password,String url) {
this.databaseType = databaseType;
this.username = username;
this.password = password;
this.url = url;
forName();
}
private void forName() {
try {
if (null == databaseType) {
throw new RuntimeException("没有指定数据库类型");
}
if (databaseType == DatabaseType.MYSQL) {
Class.forName(mysqldriver).newInstance();
} else if (databaseType == DatabaseType.ORACLE) {
Class.forName(oracledriver).newInstance();
} else if (databaseType == DatabaseType.DM) {
Class.forName(dm).newInstance();
} else if(databaseType == DatabaseType.SQLSERVER) {
Class.forName(sqlserverdriver).newInstance();
}
} catch (Exception e) {
log.error(e.getMessage(),e);
throw new RuntimeException("加载数据库驱动失败");
}
}
/**
* 如果程序发生异常,则表明无法连接
* @return
* @throws SQLException
*/
public Connection testConnection() throws SQLException {
Connection conn = DriverManager.getConnection(
url, username, password);// 获取连接对象
return conn;
}
public Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(
url, username, password);// 获取连接对象
return conn;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public boolean connIsOk(Connection conn) throws SQLException {
if (null != conn && !conn.isClosed()) {
return true;
}
return false;
}
public void closeConn(Connection conn) {
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public enum DatabaseType {
MYSQL, ORACLE, DM, SQLSERVER
}
}
查询到数据库配置进行封装
package com.centre.business.dict.service.common;
import com.centre.business.dict.dao.CenHospDbDictDao;
import com.centre.business.dict.entity.CenHospDbDict;
import com.framework.conf.dynamic_db.DataSource;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
/**
* 获取数据源
*/
@Component
public class DataSourceCommon {
@Resource
CenHospDbDictDao cenHospDbDictDao;
public DataSource getDbSource(String dbId){
CenHospDbDict cenHospDbDict = cenHospDbDictDao.queryById(dbId);
DataSource dataSource = new DataSource();
dataSource.setDatasourceId(cenHospDbDict.getId());
dataSource.setDatabasetype(cenHospDbDict.getDbType());
dataSource.setUrl(cenHospDbDict.getDbUrl());
dataSource.setUserName(cenHospDbDict.getDbUserName());
dataSource.setPassWord(cenHospDbDict.getDbPassword());
return dataSource;
}
}
调用的时候这样
/**
* 校验远程数据唯一性
* @param dataSource
* @param tabName
* @param filds
*/
@DBChange
public int checkDbDatas(@DBChange DataSource dataSource, String tabName,List<Filds> filds){
return cenHospColumnDictDao.checkDbDatas(tabName,filds);
}