SpringBoot动态切换数据源

1.基础数据准备 

1.1表结构准备

package com.wang.entity;

import lombok.Data;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author wangzuohong1
 * @date 2022/5/27
 */
@Data
@Table(name = "table_a")
public class TableA {
    @Id
    @GeneratedValue(generator = "JDBC")
    private Long id;
    private String name;
    private String address;
    private String hobby;
}

package com.wang.entity;

import lombok.Data;

import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author wangzuohong1
 * @date 2022/5/27
 */
@Data
@Table(name = "table_b")
public class TableB {

    @Id
    @GeneratedValue(generator = "JDBC")
    private Long id;
    private String name;
    private String address;
    private String hobby;

}

1.2数据源准备

mybatis:
  mapper-locations: classpath:mybatis/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
  type-aliases-package: com.wang.entity

spring:
  datasource:
    username: root
    password: zhouliang
    jdbc-url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull
    driver-class-name: com.mysql.jdbc.Driver

  test:
    datasource:
        driver-class-name: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://db1-server.360cec.com:3306/legend?characterEncoding=UTF-8&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&failOverReadOnly=false&autoReconnect=true&useSSL=false
        username: legend_s
        password: erFjf7Usf6hk

1.3 启动类准备

package com.wang;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan(basePackages = "com.wang.dao")
public class SpringbootApplication {

   public static void main(String[] args) {
      SpringApplication.run(SpringbootApplication.class, args);
   }

}


1.4Controller准备

package com.wang;

import com.alibaba.fastjson.JSON;
import com.wang.dao.TableADao;
import com.wang.entity.TableA;
import com.wang.service.ADemoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * @author wangzuohong1
 * @date 2022/5/27
 */
@RestController
@RequestMapping("/base")
public class BaseController {

    @Autowired
    private ADemoService aDemoService;

    @Autowired
    private TableADao tableADao;

    @GetMapping("/a/insertB")
    public String addToB(String tips) {
        aDemoService.addToB(tips);
        return "成功";
    }

    @GetMapping("/a/insertA")
    public String addToA(String tips) {
        aDemoService.addToA(tips);
        return "成功";
    }

    @GetMapping("/a/finish")
    public String finish(String tips) {
        aDemoService.finsih(tips);
        return "成功";
    }

    @GetMapping("/a/get")
    public String getA() {
        TableA tableA = tableADao.selectByPrimaryKey(18);
        String hobby = tableA.getHobby();
        List<String> strings = JSON.parseArray(hobby, String.class);
        strings.forEach(System.out::println);
        return "";
    }


    public static void main(String[] args) {

    }
}

1.5 service准备

package com.wang.service.impl;

import com.wang.config.DataSourceEnum;
import com.wang.config.RoutingDataSource;
import com.wang.dao.net.TabelBDao;
import com.wang.dao.TableADao;
import com.wang.entity.TableA;
import com.wang.entity.TableB;
import com.wang.service.ADemoService;
import com.wang.service.BDemoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author wangzuohong1
 * @date 2022/5/27
 */
@Service
public class ADemoServiceImpl implements ADemoService {

    @Autowired
    private TableADao tableADao;

    @Autowired
    private TabelBDao tabelBDao;

    @Autowired
    private BDemoService bDemoService;

    private final static int age;

    static {
        age = (Runtime.getRuntime().availableProcessors() > 1 ?
                1 << 8 : 0);
    }

    @Override
    @RoutingDataSource(DataSourceEnum.LEGEND)
    @Transactional(rollbackFor = Exception.class)
    public void addToB(String tips) {
        bDemoService.addToB(tips);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void finsih(String tips) {
        // mock consume
        bDemoService.addToB(tips);

        // mock finish
        addToA(tips);

    }

    @Override
    public void addToA(String tips) {
        bDemoService.addToA(tips);
    }

    private void insert(String tips) {
        TableA tableA = new TableA();
        tableA.setAddress("京东");
        tableA.setHobby("打球");
        tableA.setName("wzh");
        tableADao.insert(tableA);
        if ("1".equals(tips)) {
            int i = 1 / 0;
        }
        TableB tableB = new TableB();
//        tableB.setTableAId(tableA.getId());
//        tableB.setRefer("23");
//        tableB.setOtherComment("测试事务");
        tabelBDao.insertSelective(tableB);
        if ("2".equals(tips)) {
            int i = 1 / 0;
        }
    }
}

package com.wang.service.impl;

import com.wang.dao.net.TabelBDao;
import com.wang.dao.TableADao;
import com.wang.entity.TableA;
import com.wang.entity.TableB;
import com.wang.service.BDemoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @author wangzuohong1
 * @date 2022/5/27
 */
@Service
public class BDemoServiceImpl implements BDemoService {

