需求
Springboot项目,配置多个数据源,动态自动切换数据源。
实现
1.mysql建库建表
1.创建4个库 test/customer/labormoney/employee
2.对应库下创建表:person/customer/labor/employee
3.每个表都是两个字段: id varchar(20),name varchar(20)
2.pom.xml
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.5.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.7.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.17</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
</dependencies>
3.application.yml
server:
port: 8901
# 默认的数据库连接
spring:
datasource:
druid:
name: vinshine
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&failOverReadOnly=false
username: root
password: root@hiekn
driver-class-name: com.mysql.jdbc.Driver
initial-size: 3
max-active: 20
min-idle: 1
max-wait: 6000
validation-query: select 'x'
validation-query-timeout: 15
test-on-borrow: false
test-while-idle: true
keep-alive: true
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
time-between-log-stats-millis: 300000
filter:
stat:
merge-sql: true
log-slow-sql: true
slow-sql-millis: 5000
commons-log:
statement-executable-sql-log-enable: true
# 需要注入的其他数据库连接信息
bz:
datasource:
customer:
url: jdbc:mysql://localhost:3306/customer?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&failOverReadOnly=false
username: root
password: root@hiekn
driver-class-name: com.mysql.jdbc.Driver
employee:
url: jdbc:mysql://localhost:3306/employee?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&failOverReadOnly=false
username: root
password: root@hiekn
driver-class-name: com.mysql.jdbc.Driver
labormoney:
url: jdbc:mysql://localhost:3306/labormoney?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&failOverReadOnly=false
username: root
password: root@hiekn
driver-class-name: com.mysql.jdbc.Driver
4.App.java
@SpringBootApplication
public class App {
public static void main(String[] args) {
SpringApplication.run(App.class, args);
}
}
5.bean
@Data
public class Customer {
private String id;
private String name;
}
@Data
public class Employee {
private String id;
private String name;
}
@Data
public class Labor {
private String id;
private String name;
}
@Data
public class Person {
private String id;
private String name;
}
5.service
public interface CustomerService extends IService<Customer> {
List<Customer> listggg();
}
public interface EmployeeService extends IService<Employee> {
List<Employee> list22();
public List<Labor> list33();
}
public interface LaborService extends IService<Labor> {
List<Labor> list1();
}
public interface PersonService extends IService<Person> {
List<Person> listx();
List<Person> listxxx();
}
6.service.impl
@Service
public class CustomerServiceImpl extends ServiceImpl<CustomerMapper, Customer> implements CustomerService {
//方法上切换数据源
@UsingDataSource(value = DataSourceEnum.PROD_CUSTOMER)
@Override
public List<Customer> listggg() {
return this.list();
}
}
@Service
public class EmplyeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements EmployeeService {
//一个类中,不同方法使用不同的数据源
@UsingDataSource(value = DataSourceEnum.PROD_EMPLOYEE)
@Override
public List<Employee> list22() {
return this.list();
}
@Autowired
private LaborMapper laborMapper;
@UsingDataSource(value = DataSourceEnum.PROD_LABORMONEY)
@Override
public List<Labor> list33(){
return laborMapper.findList();
}
}
//类上使用数据源注解
@Service
@UsingDataSource(value = DataSourceEnum.PROD_LABORMONEY)
public class LaborServiceImpl extends ServiceImpl<LaborMapper, Labor> implements LaborService {
@Override
public List<Labor> list1() {
System.out.println("执行方法------------");
return list();
}
}
@Service
public class PersonServiceImpl extends ServiceImpl<PersonMapper, Person> implements PersonService {
@Autowired
private PersonMapper personMapper;
@UsingDataSource(value = DataSourceEnum.DEFAULT)
@Override
public List<Person> listx() {
return personMapper.listx();
}
@UsingDataSource(value = DataSourceEnum.DEFAULT)
@Override
public List<Person> listxxx() {
return personMapper.listx();
}
}
7.mapper
@Mapper
public interface CustomerMapper extends BaseMapper<Customer> {
}
@Mapper
public interface PersonMapper extends BaseMapper<Person> {
@Select("select * from person where id = 1")
List<Person> listx();
}
@Mapper
public interface EmployeeMapper extends BaseMapper<Employee> {
}
@Mapper
public interface LaborMapper extends BaseMapper<Labor> {
@Select("select * from labor")
List<Labor> findList();
}
7.自定义注解UsingDataSource.java
@Target({ElementType.TYPE,ElementType.METHOD}) // TYPE: 应用于类、接口(包括注解类型)、枚举
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UsingDataSource {
DataSourceEnum value() default DataSourceEnum.DEFAULT;
}
8.枚举类DataSourceEnum.java
public enum DataSourceEnum {
DEFAULT("defaultDataSource"),PROD_CUSTOMER("prodCustomerDataSource"),
PROD_EMPLOYEE("prodEmployeeDataSource"),PROD_LABORMONEY("prodLabormoneyDataSource");
private String value;
DataSourceEnum(String value){this.value=value;}
public String getValue() {
return value;
}
}
9.核心:数据源配置相关
DataSourceContextHolder.java
public class DataSourceContextHolder {
//每个线程独立拥有一个副本,每个线程都可以独立改变自己的副本。互相不影响
private static final ThreadLocal<String> CONTEXT_HOLDER = new InheritableThreadLocal<>();
/**
* 设置数据源
*/
public static void setDataSource(String db){
CONTEXT_HOLDER.set(db);
}
/**
* 取得当前数据源
*/
public static String getDataSource(){
return CONTEXT_HOLDER.get();
}
/**
* 清除上下文数据
*/
public static void clear(){
CONTEXT_HOLDER.remove();
}
}
MultipleDataSource.java
public class MultipleDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSource();
}
}
@Configuration
@MapperScan(basePackages = "ai.test.mapper")
@Slf4j
public class MyDataSourceConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* SQL执行效率插件
*/
@Bean
@Profile({"defaultDataSource","prodCustomerDataSource","prodEmployeeDataSource","prodLabormoneyDataSource"})
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setMaxTime(1000);
performanceInterceptor.setFormat(true);
return performanceInterceptor;
}
@Bean(name = "defaultDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid")
public DataSource getDefaultDataSource() {
log.info("defaultDataSource");
return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
}
@Bean(name = "prodCustomerDataSource")
@ConfigurationProperties(prefix = "bz.datasource.customer")
public DataSource getProdCustomerDataSource() {
log.info("prodCustomerDataSource");
return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
}
@Bean(name = "prodEmployeeDataSource")
@ConfigurationProperties(prefix = "bz.datasource.employee")
public DataSource getProdEmployeeDataSource() {
log.info("prodEmployeeDataSource");
return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
}
@Bean(name = "prodLabormoneyDataSource")
@ConfigurationProperties(prefix = "bz.datasource.labormoney")
public DataSource getProdLabormoneyDataSource() {
log.info("prodLabormoneyDataSource");
return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
}
@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("defaultDataSource") DataSource defaultDataSource,
@Qualifier("prodCustomerDataSource") DataSource prodCustomerDataSource,
@Qualifier("prodEmployeeDataSource") DataSource prodEmployeeDataSource,
@Qualifier("prodLabormoneyDataSource") DataSource prodLabormoneyDataSource) {
MultipleDataSource multipleDataSource = new MultipleDataSource();
Map< Object, Object > targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceEnum.DEFAULT.getValue(), defaultDataSource);
targetDataSources.put(DataSourceEnum.PROD_CUSTOMER.getValue(), prodCustomerDataSource);
targetDataSources.put(DataSourceEnum.PROD_EMPLOYEE.getValue(), prodEmployeeDataSource);
targetDataSources.put(DataSourceEnum.PROD_LABORMONEY.getValue(), prodLabormoneyDataSource);
//添加数据源
multipleDataSource.setTargetDataSources(targetDataSources);
//设置默认数据源
multipleDataSource.setDefaultTargetDataSource(defaultDataSource);
return multipleDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(multipleDataSource(getDefaultDataSource(),getProdCustomerDataSource(),
getProdEmployeeDataSource(),getProdLabormoneyDataSource()));
sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/**/*Mapper.xml"));
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setDefaultExecutorType(ExecutorType.SIMPLE);
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setMapUnderscoreToCamelCase(true);
configuration.setCacheEnabled(false);
sqlSessionFactory.setConfiguration(configuration);
sqlSessionFactory.setPlugins(new Interceptor[]{
paginationInterceptor() //添加分页功能
});
return sqlSessionFactory.getObject();
}
}
10.核心:切面类
package ai.test.aspect;
import ai.test.anno.UsingDataSource;
import ai.test.config.DataSourceContextHolder;
import ai.test.config.DataSourceEnum;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import sun.reflect.generics.tree.ClassSignature;
import javax.xml.crypto.dsig.SignatureMethod;
import java.lang.reflect.Method;
@Component
@Slf4j
@Aspect
@Order(-1)
public class DataSourceUsingAspect {
// mapper包下所有包,下所有类,所有方法,参数任意,返回值任意
@Pointcut("execution(* ai.test.service.impl.*.*(..))")
public void pointCut() {
}
/**
* 说明:
* 1.注解不能加在Mapper上面。因为Mybatis中默认的方法不能被获取到。
* 2.可以吧注解加在到service层上,每个方法上都带有切面数据库的值。但是所有的查询语句必须要写到service这一层,不能直接在controller中调用mybatisplus在service中中默认的方法。例如直接在controller中调用: laborService.list() ==》这个是不会被切面监察到的。
*
* 注解逻辑:
* 如果类上有注解,直接使用类的注解,不管方法上是否存在注解。
* 如果类上没有注解,使用方法上的注解。
* 如果类和方法上都没有注解,使用默认的数据源
*/
//前置通知,设置数据源
@Before("pointCut()")
public void doBefore(JoinPoint point) throws ClassNotFoundException {
//获取接口或者类的名称
String cName = point.getSignature().getDeclaringType().getSimpleName();
//获取到了这个方法的名称
String mName = point.getSignature().getName();
String declaringTypeName = point.getSignature().getDeclaringTypeName();
//获取到了类的全路径
Class<?> aClass = Class.forName(declaringTypeName);
//获取类上的注解
UsingDataSource annotation = aClass.getAnnotation(UsingDataSource.class);
if (annotation == null) {
//获取方法上的注解
Method[] methods = aClass.getMethods();
for (int i = 0; i < methods.length; i++) {
if (methods[i].getName().equals(mName)) {
UsingDataSource mAnno = methods[i].getAnnotation(UsingDataSource.class);
if (mAnno == null) {
DataSourceContextHolder.setDataSource(DataSourceEnum.DEFAULT.getValue());
} else {
DataSourceContextHolder.setDataSource(mAnno.value().getValue());
}
break;
}
}
} else {
//类上的注解不为空
DataSourceContextHolder.setDataSource(annotation.value().getValue());
}
}
//后置通知,把数据源清除
@After("pointCut()")
public void doAfter() {
DataSourceContextHolder.clear();
}
}