springboot如何配置多数据源及单库事务控制？

共 6383字，需浏览 13分钟

· 2020-11-03

1.背景介绍

通过springboot操作mysql数据库，但是在实际业务场景中，数据量迅速增长，一个库一个表已经满足不了我们的需求的时候，我们就会考虑分库分表的操作，在springboot中如何实现多数据源，动态数据源切换，读写分离等操作。

2.所需依赖

#父级项目依赖
<parent>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-parentartifactId>
    <version>2.1.5.RELEASEversion>
  parent>
  #其他依赖
  <dependencies>
    
    
    <dependency>
      <groupId>org.springframework.bootgroupId>
      <artifactId>spring-boot-starter-webartifactId>
    dependency>
    <dependency>
      <groupId>org.springframework.bootgroupId>
      <artifactId>spring-boot-starter-testartifactId>
    dependency>
    <dependency>
      <groupId>org.springframework.bootgroupId>
      <artifactId>spring-boot-starter-aopartifactId>
    dependency>
    <dependency>
      <groupId>com.alibabagroupId>
      <artifactId>druidartifactId>
      <version>1.0.2version>
    dependency>
    <dependency>
      <groupId>mysqlgroupId>
      <artifactId>mysql-connector-javaartifactId>
      <scope>runtimescope>
    dependency>
    
    <dependency>
      <groupId>org.mybatis.spring.bootgroupId>
      <artifactId>mybatis-spring-boot-starterartifactId>
      <version>1.3.1version>
    dependency>
    
    <dependency>
      <groupId>org.aspectjgroupId>
      <artifactId>aspectjweaverartifactId>
    dependency>
    <dependency>
      <groupId>org.projectlombokgroupId>
      <artifactId>lombokartifactId>
    dependency>
  dependencies>

yml文件中配置

#多数据源 1主2从
datasource:
  #从库数量
  readSize: 2
  # 使用druid数据源
  type: com.alibaba.druid.pool.DruidDataSource
  #主库
  write:
    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQueryTimeout: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
  read1:
    url: jdbc:mysql://localhost:3306/wb?characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQueryTimeout: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
  read2:
    url: jdbc:mysql://localhost:3306/sys?characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQueryTimeout: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

1.自定义注解标签TargetDataSource,主要用于在接口处通过注解来切换数据源

package com.lenovo.annotation;
import com.lenovo.meiju.DataSourceType;
import java.lang.annotation.*;
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
  //默认使用写库
    DataSourceType value() default DataSourceType.write;
}

2.定义切面DataSourceAop

通过获取接口处的注解,获取注解所需要切换的数据源名称,从而来切换该数据源

