SpringBoot2.0 多数据源切换

最新推荐文章于 2025-06-15 23:59:05 发布

原创最新推荐文章于 2025-06-15 23:59:05 发布 · 1.1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#多数据源切换 #AOP #SpringBoot2.0

springboot 专栏收录该内容

19 篇文章

订阅专栏

这几天做了一个需求，根据业务规则，对数据进行水平切分（划分到不同的数据库），我也是菜鸟，刚开始没有头绪，查了很多资料，自己一步步撸代码，总算完成了。下面把自己写的demo整理一下。

1、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.cn.dl</groupId>
	<artifactId>springconfigdemo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springconfigdemo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</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-aop</artifactId>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.1</version>
		</dependency>

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

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.46</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.9</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
		</dependency>

		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

2、User

package com.cn.dl.bean;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * Created by yanshao on 2019/2/27.
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor

@Component
@ConfigurationProperties(prefix = "test.user-info")
@EnableConfigurationProperties(value = User.class)
public class User {

    private String name;
    private Integer age;
    private String sex;
    private String address;

}

@ConfigurationProperties:将application.properties中以test.userInfo开头的属性set到User的field中，在userInfo.properties配置了四个属性，SpringBoot默认加载application.properties或者application.yml，所有自定义的properties需要手动引入

package com.cn.dl.config;

import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.annotation.Order;

/**
 * Created by yanshao on 2019/2/28.
 */
@SpringBootConfiguration
@Order(Integer.MIN_VALUE)
@PropertySource(value = {"classpath:/config/userInfo.properties"},encoding = "utf-8")

// TODO: 2019/2/28 @ImportResource 用来引入xml类型的配置文件
//@ImportResource(locations = {"classpath:config/*.xml"})
public class LoadPropertyConfigure {
    //TODO: 可以做一下其它扩充
}

通过@PropertySource引入userInfo.properties配置，发现@PropertySource不能使用正则匹配，很郁闷，渴望道友指点迷津，在Test中测试一下自动封装是否OK？

注意：prefix = "test.user-info"，必须全部小写，大写字母使用中划线"-"，比如"test.userInfo"-> "test.user-info"

Caused by: org.springframework.boot.context.properties.source.InvalidConfigurationPropertyNameException: Configuration property name 'test.user-Info' is not valid
	at org.springframework.boot.context.properties.source.ConfigurationPropertyName.of(ConfigurationPropertyName.java:444)
	at org.springframework.boot.context.properties.source.ConfigurationPropertyName.of(ConfigurationPropertyName.java:411)
	at org.springframework.boot.context.properties.bind.Binder.bind(Binder.java:190)
	at org.springframework.boot.context.properties.ConfigurationPropertiesBinder.bind(ConfigurationPropertiesBinder.java:83)
	at org.springframework.boot.context.properties.ConfigurationPropertiesBindingPostProcessor.bind(ConfigurationPropertiesBindingPostProcessor.java:107)
... 42 more

package com.cn.dl;

import com.cn.dl.bean.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * Created by yanshao on 2019/3/1.
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserInfoTest {
    @Autowired
    User user;

    @Test
    public void test(){
        System.out.println(user.toString());
    }
}

自动封装没有问题。数据源的配置都在dataSource.properties中

3、dataSource.properties

#masterDatasource
test.masterDatasource.jdbc-url=jdbc:mysql://127.0.0.1:3306/tiger_base?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&generateSimpleParameterMetadata=true
#test.masterDatasource.url=jdbc:mysql://127.0.0.1:3306/tiger_base?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&generateSimpleParameterMetadata=true
test.masterDatasource.username=root
test.masterDatasource.password=root
test.masterDatasource.driver-class-name=com.mysql.jdbc.Driver
test.masterDatasource.type=com.alibaba.druid.pool.DruidDataSource
test.masterDatasource.max-idle=10
test.masterDatasource.max-wait=60000
test.masterDatasource.min-idle=5
test.masterDatasource.initial-size=5
test.masterDatasource.validationQuery=select 'x'

