Spring多数据源动态切换

原理

接口 DataSource 向外提供一个 getConnection() 方法，用来获取数据库连接，AbstractRoutingDataSource 实现了 getConnection() 方法

// line 166
@Override
public Connection getConnection() throws SQLException {
	return determineTargetDataSource().getConnection();
}

... 省略若干代码 
       
   // line 190
   /**
 * Retrieve the current target DataSource. Determines the
 * {@link #determineCurrentLookupKey() current lookup key}, performs
 * a lookup in the {@link #setTargetDataSources targetDataSources} map,
 * falls back to the specified
 * {@link #setDefaultTargetDataSource default target DataSource} if necessary.
 * @see #determineCurrentLookupKey()
 */
protected DataSource determineTargetDataSource() {
	Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
	Object lookupKey = determineCurrentLookupKey();
	DataSource dataSource = this.resolvedDataSources.get(lookupKey);
	if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
		dataSource = this.resolvedDefaultDataSource;
	}
	if (dataSource == null) {
		throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
	}
	return dataSource;
}

/**
 * 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.
 */
@Nullable
protected abstract Object determineCurrentLookupKey();

然而 ….

AbstractRoutingDataSource 的getConnection() 方法只是调用了 determinTargetDataSource().getConnection() 来获取真正DataSource的getConnection()。

这是典型的装饰模式！！自己没有的功能通过引入其他类来增强。

我们先来看看 AbstractRoutingDataSource 的类结构

被框框套住的都是重要的。

方法determineCurrentLookupKey() 是留给我们开发者的（就像你家的网线口），我们通过实现该方法在不同数据源之间切换。

实践

1. 配置多数据源

在 application.yml 如下配置

spring:
  datasource:
    # 数据源类型
    type: com.alibaba.druid.pool.DruidDataSource
    # 默认数据源
    default-datasource:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&allowMultiQueries=true&serverTimezone=GMT%2B8
      username: root
      password: 123456

    # 多数据源
    target-datasources:
      datasource1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: 123456

      datasource2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&allowMultiQueries=true&serverTimezone=GMT%2B8
        username: root
        password: 123456

    # druid 默认配置
    druid:
      # 初始连接数
      initial-size: 10
      # 最大连接池数量
      max-active: 100
      # 最小连接池数量
      min-idle: 10
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1 FROM DUAL
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      stat-view-servlet:
        enabled: true
        url-pattern: /monitor/druid/*
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false
        wall:
          config:
            multi-statement-allow: true

# MyBatis
mybatis:
  # 搜索指定包别名
  typeAliasesPackage: com.liuchuanv
  # 配置mapper的扫描，找到所有的mapper.xml映射文件
  mapperLocations: classpath*:mapper/**/*Mapper.xml
  # 加载全局的配置文件
  configLocation: classpath:mybatis-config.xml

此处配置的名称（如 defaultDataSource、targetDataSources）的命名并无特殊要求，只要和下面第n步的 DataSourceConfig 中对应起来就可以

使用 Druid 数据源的话，要在 pom.xml 中引入依赖

<!--阿里数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

2. 实现动态数据源

DynamicDataSource 动态数据源，在多个数据源之间切换

public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceType();
    }
}

DataSourceContextHolder 数据源上下文，使用线程变量来存储代表当前使用的数据源的key值（每个key值都对应一个数据源，用以区分多数据源）

public class DataSourceContextHolder {

    public static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>();

    public static void setDataSourceType(String dsType) {
        CONTEXT_HOLDER.set(dsType);
    }

    public static String getDataSourceType() {
        return CONTEXT_HOLDER.get();
    }

    public static void removeDataSourceType() {
        CONTEXT_HOLDER.remove();
    }

}

DataSourceType 数据源对应的key（其实单纯的用字符串来表示数据源，替换枚举类DataSourceType也是可以的，但是写代码时要注意字符串统一）

public enum  DataSourceType {
    /** 默认数据源key */
    DEFAULT_DATASOURCE,

    /** 数据源1key*/
    DATASOURCE1,

    /** 数据源2key*/
    DATASOURCE2;
}