    @Autowired
    private TableADao tableADao;

    @Autowired
    private TabelBDao tabelBDao;

    @Override
    public void addToA(String tips) {
        if ("3".equals(tips)) {
            int a = 1 / 0;
        }
        TableA tableA = new TableA();
        tableA.setId(0L);
        tableA.setName("asdasd");
        tableA.setAddress("asdasdasdas");
        tableA.setHobby("1231231");
        tableADao.insert(tableA);
    }

    @Override
//    @Transactional(rollbackFor = Exception.class)
    public void addToB(String tips) {
        if ("3".equals(tips)) {
            int a = 1 / 0;
        }
        TableB tableB = new TableB();
        tableB.setId(0L);
        tableB.setName("adasd");
        tableB.setAddress("asdasd");
        tableB.setHobby("adsasdas");
        tabelBDao.insert(tableB);
    }
}

 

1.6  切点配置

package com.wang.aop;

import com.alibaba.fastjson.JSON;

import com.wang.config.DataSourceEnum;
import com.wang.config.DynamicDataSourceContextHolder;
import com.wang.config.RoutingDataSource;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 * 执行sql切入点方面
 * <p>
 * PerformSqlPointcutAspect
 */
@Slf4j
@Aspect
@Order(-1)
@Component
public class PerformSqlPointcutAspect {

    /**
     * 注释点切
     *
     * @param dataSource 数据源
     */
    @Pointcut(value = "@annotation(dataSource)", argNames = "dataSource")
    private void annotationsPointCut(RoutingDataSource dataSource) {

    }

    /**
     * 之前
     * 前置通知，切换数据源
     *
     * @param dataSource 数据源
     */
    @Before(value = "annotationsPointCut(dataSource)", argNames = "dataSource")
    public void before(RoutingDataSource dataSource) {
        log.info("切换数据源 ============================== {}", dataSource.value());
        DynamicDataSourceContextHolder.setCurrentDataSource(dataSource.value());
    }

    /**
     * 后
     * 后置通知，切换数据源
     *
     * @param dataSource 数据源
     */
    @After(value = "annotationsPointCut(dataSource)", argNames = "dataSource")
    public void after(RoutingDataSource dataSource) {
        log.info("清除数据源 ============================== {}", dataSource.value());
        DynamicDataSourceContextHolder.removeDataSource();
    }

    /**
     * 包路径点切
     */
    @Pointcut("execution(* com.wang.dao.net.*.*(..))")
    private void packagePathPointCut() {
    }

    /**
     * 周围
     *
     * @param proceedingJoinPoint 进行连接点
     * @return {@link Object}
     */
    @Around(value = "packagePathPointCut()")
    public Object around(ProceedingJoinPoint proceedingJoinPoint) throws Throwable {
        Object result;

        String className = proceedingJoinPoint.getTarget().getClass().getInterfaces()[0].getSimpleName();
        String methodName = proceedingJoinPoint.getSignature().getName();
        String format = String.format("%s#%s", className, methodName);
        try {
            log.info("{} 包路径切面切换数据源！", format);
            System.out.println(format);
            DynamicDataSourceContextHolder.setCurrentDataSource(DataSourceEnum.LEGEND);
            result = proceedingJoinPoint.proceed();
            System.out.println("dfsdfsdfsdfsdf"+result);
            DynamicDataSourceContextHolder.removeDataSource();
            log.info("{} 包路径切面清除数据源！", format);
            System.out.println("包路径切面清除数据源"+format);
            return result;
        } catch (Exception e) {
            log.error(String.format("dao monitor invoke error, class:%s, method:%s, param:%s",
                    className,
                    methodName,
                    JSON.toJSONString(proceedingJoinPoint.getArgs())), e);
            throw e;
        }
    }

