SpringBoot+AOP實現多數據源動態切換

• 背景
• 設計總體思路
• 步驟

背景

系統后端需要訪問多個數據庫,現有的數據庫連接配置寫入配置文件中。后端需要從一個數據庫的配置表里動態的讀取其它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" 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" DataBase庫表設計

DataSource庫表設計：

ER圖如下圖所示：

往yu數據庫中的表dataSource里增加數據庫的相關配置信息。

在配置文件application-dev.yml中，進行多數據源配置spring:devtools:restart:enabled: true #設置開啟熱部署additional-paths: src/main/java #重啟目錄exclude: WEB-INF/**datasource:main:username: YUDBpassword: YUDB_HHurl: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/RAC1driver-class-name: oracle.jdbc.driver.OracleDriverzyml:username: xxxxxxpassword: xxxxxxurl: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/HHUdriver-class-name: oracle.jdbc.driver.OracleDriverbjobj:username: xxxxxxpassword: xxxxxxurl: jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/BJOBJdriver-class-name: oracle.jdbc.driver.OracleDriver#####DruidDataSource配置#####################type: com.alibaba.druid.pool.DruidDataSourceinitialSize: 5minIdle: 5maxActive: 20# 配置獲取連接等待超時的時間maxWait: 60000# 配置間隔多久才進行一次檢測，檢測需要關閉的空閑連接，單位是毫秒timeBetweenEvictionRunsMillis: 60000# 配置一個連接在池中最小生存的時間，單位是毫秒minEvictableIdleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: false# 打開PSCache，并且指定每個連接上PSCache的大小poolPreparedStatements: truemaxPoolPreparedStatementPerConnectionSize: 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實體類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; } 然后創建DataSource實體類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;@NonNullprivate 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);// 默認數據源配置 DefaultTargetDataSourcedynamicDataSource.setDefaultTargetDataSource(mainConfig());Map<Object, Object> targetDataSources = new HashMap<Object, Object>();//多數據源配置 TargetDataSourcestargetDataSources.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;@Overrideprotected 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;}@Overridepublic void setTargetDataSources(Map<Object, Object> targetDataSources) {super.setTargetDataSources(targetDataSources);this.dynamicTargetDataSources = targetDataSources;}@Overridepublic 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:walldruidDataSource.setFilters("stat");//配置間隔多久才進行一次檢測，檢測需要關閉的空閑連接，單位是毫秒druidDataSource.setTimeBetweenEvictionRunsMillis(60000);//配置一個連接在池中最小生存的時間，單位是毫秒，這里配置為3分鐘180000druidDataSource.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賦值給父類的TargetDataSourcessetTargetDataSources(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賦值給父類的TargetDataSourcessetTargetDataSources(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;}}} 與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的SpringBoot+AOP实现多数据源动态切换的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。