Springboot整合多数据源（自定义注解+aop切面实现）

明湖起风了

已于 2022-12-02 17:35:48 修改

阅读量1.1k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： springBoot 文章标签： spring boot java mybatis

于 2022-11-27 19:56:15 首次发布

本文链接：https://blog.csdn.net/m0_47743175/article/details/128067759

springBoot 专栏收录该内容

17 篇文章

订阅专栏

原理：

通过后台配置多个数据源，自定义注解，通过aop配置注解切面，前端调用需要传递数据源参数，根据判断数据源参数，调用相应的service或mapper方法。

实现：

准备俩个数据库：俩张表

表sql:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

pom:

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- Mybatis核心 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <!-- junit测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- MySQL驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <!--引入quartz定时框架-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-quartz</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

yml:

server:
  port: 8088
spring:
  datasource:
    #数据源1
    db1:
      url: jdbc:mysql://localhost:3306/sg_security?characterEncoding=utf-8&serverTimezone=UTC
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    #数据源2
    db2:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/mytest?characterEncoding=utf-8&serverTimezone=UTC
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource

mybatis:
  mapper-locations: classpath:/mapper/*.xml
  #开启驼峰映射
  configuration:
    map-underscore-to-camel-case: true

user实体：

@Data
public class UserEntity implements Serializable {

    private static final long serialVersionUID = 2735011165270709366L;

    private int id;

    private String name;

    private int age;
}

创建一个枚举存数据源

public enum DataSourceType {
    DB1,
    DB2
}

线程参数配置类

定义一个工具类来设置当前线程的数据源枚举值

package com.example.mybatisinterceptor.ContextHolder;

import com.example.mybatisinterceptor.enums.DataSourceType;

/**
 * 线程参数配置类
 */
public class DataSourceContextHolder {
    // 存放当前线程使用的数据源类型,使用 ThreadLocal确保线程安全
    private static final ThreadLocal<DataSourceType> contextHolder = new ThreadLocal<>();

    // 设置数据源
    public static void setDataSource(DataSourceType type){
        contextHolder.set(type);
    }

    // 获取数据源
    public static DataSourceType getDataSource(){
        return contextHolder.get();
    }

    // 清除数据源
    public static void clearDataSource(){
        contextHolder.remove();
    }
}

数据源动态切换类

这里说说为啥继承 AbstractRoutingDataSource 类

AbstractRoutingDataSource 基于特定的查找key路由到特定的数据源。它内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。（通过我们）

package com.example.mybatisinterceptor.ContextHolder;

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

/**
 * 数据源动态切换类
 */
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

数据源配置类

package com.example.mybatisinterceptor.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.example.mybatisinterceptor.ContextHolder.MyRoutingDataSource;
import com.example.mybatisinterceptor.enums.DataSourceType;
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.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;


import java.util.HashMap;
import java.util.Map;

@Configuration
@MapperScan(basePackages = "com.example.mybatisinterceptor.mapper")
public class DataSourceConfig {
    //数据库db1数据源
    @Bean(name = "dataSource1")
    @Primary
    @ConfigurationProperties("spring.datasource.db1")
    public DruidDataSource dataSource1 () {
        return DruidDataSourceBuilder.create().build();
    }

    //数据库db2数据源
    @Bean(name = "dataSource2")
    @ConfigurationProperties("spring.datasource.db2")
    public DruidDataSource dataSource2 () {
        return DruidDataSourceBuilder.create().build();
    }

    //将两个数据源添加至动态数据源配置类中
    @Bean(name = "myRoutingDataSource")
    public MyRoutingDataSource myRoutingDataSource (@Qualifier("dataSource1") DruidDataSource dataSource1,
                                                    @Qualifier("dataSource2") DruidDataSource dataSource2) {
        Map<Object, Object> map = new HashMap<>();
        map.put(DataSourceType.DB1, dataSource1);
        map.put(DataSourceType.DB2, dataSource2);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setTargetDataSources(map);
        myRoutingDataSource.setDefaultTargetDataSource(dataSource1);
        return myRoutingDataSource;
    }
    //数据源session
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory (@Qualifier("dataSource1") DruidDataSource dataSource1,
                                                @Qualifier("dataSource2") DruidDataSource dataSource2) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(myRoutingDataSource(dataSource1,dataSource2));
        // 设置mapper.xml的位置路径
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml");
        factoryBean.setMapperLocations(resources);
        return factoryBean.getObject();
    }

    //数据源事物配置
    @Bean
    public PlatformTransactionManager transactionManager (@Qualifier("myRoutingDataSource")MyRoutingDataSource myRoutingDataSource){
        return new DataSourceTransactionManager(myRoutingDataSource);
    }
}

自定义数据源注解

package com.example.mybatisinterceptor.MyInterface;

import com.example.mybatisinterceptor.enums.DataSourceType;

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

@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface MyDataSource {
    DataSourceType value() default DataSourceType.DB1;
}

自定义注解aop切面

package com.example.mybatisinterceptor.aspect;

