14.Spring Boot 多数据源

编程语言Codelang

已于 2022-03-15 10:57:53 修改

阅读量4.5k

点赞数

于 2016-12-28 11:33:14 首次发布

本文链接：https://blog.csdn.net/cl_andywin/article/details/53907046

版权

Spring Boot 专栏收录该内容

18 篇文章 6 订阅

订阅专栏

Spring Boot 多数据源

在项目中有的时候需要用到多个数据源，有个问题就是单数据源的事务是没有问题的，多数据源是会存在事务问题的。这里不做事务讲解，事务可以用JTA分布式事务，也可以用MQ。具体不做叙述，接下来说如何实现多数据源并且使用AOP来切换。

本例代码使用Mybatis具体请看： 10.玩转Spring Boot 集成Mybatis， 11.玩转Spring Boot 集成Druid

1.在pom中加入以下依赖：

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>

2.application.properties内容如下：

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root

spring.datasource.driver-class-name1=com.mysql.jdbc.Driver
spring.datasource.url1=jdbc:mysql://localhost:3306/springboot2?useUnicode=true&characterEncoding=utf8
spring.datasource.username1=root
spring.datasource.password1=root

#最小连接数量
spring.datasource.minIdle=2
#最大连接数量
spring.datasource.maxActive=5
#获取连接等待超时的时间
spring.datasource.maxWait=60000
#间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
#连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
#验证SQL
spring.datasource.validationQuery=SELECT 'x' FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
#打开PSCache，并且指定每个连接上PSCache的大小如果用Oracle，
#则把poolPreparedStatements配置为true，mysql可以配置为false。分库分表较多的数据库，建议配置为false。
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
#配置监控统计拦截的filters
spring.datasource.filters=stat

3.MybatisConfig代码如下：

package com.chengli.springboot.dynamicds.config;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.alibaba.druid.pool.DruidDataSource;
import com.chengli.springboot.dynamicds.dynmic.DynmicDataSource;

@Configuration
@MapperScan(basePackages = { "com.chengli.springboot.dynamicds" }, annotationClass = Mapper.class) // 定义扫描的ROOT包，以及注解
@EnableTransactionManagement // 开启注解事务
public class MybatisConfig {
	@Autowired
	private DruidConfigProperties druidConfigProperties;

	@Primary//设置为主要的，当同一个类型存在多个Bean的时候，spring 会默认注入以@Primary注解的bean
	@Bean(initMethod = "init", destroyMethod = "close")
	public DataSource springbootDataSource() throws SQLException {
		DruidDataSource druidDataSource = new DruidDataSource();
		druidDataSource.setDriverClassName(druidConfigProperties.getDriverClassName());
		druidDataSource.setUrl(druidConfigProperties.getUrl());
		druidDataSource.setUsername(druidConfigProperties.getUsername());
		druidDataSource.setPassword(druidConfigProperties.getPassword());
		druidDataSource.setInitialSize(druidConfigProperties.getMinIdle());
		druidDataSource.setMinIdle(druidConfigProperties.getMinIdle());
		druidDataSource.setMaxActive(druidConfigProperties.getMaxActive());
		druidDataSource.setMaxWait(druidConfigProperties.getMaxWait());
		druidDataSource.setTimeBetweenEvictionRunsMillis(druidConfigProperties.getTimeBetweenEvictionRunsMillis());
		druidDataSource.setMinEvictableIdleTimeMillis(druidConfigProperties.getMinEvictableIdleTimeMillis());
		druidDataSource.setValidationQuery(druidConfigProperties.getValidationQuery());
		druidDataSource.setTestWhileIdle(druidConfigProperties.getTestWhileIdle());
		druidDataSource.setTestOnBorrow(druidConfigProperties.getTestOnBorrow());
		druidDataSource.setTestOnReturn(druidConfigProperties.getTestOnReturn());
		druidDataSource.setPoolPreparedStatements(druidConfigProperties.getPoolPreparedStatements());
		druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(druidConfigProperties.getMaxPoolPreparedStatementPerConnectionSize());
		druidDataSource.setFilters(druidConfigProperties.getFilters());
		return druidDataSource;
	}