#slaveDatasource
test.slaveDatasource.jdbc-url=jdbc:mysql://127.0.0.1:3306/tiger_data1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&generateSimpleParameterMetadata=true
#test.slaveDatasource.url=jdbc:mysql://127.0.0.1:3306/tiger_base?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&generateSimpleParameterMetadata=true
test.slaveDatasource.username=root
test.slaveDatasource.password=root
test.slaveDatasource.driver-class-name=com.mysql.jdbc.Driver
test.slaveDatasource.type=com.alibaba.druid.pool.DruidDataSource
test.slaveDatasource.max-idle=10
test.slaveDatasource.max-wait=60000
test.slaveDatasource.min-idle=5
test.slaveDatasource.initial-size=5
test.slaveDatasource.validationQuery=select 'x'

注意：自动封装时配置数据源url的key是test.masterDatasource.jdbc-url=XXXXXX，而不是test.masterDatasource.url=XXXXXX

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.
### The error may exist in com/cn/dl/mapper/UserInfoMapper.java (best guess)
### The error may involve com.cn.dl.mapper.UserInfoMapper.findByName
### The error occurred while executing a query
### Cause: java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.

4、LoadPropertyConfigureSource：加载dataSource.properties配置文件

package com.cn.dl.config;

import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.core.annotation.Order;

import java.io.InputStream;
import java.util.Properties;

/**
 * 加载src/main/resources下dataSource.properties配置文件
 * PropertySourcesPlaceholderConfigurer: 加载配置文件然后在自动装配中可以取到对应key
 * PropertyPlaceholderConfigurer：加载配置文件，@Value(${key})能够取到对应的key，但是在自动封装时无法取到对应key
 * Created by yanshao on 2019/2/27.
 */
@SpringBootConfiguration
@Order(value = Integer.MIN_VALUE)
public class LoadPropertyConfigureSource extends PropertySourcesPlaceholderConfigurer {

    public LoadPropertyConfigureSource(){
        try {
           InputStream in = LoadPropertyConfigureSource.class.getResourceAsStream("/config/dataSource.properties");
           Properties properties = new Properties();
           properties.load(in);
           super.setProperties(properties);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

}

PropertySourcesPlaceholderConfigurer和@PropertySource作用一样，只不过PropertySourcesPlaceholderConfigurer更加灵活，可以从redis、apollo中获取配置并加载到上下文参数变量（Environment）中

注意： PropertyPlaceholderConfigurer加载配置文件，@Value(${key})能够取到对应的key，但是在自动封装时无法取到对应key

5、DataSourceConfigure：配置多数据源

package com.cn.dl.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
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.transaction.annotation.EnableTransactionManagement;
import org.springframework.util.CollectionUtils;

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

/**
 * Created by yanshao on 2019/2/27.
 */
@Configuration
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties
@EnableTransactionManagement
public class DataSourceConfigure {


    @Bean(value = "masterDataSource")
    @Primary //当自动装配时出现多个bean时，被注解为@Primary的bean将作为首选者
    @ConfigurationProperties(prefix = "test.master-datasource")
    public DataSource getMasterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(value = "slaveDataSource")
    @ConfigurationProperties(prefix = "test.slave-datasource")
    public DataSource getSlaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSources")
    public DataSource dynamicDataSource(Map<String,DataSource> dataSources) {
        System.out.println("dynamicDataSource>>>"+ dataSources.size());
        if (CollectionUtils.isEmpty(dataSources)) {
            throw new IllegalArgumentException(" The dataSources can not be null!!!");
        }
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setTargetDataSources(new HashMap<>(dataSources));
        dynamicDataSource.setDefaultTargetDataSource(getMasterDataSource());
        return dynamicDataSource;
    }

    //配置sqlSessionFactory
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSources")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
}

注意：@Primary不能放在（public DataSource dynamicDataSource(Map<String,DataSource> dataSources)）方法上，会出现循环依赖问题


Caused by: org.springframework.beans.factory.BeanCurrentlyInCreationException: Error creating bean with name 'dataSources': Requested bean is currently in creation: Is there an unresolvable circular reference?
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.beforeSingletonCreation(DefaultSingletonBeanRegistry.java:339)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:215)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
	at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:277)

6、DataSourceType:数据源类型

package com.cn.dl.config;

/**
 * Created by yanshao on 2019/2/28.
 */
public enum  DataSourceType {

    MASTER_DATASOURCE("masterDataSource","master 主库"),
    SLAVE_DATASOURCE("slaveDataSource","slave 丛库"),

    ;

    private String dataSourceName;
    private String description;

    DataSourceType(String dataSourceName,String description) {
        this.dataSourceName = dataSourceName;
    }