3. 将数据源添加到 Spring 容器中

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.default-datasource")
    public DataSource defaultDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.target-datasources.datasource1")
    public DataSource dataSource1() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.target-datasources.datasource2")
    public DataSource dataSource2() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource dynamicDataSource(DataSource defaultDataSource, DataSource dataSource1, DataSource dataSource2) {
        // 注意：该方法的参数名称要和前面前面三个datasource对象在Spring容器中的bean名称一样
        // 或者使用 @Qualifier 指定具体的bean
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.DEFAULT_DATASOURCE.name(), defaultDataSource);
        targetDataSources.put(DataSourceType.DATASOURCE1.name(), dataSource1);
        targetDataSources.put(DataSourceType.DATASOURCE2.name(), dataSource2);
        return new DynamicDataSource(defaultDataSource, targetDataSources);
    }
}

测试

为了方便，省略了 Service 层

TestController

@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private TestMapper testMapper;

    @GetMapping
    public List<Map<String, Object>> test(String dataSourceIndex) {
        // 根据参数值的不同，切换数据源
        if ("1".equals(dataSourceIndex)) {
            DataSourceContextHolder.setDataSourceType(DataSourceType.DATASOURCE1.name());
        } else if ("2".equals(dataSourceIndex)) {
            DataSourceContextHolder.setDataSourceType(DataSourceType.DATASOURCE2.name());
        }
        List<Map<String, Object>> mapList = testMapper.selectList();
        // 清除线程内部变量数据源key
        DataSourceContextHolder.removeDataSourceType();
        return mapList;
    }
}

TestMapper

@Repository
public interface TestMapper {
    /**
     * 查询列表
     * @return
     */
    List<Map<String, Object>> selectList();
}

TestMapper.xml

<?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.liuchuanv.dynamicdatasource.mapper.TestMapper">
	<select id="selectList" resultType="java.util.Map">
		SELECT * FROM test
	</select>
</mapper>

别忘了要准备数据哦！

下面SQL语句，创建3个数据库，然后在3个数据库中都创建一张test表，并各自插入不同的数据。


-- 创建数据库
create database db0 character set utf8 collate utf8_general_ci;
create database db1 character set utf8 collate utf8_general_ci;
create database db2 character set utf8 collate utf8_general_ci;

-- 在数据库db1下执行以下SQL
use db0;
create table test(
	id int(11) primary key auto_increment,
	name varchar(20)
) ;
insert into test(name) values('张三');


-- 在数据库db1下执行以下SQL
use db1;
create table test(
	id int(11) primary key auto_increment,
	name varchar(20)
) ;
insert into test(name) values('李四');

-- 在数据库db2下执行以下SQL
use db2;
create table test(
	id int(11) primary key auto_increment,
	name varchar(20)
) ;
insert into test(name) values('王五');

OK，一切准备就绪，启动应用吧！！！

一启动就出现了各种各样的，似乎无穷无尽的报错！一头黑线。

1. 找不到TestMapper

1	Field testMapper in com.liuchuanv.dynamicdatasource.controller.TestController required a bean of type 'com.liuchuanv.dynamicdatasource.mapper.TestMapper' that could not be found.

解决方法：在 DynamicdatasourceApplication 头上添加注解 @MapperScan("com.liuchuanv.*.mapper")

2. dynamicDataSource 依赖循环

┌─────┐
|  dynamicDataSource defined in class path resource [com/liuchuanv/dynamicdatasource/common/DataSourceConfig.class]
↑     ↓
|  defaultDataSource defined in class path resource [com/liuchuanv/dynamicdatasource/common/DataSourceConfig.class]
↑     ↓
|  org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker
└─────┘

解决方法：在 DynamicdatasourceApplication 头上修改注解 @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

终于处理好所有的问题，终于能痛痛快快的访问 http://localhost:8080/test

使用的是默认数据源 defaultDataSource

使用的是数据源 dataSource1

使用的是数据源 dataSource2

建议大家在心里总结一下整个的过程，其实很简单

