使用java动态连接数据库，通过主库的方式连接其他数据库

spring:datasource:driver-class-name: sql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/test?useSSL=falseusername: rootpassword: rootdruid:# 初始化大小，最小，最大initialSize: 10minIdle: 5maxActive: 100# 获取数据库连接等待的超时时间maxWait: 60000maxOpenPreparedStatements: -1# 系统启动时通过该sql语句验证数据库是否可用，如果不配置validationQuery，则下面三项无效validationQuery: SELECT 1# 启用空闲连接检测，以便回收testWhileIdle: true# 从连接池获取连接时，是否检测连接可用性，开启性能会有些许影响testOnBorrow: false# 释放连接到连接池时，是否检测连接可用性，开启性能会有些许影响testOnReturn: false# 配置多久进行一次检测，检测需要关闭的空闲连接 单位毫秒timeBetweenEvictionRunsMillis: 60000# 配置连接在池中的最小生存时间minEvictableIdleTimeMillis: 300000# 配置连接在池中的最大生存时间maxEvictableIdleTimeMillis: 400000# 打开PSCache，并且指定每个连接上PSCache的大小poolPreparedStatements: truemaxPoolPreparedStatementPerConnectionSize: 20# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙filters: statuseGlobalDataSourceStat: true# 通过connectProperties属性来打开mergeSql功能；慢SQL记录connectProperties: Sql=true;druid.stat.slowSqlMillis=5000# 设置链接超时回收removeAbandoned: true# 从获取到链接开始，超过这么长时间，链接将被连接池强制回收removeAbandonedTimeout: 180# 强制回收链接是，将堆栈追踪信息打印到日志中logAbandoned: truebreakAfterAcquireFailure: trueconnectionErrorRetryAttempts: 0mybatis:type-aliases-package: itymapper-locations: classpath:mapper/*.xmlconfiguration:auto-mapping-behavior: fulluse-generated-keys: truejdbc-type-for-null: null

App.class

batis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;/*** @Auther: ljq* @Date: 2023/8/22 13:31*/
@MapperScan(basePackages = "com.mapper")
// 必须依赖阿里的数据源，排除默认的数据源
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class})
@EnableScheduling// 启用定时任务
public class App {public static void main(String[] args) {SpringApplication.run(App.class, args);}
}

DDSSource.java

管理数据源dataSource

import com.alibaba.druid.pool.DruidDataSource;/*** 动态数据源定时器管理。存放连接源** @Auther: ljq* @Date: 2023/8/22 13:37*/
public class DDSSource {/*** 动态数据源*/private final DruidDataSource dds;public DDSSource(DruidDataSource dds) {this.dds = dds;}/*** 关闭连接。*/public void close() {dds.close();}/*** 获取连接源*/public DruidDataSource getDds() {return dds;}
}

DDSHolder.java

使用单例的方式存放已经连接好的数据源

import com.alibaba.druid.pool.DruidDataSource;
import org.apachemons.lang3.ObjectUtils;
import java.util.HashMap;
import java.util.Map;/*** 动态数据源管理器** @Auther: ljq* @Date: 2023/8/22 13:37*/
public class DDSHolder {private static DDSHolder ddsHolder;/*** 管理动态数据源列表。<工程编码，数据源>*/private final Map<String, DDSSource> ddsMap = new HashMap<>();private DDSHolder() {}/*** 获取单例对象*/public static DDSHolder instance() {if (ObjectUtils.isEmpty(ddsHolder)) {ddsHolder = new DDSHolder();}return ddsHolder;}/*** 添加动态数据源。*/public synchronized void addDDS(String databaseName, DruidDataSource dds) {DDSSource ddst = new DDSSource(dds);ddsMap.put(databaseName, ddst);}/*** 查询动态数据源** @param databaseName 数据库名* @return dds*/public synchronized DruidDataSource getDDS(String databaseName) {if (ainsKey(databaseName)) {DDSSource ddst = (databaseName);Dds();}return null;}/*** 关闭数据源** @param databaseName 数据库名*/public synchronized void closeDDSByName(String databaseName) {if (ainsKey(databaseName)) {DDSSource ddsSource = (databaseName);ddsSource.close();ve(databaseName);}}
}

DBIdentifier.java

用于区分当前连接的数据源，专门用于切换数据源，只需要在要切换的数据源的地方调用set

