Springboot MyBatis多数据源切换
在实际开发中,我们一个项目可能会用到多个数据库,通常一个数据库对应一个数据源。本示例,通过两种方式实现多数据源切换
1)手动切换
2)使用注解进行切换
代码结构:
简要原理:
1)DataSourceType列出所有的数据源的key—key
2)DataSourceContextHolder是一个线程安全的DataSourceEnum容器,并提供了向其中设置和获取DataSourceEnum的方法
3)DynamicDataSource继承AbstractRoutingDataSource并重写其中的方法determineCurrentLookupKey(),在该方法中使用DatabaseContextHolder获取当前线程的DatabaseType
4)MyBatisConfig中生成2个数据源DataSource的bean—value
5)MyBatisConfig中将1)和4)组成的key-value对写入到DynamicDataSource动态数据源的targetDataSources属性(当然,同时也会设置2个数据源其中的一个为DynamicDataSource的defaultTargetDataSource属性中)
6)将DynamicDataSource作为primary数据源注入到SqlSessionFactory的dataSource属性中去,并且该dataSource作为transactionManager的入参来构造DataSourceTransactionManager
7)使用的时候,在dao层或service层先使用DatabaseContextHolder设置将要使用的数据源key,然后再调用mapper层进行相应的操作,建议放在dao层去做(当然也可以使用spring aop+自定注解去做)
8)定义了一个DataSourceTypeAnno注解,用于说明方法执行时使用的数据源
9)DataSourceAspect切面用于捕获DataSourceTypeAnno注解的方法,根据DataSourceTypeAnno注解内对应的值设置数据源
注意:在mapper层进行操作的时候,会先调用determineCurrentLookupKey()方法获取一个数据源(获取数据源:先根据设置去targetDataSources中去找,若没有,则选择defaultTargetDataSource),之后在进行数据库操作。
Maven依赖
<?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>
<groupId>com.example.springboot</groupId>
<artifactId>multi-datasource</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.2.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<!-- 使用aspectj时需要 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.properties配置
#the master datasource
datasource.master.driverClassName=com.mysql.jdbc.Driver
datasource.master.url=jdbc:mysql://localhost:3306/master?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
datasource.master.username=root
datasource.master.password=123456
#the slaver datasource
datasource.slaver.driverClassName=com.mysql.jdbc.Driver
datasource.slaver.url=jdbc:mysql://localhost:3306/slaver?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
datasource.slaver.username=root
datasource.slaver.password=123456
# mybatis接口文件位置
#mybatis.typeAliasesPackage=
# mybatis *.xml文件的位置
#mybatis.mapperLocations=
mysql数据库数据准备
#向master和slaver两个数据库中各建立一张表
CREATE TABLE t_shop (
id INT PRIMARY KEY,
shop_name CHAR(64) NOT NULL
);
# 向master库中插入一条记录
INSERT INTO t_shop(id, shop_name) VALUES (1, 'MasterShop');
# 向slaver库中插入一条记录
INSERT INTO t_shop(id, shop_name) VALUES (1, 'SlaverShop');
Shop实例类
package com.example.springboot.multidatasource.domain;
/**
* Author: 王俊超
* Date: 2017-04-09 08:58
* All Rights Reserved !!!
*/
public class Shop {
private int id;
private String shopName;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getShopName() {
return shopName;
}
public void setShopName(String shopName) {
this.shopName = shopName;
}
@Override
public String toString() {
return "Shop{" +
"id=" + id +
", shopName='" + shopName + '\'' +
'}';
}
}
DataSourceEnum
作用:列举数据源的key
package com.example.springboot.multidatasource.common;
/**
* Author: 王俊超
* Date: 2017-04-09 08:30
* All Rights Reserved !!!
*/
public enum DataSourceEnum {
master,slaver;
}
DataSourceContextHolder
作用:构建一个DataSourceEnum容器,并提供了向其中设置和获取DataSorceEnum的方法
package com.example.springboot.multidatasource.common;
/**
* Author: 王俊超
* Date: 2017-04-09 08:30
* All Rights Reserved !!!
*/
public class DataSourceContextHolder {
private static final ThreadLocal<DataSourceEnum> CONTEXT_HOLDER = new ThreadLocal<DataSourceEnum>() {
@Override
protected DataSourceEnum initialValue() {
return DataSourceEnum.master;
}
};
public static void setDataSourceType(DataSourceEnum type) {
CONTEXT_HOLDER.set(type);
}
public static DataSourceEnum getDataSourceType() {
return CONTEXT_HOLDER.get();
}
public static void resetDataSourceType() {
CONTEXT_HOLDER.set(DataSourceEnum.master);
}
}
DynamicDataSource
作用:使用DatabaseContextHolder获取当前线程的DataSoureEnum
package com.example.springboot.multidatasource.common;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* Author: 王俊超
* Date: 2017-04-09 08:30
* All Rights Reserved !!!
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}
MyBatisConfig
通过读取application.properties文件生成两个数据源(masterDataSource,slaverDataSource)
使用以上生成的两个数据源构造动态数据源dataSource
@Primary:指定在同一个接口有多个实现类可以注入的时候,默认选择哪一个,而不是让@Autowire注解报错(一般用于多数据源的情况下)
@Qualifier:指定名称的注入,当一个接口有多个实现类的时候使用(在本例中,有三个DataSource类型的实例,DynamicDataSource也是一种DataSource,需要指定名称注入)
@Bean:生成的bean实例的名称是方法名(例如上边的@Qualifier注解中使用的名称是前边两个数据源的方法名,而这两个数据源也是使用@Bean注解进行注入的)
通过动态数据源构造SqlSessionFactory和事务管理器(如果不需要事务,后者可以去掉)
package com.example.springboot.multidatasource.config;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import com.example.springboot.multidatasource.common.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* Author: 王俊超
* Date: 2017-04-09 08:30
* All Rights Reserved !!!
*/
@Configuration
@MapperScan(basePackages = "com.example.springboot.multidatasource.mapper")
public class MyBatisConfig {
/**
* @return
* @throws Exception
* @Primary 该注解表示在同一个接口有多个实现类可以注入的时候,默认选择哪一个,而不是让@autowire注解报错
*/
@Primary
@Bean("masterDataSource")
@ConfigurationProperties(prefix = "datasource.master")
public DataSource masterDataSource() throws Exception {
return DataSourceBuilder.create().build();
}
@Bean("slaverDataSource")
@ConfigurationProperties(prefix = "datasource.slaver")
public DataSource slaverDataSource() throws Exception {
return DataSourceBuilder.create().build();
}
/**
* @Qualifier 根据名称进行注入,通常是在具有相同的多个类型的实例的一个注入(例如有多个DataSource类型的实例)
*/
@Bean("dynamicDataSource")
public DynamicDataSource dynamicDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slaverDataSource") DataSource slaverDataSource) {
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
targetDataSources.put(DataSourceEnum.master, masterDataSource);
targetDataSources.put(DataSourceEnum.slaver, slaverDataSource);
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
dataSource.setDefaultTargetDataSource(masterDataSource);// 默认的datasource设置为myTestDbDataSource
return dataSource;
}
/**
* 根据数据源创建SqlSessionFactory
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dynamicDataSource,
@Value("mybatis.typeAliasesPackage") String typeAliasesPackage,
@Value("mybatis.mapperLocations") String mapperLocations) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dynamicDataSource);// 指定数据源(这个必须有,否则报错)
// 下边两句仅仅用于*.xml文件,如果整个持久层操作不需要使用到xml文件的话(只用注解就可以搞定),则不加
// factoryBean.setTypeAliasesPackage(typeAliasesPackage);// 指定基包
// factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));//
return factoryBean.getObject();
}
/**
* 配置事务管理器
*/
@Bean
public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
return new DataSourceTransactionManager(dataSource);
}
}
ShopMapper
数据映射文件
package com.example.springboot.multidatasource.mapper;
import com.example.springboot.multidatasource.domain.Shop;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
/**
* Author: 王俊超
* Date: 2017-04-09 08:58
* All Rights Reserved !!!
*/
public interface ShopMapper {
@Select("SELECT * FROM t_shop WHERE id = #{id}")
@Results(value = { @Result(id = true, column = "id", property = "id"),
@Result(column = "shop_name", property = "shopName") })
public Shop getShop(@Param("id") int id);
}
ShopDao
package com.example.springboot.multidatasource.service;
import com.example.springboot.multidatasource.annotation.DataSourceTypeAnno;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import com.example.springboot.multidatasource.dao.ShopDao;
import com.example.springboot.multidatasource.domain.Shop;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* Author: 王俊超
* Date: 2017-04-09 09:09
* All Rights Reserved !!!
*/
@Service("shopService")
public class ShopService {
@Autowired
private ShopDao dao;
public Shop getShop(int id) {
return dao.getShop(id);
}
public Shop getMasterShopByHandle(int id) {
return dao.getMasterShopByHandle(id);
}
public Shop getSlaverShopByHandle(int id) {
return dao.getSlaverShopByHandle(id);
}
@DataSourceTypeAnno(DataSourceEnum.master)
public Shop getMasterShopByAnnotation(int id) {
return dao.getMasterShopByAnnotation(id);
}
@DataSourceTypeAnno(DataSourceEnum.slaver)
public Shop getSlaverShopByAnnotation(int id) {
return dao.getSlaverShopByAnnotation(id);
}
}
ShopController
package com.example.springboot.multidatasource.controller;
import com.example.springboot.multidatasource.annotation.DataSourceTypeAnno;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import com.example.springboot.multidatasource.domain.Shop;
import com.example.springboot.multidatasource.service.ShopService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
/**
* Author: 王俊超
* Date: 2017-04-09 09:12
* All Rights Reserved !!!
*/
@RestController
public class ShopController {
@Autowired
private ShopService service;
@RequestMapping(value = "/getShop")
public Shop getShop(@RequestParam("id") int id) {
return service.getShop(id);
}
@RequestMapping(value = "/getMasterShopByHandle")
public Shop getMasterShopByHandle(int id) {
return service.getMasterShopByHandle(id);
}
@RequestMapping(value = "/getSlaverShopByHandle")
public Shop getSlaverShopByHandle(int id) {
return service.getSlaverShopByHandle(id);
}
@RequestMapping(value = "/getMasterShopByAnnotation")
@DataSourceTypeAnno(DataSourceEnum.master)
public Shop getMasterShopByAnnotation(int id) {
return service.getMasterShopByAnnotation(id);
}
@RequestMapping(value = "/getSlaverShopByAnnotation")
public Shop getSlaverShopByAnnotation(int id) {
return service.getSlaverShopByAnnotation(id);
}
}
MultiDataSourceApplication
程序启动入口
package com.example.springboot.multidatasource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
/**
* Author: 王俊超
* Date: 2017-04-09 09:14
* All Rights Reserved !!!
*/
@SpringBootApplication
@EnableAutoConfiguration
@EnableWebMvc
public class MultiDataSourceApplication {
public static void main(String[] args) {
SpringApplication.run(MultiDataSourceApplication.class, args);
}
}
DataSourceTypeAnno数据源类型注解
@Retention(RetentionPolicy.RUNTIME) 说是此注解在运行时可见
@Target(ElementType.METHOD) // 注解可以用在方法上
package com.example.springboot.multidatasource.annotation;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 数据源类型注解
*
* Author: 王俊超
* Date: 2017-04-09 09:50
* All Rights Reserved !!!
*/
@Retention(RetentionPolicy.RUNTIME) // 在运行时可见
@Target(ElementType.METHOD) // 注解可以用在方法上
public @interface DataSourceTypeAnno {
DataSourceEnum value() default DataSourceEnum.master;
}
DataSourceAspect 数据源切面
用于捕获使用数据源注解的方法,并且根据注解上的数据源类型进行切换
package com.example.springboot.multidatasource.aspect;
import com.example.springboot.multidatasource.annotation.DataSourceTypeAnno;
import com.example.springboot.multidatasource.common.DataSourceContextHolder;
import com.example.springboot.multidatasource.common.DataSourceEnum;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* Author: 王俊超
* Date: 2017-04-09 09:54
* All Rights Reserved !!!
*/
@Component
@Aspect
public class DataSourceAspect {
@Pointcut("execution(* com.example.springboot.multidatasource.dao..*(..)) " +
"&& @annotation(com.example.springboot.multidatasource.annotation.DataSourceTypeAnno)")
public void dataSourcePointcut() {
}
@Around("dataSourcePointcut()")
public Object doAround(ProceedingJoinPoint pjp) {
MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
Method method = methodSignature.getMethod();
DataSourceTypeAnno typeAnno = method.getAnnotation(DataSourceTypeAnno.class);
DataSourceEnum sourceEnum = typeAnno.value();
if (sourceEnum == DataSourceEnum.master) {
DataSourceContextHolder.setDataSourceType(DataSourceEnum.master);
} else if (sourceEnum == DataSourceEnum.slaver) {
DataSourceContextHolder.setDataSourceType(DataSourceEnum.slaver);
}
Object result = null;
try {
result = pjp.proceed();
} catch (Throwable throwable) {
throwable.printStackTrace();
} finally {
DataSourceContextHolder.resetDataSourceType();
}
return result;
}
}