    public String getDataSourceName() {
        return dataSourceName;
    }

    public String getDescription() {
        return description;
    }

}

7、DynamicDataSource：数据源路由

package com.cn.dl.config;

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

/**
 * Created by yanshao on 2019/2/28.
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> contextHolder =
            ThreadLocal.withInitial(() -> DataSourceType.MASTER_DATASOURCE.getDataSourceName());

    // TODO: 2019/2/28 这个方法没有被调用？原因：需要配置SqlSessionFactory
    @Override
    protected Object determineCurrentLookupKey() {
        String dataSourceName = getDataSource();
        System.out.println("DynamicDataSource>>>>>切换数据源到>>>" + dataSourceName);
        return dataSourceName;
    }

    /**
     * 设置数据源
     * @param dataSourceName
     */
    public static void setDataSource(String dataSourceName) {
        contextHolder.set(dataSourceName);
    }

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

    /**
     * 在方法调用结束之后清除数据源
     */
    public static void removeDataSource() {
        contextHolder.remove();
    }

}

这里扩展了AbstractRoutingDataSource抽象类，重写determineCurrentLookupKey()方法，根据不同业务来指定不同数据源的key

DataSourceConfigure中配置了两个数据源，可以分别是masterDataSource、slaveDataSource，在调用DAO层之前，需要调用determineCurrentLookupKey(），来设置不同的数据源。

private static final ThreadLocal<String> contextHolder =
            ThreadLocal.withInitial(() -> DataSourceType.MASTER_DATASOURCE.getDataSourceName());

通过ThreadLocal（即本地线程变量，就是一个Map用于存储每一个线程的变量副本，Map中元素的Key为线程对象，而Value对应线程的变量副本),ThreadLocal会为每个使用该变量的线程提供独立的变量副本，所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本，对于多线程资源共享的问题，同步机制(Synchronized)采用了“以时间换空间”的方式，而ThreadLocal采用了“以空间换时间”的方式。前者仅提供一份变量，让不同的线程排队访问，而后者为每一个线程都提供了一份变量，因此可以同时访问而互不影响），在并发环境下，各个线程之间互不干扰。这里给contextHolder设置了默认值masterDataSource，如果contextHolder.get()==null，则getDataSource() = "masterDataSource"。

查看资料：线程本地变量ThreadLocal

注意：可能会遇到determineCurrentLookupKey()方法未调用，也就是数据源不会切换，我自己的原因：未配置SqlSessionFactory，未在SqlSessionFactory中指定数据源。

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

/**
 * 设置数据源
 * @param dataSourceName
 */
 public static void setDataSource(String dataSourceName) {
     contextHolder.set(dataSourceName);
 }

这里的setDataSource(String dataSourceName)是在调用DAO层之前，通过DynamicDataSource.setDataSource("数据源的key")来手动设置。

 /**
  * 在方法调用结束之后清除数据源
  */
  public static void removeDataSource() {
      contextHolder.remove();
  }

在调用DAO层结束之后，DynamicDataSource.removeDataSource()删除设置的数据源。

8、RequireDynamicDataSource：需要切换数据源，就在对应的Service方法上加上这个注解并指定数据源的key

package com.cn.dl.annotation;

import java.lang.annotation.*;

/**
 * Created by yanshao on 2019/2/28.
 */
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface RequireDynamicDataSource {
    //数据源的key
    String dataSourceName() default "";
}

9、DataSourceAspect

package com.cn.dl.aspect;

import com.cn.dl.annotation.RequireDynamicDataSource;
import com.cn.dl.config.DynamicDataSource;
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.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

/**
 * Created by yanshao on 2019/2/28.
 */
@Aspect
@Order(-10)
@Component
public class DataSourceAspect {

    @Before("@annotation(requireDynamicDataSource)")
    public void before(JoinPoint joinPoint, RequireDynamicDataSource requireDynamicDataSource){
        //指定数据源
        if(! StringUtils.isEmpty(requireDynamicDataSource.dataSourceName())){
            System.out.println("before>>>>" + requireDynamicDataSource.dataSourceName());
            DynamicDataSource.setDataSource(requireDynamicDataSource.dataSourceName());
        }
    }
    @After("@annotation(requireDynamicDataSource)")
    public void after(JoinPoint joinPoint ,RequireDynamicDataSource requireDynamicDataSource){
        System.out.println("after>>>" + requireDynamicDataSource.dataSourceName());
        if(! StringUtils.isEmpty(requireDynamicDataSource.dataSourceName())){
            DynamicDataSource.removeDataSource();
        }
    }

}

