springboot AOP实现多数据源动态切换

有3个数据源
t100019、t100049、t100138
本文旨在使用springboot、AOP、AbstractRoutingDataSource实现多数据源的动态切换

1.导入数据源、aop等相关依赖

 <properties>
        <java.version>1.8</java.version>
        <aspectjrt.version>1.9.1</aspectjrt.version>
        <aspectjweaver.version>1.9.1</aspectjweaver.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.13</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.0.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>${aspectjrt.version}</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>${aspectjweaver.version}</version>
        </dependency>
    </dependencies>

2.配置文件配置如下

server.port=8889

spring.datasource.t100019.username=root
spring.datasource.t100019.password=root
spring.datasource.t100019.url=jdbc:mysql://127.0.0.1:3306/t100019?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.t100019.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.t100019.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.t100049.username=root
spring.datasource.t100049.password=root
spring.datasource.t100049.url=jdbc:mysql://127.0.0.1:3306/t100049?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.t100049.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.t100049.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.t100138.username=root
spring.datasource.t100138.password=root
spring.datasource.t100138.url=jdbc:mysql://127.0.0.1:3306/t100138?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.t100138.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.t100138.type=com.alibaba.druid.pool.DruidDataSource

3.准备数据库表

建3个数据库，分别是t100019,t100049,t100138;分别执行如下sql，创建hy_floors表。3个库可插入不同数量数据，以便于之后切换数据源查看差异

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for hy_floors
-- ----------------------------
DROP TABLE IF EXISTS `hy_floors`;
CREATE TABLE `hy_floors`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `project_id` bigint(0) NOT NULL COMMENT '项目编号',
  `park_id` int(0) NOT NULL COMMENT '园区id',
  `building_id` int(0) NOT NULL COMMENT '楼建筑物ID',
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称（同项目名称）',
  `area_covered` float NULL DEFAULT NULL COMMENT '占地面积',
  `floor_no` int(0) NULL DEFAULT NULL COMMENT '层序号',
  `underground_number` int(0) NULL DEFAULT NULL COMMENT '地下层数(废弃)',
  `owners` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '业主编号｛JSON｝',
  `developers` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '开发商｛JSON｝',
  `operators` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '运营商｛JSON｝',
  `code` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编码(900101xxxxxx)',
  `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '平面图',
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  `deleteflag` int(0) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1209 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '建筑楼层表' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of hy_floors
-- ----------------------------
INSERT INTO `hy_floors` VALUES (1, 123456789, 12, 212, 'B1', 5.6, 1, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (2, 123456789, 12, 212, 'B1', 5.3, 2, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (3, 123456789, 12, 212, 'B1', 3.2, 3, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (4, 123456789, 12, 212, 'B1', 3, 4, NULL, '', '', '', '90.90.48.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (5, 123456789, 12, 212, 'B1', 3, 5, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (6, 123456789, 12, 212, 'B1', 3, 6, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (7, 123456789, 12, 212, 'B1', NULL, 7, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);

SET FOREIGN_KEY_CHECKS = 1;

4.写一个数据库名称的枚举类 DbTypeEnum

package com.example.dynamicdatasource.enums;

/**
 *
 * Title: DbTypeEnum
 * Description: 数据库枚举类
 */
public enum DbTypeEnum {
    T100019("t100019"), T100049("t100049"), T100138("t100138");

    private String value;

    DbTypeEnum(final String value) {

        this.value = value;
    }

    public String getValue() {

        return value;
    }
}

5.建立一个获得和设置上下文环境的类 DbContextHolder ，主要负责设置和获取上下文数据源的名称

package com.example.dynamicdatasource.config;

import com.example.dynamicdatasource.enums.DbTypeEnum;

public class DbContextHolder {

    private static final ThreadLocal contextHolder = new ThreadLocal<>();

    /**
     * 设置数据源
     * 
     * @param dbTypeEnum
     */
    public static void setDbType(final DbTypeEnum dbTypeEnum) {

        contextHolder.set(dbTypeEnum.getValue());
    }

    /**
     * 取得当前数据源
     * 
     * @return
     */
    public static String getDbType() {

        return (String) contextHolder.get();
    }

    /**
     * 清除上下文数据
     */
    public static void clearDbType() {

        contextHolder.remove();
    }
}

6.写一个动态数据源类，继承AbstractRoutingDataSource，并重写determineCurrentLookupKey（）方法。返回值就是要路由的数据源的名称

package com.example.dynamicdatasource.config;

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

public class DynamicDataSource extends AbstractRoutingDataSource {

    /**
     * 取得当前使用哪个数据源
     * 
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {

        return DbContextHolder.getDbType();
    }
}

7.写数据源配置类DataSourceConfig

package com.example.dynamicdatasource.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.example.dynamicdatasource.enums.DbTypeEnum;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.jdbc.datasource.DataSourceTransactionManager;

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

@Configuration
@MapperScan("com.example.dynamicdatasource.*.dao")
public class DataSourceConfig {

    @Bean(name = "t19DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.t100019")
    public DataSource t19DataSource() {

        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "t49DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.t100049")
    public DataSource t49DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "t138DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.t100138")
    public DataSource t138DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 动态数据源配置
     *
     * @return
     */
    @Bean("multipleDataSource")
    @Primary
    public DynamicDataSource multipleDataSource(@Qualifier("t19DataSource") final DataSource t19DataSource,
                                                @Qualifier("t49DataSource") final DataSource t49DataSource,
                                                @Qualifier("t138DataSource") final DataSource t138DataSource) {

        final DynamicDataSource dynamicDataSource = new DynamicDataSource();
        final Map<Object, Object> targetDataSources = new HashMap<>(3);
        targetDataSources.put(DbTypeEnum.T100019.getValue(), t19DataSource);
        targetDataSources.put(DbTypeEnum.T100049.getValue(), t49DataSource);
        targetDataSources.put(DbTypeEnum.T100138.getValue(), t138DataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(t19DataSource);
        return dynamicDataSource;
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(multipleDataSource(t19DataSource(), t49DataSource(), t138DataSource()));
        //设置mapperLocation
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*Mapper.xml"));
        //设置开启驼峰匹配
        sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("transactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("multipleDataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

}

8.AOP写数据源动态切换切面

package com.example.dynamicdatasource.aop;

import com.example.dynamicdatasource.config.DbContextHolder;
import com.example.dynamicdatasource.enums.DbTypeEnum;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

@Component
@Aspect
public class DataSourceSwitchAspect {

    @Pointcut("execution(* com.example.dynamicdatasource.t100019.dao..*.*(..))")
    private void t19Aspect() {
    }
    //切入点
    @Pointcut("execution(* com.example.dynamicdatasource.t100049.dao..*.*(..))")
    private void t49dbDictAspect() {
    }

    @Pointcut("execution(* com.example.dynamicdatasource.t100138.dao..*.*(..))")
    private void t138dbPartnerAspect() {
    }
    //前置通知，调对应路径下方法时拦截并设置数据源
    @Before("t19Aspect()")
    public void t19() {
        DbContextHolder.setDbType(DbTypeEnum.T100019);
    }

    @Before("t49dbDictAspect()")
    public void t49() {
        DbContextHolder.setDbType(DbTypeEnum.T100049);
    }

    @Before("t138dbPartnerAspect()")
    public void t138() {
        DbContextHolder.setDbType(DbTypeEnum.T100138);
    }
}

9.为方便测试，写一个最简单的SSM。t100019包下的SSM代码 controller层，t100049和t100138如是。

package com.example.dynamicdatasource.t100019.controller;

import com.example.dynamicdatasource.t100019.service.T19Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class T19Controller {

    @Autowired
    private T19Service t19Service;
    
    @GetMapping("/test/19")
    public Integer getCount() {
        return t19Service.getCount();
    }

}

service层

package com.example.dynamicdatasource.t100019.service;

public interface T19Service {
    Integer getCount();
}

package com.example.dynamicdatasource.t100019.service.impl;

import com.example.dynamicdatasource.t100019.dao.T19Dao;
import com.example.dynamicdatasource.t100019.service.T19Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class T19ServiceImpl implements T19Service {
    @Autowired
    private T19Dao t19Dao;
    @Override
    public Integer getCount() {
        return t19Dao.getCount();
    }
}

dao层

package com.example.dynamicdatasource.t100019.dao;
public interface T19Dao {
    Integer getCount();
}

mapper

<?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.example.dynamicdatasource.t100019.dao.T19Dao">
    <select id="getCount" resultType="java.lang.Integer">
       SELECT COUNT(id) FROM `hy_floors`
    </select>
</mapper>

t100049和t100138包下代码与t100019代码相同，只是名称第一不同。整体代码结构如下

测试：

t100019库

t100049库

t100138库

项目源码地址：

https://github.com/sidney189/dynamicDataSource.git

本质：

AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能在运行时, 根据某个key值来动态切换到真正的DataSource上。