springboot+mybatis多数据源配置

大大源码 • • 其他

目录

1、前言

2、多数据源配置

2.1  AbstractRoutingDataSource

2.2、首先maven依赖

2.3 数据源配置

2.4 mybatis配置

2.5 设置数据源的路由key 以及 查找数据源

3、切面拦截

3.1 定义切面注解

3.2 切面实现类

 4、测试Controller

1、前言

在最近的项目开发中，有需要把多个服务整合，涉及到了多个数据库访问。所以记录一下多数据源的配置。在我自己的项目中，JDK使用的是1.8版本，SpringBoot是1.5.21版本。

2、多数据源配置

2.1  AbstractRoutingDataSource

动态数据源的配置是基于key路由到特定的数据源，由路由key与目标数据源产生映射关系。springboot提供了一个抽象类AbstractRoutingDataSource来实现，我们只需要实现determineCurrentLookupKey方法即可。

 

2.2、首先maven依赖

 <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.21.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>

<dependencies>
  	<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    
    <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>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>${fastjson.version}</version>
    </dependency>
  
  	<!-- spring集成热部署 -->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-devtools</artifactId>
		<optional>true</optional>
	</dependency>
	<!-- spring集成配置文件的操作组件 -->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-configuration-processor</artifactId>
		<optional>true</optional>
	</dependency>
	<!-- druid连接池监控 -->
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid</artifactId>
		<version>${druid.version}</version>
	</dependency>
	<!-- jdbc连接驱动 -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
	</dependency>
	<dependency>
		<groupId>org.mybatis.spring.boot</groupId>
		<artifactId>mybatis-spring-boot-starter</artifactId>
		<version>2.0.1</version>
	</dependency>
	<dependency>
	    <groupId>org.springframework.boot</groupId>
	    <artifactId>spring-boot-starter-aop</artifactId>
	</dependency>
	
	<dependency>
		<groupId>org.aspectj</groupId>
		<artifactId>aspectjweaver</artifactId>
	</dependency>
	
	<!--reflectasm使用字节码生成的方式实现了更为高效的反射机制 -->
	<dependency>
		<groupId>com.esotericsoftware</groupId>
		<artifactId>reflectasm</artifactId>
		<version>1.11.9</version>
	</dependency>
	
	<!-- redis使用 -->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-redis</artifactId>
	</dependency>
	
	<dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>
	
	<dependency>
	    <groupId>org.apache.httpcomponents</groupId>
		<artifactId>httpclient</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-mongodb</artifactId>
	</dependency>
  </dependencies>
  
  <build>
	<plugins>
		<plugin>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-maven-plugin</artifactId>
		</plugin>
		<plugin>
			<groupId>org.apache.maven.plugins</groupId>
			<artifactId>maven-compiler-plugin</artifactId>
			<configuration>
				<source>1.8</source>
				<target>1.8</target>
			</configuration>
		</plugin>
	</plugins>
	<defaultGoal>compile</defaultGoal>

  </build>

2.3 数据源配置

        application.yml配置

spring:
 datasource:
  user:
   driverClassName: com.mysql.cj.jdbc.Driver
   url: jdbc:mysql://192.168.1.15:3306/test_user?serverTimezone=Asia/Shanghai&allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
   username: root
   password: root
  patient:
   driverClassName: com.mysql.cj.jdbc.Driver
   url: jdbc:mysql://192.168.1.15:3306/test_patient?serverTimezone=Asia/Shanghai&allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
   username: root
   password: root
  doctor:
   driverClassName: com.mysql.cj.jdbc.Driver
   url: jdbc:mysql://192.168.1.15:3306/test_doctor?serverTimezone=Asia/Shanghai&allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
   username: root
   password: root
 druid:
  initialSize: 5
  minIdle: 5
  maxActive: 20
  maxWait: 60000
  timeBetweenEvictionRunsMillis: 60000
  minEvictableIdleTimeMillis: 300000
  validationQuery: SELECT 1 FROM DUAL
  testWhileIdle: true
  testOnBorrow: false
  testOnReturn: false 
  poolPreparedStatements: true
  maxPoolPreparedStatementPerConnectionSize: 20
  filters: stat,log4j
  connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

        多数据源配置类

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

import javax.sql.DataSource;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import com.alibaba.druid.pool.DruidDataSourceFactory;

/**
 * 	动态数据源配置
 * @author LongBJ
 *
 */
@Configuration
@MapperScan("com.kingseok.hospital.business.*.dao")
public class DynamicDataSourceConfig implements EnvironmentAware {

	
	/**
	 * 	user数据源
	 * 
	 * @return
	 */
	@Bean
	@ConfigurationProperties(prefix = "spring.datasource.user")
	public DataSource userDataSource() {
		//DruidDataSourceFactory.createDataSource(properties);
		return DataSourceBuilder.create().build();
	}
	
	
	