package com.lenovo.interceptor;
import com.lenovo.annotation.TargetDataSource;
import com.lenovo.config.DataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import org.aspectj.lang.annotation.Before;
import java.lang.reflect.Method;
@Aspect
@Component
@Slf4j
public class DataSourceAop {
    @Before("@annotation(targetDataSource)")
    public void setWriteDataSourceType(JoinPoint point, TargetDataSource targetDataSource) {
        //获得当前访问的class
        Class className = point.getTarget().getClass();
        //获得访问的方法名
        String methodName = point.getSignature().getName();
        //得到方法的参数的类型
        Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
        //获取默认的数据源名称
        String dataSource = DataSourceContextHolder.DEFAULT_DS;
        try {
            // 得到访问的方法对象
            Method method = className.getMethod(methodName, argClass);
            // 判断是否存在@注解
            if (method.isAnnotationPresent(TargetDataSource.class)) {
                TargetDataSource annotation = method.getAnnotation(TargetDataSource.class);
                // 取出注解中的数据源名
                dataSource = annotation.value().getType();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 切换数据源
        DataSourceContextHolder.setJdbcType(dataSource);
    }
    @After("@annotation(targetDataSource)")
    public void afterSwitchDS(JoinPoint point,TargetDataSource targetDataSource){
        DataSourceContextHolder.clearDB();
    }
}

3.设置本地线程全局变量

package com.lenovo.config;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class DataSourceContextHolder {
    private static final ThreadLocal local = new ThreadLocal();
    public static final String DEFAULT_DS = "write";//默认数据源
    public static ThreadLocal getLocal() {
        return local;
    } 
    public static String getJdbcType() {
        return local.get();
    }
    public static void setJdbcType(String dbType) {
        log.info("dataSource切换到："+dbType);
        local.set(dbType);
    }
    // 清除数据源名
    public static void clearDB() {
        local.remove();
    }
}

4.数据库配置：解析application-pro.yml文件

通过@ConfigurationProperties注解获取配置文件属性自动配置,绑定其属性

通过@Bean注解,申请实例对象

package com.lenovo.config;
import com.lenovo.meiju.DataSourceType;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
@Configuration
@ConditionalOnClass({EnableTransactionManagement.class})
@Import({ DataBaseConfiguration.class})
public class MybatisConfiguration {
    @Value("${datasource.type}")
    private Class dataSourceType;
    @Value("${datasource.readSize}")
    private String dataSourceSize;
    @Resource(name = "writeDataSource")
    private DataSource dataSource;
    @Resource(name = "readDataSource1")
    private DataSource read1DataSources;
    @Resource(name = "readDataSource2")
    private DataSource read2DataSources;
    List readDataSources;
    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
        return sqlSessionFactoryBean.getObject();
    }
    /**
     * 设置默认数据库,其他数据源
     * @return
     */
    @Bean(name = "dynamicDataSource")
    public AbstractRoutingDataSource roundRobinDataSouceProxy() {
        int size = Integer.parseInt(dataSourceSize);
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
        Map targetDataSources = new HashMap();
        // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
        // 写
        targetDataSources.put(DataSourceType.write.getType(),dataSource);
        // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
        //多个读数据库时
        readDataSources=new ArrayList();
        readDataSources.add(read1DataSources);
        readDataSources.add(read2DataSources);
        for (int i = 0; i < size; i++) {
            targetDataSources.put(i, readDataSources.get(i));
        }
        proxy.setDefaultTargetDataSource(dataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }
}

5.通过枚举法区分读写库标识

package com.lenovo.meiju;
public enum  DataSourceType {
    read("read", "从库"),
    write("write", "主库");
    private String type;
    private String name;
    public String getType() {
        return type;
    }
    public void setType(String type) {
        this.type = type;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    DataSourceType(String type, String name) {
        this.type = type;
        this.name = name;
    }
}

6.通过继承AbstractRoutingDataSource实现其动态选择数据源

package com.lenovo.config;
import com.lenovo.meiju.DataSourceType;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.concurrent.atomic.AtomicInteger;
public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    //配置的读库数量
    private final int dataSourceNumber;
    private AtomicInteger count = new AtomicInteger(0);
    /**
    * @dataSourceNumber  从库的数量
    **/
    public MyAbstractRoutingDataSource(int dataSourceNumber) {
        this.dataSourceNumber = dataSourceNumber;
    }
    @Override
    protected Object determineCurrentLookupKey() {
        //获取通过aop设置的数据源名称
        String typeKey = DataSourceContextHolder.getJdbcType();
        if (typeKey==null){//未加注解 默认使用主库
            typeKey=DataSourceType.write.getType();
        }
        //如果是当前数据源是默认主库,直接返回主库
        if (typeKey.equals(DataSourceType.write.getType())){
            return DataSourceType.write.getType();
        }
        //从库 读 简单负载均衡(轮询)
        int number = count.getAndAdd(1);
        int lookupKey = number % dataSourceNumber;
        return new Integer(lookupKey);
    //如果不进行负载,直接指定数据源的话,则可以这边修改
    }
}

7.配置mybatis

package com.lenovo.config;
import com.lenovo.meiju.DataSourceType;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
@Configuration
@ConditionalOnClass({EnableTransactionManagement.class})
@Import({ DataBaseConfiguration.class})
public class MybatisConfiguration {
    @Value("${datasource.type}")
    private Class dataSourceType;
    @Value("${datasource.readSize}")
    private String dataSourceSize;
    @Resource(name = "writeDataSource")
    private DataSource dataSource;
    @Resource(name = "readDataSource1")
    private DataSource read1DataSources;
    @Resource(name = "readDataSource2")
    private DataSource read2DataSources;
    List readDataSources;
    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
        return sqlSessionFactoryBean.getObject();
    }
    /**
     * 设置默认数据库,其他数据源
     * @return
     */
    @Bean(name = "dynamicDataSource")
    public AbstractRoutingDataSource roundRobinDataSouceProxy() {
        int size = Integer.parseInt(dataSourceSize);
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
        Map targetDataSources = new HashMap();
        // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
        // 写
        targetDataSources.put(DataSourceType.write.getType(),dataSource);
        // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
        //多个读数据库时
        readDataSources=new ArrayList();
        readDataSources.add(read1DataSources);
        readDataSources.add(read2DataSources);
        for (int i = 0; i < size; i++) {
            targetDataSources.put(i, readDataSources.get(i));
        }
        proxy.setDefaultTargetDataSource(dataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }
    //配置Transaction，统一管理否则事务失效
    @Bean(name="transactionManager")
    public PlatformTransactionManager transactionManager(){
        return new DataSourceTransactionManager(roundRobinDataSouceProxy());
    }
}

8.数据源声明

package com.lenovo.config;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
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 javax.sql.DataSource;
@Slf4j
@Configuration
public class DataBaseConfiguration {
    @Value("${datasource.type}")
    private Classextends DataSource> dataSourceType;
    @Bean(name = "writeDataSource")
    @Primary
    @ConfigurationProperties(prefix = "datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
    /**
     * 有多少个从库就要配置多少个
     * @return
     */
    @Bean(name = "readDataSource1")
    @ConfigurationProperties(prefix = "datasource.read1")
    public DataSource readDataSourceOne() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
    @Bean(name = "readDataSource2")
    @ConfigurationProperties(prefix = "datasource.read2")
    public DataSource readDataSourceTwo() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
}