/*** @Auther: ljq* @Date: 2023/8/22 13:36*/
public class DBIdentifier {/*** 用不同的工程编码来区分数据库*/private static final ThreadLocal<String> databaseName = new ThreadLocal<>();/*** 获取当前使用的数据源*/public static String getDatabaseName() {();}/*** 设置数据源** @param code 数据库名*/public static void setDatabaseName(String code) {databaseName.set(code);}
}

DynamicDataSource.java

通过重写了DruidDataSource来管理动态动态连接

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
slf4j.Slf4j;
import org.apachemons.lang3.ObjectUtils;
import org.apachemons.lang3.StringUtils;import java.sql.SQLException;/*** 定义动态数据源派生类。从基础的DataSource派生，动态性自己实现** @Auther: ljq* @Date: 2023/8/22 13:36*/
@Slf4j
public class DynamicDataSource extends DruidDataSource {/*** 改写本方法是为了在请求不同工程的数据时去连接不同的数据库*/@Overridepublic DruidPooledConnection getConnection() {String databaseName = DatabaseName();if (StringUtils.isBlank(databaseName)) {databaseName = "test";// 如果默认的database是null，这调用默认的库}//1、获取数据源DruidDataSource dds = DDSHolder.instance().getDDS(databaseName);// 从单例中获取连接源//2、如果数据源不存在则创建if (ObjectUtils.isEmpty(dds)) {try {DruidDataSource newDDS = initDDS(databaseName);// 初始化连接源DDSHolder.instance().addDDS(databaseName, newDDS);// 将连接后的数据源添加至管理中} catch (IllegalArgumentException e) {("初始化失败。 databaseName:" + databaseName);return null;}}dds = DDSHolder.instance().getDDS(databaseName);// 获取数据源try {Connection();} catch (SQLException e) {(e.getMessage());return null;}}// 调用父类的getConnection@Overridepublic DruidPooledConnection getConnection(String username, String password) throws SQLException {Connection();}/*** 以当前数据对象作为模板复制一份*/private DruidDataSource initDDS(String databaseName) throws IllegalArgumentException {DruidDataSource dds = new DruidDataSource();// 2、复制PoolConfiguration的属性dds.setInitialSize(10);dds.setMaxActive(50);dds.setMinIdle(5);dds.setMaxWait(6000);dds.setValidationQuery("SELECT 1");// 设置链接超时回收dds.setRemoveAbandoned(true);// 从获取到链接开始，超过这么长时间，链接将被连接池强制回收dds.setRemoveAbandonedTimeout(180);// 强制回收链接是，将堆栈追踪信息打印到日志中dds.setLogAbandoned(true);// 一定要在yml中配置一个默认的数据源，否则将无法进行连接if ("test".equals(databaseName)) {// 如果是默认数据源，调用yml中的信息进行连接// 添加默认的连接源String urlFormat = Url();dds.setUrl(urlFormat);dds.DriverClassName());dds.Username());dds.Password());return dds;}dds.setUrl(DBMgr.instance().getDBUrl(databaseName));dds.DriverClassName());// 公用一个driver-class-namedds.setUsername(DBMgr.instance().getDBUserName(databaseName));dds.setPassword(DBMgr.instance().getDBPassword(databaseName));return dds;}
}

DBMgr.java

ity.DDSource;
import com.mapper.DDSourceMapper;
import org.apachemons.lang3.BooleanUtils;
import org.apachemons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** 项目数据库管理。提供连接唯一编码查询数据库连接配置信息。** @Auther: ljq* @Date: 2023/8/22 13:39*/
@Component
public class DBMgr {private static DBMgr dBMgr;/*** 保存数据库URL的映射关系。*/private final Map<String, String> dbUrlMap = new HashMap<>();/*** 保存数据名称的映射关系*/private final Map<String, String> dbUserNameMap = new HashMap<>();/*** 保存数据名称的映射关系*/private final Map<String, String> dbPasswordMap = new HashMap<>();/*** 存放最后更新时间的，用于判断当前数据库连接信息是否被修改，如果被修改，需要重新连接数据库*/private final Map<String, Date> dbUpdateTime = new HashMap<>();@Autowiredprivate DDSourceMapper dDSourceMapper;private DBMgr() {}// 每五分钟运行一次，这里使用定时任务是为了类似实时更新的，根据数据库中的update_time字段判断是否需要重新连接@Scheduled(fixedRate = 1000 * 60 * 5)private void process() {if (ObjectUtils.isEmpty(dBMgr)) {dBMgr = new DBMgr();}List<DDSource> dDSources = dDSourceMapper.selectAll();for (DDSource dDSource : dDSources) {boolean set = this.DbName(), DbUrl(), DbUsername(), DbPassword(), UpdateTime());// 不会查询出主库的信息，所以不存在aantdata会断开连接if (BooleanUtils.isTrue(set)) {// 需要关闭，在重新连接DDSHolder.instance().DbName());}}DBIdentifier.setDatabaseName("test");// 建议每次调用setDatabaseName之后重置为初始库}// 单例实例化public static DBMgr instance() {if (ObjectUtils.isEmpty(dBMgr)) {dBMgr = new DBMgr();}return dBMgr;}private boolean set(String dbName, String dbUrl, String userName, String dbPassword, Date updateTime) {boolean ck = false;dBMgr.dbUrlMap.put(dbName, dbUrl);dBMgr.dbUserNameMap.put(dbName, userName);dBMgr.dbPasswordMap.put(dbName, dbPassword);Date date = (dbName);// 通过判断修改时间判断是否需要重新连接if (ObjectUtils.isNotEmpty(date) && !updateTime.equals(date)) {ck = true;}dBMgr.dbUpdateTime.put(dbName, updateTime);return ck;}public String getDBUrl(String projectCode) {if (ainsKey(projectCode)) {(projectCode);}return null;}public String getDBUserName(String projectCode) {if (ainsKey(projectCode)) {(projectCode);}return null;}public String getDBPassword(String projectCode) {if (ainsKey(projectCode)) {(projectCode);}return null;}
}

DataSourceConfig.java

配置默认数据源

slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
batis.spring.SqlSessionFactoryBean;
batis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.t.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import t.annotation.Bean;
import t.annotation.Configuration;
import io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;/*** @Auther: ljq* @Date: 2023/8/22 13:36*/
@Slf4j
@Configuration
@MapperScan(basePackages = "com.mapper", sqlSessionFactoryRef = "dynamicSqlSession")
public class DataSourceConfig {/*** 根据配置参数创建数据源。使用派生的子类。** @return 数据源*/@Bean(name = "dynamicDataSource")@ConfigurationProperties(prefix = "spring.datasource")public DataSource getDataSource() {DataSourceBuilder<?> builder = ate();pe(DynamicDataSource.class);return builder.build();}/*** 创建会话工厂。** @param dataSource 数据源* @return 会话工厂*/@Bean(name = "dynamicSqlSession")public SqlSessionFactory getSqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);try {bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));Object();} catch (Exception e) {("DynamicSqlSession 创建会话工厂失败");return null;}}
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis//DTD Mapper 3.0//EN"".dtd">
<mapper namespace="com.mapper.DDSourceMapper"><resultMap id="BaseResultMap" type=&#ity.DDSource"><result property="dbName" column="db_name" jdbcType="VARCHAR"/><result property="dbUrl" column="db_url" jdbcType="VARCHAR"/><result property="dbUsername" column="db_username" jdbcType="VARCHAR"/><result property="dbPassword" column="db_password" jdbcType="VARCHAR"/><result property="createTime" column="create_time" jdbcType="TIMESTAMP"/><result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/></resultMap><sql id="Base_Column_List">db_name,db_title,db_url,db_username,db_password,create_time,update_time</sql><select id="selectAll" resultMap="BaseResultMap">select <include refid="Base_Column_List"/>        from dd_sourcewhere db_name != 'test' -- 去除了自己需要连接的数据库，因为在启动程序的时候会自动使用yml的进行连接</select>
</mapper>

ity.DDSource;
import org.springframework.stereotype.Repository;import java.util.List;/*** @Auther: ljq* @Date: 2023/5/25 17:08*/
@Repository
public interface DDSourceMapper {List<DDSource> selectAll();
}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.io.Serializable;
import java.util.Date;/*** 数据库字典表*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DDSource implements Serializable {private String dbName;private String dbUrl;private String dbUsername;private String dbPassword;private Date createTime;private Date updateTime;
}

fig.DBIdentifier;
import com.mapper.OtherMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;/*** @Auther: ljq* @Date: 2023/5/28 17:18*/
@Service
public class TestService {@Autowiredprivate OtherMapper otherMapper;public void select(String indexCode) {try {// 设置需要连接的数据库名DBIdentifier.setDatabaseName("otherDatabase");// test是默认数据库，可以不显示调用List<OtherEntity> otherList = otherMapper.selectAll();// 查询数据库// ...System.out.println(otherList);} finally {DBIdentifier.setDatabaseName("test");// 设置回默认数据库}}
}