	/**
	 * 	patient数据源
	 * @return
	 */
	@Bean
	@ConfigurationProperties(prefix = "spring.datasource.patient")
	public DataSource patientDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	
	
	/**
	 * 	doctor数据源
	 * @return
	 */
	@Bean
	@ConfigurationProperties(prefix = "spring.datasource.doctor")
	public DataSource doctorDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	
	/**
	 * 	配置动态数据源
	 * 
	 * @return
	 */
	@Primary // 此处指定动态数据源为primary
	@Bean(name = "dynamicDataSource")
	public DataSource dynamicDataSource(
			@Qualifier("userDataSource") DataSource userDataSource,
			@Qualifier("patientDataSource") DataSource patientDataSource, 
			@Qualifier("doctorDataSource") DataSource doctorDataSource) {
		Map<Object, Object> dataSourceMap = new HashMap<>(3);
		dataSourceMap.put(DataSourceEnum.DS_USER.getValue(), userDataSource);
		dataSourceMap.put(DataSourceEnum.DS_PATIENT.getValue(), patientDataSource);
		dataSourceMap.put(DataSourceEnum.DS_DOCTOR.getValue(), doctorDataSource);
		
		DynamicDataSource dynamicDataSource = new DynamicDataSource();
		dynamicDataSource.setTargetDataSources(dataSourceMap);
		dynamicDataSource.setDefaultTargetDataSource(userDataSource); // 指定默认的数据源
		return dynamicDataSource;
	}

	
	@Autowired
	private Environment env;


	@Override
	public void setEnvironment(Environment environment) {
		this.env = environment;
	}
	
	
}

此处配置了4个数据源，分别是user、patient、doctor，还有一个路由数据源。前面3个是为了生成第4个数据源，这个是动态数据源的关键，后面的数据源切换也是从这里选择。

2.4 mybatis配置

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;


/**
**   Mybatis配置
**/
@Configuration
@EnableTransactionManagement
public class MyBatisConfig {
	
	@Resource(name = "dynamicDataSource")
    private DataSource dynamicDataSource;

	
	/**
	 * sqlSessionFactory
	 * @return
	 * @throws Exception
	 */
	@Bean
	public SqlSessionFactory sqlSessionFactory() throws Exception {
		SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
		factory.setDataSource(dynamicDataSource);
		PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
		factory.setMapperLocations(resolver.getResources("classpath:mapper/**/*.xml"));
		return factory.getObject();
	}
	
	
	@Bean
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
	
	
	/**
	 * 事务管理器
	 * @return
	 */
	@Bean
	public PlatformTransactionManager platformTransactionManager() {
		return new DataSourceTransactionManager(dynamicDataSource); 
	}
}

        由于我们是多数据源，所以我们要为事务管理器和mybatis手动指定明确的数据源。

2.5 设置数据源的路由key 以及 查找数据源

        首先我们定义一个数据源枚举（常量类也行），如下：

public enum DataSourceEnum {
	
	DS_USER("DS_USER", "user数据源"),
	DS_PATIENT("DS_PATIENT", "patient数据源"),
	DS_DOCTOR("DS_DOCTOR","doctor数据源"),
	DS_KUAIMA_MEDICAL("medical", "medical数据源"),
	DS_PDD_IM("pdd_im_data", "im数据源");
	
	private String value;
	private String display;
	
	DataSourceEnum(String value, String display) {
		this.value = value;
		this.display = display;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}

	public String getDisplay() {
		return display;
	}

	public void setDisplay(String display) {
		this.display = display;
	}
}

        接下来，我们利用ThreadLocal将数据源设置到每个请求的线程上下文当中，代码如下：

/**
** 动态数据源线程上下文操作类
**/
public class DynamicDataSourceContextHolder {

	private final static ThreadLocal<String> DATASOURCE_CONTEXT_KEY_HOLDER = new ThreadLocal<>();

	
	/**
     * 设置/切换数据源
     */
    public static void setContextKey(String key){
        DATASOURCE_CONTEXT_KEY_HOLDER.set(key);
    }
    
    
    /**
     * 获取数据源名称
     */
    public static String getContextKey(){
    	String key = DATASOURCE_CONTEXT_KEY_HOLDER.get();
        return key == null ? DataSourceEnum.DS_USER.getValue() : key;
    }
    

    /**
     * 删除当前数据源名称
     */
    public static void removeContextKey(){
        DATASOURCE_CONTEXT_KEY_HOLDER.remove();
    }
    
}

