动态切换mysql数据源_主从数据源动态切换

项目中需要使用南大信通数据库GBase，因此配置多数据源用以查询不同数据库的数据.先做一个小Demo测试数据源动态切换。主要使用切面修改配置文件实现数据源切换，再切点后置通知中再切回主数据源。

引入依赖

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.4.2

com.example

demo

0.0.1-SNAPSHOT

demo

Demo project for Spring Boot

1.8

com.alibaba

druid-spring-boot-starter

1.2.4

org.projectlombok

lombok

1.18.4

provided

org.aspectj

aspectjrt

org.aspectj

aspectjweaver

org.springframework

spring-aop

com.ibm.informix

jdbc

4.10.8.1

org.springframework.boot

spring-boot-starter-jdbc

mysql

mysql-connector-java

org.mybatis

mybatis

3.4.5

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.1

log4j

log4j

1.2.17

org.springframework.boot

spring-boot-starter-jdbc

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-test

test

junit

junit

4.13.1

test

org.springframework.boot

spring-boot-maven-plugin

true

true

配置文件

spring:

datasource:

master:

jdbcUrl: jdbc:mysql://localhost:3306/ruoyi?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8

username: root

password: root

driverClassName: com.mysql.cj.jdbc.Driver

slave:

jdbcUrl: jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8

username: root

password: root

driverClassName: com.mysql.cj.jdbc.Driver

druid:

initialSize: 10

minIdle: 10

maxActive: 100

maxWait: 60000

timeBetweenEvictionRunsMillis: 2000

minEvictableIdleTimeMillis: 300000

validationQuery: SELECT 1

testWhileIdle: true

testOnBorrow: false

testOnReturn: false

poolPreparedStatements: false

maxPoolPreparedStatementPerConnectionSize: 0

filters: stat,log4j

connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

useGlobalDataSourceStat: true

mybatis:

# 搜索指定包别名

typeAliasesPackage: com.example.demo.entity

# 配置mapper的扫描，找到所有的mapper.xml映射文件

mapperLocations: classpath*:mybatis/mapping/*Mapper.xml

# 加载全局的配置文件

configLocation: classpath:mybatis/mybatis-config.xml

# 开发环境配置

server:

# 服务器的HTTP端口，默认为8080

port: 8081

servlet:

# 应用的访问路径

context-path: /

自定义注解类

package com.example.demo.annotation;

import java.lang.annotation.Documented;

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

import java.lang.annotation.Target;

import com.example.demo.datasource.DataBaseType;

/**

* @author ldb

* @date 2020-04-24

* 自定义的多数据源注解

*/

@Target(ElementType.METHOD)

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface DataSource {

/**

* 切换数据源的名称

*/

public DataBaseType value() default DataBaseType.MASTER;

}

多数据源切面类

package com.example.demo.aop;

import java.lang.reflect.Method;

import org.aspectj.lang.JoinPoint;

import org.aspectj.lang.annotation.After;

import org.aspectj.lang.annotation.Aspect;

import org.aspectj.lang.annotation.Before;

import org.aspectj.lang.annotation.Pointcut;

import org.aspectj.lang.reflect.MethodSignature;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.core.Ordered;

import org.springframework.stereotype.Component;

import org.springframework.util.StringUtils;

import com.example.demo.annotation.DataSource;

import com.example.demo.datasource.DataSourceType;

import lombok.extern.slf4j.Slf4j;

/**

* @author ldb

* @date 2020-04-24

* 多数据源切面类

*/

@Aspect

@Component

@Slf4j

public class DataSourceAspect implements Ordered {

protected Logger logger = LoggerFactory.getLogger(getClass());

@Pointcut("@annotation(com.example.demo.annotation.DataSource)")

public void pointCut(){

}

@Before("pointCut()")

public void setDataSource(JoinPoint joinPoint) {

try {

//这里根据注解进行数据源的切换

MethodSignature signature = (MethodSignature) joinPoint.getSignature();

Method method = signature.getMethod();

DataSource annotation = method.getAnnotation(DataSource.class);

if (!StringUtils.isEmpty(annotation)){

DataSourceType.setDataBaseType(annotation.value());

}

}catch (Exception e){

e.getStackTrace();

}

}

@After("pointCut()")

public void after(){

DataSourceType.clearDataBaseType();

}

@Override

public int getOrder() {

//设置order属性，配置spring容器的加载顺序

return 1;

}

}

多数据源配置类

package com.example.demo.config;