    /**
     * 执行sql异常监控点
     */
    @Pointcut("execution(* com.wang.dao.net.*.*(..))")
    public void performSqlAbnormalMonitorPointCut() {
    }

    /**
     * 周围建议
     *
     * @param proceedingJoinPoint 进行连接点
     * @return {@link Object}
     * @throws Throwable throwable
     */
    @Around(value = "performSqlAbnormalMonitorPointCut()")
    public Object aroundAdvice(ProceedingJoinPoint proceedingJoinPoint) throws Throwable {
        Object result;

        String className = proceedingJoinPoint.getTarget().getClass().getInterfaces()[0].getSimpleName();
        String methodName = proceedingJoinPoint.getSignature().getName();
        try {
            if (log.isDebugEnabled()) {
                log.debug("dao monitor invoke, class:{}, method:{}, param:{}",
                        className,
                        methodName,
                        JSON.toJSONString(proceedingJoinPoint.getArgs()));
            }
            result = proceedingJoinPoint.proceed();
            if (log.isDebugEnabled()) {
                log.debug("dao monitor invoke, class:{}, method:{}, param:{}, result:{}",
                        className,
                        methodName,
                        JSON.toJSONString(proceedingJoinPoint.getArgs()),
                        JSON.toJSONString(result));
            }

            return result;
        } catch (Exception e) {
            log.error(String.format("perform sql monitor invoke error, class:%s, method:%s, param:%s",
                    className,
                    methodName,
                    JSON.toJSONString(proceedingJoinPoint.getArgs())), e);
            throw e;
        }
    }
}

1.7 注解配置

package com.wang.config;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 路由数据来源
 */
// 注解由JVM保留，在运行时环境可以使用
@Retention(RetentionPolicy.RUNTIME)
// 注解可以应用于类、类方法
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface RoutingDataSource {

    /**
     * 值
     *
     */
    DataSourceEnum value() default DataSourceEnum.LOCAL;
}

1.8 数据源枚举配置

package com.wang.config;

/**
 * 数据源枚举
 * <p>
 * DataSourceEnum

 */
public enum DataSourceEnum {
    /**
     * 本地
     */
    LOCAL,
    /**
     * 传说
     */
    LEGEND,
    ;
}

1.9数据源配置

package com.wang.config;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
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.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * DataSourceConfig
 */
@Slf4j
@Configuration
@EnableTransactionManagement
public class DataSourceConfig {

    /**
     * 雷电数据源
     *
     * @return {@link HikariDataSource}
     */
    @Bean("raidenDataSource")
    @ConfigurationProperties(prefix = "spring.test.datasource")
    public HikariDataSource raidenDataSource() {
        log.info("raidenDataSource build success!");
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }


    @Bean("localDataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public HikariDataSource localDataSource() {
        log.info("localDataSource build success!");
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    @Primary
    public DynamicDataSource dynamicDataSource(@Qualifier("localDataSource") DataSource localDataSource,
            @Qualifier("raidenDataSource") DataSource raidenDataSource) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setDefaultTargetDataSource(localDataSource);
        Map<Object, Object> dataSources = new HashMap<>();
        dataSources.put(DataSourceEnum.LOCAL, localDataSource);
        dataSources.put(DataSourceEnum.LEGEND, raidenDataSource);
        System.out.println("asdsada"+dataSources);
        dynamicDataSource.setTargetDataSources(dataSources);
        return dynamicDataSource;
    }

    /**
     * jdbc模板
     *
     * @param dataSource 数据源
     * @return {@link JdbcTemplate}
     */
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        log.info("jdbc模板 className:{}", dataSource.getClass().getName());
        return new JdbcTemplate(dataSource);
    }

    /**
     * 平台事务管理程序
     *
     * @param dataSource 数据源
     * @return {@link PlatformTransactionManager}
     */
    @Bean
    public PlatformTransactionManager platformTransactionManager(DataSource dataSource) {
        log.info("平台事务管理程序 className:{}", dataSource.getClass().getName());
        return new DataSourceTransactionManager(dataSource);
    }
}