import com.example.mybatisinterceptor.ContextHolder.DataSourceContextHolder;
import com.example.mybatisinterceptor.MyInterface.MyDataSource;
import com.example.mybatisinterceptor.enums.DataSourceType;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DataSourceAspect {
    @Before("@annotation(ds)")
    public void beforeDataSource(MyDataSource ds) {
        DataSourceType value = ds.value();
        DataSourceContextHolder.setDataSource(value);
    }
    @After("@annotation(ds)")
    public void afterDataSource(MyDataSource ds){
        DataSourceContextHolder.clearDataSource();
    }
}

俩个mapper、mapper.xml，作为演示，我这里不写service和实现类了

注意：给mapper方法添加注解，并设置DB数据源

package com.example.mybatisinterceptor.mapper;

import com.example.mybatisinterceptor.MyInterface.MyDataSource;
import com.example.mybatisinterceptor.bean.UserEntity;
import com.example.mybatisinterceptor.enums.DataSourceType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface User1Mapper {

    @MyDataSource(value = DataSourceType.DB1)
    UserEntity queryUserById (@Param("id") int id);

}

package com.example.mybatisinterceptor.mapper;

import com.example.mybatisinterceptor.MyInterface.MyDataSource;
import com.example.mybatisinterceptor.bean.UserEntity;
import com.example.mybatisinterceptor.enums.DataSourceType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface User2Mapper {
    @MyDataSource(value = DataSourceType.DB2)
    UserEntity queryUserById (@Param("id") int id);
}

<?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.mybatisinterceptor.mapper.User1Mapper">
    <select id="queryUserById" parameterType="int" resultType="com.example.mybatisinterceptor.bean.UserEntity">
        select * from user where id = #{id}
    </select>
</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.mybatisinterceptor.mapper.User2Mapper">
    <select id="queryUserById" parameterType="int" resultType="com.example.mybatisinterceptor.bean.UserEntity">
        select * from user where id = #{id}
    </select>
</mapper>

controller层接收db源并判断：

package com.example.mybatisinterceptor.controller;

import com.example.mybatisinterceptor.bean.UserEntity;
import com.example.mybatisinterceptor.mapper.User1Mapper;
import com.example.mybatisinterceptor.mapper.User2Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping("user")
public class UserController {

    @Autowired
    private User1Mapper user1Dao;
    @Autowired
    private User2Mapper user2Dao;

    @RequestMapping("get")
    @ResponseBody
    public UserEntity getUser(int id, int DB){
        if (DB == 1)
            return user1Dao.queryUserById(id);
        else if (DB == 2)
            return user2Dao.queryUserById(id);
        else return null;
    }
}

测试： DB1

DB2

至此结束。

参考：

Springboot+Mybatis+MySql整合多数据源及其使用_橙子呼叫石榴的博客-CSDN博客_mybatis配置mysql实现多数据源

补充：今天浏览了一下别人写的多数据源代码，突然发现我写的问题很多！！！！

使用前端用户指定数据源的方法是不对的。

不管我们使用的是哪种数据源的配置，（mysql主从复制类型、msyql+其他数据库类型）都应该在定义数据源或者使用 aop 切面的时候去指定哪些类、方法使用哪个数据源！如下配置：

1. 在定义数据源的时候指定使用类方法，为每个数据源配置

/**
 * 主数据源配置
 * 有几个配置几个
 * 使用@MapperScan中的basePackages 指定哪些类要使用这个数据源
 */
@Configuration
@MapperScan(basePackages = {"com.fan.mapper.primary"}, sqlSessionFactoryRef = "sqlSessionFactory")
public class PrimaryDataSourceConfig {

    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    @Primary
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

2. 使用 aop 切面统一配置

package com.example.readandwritetest.aops;

import com.example.readandwritetest.mycontextholder.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

/**
 * 多数据源， 切面处理类
 */

@Aspect
@Component
//需要service中方法名称按一定规则编写，然后通过切面来设置数据库类别
public class DataSourceAop {
    
    //@annotation在方法上进行设置,Master为自定义注解，用来强制指定使用该数据源
    @Pointcut("!@annotation(com.example.readandwritetest.util.Master)" +
            "&& (execution(* com.example.readandwritetest.service..*.select*(..))" +
            "|| execution(* com.example.readandwritetest.service..*.get*(..)))")
    public void readPointcut() {

    }
    @Pointcut("@annotation(com.example.readandwritetest.util.Master)" +
            "|| execution(* com.example.readandwritetest.service..*.insert*(..))" +
            "|| execution(* com.example.readandwritetest.service..*.add*(..))" +
            "|| execution(* com.example.readandwritetest.service..*.update*(..))" +
            "|| execution(* com.example.readandwritetest.service..*.delete*(..)))")
    public void writePointcut() {
    }
    @Before("readPointcut()")
    public void read() {
        DBContextHolder.slave();
    }
    @Before("writePointcut()")
    public void write() {
        DBContextHolder.master();
    }
}

这样就一目了然了，抱歉，之前写的确实误人子弟了。（≧ ﹏ ≦）