import java.util.HashMap;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.jdbc.DataSourceBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import com.example.demo.datasource.DataBaseType;

import com.example.demo.datasource.DynamicDataSource;

/**

* @author ldb

* @date 2020-04-24

* 定义多个数据源，将定义好的多个数据源放到动态数据源中

*/

@Configuration

//@MapperScan(basePackages = "com.example.demo.dao", sqlSessionFactoryRef = "SqlSessionFactory")//经测试去掉这行，没有报错

public class DataSourceConfig {

@Bean("masterDataSource")

@ConfigurationProperties("spring.datasource.master")//从yml取值

public DataSource masterDataSource() {

return DataSourceBuilder.create().build();

}

@Bean("slaveDataSource")

@ConfigurationProperties("spring.datasource.slave")

public DataSource slaveDataSource() {

return DataSourceBuilder.create().build();

}

@Bean(name = "dynamicDataSource")

public DynamicDataSource dataSource() {

//把所有的数据源放入动态数据源中

HashMap targetDataSource = new HashMap<>();

targetDataSource.put(DataBaseType.MASTER, masterDataSource());

targetDataSource.put(DataBaseType.SALVE, slaveDataSource());

//设置默认的数据源

DynamicDataSource dataSource = new DynamicDataSource();

dataSource.setTargetDataSources(targetDataSource);

dataSource.setDefaultTargetDataSource(masterDataSource());

return dataSource;

}

@Bean(name = "SqlSessionFactory")

public SqlSessionFactory sqlSessionFactory(DataSource dynamicDataSource) throws Exception {

SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

bean.setDataSource(dynamicDataSource);

bean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mapping/*.xml"));

return bean.getObject();

}

}

数据源连接池配置类

package com.example.demo.config;

/*

* @Author fjp

* @date 2020/10/25-23:02

*/

import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;

import java.sql.SQLException;

/**

* Created by gaomin on 2017/12/1.

*/

@Configuration

public class DruidConfig {

@Value("${spring.datasource.druid.initialSize}")

private int initialSize;

@Value("${spring.datasource.druid.minIdle}")

private int minIdle;

@Value("${spring.datasource.druid.maxActive}")

private int maxActive;

@Value("${spring.datasource.druid.maxWait}")

private int maxWait;

@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")

private int timeBetweenEvictionRunsMillis;

@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")

private int minEvictableIdleTimeMillis;

@Value("${spring.datasource.druid.validationQuery}")

private String validationQuery;

@Value("${spring.datasource.druid.testWhileIdle}")

private boolean testWhileIdle;

@Value("${spring.datasource.druid.testOnBorrow}")

private boolean testOnBorrow;

@Value("${spring.datasource.druid.testOnReturn}")

private boolean testOnReturn;

@Bean

public DataSource druidDataSource() {

DruidDataSource datasource = new DruidDataSource();

datasource.setInitialSize(initialSize);

datasource.setMinIdle(minIdle);

datasource.setMaxActive(maxActive);

datasource.setMaxWait(maxWait);

datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

datasource.setValidationQuery(validationQuery);

datasource.setTestWhileIdle(testWhileIdle);

datasource.setTestOnBorrow(testOnBorrow);

datasource.setTestOnReturn(testOnReturn);

System.out.println("-----加载druid------");

return datasource;

}

}

其他工具类

package com.example.demo.datasource;

/**

* @author ldb

* @date 2020-04-25

*/

public enum DataBaseType {

MASTER, SALVE

}

package com.example.demo.datasource;

/**

* @author ldb

* @date 2020-04-25

* 对数据源的类型的一些操作

*/

public class DataSourceType {

//使用ThreadLocal保证线程安全

private static final ThreadLocal TYPE = new ThreadLocal();

//在当前线程内部设置多数据源

public static void setDataBaseType(DataBaseType dataBaseType){

System.out.println("切换数据源："+dataBaseType);

TYPE.set(dataBaseType);

}

//获取数据源类型

public static DataBaseType getDataBaseType(){

DataBaseType dataBaseType = TYPE.get()==null?DataBaseType.MASTER:TYPE.get();

return dataBaseType;

}

//清空数据类型

public static void clearDataBaseType(){

TYPE.remove();

}

}

package com.example.demo.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**

* @author ldb

* @date 2020-04-25

* 继承动态数据源动态类

*/

public class DynamicDataSource extends AbstractRoutingDataSource {

//确定当前的数据源类型

@Override

protected Object determineCurrentLookupKey() {

return DataSourceType.getDataBaseType();

}

}