1.准备数据库
既然是动态数据源,我们就需要准备多个库,我这里在本地建了三个数据库,分别为test、test1和test2。test库中创建表t_user,test1库中创建t_company,test2库中创建表t_dept。建表脚本如下:
-- test库
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) NOT NULL COMMENT '姓名',
`password` varchar(50) NOT NULL COMMENT '密码',
`phone` varchar(32) NOT NULL COMMENT '联系方式',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
-- test1库
CREATE TABLE `t_company` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`companyName` varchar(255) DEFAULT NULL COMMENT '公司名称',
`companyAddress` varchar(255) DEFAULT NULL COMMENT '公司编码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- test2库
CREATE TABLE `t_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`deptName` varchar(255) DEFAULT NULL COMMENT '名称',
`deptNo` varchar(20) DEFAULT NULL COMMENT '编码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.创建springboot2.1.5项目
pom.xml如下:
<?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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5.RELEASE</version>
<relativePath/>
</parent>
<groupId>com.hyc</groupId>
<artifactId>sb-multids</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sb-multids</name>
<description>SpringBoot+动态数据源</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.使用mybatis-generator生成相关代码
实体类:
/**
* Company.java
*/
package com.hyc.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Company {
private Integer id;
private String companyname;
private String companyaddress;
}
/**
* Dept.java
*/
package com.hyc.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
private Integer id;
private String deptname;
private String deptno;
}
/**
* User.java
*/
package com.hyc.entity;
import lombok.*;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String name;
private String password;
private String phone;
}
dao接口:
/**
* CompanyMapper.java
*/
package com.hyc.dao;
import com.hyc.entity.Company;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface CompanyMapper {
int deleteByPrimaryKey(Integer id);
int insert(Company record);
int insertSelective(Company record);
Company selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Company record);
int updateByPrimaryKey(Company record);
}
/**
* DeptMapper.java
*/
package com.hyc.dao;
import com.hyc.entity.Dept;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface DeptMapper {
int deleteByPrimaryKey(Integer id);
int insert(Dept record);
int insertSelective(Dept record);
Dept selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Dept record);
int updateByPrimaryKey(Dept record);
}
/**
* DeptMapper.java
*/
package com.hyc.dao;
import com.hyc.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper {
int deleteByPrimaryKey(Integer id);
int insert(User record);
int insertSelective(User record);
User selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(User record);
int updateByPrimaryKey(User record);
}
xml文件:
<!-- CompanyMapper.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.hyc.dao.CompanyMapper" >
<resultMap id="BaseResultMap" type="com.hyc.entity.Company" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="companyName" property="companyname" jdbcType="VARCHAR" />
<result column="companyAddress" property="companyaddress" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, companyName, companyAddress
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from t_company
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from t_company
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.hyc.entity.Company" useGeneratedKeys="true" keyProperty="id" >
insert into t_company (companyName, companyAddress)
values (#{companyname,jdbcType=VARCHAR}, #{companyaddress,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.hyc.entity.Company" useGeneratedKeys="true" keyProperty="id" >
insert into t_company
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="companyname != null" >
companyName,
</if>
<if test="companyaddress != null" >
companyAddress,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="companyname != null" >
#{companyname,jdbcType=VARCHAR},
</if>
<if test="companyaddress != null" >
#{companyaddress,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.hyc.entity.Company" >
update t_company
<set >
<if test="companyname != null" >
companyName = #{companyname,jdbcType=VARCHAR},
</if>
<if test="companyaddress != null" >
companyAddress = #{companyaddress,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.hyc.entity.Company" >
update t_company
set companyName = #{companyname,jdbcType=VARCHAR},
companyAddress = #{companyaddress,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
<!-- DeptMapper.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.hyc.dao.DeptMapper" >
<resultMap id="BaseResultMap" type="com.hyc.entity.Dept" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="deptName" property="deptname" jdbcType="VARCHAR" />
<result column="deptNo" property="deptno" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, deptName, deptNo
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from t_dept
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from t_dept
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.hyc.entity.Dept" useGeneratedKeys="true" keyProperty="id" >
insert into t_dept (deptName, deptNo)
values (#{deptname,jdbcType=VARCHAR}, #{deptno,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.hyc.entity.Dept" useGeneratedKeys="true" keyProperty="id" >
insert into t_dept
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="deptname != null" >
deptName,
</if>
<if test="deptno != null" >
deptNo,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="deptname != null" >
#{deptname,jdbcType=VARCHAR},
</if>
<if test="deptno != null" >
#{deptno,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.hyc.entity.Dept" >
update t_dept
<set >
<if test="deptname != null" >
deptName = #{deptname,jdbcType=VARCHAR},
</if>
<if test="deptno != null" >
deptNo = #{deptno,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.hyc.entity.Dept" >
update t_dept
set deptName = #{deptname,jdbcType=VARCHAR},
deptNo = #{deptno,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
<!-- UserMapper.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.hyc.dao.UserMapper" >
<resultMap id="BaseResultMap" type="com.hyc.entity.User" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="phone" property="phone" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, name, password, phone
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from t_user
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from t_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.hyc.entity.User" useGeneratedKeys="true" keyProperty="id" >
insert into t_user (name, password, phone
)
values (#{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{phone,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="com.hyc.entity.User" useGeneratedKeys="true" keyProperty="id" >
insert into t_user
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="name != null" >
name,
</if>
<if test="password != null" >
password,
</if>
<if test="phone != null" >
phone,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="password != null" >
#{password,jdbcType=VARCHAR},
</if>
<if test="phone != null" >
#{phone,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.hyc.entity.User" >
update t_user
<set >
<if test="name != null" >
name = #{name,jdbcType=VARCHAR},
</if>
<if test="password != null" >
password = #{password,jdbcType=VARCHAR},
</if>
<if test="phone != null" >
phone = #{phone,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.hyc.entity.User" >
update t_user
set name = #{name,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
phone = #{phone,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
4.编写动态数据源相关组件
- 编写动态数据源实现类
package com.hyc.util;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 查询数据库前动态获取DataSource
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
该类继承了AbstractRoutingDataSource,并实现了determineCurrentLookupKey()方法
- 编写动态数据源上下文管理类
package com.hyc.util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.ArrayList; import java.util.List; /** * @Description 动态数据源上下文管理 */ public class DynamicDataSourceContextHolder { private Logger logger = LoggerFactory.getLogger(getClass()); //存放当前线程使用的数据源类型信息 private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>(); //存放数据源id public static List<String> dataSourceIds = new ArrayList<String>(); //设置数据源 public static void setDataSourceType(String dataSourceType) { contextHolder.set(dataSourceType); } //获取数据源 public static String getDataSourceType() { return contextHolder.get(); } //清除数据源 public static void clearDataSourceType() { contextHolder.remove(); } //判断当前数据源是否存在 public static boolean isContainsDataSource(String dataSourceId) { return dataSourceIds.contains(dataSourceId); } }
该类使用dataSourceIds变量存储了系统配置的所有的数据源的名称,如:ds1,ds2等,还使用ThreadLocal类型的变量保存了当前使用的数据源的名称,可以设置、获取和清除ThreadLocal变量中保存的值等
-
编写DynamicDataSourceRegister类
package com.hyc.util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.MutablePropertyValues; import org.springframework.beans.factory.support.BeanDefinitionRegistry; import org.springframework.beans.factory.support.GenericBeanDefinition; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.EnvironmentAware; import org.springframework.context.annotation.ImportBeanDefinitionRegistrar; import org.springframework.core.env.Environment; import org.springframework.core.type.AnnotationMetadata; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; /** * @Description 注册动态数据源 * 初始化数据源和提供了执行动态切换数据源的工具类 * EnvironmentAware(获取配置文件配置的属性值) */ public class DynamicDataSourceRegister implements ImportBeanDefinitionRegistrar, EnvironmentAware { private Logger logger = LoggerFactory.getLogger(getClass()); //指定默认数据源(springboot2.0默认数据源是hikari如何想使用其他数据源可以自己配置) private static final String DATASOURCE_TYPE_DEFAULT = "com.zaxxer.hikari.HikariDataSource"; //默认数据源 private DataSource defaultDataSource; //用户自定义数据源 private Map<String, DataSource> slaveDataSources = new HashMap<>(); @Override public void setEnvironment(Environment environment) { initDefaultDataSource(environment); initslaveDataSources(environment); } private void initDefaultDataSource(Environment env) { // 读取主数据源 Map<String, Object> dsMap = new HashMap<>(); //dsMap.put("driver", env.getProperty("spring.datasource.driver")); dsMap.put("url", env.getProperty("spring.datasource.url")); dsMap.put("username", env.getProperty("spring.datasource.username")); dsMap.put("password", env.getProperty("spring.datasource.password")); defaultDataSource = buildDataSource(dsMap); } private void initslaveDataSources(Environment env) { // 读取配置文件获取更多数据源 String dsPrefixs = env.getProperty("slave.datasource.names"); for (String dsPrefix : dsPrefixs.split(",")) { // 多个数据源 Map<String, Object> dsMap = new HashMap<>(); //dsMap.put("driver", env.getProperty("slave.datasource." + dsPrefix + ".driver")); dsMap.put("url", env.getProperty("slave.datasource." + dsPrefix + ".url")); dsMap.put("username", env.getProperty("slave.datasource." + dsPrefix + ".username")); dsMap.put("password", env.getProperty("slave.datasource." + dsPrefix + ".password")); DataSource ds = buildDataSource(dsMap); slaveDataSources.put(dsPrefix, ds); } } @Override public void registerBeanDefinitions(AnnotationMetadata annotationMetadata, BeanDefinitionRegistry beanDefinitionRegistry) { Map<Object, Object> targetDataSources = new HashMap<Object, Object>(); //添加默认数据源 targetDataSources.put("dataSource", this.defaultDataSource); DynamicDataSourceContextHolder.dataSourceIds.add("dataSource"); //添加其他数据源 targetDataSources.putAll(slaveDataSources); for (String key : slaveDataSources.keySet()) { DynamicDataSourceContextHolder.dataSourceIds.add(key); } //创建DynamicDataSource GenericBeanDefinition beanDefinition = new GenericBeanDefinition(); beanDefinition.setBeanClass(DynamicDataSource.class); beanDefinition.setSynthetic(true); MutablePropertyValues mpv = beanDefinition.getPropertyValues(); mpv.addPropertyValue("defaultTargetDataSource", defaultDataSource); mpv.addPropertyValue("targetDataSources", targetDataSources); //注册 - BeanDefinitionRegistry beanDefinitionRegistry.registerBeanDefinition("dataSource", beanDefinition); logger.info("Dynamic DataSource Registry"); } public DataSource buildDataSource(Map<String, Object> dataSourceMap) { Object type = dataSourceMap.get("type"); if (type == null) { type = DATASOURCE_TYPE_DEFAULT;// 默认DataSource } try { Class<? extends DataSource> dataSourceType; dataSourceType = (Class<? extends DataSource>) Class.forName((String) type); //String driverClassName = dataSourceMap.get("driver").toString(); String url = dataSourceMap.get("url").toString(); String username = dataSourceMap.get("username").toString(); String password = dataSourceMap.get("password").toString(); // 自定义DataSource配置 DataSourceBuilder factory = DataSourceBuilder.create().url(url).username(username).password(password).type(dataSourceType); return factory.build(); } catch (ClassNotFoundException e) { logger.error("构建数据源" + type + "出错", e); } return null; } }
这个类实现了ImportBeanDefinitionRegistrar接口,用于向容器中添加Bean。该类在容器启动时根据配置文件创建默认数据源并保存到DataSource类型的对象defaultDataSource中,创建其他数据源保存到Map<String, DataSource>类型对象slaveDataSources中,key为数据源名称,如ds1,ds2等。registerBeanDefinitions()方法添加DynamicDataSource类型的bean到容器中并命名为dataSource,设置defaultTargetDataSource属性和targetDataSources属性。
-
编写自定义数据源注解
package com.hyc.util; import java.lang.annotation.*; /** * 数据源注解 * * @Description 作用于类、接口或者方法上 */ @Target({ElementType.TYPE, ElementType.METHOD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface TargetDataSource { String name(); }
这个注解可以标注在方法上,name属性即数据源名称
-
编写service方法切面类
package com.hyc.util; 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.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.core.annotation.Order; import org.springframework.stereotype.Component; /** * @Description 动态数据源通知 */ @Aspect @Order(-1)//保证在@Transactional之前执行 @Component public class DynamicDataSourceAspect { private Logger logger = LoggerFactory.getLogger(getClass()); //改变数据源 @Before("@annotation(targetDataSource)") public void changeDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) { String dbid = targetDataSource.name(); if (!DynamicDataSourceContextHolder.isContainsDataSource(dbid)) { logger.info("数据源 " + dbid + " 不存在使用默认的数据源 -> " + joinPoint.getSignature()); } else { logger.info("使用数据源:" + dbid); DynamicDataSourceContextHolder.setDataSourceType(dbid); } } @After("@annotation(targetDataSource)") public void clearDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) { logger.info("清除数据源:" + targetDataSource.name()); DynamicDataSourceContextHolder.clearDataSourceType(); } }
该切面类拦截包含TargetDataSource注解的方法,取出name属性,方法执行前,设置当前线程的数据源保存在ThreadLocal变量中,以便后面取得数据源名称;方法执行后清除ThreadLocal变量中的值。
5.编写主配置类
package com.hyc.config;
import com.hyc.util.DynamicDataSourceRegister;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
@Configuration
@Import(DynamicDataSourceRegister.class)
public class AppConfig {
}
该类的主要作用只是引入DynamicDataSourceRegister用于为容器中添加Bean。
6.编写controller类和service类
在service类调用dao类的方法上标注自定义的数据源注解,指定要操作的数据源
/**
* DataController.java
*/
package com.hyc.controller;
import com.alibaba.fastjson.JSON;
import com.hyc.entity.Company;
import com.hyc.entity.Dept;
import com.hyc.entity.User;
import com.hyc.service.DataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.Map;
@RestController
public class DataController {
@Autowired
private DataService dataService;
@GetMapping("user/{id}")
public String findUserById(@PathVariable Integer id) {
User user = dataService.findUserById(id);
return JSON.toJSONString(user);
}
@GetMapping("dept/{id}")
public String findDeptById(@PathVariable Integer id) {
Dept dept = dataService.findDeptById(id);
return JSON.toJSONString(dept);
}
@GetMapping("company/{id}")
public String findCompanyById(@PathVariable Integer id) {
Company company = dataService.findCompanyById(id);
return JSON.toJSONString(company);
}
@GetMapping("all/{id}")
public String findAllById(@PathVariable Integer id) {
Company company = dataService.findCompanyById(id);
User user = dataService.findUserById(id);
Dept dept = dataService.findDeptById(id);
Map<String,Object> dataMap = new HashMap<>();
dataMap.put("user",user);
dataMap.put("dept",dept);
dataMap.put("company",company);
return JSON.toJSONString(dataMap);
}
}
/**
* DataService.java
*/
package com.hyc.service;
import com.hyc.dao.CompanyMapper;
import com.hyc.dao.DeptMapper;
import com.hyc.dao.UserMapper;
import com.hyc.entity.Company;
import com.hyc.entity.Dept;
import com.hyc.entity.User;
import com.hyc.util.TargetDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class DataService {
private Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
private UserMapper userMapper;
@Autowired
private DeptMapper deptMapper;
@Autowired
private CompanyMapper companyMapper;
public User findUserById(Integer id) {
logger.info("在主数据源上查询User信息");
return userMapper.selectByPrimaryKey(id);
}
@TargetDataSource(name = "ds1")
public Company findCompanyById(Integer id) {
logger.info("在ds1数据源上查询Company信息");
return companyMapper.selectByPrimaryKey(id);
}
@TargetDataSource(name = "ds2")
public Dept findDeptById(Integer id) {
logger.info("在ds2数据源上查询Dept信息");
return deptMapper.selectByPrimaryKey(id);
}
}
7.编写配置文件:application.properties
#主数据源
#spring.datasource.driver=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
#ds1,ds2 其他两个数据源
slave.datasource.names=ds1,ds2
#ds1
#slave.datasource.ds1.driver=com.mysql.jdbc.Driver
slave.datasource.ds1.url=jdbc:mysql://localhost:3306/test1?useSSL=false
slave.datasource.ds1.username=root
slave.datasource.ds1.password=123456
#ds2
#slave.datasource.ds2.driver=com.mysql.jdbc.Driver
slave.datasource.ds2.url=jdbc:mysql://localhost:3306/test2?useSSL=false
slave.datasource.ds2.username=root
slave.datasource.ds2.password=123456
#mybatis配置
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.type-aliases-package=com.hyc.entity
mybatis.mapper-locations=classpath:mappers/*.xml
#sql日志
logging.level.com.hyc.dao=debug
8.工程架构图
9.运行测试
分别从test、test1和test2库中查询了相关数据。
10.原理分析
DynamicDataSource类继承关系如图:
mybatis执行查询方法时会在适当时机调用名称为dataSource的Bean的getConnection()方法,即DynamicDataSource类Bean的getConnection()方法,然后调到父类AbstractRoutingDataSource中去。我们来看下这个类的实现
package org.springframework.jdbc.datasource.lookup;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
@Nullable
private Map<Object, Object> targetDataSources;
@Nullable
private Object defaultTargetDataSource;
private boolean lenientFallback = true;
private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
@Nullable
private Map<Object, DataSource> resolvedDataSources;
@Nullable
private DataSource resolvedDefaultDataSource;
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
}
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
this.defaultTargetDataSource = defaultTargetDataSource;
}
public void setLenientFallback(boolean lenientFallback) {
this.lenientFallback = lenientFallback;
}
public void setDataSourceLookup(@Nullable DataSourceLookup dataSourceLookup) {
this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
}
// 该类实现了InitializingBean接口容器启动时调用该方法
@Override
public void afterPropertiesSet() {
// 判断dataSource中的targetDataSources是否为空,为空则抛异常
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
}
// 新建Map类型对象resolvedDataSources来保存targetDataSources属性
this.resolvedDataSources = new HashMap<>(this.targetDataSources.size());
this.targetDataSources.forEach((key, value) -> {
// key实际上就使targetDataSources中的key即数据源的名称,如:ds1等,而值实际上是targetDataSources中的值
Object lookupKey = resolveSpecifiedLookupKey(key);
DataSource dataSource = resolveSpecifiedDataSource(value);
// 放到Map中
this.resolvedDataSources.put(lookupKey, dataSource);
});
// 将defaultTargetDataSource的值用resolvedDefaultDataSource保存
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
protected Object resolveSpecifiedLookupKey(Object lookupKey) {
return lookupKey;
}
protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
if (dataSource instanceof DataSource) {
return (DataSource) dataSource;
}
else if (dataSource instanceof String) {
return this.dataSourceLookup.getDataSource((String) dataSource);
}
else {
throw new IllegalArgumentException(
"Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
}
}
// mybatis在执行查询时会在适当时机调用该方法以获取数据库连接
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}
@Override
@SuppressWarnings("unchecked")
public <T> T unwrap(Class<T> iface) throws SQLException {
if (iface.isInstance(this)) {
return (T) this;
}
return determineTargetDataSource().unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface));
}
// 获取动态数据源
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
// 调用子类实现determineCurrentLookupKey的方法获取数据源名称
Object lookupKey = determineCurrentLookupKey();
// 根据名称从Map中取得dataSource
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
// dataSource为空时,取得默认数据源对象resolvedDefaultDataSource
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
// 若dataSource依然为空则抛异常
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
// 模板方法给子类实现,在我们的实现类DynamicDataSource中,
// 从TheadLocal中获取方法执行切面类Before拦截时保存的数据源名称:DynamicDataSourceContextHolder.getDataSourceType()
@Nullable
protected abstract Object determineCurrentLookupKey();
}
mybatis源码这里就不多说了,看下调式截图
BaseExecutor类的getConnection()方法即MyBatis获取数据源连接的方法,可以看到使用的数据源即DynamicDataSource类的实例
项目源代码已上传到github,地址:https://github.com/hycqqzj/projects/tree/master/sb-multids
思考:
1.上述例子中自定义数据源注解TargetDataSource是标注在service层方法上的(也可以加在dao层方法上),如果要把注解加在dao类上,即对该dao的每个方法都使用类上标注的数据源,应该怎么做?
解答:
- 确保注解类TargetDataSource可以标注在类上,即@Target({ElementType.TYPE, ElementType.METHOD})
- 修改类DynamicDattaSourceAspect定义
package com.hyc.util;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
/**
* @Description 动态数据源通知
*/
@Aspect
@Order(-1)//保证在@Transactional之前执行
@Component
public class DynamicDattaSourceAspect {
private Logger logger = LoggerFactory.getLogger(getClass());
//改变数据源
@Before("@within(targetDataSource)")
public void changeDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
String dbid = targetDataSource.name();
if (!DynamicDataSourceContextHolder.isContainsDataSource(dbid)) {
logger.info("数据源 " + dbid + " 不存在使用默认的数据源 -> " + joinPoint.getSignature());
} else {
logger.info("使用数据源:" + dbid);
DynamicDataSourceContextHolder.setDataSourceType(dbid);
}
}
@After("@within(targetDataSource)")
public void clearDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
logger.info("清除数据源:" + targetDataSource.name());
DynamicDataSourceContextHolder.clearDataSourceType();
}
}
注意@within注解的使用
- 在dao层如CompanyMapper等类上标注自定义数据源注解,如@TargetDataSource(name = "ds1")