应用背景:
本文只是简单的实现动态数据源,重点在于熟悉代码方面的流程,至于要满足于复杂的业务场景需要进一步扩展。
本文中的动态数据源切换需要满足两种业务场景:
- 提前知道并指定使用哪个数据源;
- 不知道使用哪个动态数据源,由逻辑判断匹配数据源(但是需要标识,例如第一个数据源中的表名为‘people’,第二个数据源中的表名为‘people2’)。
场景一:提前知道使用哪个数据源(由自己指定数据源)
1、数据准备:创建三个数据库(每个库里的表名重复),往表里插入几条数据用于测试
dynamic1:
CREATE DATABASE `dynamic1` ;
USE `dynamic1`;
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'dynamic1'),(2,'lisi',1,'dynamic1'),(3,'wangwu',1,'dynamic1');
dynamic2:
CREATE DATABASE `dynamic2` ;
USE `dynamic2`;
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'dynamic2'),(2,'lisi',1,'dynamic2'),(3,'wangwu',1,'dynamic2');
dynamic3:
CREATE DATABASE `dynamic3` ;
USE `dynamic3`;
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `people`(`id`,`name`,`status`,`content`) values (1,'zhangsan',1,'dynamic3'),(2,'lisi',1,'dynamic3'),(3,'wangwu',1,'dynamic3');
2、搭建Springboot项目(搭建过程参考:https://www.cnblogs.com/Bernard94/p/14123880.html)
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.7</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.demo</groupId>
<artifactId>dynamic</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>dynamic</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--Web请求-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--开发帮助工具-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<!--数据库链接-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!--日志-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--启动-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--AOP-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.6</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
db.yml
driverClassName1 = com.mysql.jdbc.Driver
url1 = jdbc:mysql://127.0.0.1:3306/dynamic1?useUnicode=true&characterEncoding=utf8
userName1 = root
password1 = root
driverClassName2 = com.mysql.jdbc.Driver
url2 = jdbc:mysql://127.0.0.1:3306/dynamic2?useUnicode=true&characterEncoding=utf8
userName2 = root
password2 = root
driverClassName3 = com.mysql.jdbc.Driver
url3 = jdbc:mysql://127.0.0.1:3306/dynamic3?useUnicode=true&characterEncoding=utf8
userName3 = root
password3 = root
application.yml
server:
port: 10086
mybatis:
type-aliases-package: com.demo.dynamic.bean
mapper-locations: classpath:mapper/*Mapper.xml
people(bean对象,未用到,可省略)
package com.demo.dynamic.bean;
import lombok.Data;
/**
* @Classname People
* @Description TODO
* @Date 2022/4/27 18:09
* @Created by BG
*/
@Data
public class People {
private String id;
private String name;
private String status;
private String content;
}
controller
package com.demo.dynamic.controller;
import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @Classname PeopleController
* @Description TODO
* @Date 2022/4/27 17:59
* @Created by BG
*/
@RestController
@RequestMapping("/data")
public class PeopleController {
@Autowired
public PeopleService peopleService;
@RequestMapping("/people1")
public String getPeopleMaster() {
return peopleService.getPeopleMaster();
}
@RequestMapping("/people2")
public String getPeopleSlaver1() {
return peopleService.getPeopleSlaver1();
}
@RequestMapping("/people3")
public String getPeopleSlaver2() {
return peopleService.getPeopleSlaver2();
}
}
service
package com.demo.dynamic.service;
/**
* @Classname PeopleService
* @Description TODO
* @Date 2022/4/27 18:00
* @Created by BG
*/
public interface PeopleService {
String getPeopleMaster();
String getPeopleSlaver1();
String getPeopleSlaver2();
}
package com.demo.dynamic.service.impl;
import com.demo.dynamic.mapper.PeopleMapper;
import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
/**
* @Classname PeopleServiceImpl
* @Description TODO
* @Date 2022/4/27 18:00
* @Created by BG
*/
@Service
public class PeopleServiceImpl implements PeopleService {
@Autowired
public PeopleMapper peopleMapper;
@Override
public String getPeopleMaster() {
List<Map<String, Object>> peopleList = peopleMapper.getPeopleMaster();
return peopleList.toString();
}
@Override
public String getPeopleSlaver1() {
List<Map<String, Object>> peopleList = peopleMapper.getPeopleSlaver1();
return peopleList.toString();
}
@Override
public String getPeopleSlaver2() {
List<Map<String, Object>> peopleList = peopleMapper.getPeopleSlaver2();
return peopleList.toString();
}
}
mapper
package com.demo.dynamic.mapper;
import com.demo.dynamic.dynamic.DynamicDB;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
import java.util.Map;
/**
* @Classname PeopleMapper
* @Description TODO
* @Date 2022/4/27 18:00
* @Created by BG
*/
@Mapper
public interface PeopleMapper {
@DynamicDB(dataSource = "master")
List<Map<String, Object>> getPeopleMaster();
@DynamicDB(dataSource = "slave1")
List<Map<String, Object>> getPeopleSlaver1();
@DynamicDB(dataSource = "slave2")
List<Map<String, Object>> getPeopleSlaver2();
}
mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.dynamic.mapper.PeopleMapper">
<select id="getPeopleMaster" resultType="java.util.HashMap">
SELECT
*
FROM
people t
</select>
<select id="getPeopleSlaver1" resultType="java.util.HashMap">
SELECT
*
FROM
people t
</select>
<select id="getPeopleSlaver2" resultType="java.util.HashMap">
SELECT
*
FROM
people t
</select>
</mapper>
DatasourceConfig(先配置各个数据源的基础配置)
package com.demo.dynamic.dynamic;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* @Classname DatasourceConfig
* @Description 数据源链接配置类
* @Date 2022/4/28 9:56
* @Created by BG
*/
@Configuration
@Slf4j
@Data//此注解可以省去setter和getter方法
public class DatasourceConfig {
//master
private String url1;
private String userName1;
private String password1;
private String driverClassName1;
//slaver-1
private String url2;
private String userName2;
private String password2;
private String driverClassName2;
//slaver-2
private String url3;
private String userName3;
private String password3;
private String driverClassName3;
public DatasourceConfig() {
Properties properties = DatasourceConfig.getProperties("db.yml");
initDb(properties);
}
public void initDb(Properties properties) {
if (null == properties) {
initDefaultDb();
} else {
url1 = properties.getProperty("url1");
userName1 = properties.getProperty("userName1");
password1 = properties.getProperty("password1");
driverClassName1 = properties.getProperty("driverClassName1");
url2 = properties.getProperty("url2");
userName2 = properties.getProperty("userName2");
password2 = properties.getProperty("password2");
driverClassName2 = properties.getProperty("driverClassName2");
url3 = properties.getProperty("url3");
userName3 = properties.getProperty("userName3");
password3 = properties.getProperty("password3");
driverClassName3 = properties.getProperty("driverClassName3");
}
}
public void initDefaultDb() {
url1 = "jdbc:mysql://127.0.0.1:3306/dynamic1?useUnicode=true&characterEncoding=utf8";
userName1 = "root";
password1 = "root";
driverClassName1 = "com.mysql.jdbc.Driver";
}
public static Properties getProperties(String fileName) {
try {
//先读取config目录的
String path = "DBConfig" + File.separator;
Properties properties = new Properties();
InputStream in = new FileInputStream(new File(path + fileName));
properties.load(in);
return properties;
} catch (IOException e) {
log.info("dataSource_init_failed:" + e.getMessage());
return null;
}
}
}
DynamicDataSourceConfig(整合多个数据源用于动态切换)
package com.demo.dynamic.dynamic;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @Classname DynamicDataSourceConfig
* @Description 多个数据源配置类
* @Date 2022/4/28 10:24
* @Created by BG
*/
@Configuration
public class DynamicDataSourceConfig {
@Autowired
private DatasourceConfig datasourceConfig;
@Bean
public DataSource master() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(datasourceConfig.getUserName1());
druidDataSource.setPassword(datasourceConfig.getPassword1());
druidDataSource.setUrl(datasourceConfig.getUrl1());
//其他参数配置 省略
return druidDataSource;
}
@Bean
public DataSource slave1() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(datasourceConfig.getUserName2());
druidDataSource.setPassword(datasourceConfig.getPassword2());
druidDataSource.setUrl(datasourceConfig.getUrl2());
//其他参数配置 省略
return druidDataSource;
}
@Bean
public DataSource slave2() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(datasourceConfig.getUserName3());
druidDataSource.setPassword(datasourceConfig.getPassword3());
druidDataSource.setUrl(datasourceConfig.getUrl3());
//其他参数配置 省略
return druidDataSource;
}
@Bean
@Primary
public DynamicDataSource dataSource(DataSource master, DataSource slave1, DataSource slave2) {
Map<Object, Object> map = new HashMap<>(4);
map.put("master", master);
map.put("slave1", slave1);
map.put("slave2", slave2);
return new DynamicDataSource(master, map);
}
}
DynamicDataSource(代码上调用的数据源对象)
package com.demo.dynamic.dynamic;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* @Classname DynamicDataSource
* @Description 动态数据源
* @Date 2022/4/28 10:27
* @Created by BG
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
//设置默认的数据源
super.setDefaultTargetDataSource(defaultTargetDataSource);
//设置多数据源
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
Object dataSource = getDataSource();
return dataSource;
}
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
public static String getDataSource() {
return contextHolder.get();
}
public static void clearDataSource() {
contextHolder.remove();
}
}
DynamicDB(自定义动态数据源注解)
package com.demo.dynamic.dynamic;
import java.lang.annotation.*;
/**
* @Classname DynamicDB
* @Description 动态数据源的自定义注解
* @Date 2022/4/28 10:31
* @Created by BG
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DynamicDB {
//默认主数据源
String dataSource() default "master";
}
DynamicDBAspect
package com.demo.dynamic.dynamic;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.lang.reflect.Method;
/**
* @Classname DynamicDBAspect
* @Description 动态数据源注解切面
* @Date 2022/4/28 10:38
* @Created by BG
*/
@Aspect
@Component
@Slf4j
public class DynamicDBAspect {
//事务提交或回滚
@Autowired
private DataSourceTransactionManager transactionManager;
//切点
@Pointcut("@annotation(com.demo.dynamic.dynamic.DynamicDB)")
public void dataSourcePointCut() {
}
//环绕加强
@Around(value = "dataSourcePointCut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
Object point = null;
//是否动态的标识
boolean dynamicFlag = false;//当前数据源
String currDataSource = DynamicDataSource.getDataSource();
//反射获取到注解‘对象’
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
Method method = signature.getMethod();
DynamicDB dynamicDB = method.getAnnotation(DynamicDB.class);
if (dynamicDB == null) {
//如果获取不到注解对象就设置成主数据源
DynamicDataSource.setDataSource("master");
} else {
//获取注解上的'dataSource'属性
dynamicFlag = true;
DynamicDataSource.setDataSource(dynamicDB.dataSource());
}
if (!dynamicFlag) {
point = joinPoint.proceed();
return point;
}
//如果当前存在事务,启动一个新的事务
DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
transactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
//开启事务
TransactionStatus ts = transactionManager.getTransaction(transactionDefinition);
try {
//让方法执行
point = joinPoint.proceed();
//提交事务
transactionManager.commit(ts);
} catch (Throwable throwable) {
//异常时回滚
transactionManager.rollback(ts);
throw throwable;
} finally {
//如果事务没有处理,默认进行回滚
if (!ts.isCompleted()) {
transactionManager.rollback(ts);
}
//恢复当前数据源
DynamicDataSource.setDataSource(currDataSource);
}
return point;
}
}
页面测试路径
1、http://localhost:10086/data/people1
2、http://localhost:10086/data/people2
3、http://localhost:10086/data/people3
说明确实按需求分别查了三个库里的数据!
场景二:不知道用哪个数据源,从配置变为逻辑判断(需根据业务中查询不同的表动态匹配对应的数据源)
1、数据准备:准备两个数据库“dynamic4”和“dynamic5”,注意表名要不重复(dynamic4:people、dynamic5:people2)
CREATE DATABASE `dynamic4` ;
USE `dynamic4`;
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `people`(`id`,`name`,`status`,`content`) VALUES (1,'zhangsan',1,'dynamic4'),(2,'lisi',1,'dynamic4'),(3,'wangwu',1,'dynamic4');
CREATE DATABASE `dynamic5` ;
USE `dynamic5`;
DROP TABLE IF EXISTS `people2`;
CREATE TABLE `people2` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `people2`(`id`,`name`,`status`,`content`) VALUES (1,'zhangsan',1,'dynamic5'),(2,'lisi',1,'dynamic5'),(3,'wangwu',1,'dynamic5');
2、Springboot项目构建
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.7</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.demo</groupId>
<artifactId>dynamic</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>dynamic</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--Web请求-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--开发帮助工具-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<!--数据库链接-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!--日志-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--启动-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--AOP-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.6</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
db.yml
driverClassName1 = com.mysql.jdbc.Driver
url1 = jdbc:mysql://127.0.0.1:3306/dynamic4?useUnicode=true&characterEncoding=utf8
userName1 = root
password1 = root
driverClassName2 = com.mysql.jdbc.Driver
url2 = jdbc:mysql://127.0.0.1:3306/dynamic5?useUnicode=true&characterEncoding=utf8
userName2 = root
password2 = root
application.yml
server:
port: 10086
mybatis:
type-aliases-package: com.demo.dynamic.bean
mapper-locations: classpath:mapper/*Mapper.xml
--数据源配置相关类--
DatasourceConfig
package com.demo.dynamic.dynamic;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* @Classname DatasourceConfig
* @Description 数据源链接配置类
* @Date 2022/4/28 9:56
* @Created by BG
*/
@Configuration
@Slf4j
@Data//此注解可以省去setter和getter方法
public class DatasourceConfig {
//master
private String url1;
private String userName1;
private String password1;
private String driverClassName1;
//slaver-1
private String url2;
private String userName2;
private String password2;
private String driverClassName2;
public DatasourceConfig() {
Properties properties = DatasourceConfig.getProperties("db.yml");
initDb(properties);
}
public void initDb(Properties properties) {
if (null == properties) {
initDefaultDb();
} else {
url1 = properties.getProperty("url1");
userName1 = properties.getProperty("userName1");
password1 = properties.getProperty("password1");
driverClassName1 = properties.getProperty("driverClassName1");
url2 = properties.getProperty("url2");
userName2 = properties.getProperty("userName2");
password2 = properties.getProperty("password2");
driverClassName2 = properties.getProperty("driverClassName2");
}
}
public void initDefaultDb() {
url1 = "jdbc:mysql://127.0.0.1:3306/dynamic4?useUnicode=true&characterEncoding=utf8";
userName1 = "root";
password1 = "root";
driverClassName1 = "com.mysql.jdbc.Driver";
}
public static Properties getProperties(String fileName) {
try {
//先读取config目录的
String path = "DBConfig" + File.separator;
Properties properties = new Properties();
InputStream in = new FileInputStream(new File(path + fileName));
properties.load(in);
return properties;
} catch (IOException e) {
log.info("dataSource_init_failed:" + e.getMessage());
return null;
}
}
}
DynamicDataSource
package com.demo.dynamic.dynamic;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* @Classname DynamicDataSource
* @Description 动态数据源
* @Date 2022/4/28 10:27
* @Created by BG
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
//设置默认的数据源
super.setDefaultTargetDataSource(defaultTargetDataSource);
//设置多数据源
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
Object dataSource = getDataSource();
return dataSource;
}
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
public static String getDataSource() {
return contextHolder.get();
}
public static void clearDataSource() {
contextHolder.remove();
}
}
DynamicDataSourceConfig
package com.demo.dynamic.dynamic;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @Classname DynamicDataSourceConfig
* @Description 多个数据源配置类
* @Date 2022/4/28 10:24
* @Created by BG
*/
@Configuration
public class DynamicDataSourceConfig {
@Autowired
private DatasourceConfig datasourceConfig;
@Bean
public DataSource master() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(datasourceConfig.getUserName1());
druidDataSource.setPassword(datasourceConfig.getPassword1());
druidDataSource.setUrl(datasourceConfig.getUrl1());
//其他参数配置 省略
return druidDataSource;
}
@Bean
public DataSource slave() {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername(datasourceConfig.getUserName2());
druidDataSource.setPassword(datasourceConfig.getPassword2());
druidDataSource.setUrl(datasourceConfig.getUrl2());
//其他参数配置 省略
return druidDataSource;
}
@Bean
@Primary
public DynamicDataSource dataSource(DataSource master, DataSource slave) {
Map<Object, Object> map = new HashMap<>(4);
map.put("master", master);
map.put("slave", slave);
return new DynamicDataSource(master, map);
}
}
DynamicDB
package com.demo.dynamic.dynamic;
import java.lang.annotation.*;
/**
* @Classname DynamicDB
* @Description 动态数据源的自定义注解
* @Date 2022/4/28 10:31
* @Created by BG
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DynamicDB {
}
DynamicDBAspect
package com.demo.dynamic.dynamic;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.lang.reflect.Method;
/**
* @Classname DynamicDBAspect
* @Description 动态数据源注解切面
* @Date 2022/4/28 10:38
* @Created by BG
*/
@Aspect
@Component
@Slf4j
public class DynamicDBAspect {
//事务提交或回滚
@Autowired
private DataSourceTransactionManager transactionManager;
//切点
@Pointcut("@annotation(com.demo.dynamic.dynamic.DynamicDB)")
public void dataSourcePointCut() {
}
//环绕加强
@Around(value = "dataSourcePointCut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
Object point = null;
//是否动态的标识
boolean dynamicFlag = false;
//当前数据源
String currDataSource = DynamicDataSource.getDataSource();
//参数
Object[] args = joinPoint.getArgs();
//通过参数逻辑判断是否动态数据源
for (Object arg : args) {
//判断mapper中是sql还是参数对象
if (arg instanceof String) {
dynamicFlag = switchDataSource((String) arg, "sql");
break;
} else if (arg instanceof DynamicParam) {
dynamicFlag = switchDataSource(((DynamicParam) arg).getSqlOrTableName(), "tableName");
break;
}
}
if (!dynamicFlag) {
point = joinPoint.proceed();
return point;
}
//如果当前存在事务,启动一个新的事务
DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
transactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
//开启事务
TransactionStatus ts = transactionManager.getTransaction(transactionDefinition);
try {
//让方法执行
point = joinPoint.proceed();
//提交事务
transactionManager.commit(ts);
} catch (Throwable throwable) {
//异常时回滚
transactionManager.rollback(ts);
throw throwable;
} finally {
//如果事务没有处理,默认进行回滚
if (!ts.isCompleted()) {
transactionManager.rollback(ts);
}
//恢复当前数据源
DynamicDataSource.setDataSource(currDataSource);
}
return point;
}
private static boolean switchDataSource(String arg, String type) {
//sql语句中包含指定表名
if (type.equals("sql") && arg.contains("people2")) {
DynamicDataSource.setDataSource("slave");
return true;
//表名等于指定表名
} else if (type.equals("tableName") && arg.equals("people2")) {
DynamicDataSource.setDataSource("slave");
return true;
}
return false;
}
}
DynamicParam
package com.demo.dynamic.dynamic;
/**
* @Classname DynamicParam
* @Description 动态数据源参数基础接口
* @Date 2022/4/28 13:58
* @Created by BG
*/
public interface DynamicParam {
String getSqlOrTableName();
}
--数据源配置-end--
PeopleParam
package com.demo.dynamic.bean;
import com.demo.dynamic.dynamic.DynamicParam;
import lombok.Data;
/**
* @Classname People
* @Description 满足匹配动态数据源的对象
* @Date 2022/4/27 18:09
* @Created by BG
*/
@Data
public class PeopleParam implements DynamicParam {
private String id;
private String name;
private String status;
private String content;
private String tableName;
@Override
public String getSqlOrTableName() {
return tableName;
}
}
controller
package com.demo.dynamic.controller;
import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @Classname PeopleController
* @Description TODO
* @Date 2022/4/27 17:59
* @Created by BG
*/
@RestController
@RequestMapping("/data")
public class PeopleController {
@Autowired
public PeopleService peopleService;
@RequestMapping("/people1")
public String getPeople1() {
return peopleService.getPeople1();
}
@RequestMapping("/people2")
public String getPeople2() {
return peopleService.getPeople2();
}
@RequestMapping("/people3")
public String getPeople3() {
return peopleService.getPeople3();
}
}
service
package com.demo.dynamic.service;
/**
* @Classname PeopleService
* @Description TODO
* @Date 2022/4/27 18:00
* @Created by BG
*/
public interface PeopleService {
String getPeople1();
String getPeople2();
String getPeople3();
}
package com.demo.dynamic.service.impl;
import com.demo.dynamic.bean.PeopleParam;
import com.demo.dynamic.mapper.PeopleMapper;
import com.demo.dynamic.service.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
/**
* @Classname PeopleServiceImpl
* @Description TODO
* @Date 2022/4/27 18:00
* @Created by BG
*/
@Service
public class PeopleServiceImpl implements PeopleService {
@Autowired
public PeopleMapper peopleMapper;
@Override
public String getPeople1() {
List<Map<String, Object>> peopleList = peopleMapper.getPeople1();
return peopleList.toString();
}
@Override
public String getPeople2() {
String sql = "select * from people2";
List<Map<String, Object>> peopleList = peopleMapper.getPeople2(sql);
return peopleList.toString();
}
@Override
public String getPeople3() {
PeopleParam param = new PeopleParam();
param.setTableName("people2");
List<Map<String, Object>> peopleList = peopleMapper.getPeople3(param);
return peopleList.toString();
}
}
mapper
package com.demo.dynamic.mapper;
import com.demo.dynamic.bean.PeopleParam;
import com.demo.dynamic.dynamic.DynamicDB;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
/**
* @Classname PeopleMapper
* @Description TODO
* @Date 2022/4/27 18:00
* @Created by BG
*/
@Mapper
public interface PeopleMapper {
@DynamicDB
List<Map<String, Object>> getPeople1();
@DynamicDB
List<Map<String, Object>> getPeople2(@Param("getDataSql") String sql);
//限制:如果参数不是一条Sql则需要封装成实现‘DynamicParam’接口的对象
//实际是需要用到‘getSqlOrTableName’获取到表名用来匹配数据源
@DynamicDB
List<Map<String, Object>> getPeople3(@Param("param") PeopleParam param);
}
mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.dynamic.mapper.PeopleMapper">
<select id="getPeople1" resultType="java.util.HashMap">
SELECT
*
FROM
people t
</select>
<select id="getPeople2" resultType="java.util.HashMap">
${getDataSql}
</select>
<select id="getPeople3" resultType="java.util.HashMap">
SELECT
*
FROM
${param.tableName} t
</select>
</mapper>
页面测试
1、http://localhost:10086/data/people1
2、http://localhost:10086/data/people2
3、http://localhost:10086/data/people3
说明和设想的一样:当查询表‘people’时去数据库dynamic4中查询数据,当查询表‘people2’时去数据库dynamic5中查询数据!
总结:
Springboot项目启动时会自动加载配置‘@Configuration’注解的配置类(DatasourceConfig、DynamicDataSourceConfig),而在‘DynamicDataSourceConfig’中又对“DynamicDataSource”进行了设置,即完成了多个数据源配置的加载。在调用时,通过切面“DynamicDBAspect”获取到被自定义注解“DynamicDB”的方法,然后在切面中进行逻辑分析并动态切换成对应的数据源,完成多数据源的调用。