调用DAO层之前，在before()中设置数据源，调用DAO层之后，在after()中清除对应数据源的key。

多数据源配置，AOP来完成切换已经OK，接下来测试测试一波，以下都是测试代码，不重要！！！

package com.cn.dl.service;

/**
 * Created by yanshao on 2019/2/21.
 */
public interface UserService {
    void say(String words);
}

package com.cn.dl.service;

import com.cn.dl.annotation.RequireDynamicDataSource;
import com.cn.dl.bean.User;
import com.cn.dl.mapper.UserInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created by yanshao 2019/3/1.
 */
@Service
public class UserInfoServieImpl1 implements UserInfoService {
    @Autowired
    UserInfoMapper userInfoMapper;

    @Override
    public User findByName(String name) {
        return userInfoMapper.findByName(name);
    }

}

package com.cn.dl.service;

import com.cn.dl.annotation.RequireDynamicDataSource;
import com.cn.dl.bean.User;
import com.cn.dl.mapper.UserInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * Created by yanshao on 2019/3/1.
 */
@Service
public class UserInfoServiceImpl implements UserInfoService {

    @Autowired
    UserInfoMapper userInfoMapper;

    @Override
    @RequireDynamicDataSource(dataSourceName = "slaveDataSource")
    public User findByName(String name) {
        return userInfoMapper.findByName(name);
    }
}

package com.cn.dl.mapper;

import com.cn.dl.bean.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * Created by yanshao 2019/2/27.
 */
@Mapper
public interface UserInfoMapper {
    @Select("select * from user_info where name = #{name}")
    User findByName(@Param("name") String name);
}

package com.cn.dl.controller;

import com.cn.dl.annotation.RequireDynamicDataSource;
import com.cn.dl.annotation.RequireParam;
import com.cn.dl.bean.User;
import com.cn.dl.mapper.UserInfoMapper;
import com.cn.dl.service.UserInfoService;
import com.cn.dl.service.UserInfoServiceImpl;
import com.cn.dl.service.UserInfoServieImpl1;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


/**
 * Created by yanshao on 2019/2/21.
 */
@RestController
@RequestMapping({"/api/user"})
public class UserController {


    @Autowired
    private UserInfoServiceImpl userInfoServieImpl;
    @Autowired
    private UserInfoServieImpl1 userInfoServieImpl1;

    @PostMapping("/register")
    public Object register(String name,Integer age){
        System.out.println("register >>> " + name + "," + age);
        return name + age;
    }
    /**
     * 默认从主库中获取
     * @param name
     * @return
     * */
    @PostMapping("/get_user_info")
    public Object getUserInfo(@RequireParam("name") String name){
        User user = userInfoServieImpl1.findByName(name);
        if(user == null){
            return "This person does not exist !";
        }
        return user.toString();
    }

    /**
     * 从丛库中获取信息
     * @param name
     * @return
     * {@link com.cn.dl.config.DataSourceType}
     * */
    @PostMapping("/get_salve_user_info")
    public Object getSalveUserInfo(@RequireParam("name") String name){
        User user = userInfoServieImpl.findByName(name);
        if(user == null){
            return "This person does not exist !";
        }
        return user.toString();
    }

}

数据库：tiger_base, 表：user_info

数据库：tiger_data1, 表：user_info

a、访问127.0.0.1:8080/api/user/get_user_info

b、访问127.0.0.1:8080/api/user/get_salve_user_info

before>>>>slaveDataSource
DynamicDataSource>>>>>切换数据源到>>>slaveDataSource
2019-03-01 15:19:22.851  INFO 6152 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-03-01 15:19:23.067  INFO 6152 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
after>>>slaveDataSource
DynamicDataSource>>>>>切换数据源到>>>masterDataSource
2019-03-01 15:19:26.099  INFO 6152 --- [nio-8080-exec-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2019-03-01 15:19:26.154  INFO 6152 --- [nio-8080-exec-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.

这里只是我在实际开发前写的demo，有些代码逻辑不够严谨，切勿直接copy到生产线，在开发过程中遇到的异常到贴出来了，希望大家加我的QQ，关注微信公众号来更进一步的探讨，谢谢！