	@Bean(initMethod = "init", destroyMethod = "close")
	public DataSource eziliaoDataSource() throws SQLException {
		DruidDataSource druidDataSource = new DruidDataSource();
		druidDataSource.setDriverClassName(druidConfigProperties.getDriverClassName1());
		druidDataSource.setUrl(druidConfigProperties.getUrl1());
		druidDataSource.setUsername(druidConfigProperties.getUsername1());
		druidDataSource.setPassword(druidConfigProperties.getPassword1());
		druidDataSource.setInitialSize(druidConfigProperties.getMinIdle());
		druidDataSource.setMinIdle(druidConfigProperties.getMinIdle());
		druidDataSource.setMaxActive(druidConfigProperties.getMaxActive());
		druidDataSource.setMaxWait(druidConfigProperties.getMaxWait());
		druidDataSource.setTimeBetweenEvictionRunsMillis(druidConfigProperties.getTimeBetweenEvictionRunsMillis());
		druidDataSource.setMinEvictableIdleTimeMillis(druidConfigProperties.getMinEvictableIdleTimeMillis());
		druidDataSource.setValidationQuery(druidConfigProperties.getValidationQuery());
		druidDataSource.setTestWhileIdle(druidConfigProperties.getTestWhileIdle());
		druidDataSource.setTestOnBorrow(druidConfigProperties.getTestOnBorrow());
		druidDataSource.setTestOnReturn(druidConfigProperties.getTestOnReturn());
		druidDataSource.setPoolPreparedStatements(druidConfigProperties.getPoolPreparedStatements());
		druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(druidConfigProperties.getMaxPoolPreparedStatementPerConnectionSize());
		druidDataSource.setFilters(druidConfigProperties.getFilters());
		return druidDataSource;
	}

	@Bean
	public DataSource dynmicDataSource() throws SQLException {
		DynmicDataSource dynmicDataSource = new DynmicDataSource();
		Map<Object, Object> targetDataSources = new HashMap<>();
		targetDataSources.put("springbootDataSource", springbootDataSource());
		targetDataSources.put("eziliaoDataSource", eziliaoDataSource());
		dynmicDataSource.setTargetDataSources(targetDataSources);
		
		dynmicDataSource.setDefaultTargetDataSource(springbootDataSource());
		return dynmicDataSource;
	}
	
	@Bean
	public SqlSessionFactory sqlSessionFactory(@Qualifier("dynmicDataSource")DataSource dataSource) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(dataSource);
		sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));

		// 添加Mybatis插件，例如分页，在之类创建你插件添加进去即可，这里我就不做叙述了。
		// sqlSessionFactoryBean.setPlugins(new Interceptor[]{你的插件});

		return sqlSessionFactoryBean.getObject();
	}

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

}

4.DruidConfigProperties代码修改，代码如下：

@Component
@ConfigurationProperties(prefix = "spring.datasource")
public class DruidConfigProperties {
	private String driverClassName;
	private String url;
	private String username;
	private String password;

	private String driverClassName1;
	private String url1;
	private String username1;
	private String password1;

	private Integer minIdle;
	private Integer maxActive;
	private Integer maxWait;
	private Long timeBetweenEvictionRunsMillis;
	private Long minEvictableIdleTimeMillis;
	private String validationQuery;
	private Boolean testWhileIdle;
	private Boolean testOnBorrow;
	private Boolean testOnReturn;
	private Boolean poolPreparedStatements;
	private Integer maxPoolPreparedStatementPerConnectionSize;
	private String filters;
.........get set 方法省略
}

5.定义类，实现AbstractRoutingDataSource，代码如下：

package com.chengli.springboot.dynamicds.dynmic;

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

public class DynmicDataSource extends AbstractRoutingDataSource {

	/**
	 * 返回的内容是targetDataSources 的Key
	 */
	@Override
	protected Object determineCurrentLookupKey() {
		return DynmicDataSourceContextHolder.getDataSourceKey();
	}

}

6.自定义注解UseDataSource

package com.chengli.springboot.dynamicds.dynmic;

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

@Target({ ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UseDataSource {
	String value();
}

7.创建DynmicDataSourceContextHolder，用于设置当前使用的数据源Key

package com.chengli.springboot.dynamicds.dynmic;

public class DynmicDataSourceContextHolder {
	private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

	public static String getDataSourceKey() {
		return contextHolder.get();
	}

	public static void setDataSourceKey(String dataSourcekey) {
		contextHolder.set(dataSourcekey);
	}
        public static void clear() {
               contextHolder.remove();
        }
}

8.自定义AOP，设置数据源DynamicDataSourceAspect

package com.chengli.springboot.dynamicds.dynmic;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Aspect
@Order(-1) //spring order排序后执行顺序是从小到大，目的是确保在事务管理器执行前先执行
@Component
public class DynamicDataSourceAspect {

	@Before("@annotation(useDataSource)")//拦截注解 UseDataSource
	public void setDataSourceType(JoinPoint point, UseDataSource useDataSource) throws Throwable {
		DynmicDataSourceContextHolder.setDataSourceKey(useDataSource.value());
	}
        @After("@annotation(useDataSource)")
        public void clearDataSourceType(JoinPoint point, UseDataSource useDataSource) {
                DynmicDataSourceContextHolder.clear();
        }
}

到这里就完成啦，主要代码就上面这些。