1	// line 166
2	@Override
3	public Connection getConnection() throws SQLException {
4	return determineTargetDataSource().getConnection();
5	}
6
7	... 省略若干代码
8
9	// line 190
10	/**
11	* Retrieve the current target DataSource. Determines the
12	* {@link #determineCurrentLookupKey() current lookup key}, performs
13	* a lookup in the {@link #setTargetDataSources targetDataSources} map,
14	* falls back to the specified
15	* {@link #setDefaultTargetDataSource default target DataSource} if necessary.
16	* @see #determineCurrentLookupKey()
17	*/
18	protected DataSource determineTargetDataSource() {
19	Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
20	Object lookupKey = determineCurrentLookupKey();
21	DataSource dataSource = this.resolvedDataSources.get(lookupKey);
22	if (dataSource == null && (this.lenientFallback \|\| lookupKey == null)) {
23	dataSource = this.resolvedDefaultDataSource;
24	}
25	if (dataSource == null) {
26	throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
27	}
28	return dataSource;
29	}
30
31	/**
32	* Determine the current lookup key. This will typically be
33	* implemented to check a thread-bound transaction context.
34	* <p>Allows for arbitrary keys. The returned key needs
35	* to match the stored lookup key type, as resolved by the
36	* {@link #resolveSpecifiedLookupKey} method.
37	*/
38	@Nullable
39	protected abstract Object determineCurrentLookupKey();

1	spring:
2	datasource:
3	# 数据源类型
4	type: com.alibaba.druid.pool.DruidDataSource
5	# 默认数据源
6	default-datasource:
7	driver-class-name: com.mysql.cj.jdbc.Driver
8	url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&allowMultiQueries=true&serverTimezone=GMT%2B8
9	username: root
10	password: 123456
11
12	# 多数据源
13	target-datasources:
14	datasource1:
15	driver-class-name: com.mysql.cj.jdbc.Driver
16	url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&allowMultiQueries=true&serverTimezone=GMT%2B8
17	username: root
18	password: 123456
19
20	datasource2:
21	driver-class-name: com.mysql.cj.jdbc.Driver
22	url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&allowMultiQueries=true&serverTimezone=GMT%2B8
23	username: root
24	password: 123456
25
26	# druid 默认配置
27	druid:
28	# 初始连接数
29	initial-size: 10
30	# 最大连接池数量
31	max-active: 100
32	# 最小连接池数量
33	min-idle: 10
34	# 配置获取连接等待超时的时间
35	max-wait: 60000
36	# 打开PSCache，并且指定每个连接上PSCache的大小
37	pool-prepared-statements: true
38	max-pool-prepared-statement-per-connection-size: 20
39	# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
40	timeBetweenEvictionRunsMillis: 60000
41	# 配置一个连接在池中最小生存的时间，单位是毫秒
42	min-evictable-idle-time-millis: 300000
43	validation-query: SELECT 1 FROM DUAL
44	test-while-idle: true
45	test-on-borrow: false
46	test-on-return: false
47	stat-view-servlet:
48	enabled: true
49	url-pattern: /monitor/druid/*
50	filter:
51	stat:
52	log-slow-sql: true
53	slow-sql-millis: 1000
54	merge-sql: false
55	wall:
56	config:
57	multi-statement-allow: true
58
59	# MyBatis
60	mybatis:
61	# 搜索指定包别名
62	typeAliasesPackage: com.liuchuanv
63	# 配置mapper的扫描，找到所有的mapper.xml映射文件
64	mapperLocations: classpath:mapper//Mapper.xml
65	# 加载全局的配置文件
66	configLocation: classpath:mybatis-config.xml

1	<!--阿里数据库连接池 -->
2	<dependency>
3	<groupId>com.alibaba</groupId>
4	<artifactId>druid-spring-boot-starter</artifactId>
5	<version>1.1.10</version>
6	</dependency>

1	public class DynamicDataSource extends AbstractRoutingDataSource {
2
3	public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
4	super.setDefaultTargetDataSource(defaultTargetDataSource);
5	super.setTargetDataSources(targetDataSources);
6	super.afterPropertiesSet();
7	}
8
9	@Override
10	protected Object determineCurrentLookupKey() {
11	return DataSourceContextHolder.getDataSourceType();
12	}
13	}

1	public class DataSourceContextHolder {
2
3	public static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>();
4
5	public static void setDataSourceType(String dsType) {
6	CONTEXT_HOLDER.set(dsType);
7	}
8
9	public static String getDataSourceType() {
10	return CONTEXT_HOLDER.get();
11	}
12
13	public static void removeDataSourceType() {
14	CONTEXT_HOLDER.remove();
15	}
16
17	}

1	public enum DataSourceType {
2	/** 默认数据源key */
3	DEFAULT_DATASOURCE,
4
5	/** 数据源1key*/
6	DATASOURCE1,
7
8	/** 数据源2key*/
9	DATASOURCE2;
10	}

