Springboot整合Mybatis实现多数据源动态切换
1 业务背景
最近一个项目中需要在oracle数据库中读取用户信息,需要在mysql中读取业务数据。
2 解决方案
- 多数据源配置
在比较大型的项目中,数据库可能会分布在多台服务器上,例如有若干个数据库服务是专门存放日志数据的,又有若干个数据库服务是专门存放业务数据、读写分离的等等....这时候应用程序如果需要对这两种类型的数据进行处理的话,就需要配置多数据源了。
- 微服务化
在如今微服务大行其道的今天,没有啥是为服务解决不了的,既然需要在不同的数据库读取信息,那么我们就可以将业务拆分为二,独立一个服务出来专门负责oracle数据库的连接查询,独立一个服务负责mysql数据库的业务处理。再新建一个工程调用这两个服务问题就迎刃而解。
由于该项目是存在已久的老项目,目前不具备微服务化得能力而且时间也不允许。因此只能退而求其次在项目中引进多数据源。
达到按照需求动态切换的目的。
3 具体实现
pom文件配置
<?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>
<artifactId>spring-boot-mybatis-multiple-datasource</artifactId>
<packaging>jar</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-mybatis-multiple-datasource</name>
<description>Demo Multiple Datasource for Spring Boot</description>
<parent>
<groupId>com.along</groupId>
<artifactId>spring-boot-all</artifactId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</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-aop</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
<!-- mybatis-generator-core 反向生成java代码-->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!--mybatis逆向工程maven插件-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.5</version>
<configuration>
<!--允许移动生成的文件-->
<verbose>true</verbose>
<!--允许覆盖生成的文件-->
<overwrite>true</overwrite>
<!--配置文件的路径 默认resources目录下-->
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
</configuration>
<!--插件依赖的jar包-->
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
application.properties文件
spring.application.name=springboot-datasource
server.port=8081
## mysql database
spring.datasource.local.url=****
spring.datasource.local.username=****
spring.datasource.local.password=****
spring.datasource.local.driver-class-name=com.mysql.jdbc.Driver
## oracle database
spring.datasource.oracle.url=****
spring.datasource.oracle.username=****
spring.datasource.oracle.password****
spring.datasource.oracle.driver-class-name=****
实际配置以实际为准,以上仅供参考:
- 基于分包方式实现
配置mysql数据库的datasource
package com.lb.api.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
@Configuration
//开启事务管理
@MapperScan(basePackages = "com.lb.api.dao.local", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class LocalDataSourceConfig {
//这里必须要要加 @Qualifier(value = "localDatasource")注解
//否则Spring将不会知道用哪个Bean
@Bean(name = "localDatasource")
@Qualifier(value = "localDatasource")
@ConfigurationProperties(prefix = "spring.datasource.local")
public DataSource localDatasource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test1SqlSessionFactory")
// 表示这个数据源是默认数据源
@Primary
// @Qualifier表示查找Spring容器中名字为test1DataSource的对象
public SqlSessionFactory test1SqlSessionFactory(@Qualifier("localDatasource") DataSource datasource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setMapperLocations(
// 设置mybatis的xml所在位置
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/test01/*.xml"));
return bean.getObject();
}
@Bean("test1SqlSessionTemplate")
// 表示这个数据源是默认数据源
@Primary
public SqlSessionTemplate test1sqlsessiontemplate(
@Qualifier("test1SqlSessionFactory") SqlSessionFactory sessionfactory) {
return new SqlSessionTemplate(sessionfactory);
}
}
1:@MapperScan(basePackages = "com.lb.api.dao.local", sqlSessionFactoryRef = "test1SqlSessionFactory")
其中basePackages配置是你需要连接mysql数据库的mapper文件存放的路径。
2:@ConfigurationProperties(prefix = "spring.datasource.local")
prefix 配置读取以local开头的数据库配置属性
3:@Primary一定要加,并且只能加在一个datasource上,因为它代表默认配置,加在你需要设置的默认datasource上。
4: new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/test02/*.xml"));
配置你需要扫描的mapper.xml文件的路径,记得不同数据源需要区分开来。
配置oracle数据库的datasource
package com.lb.api.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.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.TransactionManagementConfigurer;
@Configuration
//配置mybatis的接口类放的地方
@MapperScan(basePackages = "com.lb.api.dao.oracle", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class OracleDatasourceConfig {
@Bean(name = "oracleDatasource")
@Qualifier(value = "oracleDatasource")
@ConfigurationProperties(prefix = "spring.datasource.oracle")
public DataSource oracleDatasource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
// 表示这个数据源是默认数据源
// @Qualifier表示查找Spring容器中名字为test1DataSource的对象
public SqlSessionFactory test1SqlSessionFactory(@Qualifier("oracleDatasource") DataSource datasource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setMapperLocations(
// 设置mybatis的xml所在位置
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/test02/*.xml"));
return bean.getObject();
}
@Bean("test2SqlSessionTemplate")
// 表示这个数据源是默认数据源
public SqlSessionTemplate test1sqlsessiontemplate(
@Qualifier("test2SqlSessionFactory") SqlSessionFactory sessionfactory) {
return new SqlSessionTemplate(sessionfactory);
}
}
至此一个双数据源的服务就搭建好了,当你调用相应mapper文件里的数据库操作时会调用不同的数据库。
- 基于AOP方式的动态切换
1. 数据源配置类 MultipleDataSourceConfig.java
package com.lb.api.config;
import java.util.HashMap;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.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;
@Configuration
public class DataSourceConfig {
@Bean(name = "localDatasource")
@Qualifier(value = "localDatasource")
@ConfigurationProperties(prefix = "spring.datasource.local")
public DataSource localDatasource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "oracleDatasource")
@Qualifier(value = "oracleDatasource")
@ConfigurationProperties(prefix = "spring.datasource.oracle")
public DataSource oracleDatasource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "sqlSessionFactory")
// 表示这个数据源是默认数据源
// @Qualifier表示查找Spring容器中名字为test1DataSource的对象
public SqlSessionFactory sqlSessionFactory()
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dynamicDataSource());
bean.setMapperLocations(
// 设置mybatis的xml所在位置
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
return bean.getObject();
}
@Bean("sqlSessionTemplate")
// 表示这个数据源是默认数据源
public SqlSessionTemplate sqlsessiontemplate(
@Qualifier("sqlSessionFactory") SqlSessionFactory sessionfactory) {
return new SqlSessionTemplate(sessionfactory);
}
@Primary
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
//配置默认数据源
dynamicDataSource.setDefaultTargetDataSource(localDatasource());
//配置多数据源
HashMap<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put(ContextConst.DataSourceType.PRIMARY.name(), oracleDatasource());
dataSourceMap.put(ContextConst.DataSourceType.LOCAL.name(), localDatasource());
dynamicDataSource.setTargetDataSources(dataSourceMap); // 该方法是AbstractRoutingDataSource的方法
return dynamicDataSource;
}
@Bean
public PlatformTransactionManager bfTransactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
// @Bean
// public PlatformTransactionManager bfscrmTransactionManager(@Qualifier("oracleDatasource")DataSource oracleDatasource) {
// return new DataSourceTransactionManager(oracleDatasource);
// }
}
2. 数据源持有类 DataSourceContextHolder.java
/**
* 数据源持有类
*/
public class DataSourceContextHolder {
private static final Logger logger = LoggerFactory.getLogger(DataSourceContextHolder.class);
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static void setDataSource(String dbType){
logger.info("切换到[{}]数据源",dbType);
contextHolder.set(dbType);
}
public static String getDataSource(){
return contextHolder.get();
}
public static void clearDataSource(){
contextHolder.remove();
}
}
3. 数据源路由实现类 DynamicDataSource.java
/**
* 数据源路由实现类
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
String dataSource = DataSourceContextHolder.getDataSource();
if (dataSource == null) {
logger.info("当前数据源为[primary]");
} else {
logger.info("当前数据源为{}", dataSource);
}
return dataSource;
}
}
4. 自定义切换数据源的注解
/**
* 切换数据源的注解
*/
@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
ContextConst.DataSourceType value() default ContextConst.DataSourceType.PRIMARY;
}
5. 数据源枚举类
/**
* 上下文常量
*/
public interface ContextConst {
/**
* 数据源枚举
*/
enum DataSourceType {
PRIMARY, LOCAL, PROD, TEST
}
}
6. 定义切换数据源的切面,为注解服务
package com.lb.api.config;
import java.lang.reflect.Method;
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.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* 切换数据源的切面
*/
@Component
@Aspect
@Order(1) //这是关键,要让该切面调用先于AbstractRoutingDataSource的determineCurrentLookupKey()
public class DynamicDataSourceAspect {
@Before("execution(* com.lb.api.service..*.*(..))")
public void before(JoinPoint point) {
try {
DataSource annotationOfClass = point.getTarget().getClass().getAnnotation(DataSource.class);
String methodName = point.getSignature().getName();
Class[] parameterTypes = ((MethodSignature) point.getSignature()).getParameterTypes();
Method method = point.getTarget().getClass().getMethod(methodName, parameterTypes);
DataSource methodAnnotation = method.getAnnotation(DataSource.class);
methodAnnotation = methodAnnotation == null ? annotationOfClass : methodAnnotation;
ContextConst.DataSourceType dataSourceType = methodAnnotation != null
&& methodAnnotation.value() != null ? methodAnnotation.value() : ContextConst.DataSourceType.PRIMARY;
DataSourceContextHolder.setDataSource(dataSourceType.name());
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
@After("execution(* com.lb.api.service..*.*(..))")
public void after(JoinPoint point) {
DataSourceContextHolder.clearDataSource();
}
}
7. 修改启动类
package com.lb.api;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.lb.api.service.DataSourceService;
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@MapperScan(basePackages = "com.lb.api.dao")
@RestController
public class ApiApplication {
public static void main(String[] args) {
SpringApplication.run(ApiApplication.class, args);
}
@Autowired
private DataSourceService dataSourceService;
@RequestMapping(value="/test")
public void test(){
dataSourceService.test01();
dataSourceService.test02();
}
}
8 使用
在方法上通过注解@DataSource指定该方法所用的数据源,如果没有使用注解指定则使用默认数据源
下面是在service实现类中的应用:
package com.lb.api.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.lb.api.config.ContextConst;
import com.lb.api.config.DataSource;
import com.lb.api.dao.LivesubscriptionMapper;
import com.lb.api.dao.TOperHisMapper;
@Service
public class DataSourceService {
@Autowired
private LivesubscriptionMapper livesubscriptionMapper;
@Autowired
private TOperHisMapper tOperHisMapper;
@DataSource(ContextConst.DataSourceType.LOCAL) // 指定该方法使用prod数据源
public void test01() {
System.out.println(livesubscriptionMapper.selectByExample().size());
}
@DataSource(ContextConst.DataSourceType.PRIMARY) // 指定该方法使用prod数据源
public void test02() {
System.out.println(tOperHisMapper.selectByExample().size());
}
}
访问链接localhost:8081/test
2019-10-09 16:36:35.777 INFO 14752 --- [nio-8081-exec-7] c.lb.api.config.DataSourceContextHolder : 切换到[LOCAL]数据源
2019-10-09 16:36:35.785 INFO 14752 --- [nio-8081-exec-7] com.lb.api.config.DynamicDataSource : 当前数据源为LOCAL
83
2019-10-09 16:36:35.838 INFO 14752 --- [nio-8081-exec-7] c.lb.api.config.DataSourceContextHolder : 切换到[PRIMARY]数据源
2019-10-09 16:36:35.838 INFO 14752 --- [nio-8081-exec-7] com.lb.api.config.DynamicDataSource : 当前数据源为PRIMARY
0
达到了动态切换的目的。