1、创建数据库test1,test2,test3
test1库
CREATE TABLE `databasesource` (
`datasource_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据源的id',
`url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '连接信息',
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
`pass_word` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
`code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '暂留字段',
`databasetype` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据库类型'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test1`.`databasesource`(`datasource_id`, `url`, `user_name`, `pass_word`, `code`, `databasetype`) VALUES ('dbtest2', 'jdbc:mysql://localhost:3306/test2?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull', 'root', 'root', NULL, 'mysql');
INSERT INTO `test1`.`databasesource`(`datasource_id`, `url`, `user_name`, `pass_word`, `code`, `databasetype`) VALUES ('dbtest3', 'jdbc:mysql://localhost:3306/test3?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull', 'root', 'root', NULL, 'mysql');
test2和test3
CREATE TABLE `user` (
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(3) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test2`.`user`(`user_name`, `age`) VALUES ('数据库2-小明', 20);
INSERT INTO `test2`.`user`(`user_name`, `age`) VALUES ('数据库2-小方', 17);
INSERT INTO `test3`.`user`(`user_name`, `age`) VALUES ('数据库3-啊强', 11);
INSERT INTO `test3`.`user`(`user_name`, `age`) VALUES ('数据库3-啊木', 12);
2、pom.xml
package com.controller;
import com.configs.DBContextHolder;
import com.github.pagehelper.Page;
import com.pojo.DataSource;
import com.pojo.User;
import com.service.DBChangeService;
import com.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Map;
@RestController
@CrossOrigin
@RequestMapping("/admin")
public class AdminController{
@Autowired
private UserService userService;
@Autowired
private DBChangeService dbChangeServiceImpl;
//@Transactional//事务(插入、更新、删除使用,只能库,同时操作两个只有一个生效)
@GetMapping("All/{id}")
public String findById(@PathVariable Long id) throws Exception {
//切换到数据库dbtest2
String datasourceId="dbtest2";
dbChangeServiceImpl.changeDb(datasourceId);
List<User> userList= userService.oneUser((long) 10);
System.out.println("2库:"+userList.toString());
//再切换到数据库dbtest3
dbChangeServiceImpl.changeDb("dbtest3");
List<User> userList3= userService.oneUser((long) 10);
System.out.println("3库:"+userList3.toString());
//切回主数据源
DBContextHolder.clearDataSource();
List<User> userList1= userService.oneUser((long) 10);
System.out.println("1库:"+userList1.toString());
System.out.println("66996");
return "333";
}
}
3、紧接着,application.yml(这里面的数据库配置信息将作为默认数据库):
server:
port: 9002
#spring:
# application:
# name: dade02
# aop:
# proxy-target-class: true #true为使用CGLIB代理
# datasource:
# driver-class-name: com.mysql.jdbc.Driver
# url: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
# username: root
# password: 123456
spring:
aop:
proxy-target-class: true #true为使用CGLIB代理
datasource:
#nullCatalogMeansCurrent=true&
url: jdbc:mysql://localhost:3306/test1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull
username: root
password: 123456
#新版mysql驱动配置方法
driverClassName: com.mysql.cj.jdbc.Driver
###################以下为druid增加的配置###########################
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添加的配置########################################
4、先创建DataSource.java实体类,数据源信息装配的时候用:
package com.pojo;
import javax.persistence.Table;
/**
* @Author : JCccc
* @CreateTime : 2019/10/22
* @Description :
**/
@Table(name="databasesource")
public class DataSource {
String datasourceId;
String url;
String userName;
String passWord;
String code;
String databasetype;
public String getDatasourceId() {
return datasourceId;
}
public String getUrl() {
return url;
}
public String getUserName() {
return userName;
}
public String getPassWord() {
return passWord;
}
public String getCode() {
return code;
}
public String getDatabasetype() {
return databasetype;
}
public void setDatasourceId(String datasourceId) {
this.datasourceId = datasourceId;
}
public void setUrl(String url) {
this.url = url;
}
public void setUserName(String userName) {
this.userName = userName;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public void setCode(String code) {
this.code = code;
}
public void setDatabasetype(String databasetype) {
this.databasetype = databasetype;
}
@Override
public String toString() {
return "DataSource{" +
"datasourceId='" + datasourceId + '\'' +
", url='" + url + '\'' +
", userName='" + userName + '\'' +
", passWord='" + passWord + '\'' +
", code='" + code + '\'' +
", databasetype='" + databasetype + '\'' +
'}';
}
}
5、接下来,创建DruidDBConfig.java:
这里主要是配置默认的数据源,配置Druid数据库连接池,配置sql工厂加载mybatis的文件,扫描实体类等
package com.configs;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
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.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
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.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* @Author : JCccc
* @CreateTime : 2019/10/22
* @Description :
**/
@Configuration
@EnableTransactionManagement
public class DruidDBConfig {
private final Logger log = LoggerFactory.getLogger(getClass());
// adi数据库连接信息
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driverClassName}")
private String driverClassName;
// 连接池连接信息
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Bean // 声明其为Bean实例
@Primary // 在同样的DataSource中,首先使用被标注的DataSource
@Qualifier("mainDataSource")
public DataSource dataSource() throws SQLException {
DruidDataSource datasource = new DruidDataSource();
// 基础连接信息
datasource.setUrl(this.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("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout=60000");//对于耗时长的查询sql,会受限于ReadTimeout的控制,单位毫秒
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;
}
/**
* 注册一个StatViewServlet druid监控页面配置1-帐号密码配置
*
* @return servlet registration bean
*/
@Bean
public ServletRegistrationBean druidStatViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(
new StatViewServlet(), "/druid/*");
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "123456");
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
/**
* 注册一个:filterRegistrationBean druid监控页面配置2-允许页面正常浏览
*
* @return filter registration bean
*/
@Bean
public FilterRegistrationBean druidStatFilter() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(
new WebStatFilter());
// 添加过滤规则.
filterRegistrationBean.addUrlPatterns("/*");
// 添加不需要忽略的格式信息.
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
@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 SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dynamicDataSource());
//解决手动创建数据源后字段到bean属性名驼峰命名转换失效的问题
sqlSessionFactoryBean.setConfiguration(configuration());
// 设置mybatis的主配置文件
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
// Resource mybatisConfigXml = resolver.getResource("classpath:mybatis/mybatis-config.xml");
// sqlSessionFactoryBean.setConfigLocation(mybatisConfigXml);
// 设置别名包
// sqlSessionFactoryBean.setTypeAliasesPackage("com.testdb.dbsource.pojo");
//手动配置mybatis的mapper.xml资源路径,如果单纯使用注解方式,不需要配置该行
// sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mybatis/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();
}
}
6、然后是用于手动切换数据源的 DBContextHolder.java:
package com.configs;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @Author : JCccc
* @CreateTime : 2019/10/22
* @Description :
**/
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("已切换到主数据源");
}
}
7、然后是核心,手动加载默认数据源、创建数据源连接、检查数据源连接、删除数据源连接等 ,DynamicDataSource.java:
package com.configs;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.stat.DruidDataSourceStatManager;
import com.pojo.DataSource;
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;
// throw new ADIException("不存在的数据源:"+datasource,500);
}
} 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;
}
@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(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 = ADIPropUtil.sqlQueryTimeOut();
// druidDataSource.setConnectionProperties("oracle.net.CONNECT_TIMEOUT=6000;oracle.jdbc.ReadTimeout="+sqlQueryTimeout);//对于耗时长的查询sql,会受限于ReadTimeout的控制,单位毫秒
// } else if("sqlserver2000".equalsIgnoreCase(databasetype)){
// driveClass = DBUtil.sql2000driver;
// validationQuery = "select 1";
// } else if("sqlserver".equalsIgnoreCase(databasetype)){
// driveClass = DBUtil.sql2005driver;
// 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+"数据源初始化成功");
//log.info(key+"数据源的概况:"+druidDataSource.dump());
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+"之前已经创建,准备测试数据源是否正常...");
//DataSource druidDataSource = (DataSource) dynamicTargetDataSources2.get(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 = "com.mysql.cj.jdbc.Driver";
// if("mysql".equalsIgnoreCase(databasetype)) {
// driveClass = DBUtil.mysqldriver;
// } else if("oracle".equalsIgnoreCase(databasetype)){
// driveClass = DBUtil.oracledriver;
// } else if("sqlserver2000".equalsIgnoreCase(databasetype)){
// driveClass = DBUtil.sql2000driver;
// } else if("sqlserver".equalsIgnoreCase(databasetype)){
// driveClass = DBUtil.sql2005driver;
// }
if(testDatasource(datasourceId,driveClass,url,username,password)) {
boolean result = this.createDataSource(datasourceId, driveClass, url, username, password, databasetype);
if(!result) {
log.error("数据源"+datasourceId+"配置正确,但是创建失败");
// throw new ADIException("数据源"+datasourceId+"配置正确,但是创建失败",500);
}
} else {
log.error("数据源配置有错误");
// throw new ADIException("数据源配置有错误",500);
}
}
}
8、DataSourceMapper.java :
package com.dao;
import com.pojo.DataSource;
import tk.mybatis.mapper.common.Mapper;
public interface DataSourceMapper extends Mapper<DataSource> {
}
9、DBChangeService.java:
package com.service;
import com.pojo.DataSource;
import java.util.List;
public interface DBChangeService {
List<DataSource> get();
boolean changeDb(String datasourceId) throws Exception;
}
10、DBChangeServiceImpl.java:
package com.service.impl;
import com.configs.DBContextHolder;
import com.configs.DynamicDataSource;
import com.dao.DataSourceMapper;
import com.pojo.DataSource;
import com.service.DBChangeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @Author : JCccc
* @CreateTime : 2019/10/22
* @Description :
**/
@Service
public class DBChangeServiceImpl implements DBChangeService {
@Autowired
DataSourceMapper dataSourceMapper;
@Autowired
private DynamicDataSource dynamicDataSource;
@Override
public List<DataSource> get() {
return dataSourceMapper.selectAll();
}
@Override
public boolean changeDb(String datasourceId) throws Exception {
//默认切换到主数据源,进行整体资源的查找
DBContextHolder.clearDataSource();
List<DataSource> dataSourcesList = dataSourceMapper.selectAll();
for (DataSource dataSource : dataSourcesList) {
if (dataSource.getDatasourceId().equals(datasourceId)) {
System.out.println("需要使用的的数据源已经找到,datasourceId是:" + dataSource.getDatasourceId());
//创建数据源连接&检查 若存在则不需重新创建
dynamicDataSource.createDataSourceWithCheck(dataSource);
//切换到该数据源
DBContextHolder.setDataSource(dataSource.getDatasourceId());
return true;
}
}
return false;
}
}
11、接下来,写相关操作user表的代码,因为user表分别在test2、test3数据库里,这样用于我们切换到test2或者test3数据库操作这些数据。
User.java:
package com.pojo;
import javax.persistence.Table;
import java.io.Serializable;
@Table(name="user")
public class User implements Serializable {
String userName;
String age;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"userName='" + userName + '\'' +
", age='" + age + '\'' +
'}';
}
}
12、UserMappper.java (上面简单介绍了下使用注解的方式获取表数据,可能有些人不习惯,那么这里也使用传统的mapper.xml方式编写mysql语句):
package com.dao;
import com.pojo.User;
import tk.mybatis.mapper.common.Mapper;
public interface UserMapper extends Mapper<User> {
}
13、AdminController.java
package com.controller;
import com.configs.DBContextHolder;
import com.github.pagehelper.Page;
import com.pojo.DataSource;
import com.pojo.User;
import com.service.DBChangeService;
import com.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Map;
@RestController
@CrossOrigin
@RequestMapping("/admin")
public class AdminController{
@Autowired
private UserService userService;
@Autowired
private DBChangeService dbChangeServiceImpl;
//@Transactional//事务(插入、更新、删除使用,只能库,同时操作两个只有一个生效)
@GetMapping("All/{id}")
public String findById(@PathVariable Long id) throws Exception {
//切换到数据库dbtest2
String datasourceId="dbtest2";
dbChangeServiceImpl.changeDb(datasourceId);
List<User> userList= userService.oneUser((long) 10);
System.out.println("2库:"+userList.toString());
//再切换到数据库dbtest3
dbChangeServiceImpl.changeDb("dbtest3");
List<User> userList3= userService.oneUser((long) 10);
System.out.println("3库:"+userList3.toString());
//切回主数据源
DBContextHolder.clearDataSource();
List<User> userList1= userService.oneUser((long) 10);
System.out.println("1库:"+userList1.toString());
System.out.println("66996");
return "333";
}
}
源代码位置和效果图
https://blog.csdn.net/qq_34631220/article/details/138823009
前面这个是mysql版本,改成mysql+sqlserver版
修改
需要注意,先去测试单库可以连接sqlserver,因为可能要改配置文件才能连接sqlserver数据库
https://blog.csdn.net/qq_34631220/article/details/138854978
代码
链接:https://pan.baidu.com/s/1hav2WCpIY8vuk8OHU5LijA
提取码:sxyi
–来自百度网盘超级会员V5的分享
这个单独写进去,上面没写,加到DruidDBConfig.java底部,事务才有效
补充该实战教学的事务相关介绍配置和介绍:
实现动态数据源事务,找到该篇项目实例中的DruidDBConfig.java ,
将 我们实现的动态数据源加载类DynamicDataSource 添加到数据事务管理器里:
@Bean
public DataSourceTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
return new DataSourceTransactionManager(dynamicDataSource);
}
@Transactional
@Override
public Boolean install(Map map) {
try {
String traderInfo = JSON.toJSONString(map.get("trader"));
WbTrader wbTrader = JSONObject.parseObject(traderInfo,WbTrader.class);
wbTrader.setId(88);
wbTraderMapper.insert(wbTrader);
wbTraderMapper.insert(wbTrader);
int id = wbTraderMapper.id();
//供销货品
// if(!"".equals(map.get("traderGoods")) && map.get("traderGoods") != null){
// System.out.print("111111111111111");
// String traderGoodsInfo = JSON.toJSONString(map.get("traderGoods"));
// WbTradergoods wbTradergoods = JSONObject.parseObject(traderGoodsInfo, WbTradergoods.class);
// wbTradergoodsMapper.insert(wbTradergoods);
// }
int s = 1/0;
}catch (Exception e){
throw new RuntimeException(e.getMessage());
}
return true;
}