背景
系统后端需要访问多个数据库,现有的数据库连接配置写入配置文件中。后端需要从一个数据库的配置表里动态的读取其它mysql数据库的链接配置信息，并根据链接信息动态创建数据库链接，发起请求，而且还要能使用现在的一些连接池
设计总体思路
SpringBoot+AOP方式实现多数据源切换，继承AbstractRoutingDataSource实现数据源动态的获取，在service层使用注解指定数据源
步骤
- 对数据源库表进行设计，相关SQL语句如下所示：
CREATE TABLE "YUDB"."DOM_DATABASE" ( "DB_ID" NUMBER NOT NULL ENABLE, "SRC_ID" NUMBER, "DB_NAME" VARCHAR2 ( 30 ), "DB_C_NAME" VARCHAR2 ( 60 ), "NOTE" VARCHAR2 ( 60 ), "TAB_NUM" NUMBER, "DB_SIZE" NUMBER, "UD_TIME" DATE NOT NULL ENABLE, "MD_FILE_ID" VARCHAR2 ( 32 ), CONSTRAINT "PK_DATABASE" PRIMARY KEY ( "DB_ID" ) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "USERS" ENABLE, CONSTRAINT "SYS_C0011295" CHECK ( "DB_ID" IS NOT NULL ) ENABLE, CONSTRAINT "SYS_C0011296" CHECK ( "UD_TIME" IS NOT NULL ) ENABLE, CONSTRAINT "FK_DATABASE" FOREIGN KEY ( "SRC_ID" ) REFERENCES "YUDB"."DOM_DATASOURCE" ( "SRC_ID" ) ON DELETE CASCADE ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "YU"
DataBase库表设计CREATE TABLE "YUDB"."DOM_DATASOURCE" ( "SRC_ID" NUMBER NOT NULL ENABLE, "CHG_ID" NUMBER, "SRC_SID" VARCHAR2 ( 20 ), "TABLE_SPACE" VARCHAR2 ( 20 ), "IP_ADDR" VARCHAR2 ( 30 ), "SRC_PORT" VARCHAR2 ( 10 ), "SRC_USER" VARCHAR2 ( 20 ), "SRC_PSW" VARCHAR2 ( 20 ), "ORGID" NUMBER, "DBTYPE" VARCHAR2 ( 100 ), CONSTRAINT "PK_DATASOURCE" PRIMARY KEY ( "SRC_ID" ) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "USERS" ENABLE, CONSTRAINT "SYS_C0011298" CHECK ( "SRC_ID" IS NOT NULL ) ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "YU"
DataSource库表设计：
ER图如下图所示：
往yu数据库中的表dataSource里增加数据库的相关配置信息。
- 在配置文件application-dev.yml中，进行多数据源配置
spring: devtools: restart: enabled: true #设置开启热部署 additional-paths: src/main/java #重启目录 exclude: WEB-INF/** datasource: main: username: YUDB password: YUDB_HH url: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/RAC1 driver-class-name: oracle.jdbc.driver.OracleDriver zyml: username: xxxxxx password: xxxxxx url: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/HHU driver-class-name: oracle.jdbc.driver.OracleDriver bjobj: username: xxxxxx password: xxxxxx url: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/BJOBJ driver-class-name: oracle.jdbc.driver.OracleDriver #####DruidDataSource配置##################### type: com.alibaba.druid.pool.DruidDataSource initialSize: 5 minIdle: 5 maxActive: 20 # 配置获取连接等待超时的时间 maxWait: 60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 timeBetweenEvictionRunsMillis: 60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false # 打开PSCache，并且指定每个连接上PSCache的大小 poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 filters: stat,wall,log4j # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 #合并多个DruidDataSource的监控数据 useGlobalDataSourceStat: true ###############以上为配置druid添加的配置######################################## - 创建实体类DataSource和DataBase
DataBase表和DataSource表通过外键src_id链接，先创建DataBase实体类
然后创建DataSource实体类package org.hhu.yu.system.rdbms.entity; import lombok.Data; import javax.persistence.*; import java.util.Date; @Data public class DataBase { private Long db_id; @JoinColumn(name="SRC_ID") @ManyToOne(cascade = CascadeType.ALL) //数据库名 private String db_name; //数据库中文名 private String db_c_nname; //数据库描述 private String db_desc; //表数量 private Long table_num; //数据库大小 private Long data_size; //数据库更新时间 private Date update_time; private String metaId; }import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.*; @ApiModel(description = "数据源对象Model") @Data @AllArgsConstructor @NoArgsConstructor @RequiredArgsConstructor public class DataSource{ @NonNull @ApiModelProperty(value = "数据源id",name ="src_id" ) private Long src_id; @NonNull @ApiModelProperty(value= "数据源实例名",name="src_sid",required=true) private String src_sid; //数据源表空间 private String table_space; @ApiModelProperty(value= "数据源ip地址",name="ip_addr",required=true) private String ip_addr; @ApiModelProperty(value= "数据源端口",name="src_port",required=true) private String src_port; @ApiModelProperty(value= "数据源用户名",name="src_user",required=true) private String src_user; @ApiModelProperty(value= "数据源密码",name="src_pwd",required=true) private String src_pwd; @ApiModelProperty(value= "数据库类型",name="db_type",required=true) private String db_type; @NonNull private DataBase dataBase; } - 创建DruidDBConfig.java，配置Druid数据库连接池
@Configuration
public class DruidDBConfig {
@Bean
@Qualifier("mainDataSource")
@ConfigurationProperties(prefix = "spring.datasource.main")
DataSource mainConfig() throws SQLException{
DruidDataSource build = DruidDataSourceBuilder.create().build();
List<Filter> filters = new ArrayList<>();
filters.add(statFilter());
filters.add(logFilter());
build.setProxyFilters(filters);
return build;
}
@Bean
@Qualifier("zymlDataSource")
@ConfigurationProperties(prefix = "spring.datasource.zyml")
DataSource zymlConfig(){
return DruidDataSourceBuilder.create().build();
}
@Bean
@Qualifier("bjobjDataSource")
@ConfigurationProperties(prefix = "spring.datasource.bjobj")
DataSource bjobjConfig(){
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "dynamicDataSource")
@Primary //优先使用，多数据源
@Qualifier("dynamicDataSource")
public DynamicDataSource dynamicDataSource() throws SQLException {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setDebug(false);
// 默认数据源配置 DefaultTargetDataSource
dynamicDataSource.setDefaultTargetDataSource(mainConfig());
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
//多数据源配置 TargetDataSources
targetDataSources.put("mainDataSource", mainConfig());
targetDataSources.put("zymlDataSource", zymlConfig());
targetDataSources.put("bjobjDataSource", mainConfig());
dynamicDataSource.setTargetDataSources(targetDataSources);
return dynamicDataSource;
}
}
-
创建DBContextHolder类，实现数据源的切换服务
@Slf4j public class DBContextHolder { // 对当前线程的操作-线程安全的 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("已切换到主数据源"); } } -
创建核心的动态数据源配置类，该类继承AbstractRoutingDataSource实现数据源动态的获取。
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
private boolean debug = true;
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("不存在的数据源：");
throw new ADIException("不存在的数据源："+datasource,500);
return null;
}
} else {
log.info("---当前数据源：默认数据源---");
}
return datasource;
}
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
this.dynamicTargetDataSources = targetDataSources;
}
@Override
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
this.dynamicDefaultTargetDataSource = defaultTargetDataSource;
}
/**
* @param debug
* @description 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;
}
/**
* @param dataSource
* @throws Exception
* @description 创建新数据源时检查数据源是否存在
*/
public void createDataSourceWithCheck(DataSource dataSource) throws Exception {
@NonNull Long src_id = dataSource.getSrc_id();
log.info("正在检查数据源："+src_id);
Map<Object, Object> currentDynamicTargetDataSources = this.dynamicTargetDataSources;
if (currentDynamicTargetDataSources.containsKey(src_id)) {
log.info("数据源"+src_id+"之前已经创建，准备测试数据源是否正常...");
DruidDataSource druidDataSource = (DruidDataSource) currentDynamicTargetDataSources.get(src_id);
boolean rightFlag = true;
Connection connection = null;
try {
log.info(src_id+"数据源的概况->当前闲置连接数："+druidDataSource.getPoolingCount());
long activeCount = druidDataSource.getActiveCount();
log.info(src_id+"数据源的概况->当前活动连接数："+activeCount);
if(activeCount > 0) {
log.info(src_id+"数据源的概况->活跃连接堆栈信息："+druidDataSource.getActiveConnectionStackTrace());
}
log.info("准备获取数据库连接...");
connection = druidDataSource.getConnection();
log.info("数据源"+src_id+"正常");
} catch (Exception e) {
log.error(e.getMessage(),e); //把异常信息打印到日志文件
rightFlag = false;
log.info("缓存数据源"+src_id+"已失效，准备删除...");
if(delDatasources(src_id)) {
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 {
DBUtils dbUtils = SpringUtils.getBean(DBUtils.class);
@NonNull Long src_id = dataSource.getSrc_id();
log.info("准备创建数据源"+src_id);
String db_type = dataSource.getDb_type();
String username = dataSource.getSrc_user();
String password = dataSource.getSrc_pwd();
String ip_addr = dataSource.getIp_addr();
String src_port = dataSource.getSrc_port();
@NonNull String src_sid = dataSource.getSrc_sid();
String url = null;
String driveClass = null;
if("mysql".equalsIgnoreCase(db_type)) {
driveClass = DBUtils.MYSQL_DRIVER;
dbUtils.setMySQLYUrl(ip_addr,src_port,src_sid);
url = dbUtils.getMySQLYUrl();
} else if("oracle".equalsIgnoreCase(db_type)){
driveClass = DBUtils.ORACLE_DRIVER;
dbUtils.setOracleUrl(ip_addr,src_port,src_sid);
url = dbUtils.getOracleUrl();
} else if("dm".equalsIgnoreCase(db_type)){
driveClass = DBUtils.DM_DRIVER;
dbUtils.setDMUrl(ip_addr,src_port,src_sid);
url = dbUtils.getDMUrl();
} else if("sqlserver".equalsIgnoreCase(db_type)){
driveClass = DBUtils.SQLSERVER_DRIVER;
dbUtils.setSQLserverUrl(ip_addr,src_port,src_sid);
url = dbUtils.getSQLserverUrl();
}
if(testDatasource(src_id.toString(),driveClass,url,username,password)) {
boolean result = this.createDataSource(src_id.toString(), driveClass, url, username, password, db_type);
if(!result) {
log.error("数据源"+src_id+"配置正确，但是创建失败");
throw new ADIException("数据源"+src_id+"配置正确，但是创建失败",500);
}
} else {
log.error("数据源配置有错误");
throw new ADIException("数据源配置有错误",500);
}
}
/**
* @description 自定义创建数据源
* @param key
* @param driveClass
* @param url
* @param username
* @param password
* @param db_type
* @return
*/
public boolean createDataSource(String key, String driveClass, String url, String username, String password, String db_type) {
try {
try { // 排除连接不上的错误
Class.forName(driveClass);
DriverManager.getConnection(url, username, password);//连接数据库
} catch (Exception e) {
return false;
}
@SuppressWarnings("resource")
// HikariDataSource druidDataSource = new HikariDataSource();
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(db_type)) {
driveClass = DBUtils.MYSQL_DRIVER;
validationQuery = "select 1";
} else if("oracle".equalsIgnoreCase(db_type)){
driveClass = DBUtils.ORACLE_DRIVER;
druidDataSource.setPoolPreparedStatements(true); //是否缓存preparedStatement,PSCache对支持游标的ORACLE数据库性能提升巨大,在mysql下建议关闭。
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(50);
druidDataSource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout=300000");//对于耗时长的查询sql，会受限于ReadTimeout的控制，单位毫秒
} else if("dm".equalsIgnoreCase(db_type)){
driveClass = DBUtils.DM_DRIVER;
validationQuery = "select 1";
} else if("sqlserver".equalsIgnoreCase(db_type)){
driveClass = DBUtils.SQLSERVER_DRIVER;
validationQuery = "select 1";
}
//申请连接时执行validationQuery检测连接是否有效，这里建议配置为TRUE，防止取到的连接不可用
druidDataSource.setTestOnBorrow(true);
//申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
druidDataSource.setTestWhileIdle(true);
//用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
druidDataSource.setValidationQuery(validationQuery);
//属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
druidDataSource.setFilters("stat");
//配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
//配置一个连接在池中最小生存的时间，单位是毫秒，这里配置为3分钟180000
druidDataSource.setMinEvictableIdleTimeMillis(180000);
//打开druid.keepAlive之后，当连接池空闲时，池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，
// 则会执行keepAlive操作，即执行druid.validationQuery指定的查询SQL，一般为select * from dual，只要
// minEvictableIdleTimeMillis设置的小于防火墙切断连接时间，就可以保证当连接空闲时自动做保活检测，不会被防火墙切断
druidDataSource.setKeepAlive(true);
//是否移除泄露的连接/超过时间限制是否回收。
druidDataSource.setRemoveAbandoned(true);
//泄露连接的定义时间(要超过最大事务的处理时间)；单位为秒。这里配置为1小时
druidDataSource.setRemoveAbandonedTimeout(3600);
//移除泄露连接发生是是否记录日志
druidDataSource.setLogAbandoned(true);
druidDataSource.init();
this.dynamicTargetDataSources.put(key, druidDataSource);
// 将map赋值给父类的TargetDataSources
setTargetDataSources(this.dynamicTargetDataSources);
super.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
log.info(key+"数据源初始化成功");
//log.info(key+"数据源的概况："+druidDataSource.dump());
return true;
} catch (Exception e) {
log.error(e + "");
return false;
}
}
/**
* 删除数据源
* @param db_id
* @return
*/
public boolean delDatasources(Long db_id) {
Map<Object, Object> currentDynamicTargetDataSources = this.dynamicTargetDataSources;
if (currentDynamicTargetDataSources.containsKey(db_id)) {
Set<DruidDataSource> druidDataSourceInstances = DruidDataSourceStatManager.getDruidDataSourceInstances();
for (DruidDataSource l : druidDataSourceInstances) {
if (db_id.equals(l.getName())) {
currentDynamicTargetDataSources.remove(db_id);
DruidDataSourceStatManager.removeDataSource(l);
// 将map赋值给父类的TargetDataSources
setTargetDataSources(currentDynamicTargetDataSources);
// 将TargetDataSources中的连接信息放入resolvedDataSources管理
super.afterPropertiesSet();
return true;
}
}
return false;
} else {
return false;
}
}
/**
* 测试数据源连接
* @param driveClass
* @param url
* @param username
* @param password
* @return
*/
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;
}
}
}
扫一扫
841
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