1.10 动态数据来源

package com.wang.config;

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

/**
 * 动态数据来源
 * <p>
 * DynamicDataSource
 *
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    /**
     * Determine the current lookup key. This will typically be
     * implemented to check a thread-bound transaction context.
     * <p>Allows for arbitrary keys. The returned key needs
     * to match the stored lookup key type, as resolved by the
     * {@link #resolveSpecifiedLookupKey} method.
     */
    @Override
    protected Object determineCurrentLookupKey() {
        System.out.println("------------------"+DynamicDataSourceContextHolder.getCurrentDataSource());
        return DynamicDataSourceContextHolder.getCurrentDataSource();
    }
}

1.11上下文 

package com.wang.config;

/**
 * DynamicDataSourceContextHolder
 *
 */
public class DynamicDataSourceContextHolder {

    /**
     * ThreadLocal 用于记录当前数据源
     */
    private static final ThreadLocal<DataSourceEnum> CONTEXT_HOLDER = ThreadLocal.withInitial(() -> DataSourceEnum.LOCAL);

    /**
     * 获取当前数据源
     *
     */
    public static DataSourceEnum getCurrentDataSource() {
        return CONTEXT_HOLDER.get();
    }

    /**
     * 设置当前数据源
     *
     * @param dataSourceEnum 数据源枚举
     */
    public static void setCurrentDataSource(DataSourceEnum dataSourceEnum) {
        System.out.println("dada:"+dataSourceEnum);
        CONTEXT_HOLDER.set(dataSourceEnum);
    }

    /**
     * 删除当前数据源记录
     */
    public static void removeDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

1.12数据源

package com.wang.config;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanInitializationException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.*;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Map;

/**
 * ValidationConnection

 */
@Slf4j
@Component
public class ValidationConnection implements ApplicationContextAware {
    /**
     * hikari数据源
     */
    @Resource
    private HikariDataSource raidenDataSource;

    /**
     * Set the ApplicationContext that this object runs in.
     * Normally this call will be used to initialize the object.
     * <p>Invoked after population of normal bean properties but before an init callback such
     * as {@link InitializingBean#afterPropertiesSet()}
     * or a custom init-method. Invoked after {@link ResourceLoaderAware#setResourceLoader},
     * {@link ApplicationEventPublisherAware#setApplicationEventPublisher} and
     * {@link MessageSourceAware}, if applicable.
     *
     * @param applicationContext the ApplicationContext object to be used by this object
     * @throws ApplicationContextException in case of context initialization errors
     * @throws BeansException              if thrown by application context methods
     * @see BeanInitializationException
     */
    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        Map<String, DataSource> beansOfType = applicationContext.getBeansOfType(DataSource.class);
        log.info("dataSourceName:{}", beansOfType.keySet());
        for (Map.Entry<String, DataSource> entry : beansOfType.entrySet()) {
            log.warn("dataSourceConnection name:{} start! ", entry.getKey());
            try {
                DataSource dataSource = entry.getValue();
                log.warn("dataSourceConnection name:{} 数据库连接为：{}", entry.getKey(), dataSource.getConnection());
                if (dataSource instanceof HikariDataSource) {
                    HikariDataSource hikariDataSource = (HikariDataSource) dataSource;
                    log.warn("dataSourceConnection name:{} jdbcUrl：{}", entry.getKey(), hikariDataSource.getJdbcUrl());
                    log.warn("dataSourceConnection name:{} username：{}", entry.getKey(), hikariDataSource.getUsername());
                    log.warn("dataSourceConnection name:{} password：{}", entry.getKey(), hikariDataSource.getPassword());
                }
            } catch (SQLException e) {
                log.warn("dataSourceConnection name:{} error!", entry.getKey());
                continue;
            }
            log.warn("dataSourceConnection name:{} end! ", entry.getKey());
        }
        try {
            log.info("raidenDataSource name:{} pwd:{} url:{}", raidenDataSource.getUsername(), raidenDataSource.getPassword(), raidenDataSource.getJdbcUrl());
        } catch (Exception e) {
            log.error("hikariDataSource error!", e);
        }
    }
}