1	@Configuration
2	public class DataSourceConfig {
3
4	@Bean
5	@ConfigurationProperties(prefix = "spring.datasource.default-datasource")
6	public DataSource defaultDataSource() {
7	return DruidDataSourceBuilder.create().build();
8	}
9
10	@Bean
11	@ConfigurationProperties(prefix = "spring.datasource.target-datasources.datasource1")
12	public DataSource dataSource1() {
13	return DruidDataSourceBuilder.create().build();
14	}
15
16	@Bean
17	@ConfigurationProperties(prefix = "spring.datasource.target-datasources.datasource2")
18	public DataSource dataSource2() {
19	return DruidDataSourceBuilder.create().build();
20	}
21
22	@Bean
23	@Primary
24	public DataSource dynamicDataSource(DataSource defaultDataSource, DataSource dataSource1, DataSource dataSource2) {
25	// 注意：该方法的参数名称要和前面前面三个datasource对象在Spring容器中的bean名称一样
26	// 或者使用 @Qualifier 指定具体的bean
27	Map<Object, Object> targetDataSources = new HashMap<>();
28	targetDataSources.put(DataSourceType.DEFAULT_DATASOURCE.name(), defaultDataSource);
29	targetDataSources.put(DataSourceType.DATASOURCE1.name(), dataSource1);
30	targetDataSources.put(DataSourceType.DATASOURCE2.name(), dataSource2);
31	return new DynamicDataSource(defaultDataSource, targetDataSources);
32	}
33	}

1	@RestController
2	@RequestMapping("/test")
3	public class TestController {
4
5	@Autowired
6	private TestMapper testMapper;
7
8	@GetMapping
9	public List<Map<String, Object>> test(String dataSourceIndex) {
10	// 根据参数值的不同，切换数据源
11	if ("1".equals(dataSourceIndex)) {
12	DataSourceContextHolder.setDataSourceType(DataSourceType.DATASOURCE1.name());
13	} else if ("2".equals(dataSourceIndex)) {
14	DataSourceContextHolder.setDataSourceType(DataSourceType.DATASOURCE2.name());
15	}
16	List<Map<String, Object>> mapList = testMapper.selectList();
17	// 清除线程内部变量数据源key
18	DataSourceContextHolder.removeDataSourceType();
19	return mapList;
20	}
21	}

1	@Repository
2	public interface TestMapper {
3	/**
4	* 查询列表
5	* @return
6	*/
7	List<Map<String, Object>> selectList();
8	}

1	<?xml version="1.0" encoding="UTF-8" ?>
2	<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
3	<mapper namespace="com.liuchuanv.dynamicdatasource.mapper.TestMapper">
4	<select id="selectList" resultType="java.util.Map">
5	SELECT * FROM test
6	</select>
7	</mapper>

1
2	-- 创建数据库
3	create database db0 character set utf8 collate utf8_general_ci;
4	create database db1 character set utf8 collate utf8_general_ci;
5	create database db2 character set utf8 collate utf8_general_ci;
6
7	-- 在数据库db1下执行以下SQL
8	use db0;
9	create table test(
10	id int(11) primary key auto_increment,
11	name varchar(20)
12	) ;
13	insert into test(name) values('张三');
14
15
16	-- 在数据库db1下执行以下SQL
17	use db1;
18	create table test(
19	id int(11) primary key auto_increment,
20	name varchar(20)
21	) ;
22	insert into test(name) values('李四');
23
24	-- 在数据库db2下执行以下SQL
25	use db2;
26	create table test(
27	id int(11) primary key auto_increment,
28	name varchar(20)
29	) ;
30	insert into test(name) values('王五');

1	┌─────┐
2	\| dynamicDataSource defined in class path resource [com/liuchuanv/dynamicdatasource/common/DataSourceConfig.class]
3	↑ ↓
4	\| defaultDataSource defined in class path resource [com/liuchuanv/dynamicdatasource/common/DataSourceConfig.class]
5	↑ ↓
6	\| org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker
7	└─────┘