前面说过动态数据源选择，springboot提供的抽象类AbstractRoutingDataSource提供了一个方法，我们实现这个方法即可，所以我们创建一个动态数据源选择类，然后继承AbstractRoutingDataSource，如下：

/**
 * 	动态数据源选择
 * @author LongBJ
 *
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

	
	@Override
	protected Object determineCurrentLookupKey() {
        // 前面将用户请求的数据源路由key set到threadLocal, 此处从ThreadLocal获取出来，
        // 拿到key之后，找到对应的数据源（key就是DynamicDataSourceConfig类中配置的key）
		return DynamicDataSourceContextHolder.getContextKey();
	}

}

        至此，多数据源便已完全配置，接下来就是如何识别用户的访问哪一个数据源，我自己的项目是通过AOP切面来实现的。

3、切面拦截

3.1 定义切面注解

        定义一个切面注解用于访问的类或者方法上，如下：

import static java.lang.annotation.ElementType.TYPE;
import static java.lang.annotation.ElementType.METHOD;

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

import com.kingseok.hospital.common.configuration.DataSourceEnum;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ TYPE, METHOD })   // TYPE可用户类上，METHOD用于请求方法上
public @interface SqlDynamicDS {

	
	DataSourceEnum value() default DataSourceEnum.DS_USER;
	
}

3.2 切面实现类

        有了切面注解，还需要实现切面拦截，实现非常简单，只需要定义一个类，在类上使用@Aspect和@Component即可实现。

/**
 * 	mysql动态数据源切面
 * @author LongBJ
 *
 */
@Aspect
@Component
@Order(value = Ordered.HIGHEST_PRECEDENCE)
public class DynamicDatasourceAspect {

	private final static Logger _logger = LoggerFactory.getLogger(DynamicDatasourceAspect.class);
	
	
	@Pointcut("@within(com.kingseok.hospital.common.annotation.SqlDynamicDS)")
	public void dataSourcePointCut() {}
	
	@Pointcut("@annotation(com.kingseok.hospital.common.annotation.SqlDynamicDS)")
	public void dataSourcePointCut2() {}
	
	
	
	@Around("dataSourcePointCut() || dataSourcePointCut2()")
	public Object getSqlDynamicDS(ProceedingJoinPoint joinPoint) throws Throwable {
		DataSourceEnum ds = getDSAnnocation(joinPoint).value();
		_logger.info("DynamicDatasourceAspect : getSqlDynamicDS -> ds_key={}", ds.getValue());
		DynamicDataSourceContextHolder.setContextKey(ds.getValue());
		try{
            return joinPoint.proceed();
        }finally {
        	DynamicDataSourceContextHolder.removeContextKey();
            _logger.info("DynamicDatasourceAspect : getSqlDynamicDS -> ThreadLocal已删除ds_key={}", ds.getValue());
        } 
	}
	
	
	private SqlDynamicDS getDSAnnocation(ProceedingJoinPoint joinPoint) {
		Class<?> targetClazz = joinPoint.getTarget().getClass();
		SqlDynamicDS ds = targetClazz.getAnnotation(SqlDynamicDS.class);
		//	先判断类的注解，再判断方法注解
		if(Objects.nonNull(ds)) {
			return ds;
		}
		MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
		return methodSignature.getMethod().getAnnotation(SqlDynamicDS.class);
	}
	
}

        这里需要特别注意，我定义了2个切面。为什么定义2个，有眼尖的同学会发现@Pointcut("@within(com.kingseok.hospital.common.annotation.SqlDynamicDS)")和@Pointcut("@annotation(com.kingseok.hospital.common.annotation.SqlDynamicDS)")仅仅是@within和@annotation不同，其实@within：用于匹配所持有的指定直接类型内的所有方法；而@annotation：用于配置当前执行方法持有指定注解的方法。参考如下：

 4、测试Controller

import com.cjs.example.entity.Member;
import com.cjs.example.service.MemberService;
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;

@RunWith(SpringRunner.class)
@SpringBootTest
@SqlDynamicDS(value = DataSourceEnum.DS_USER) // 作用于类上，则此类所有方法请求都将路由到数据源DataSourceEnum.DS_USER上  类的优先级高于方法
public class DatasourceDemoApplicationTests {

    @Autowired
    private MemberService memberService;

    @Test
    public void testWrite() {
        Member member = new Member();
        member.setName("zhangsan");
        memberService.insert(member);
    }

    

    @Test
    @SqlDynamicDS(value = DataSourceEnum.DS_USER) // 作用于方法上
    public void testReadFromMaster() {
        memberService.getUser("zhangsan");
    }

}

至此，这个多数据源配置已完成

版权声明：本文为AP0906424原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
THE END
< <上一篇
下一篇>>