有3个数据源
t100019、t100049、t100138
本文旨在使用springboot、AOP、AbstractRoutingDataSource实现多数据源的动态切换
1.导入数据源、aop等相关依赖
<properties>
<java.version>1.8</java.version>
<aspectjrt.version>1.9.1</aspectjrt.version>
<aspectjweaver.version>1.9.1</aspectjweaver.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</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-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.13</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.0.3.RELEASE</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>${aspectjrt.version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>${aspectjweaver.version}</version>
</dependency>
</dependencies>
2.配置文件配置如下
server.port=8889
spring.datasource.t100019.username=root
spring.datasource.t100019.password=root
spring.datasource.t100019.url=jdbc:mysql://127.0.0.1:3306/t100019?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.t100019.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.t100019.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.t100049.username=root
spring.datasource.t100049.password=root
spring.datasource.t100049.url=jdbc:mysql://127.0.0.1:3306/t100049?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.t100049.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.t100049.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.t100138.username=root
spring.datasource.t100138.password=root
spring.datasource.t100138.url=jdbc:mysql://127.0.0.1:3306/t100138?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.t100138.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.t100138.type=com.alibaba.druid.pool.DruidDataSource
3.准备数据库表
建3个数据库,分别是t100019,t100049,t100138;分别执行如下sql,创建hy_floors表。3个库可插入不同数量数据,以便于之后切换数据源查看差异
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for hy_floors
-- ----------------------------
DROP TABLE IF EXISTS `hy_floors`;
CREATE TABLE `hy_floors` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`project_id` bigint(0) NOT NULL COMMENT '项目编号',
`park_id` int(0) NOT NULL COMMENT '园区id',
`building_id` int(0) NOT NULL COMMENT '楼建筑物ID',
`name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称(同项目名称)',
`area_covered` float NULL DEFAULT NULL COMMENT '占地面积',
`floor_no` int(0) NULL DEFAULT NULL COMMENT '层序号',
`underground_number` int(0) NULL DEFAULT NULL COMMENT '地下层数(废弃)',
`owners` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '业主编号{JSON}',
`developers` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '开发商{JSON}',
`operators` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '运营商{JSON}',
`code` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编码(900101xxxxxx)',
`image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '平面图',
`description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
`deleteflag` int(0) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1209 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '建筑楼层表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of hy_floors
-- ----------------------------
INSERT INTO `hy_floors` VALUES (1, 123456789, 12, 212, 'B1', 5.6, 1, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (2, 123456789, 12, 212, 'B1', 5.3, 2, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (3, 123456789, 12, 212, 'B1', 3.2, 3, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (4, 123456789, 12, 212, 'B1', 3, 4, NULL, '', '', '', '90.90.48.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (5, 123456789, 12, 212, 'B1', 3, 5, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (6, 123456789, 12, 212, 'B1', 3, 6, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
INSERT INTO `hy_floors` VALUES (7, 123456789, 12, 212, 'B1', NULL, 7, NULL, '', '', '', '90.90.90.90.90.90', NULL, '', 0);
SET FOREIGN_KEY_CHECKS = 1;
4.写一个数据库名称的枚举类 DbTypeEnum
package com.example.dynamicdatasource.enums;
/**
*
* Title: DbTypeEnum
* Description: 数据库枚举类
*/
public enum DbTypeEnum {
T100019("t100019"), T100049("t100049"), T100138("t100138");
private String value;
DbTypeEnum(final String value) {
this.value = value;
}
public String getValue() {
return value;
}
}
5.建立一个获得和设置上下文环境的类 DbContextHolder ,主要负责设置和获取上下文数据源的名称
package com.example.dynamicdatasource.config;
import com.example.dynamicdatasource.enums.DbTypeEnum;
public class DbContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal<>();
/**
* 设置数据源
*
* @param dbTypeEnum
*/
public static void setDbType(final DbTypeEnum dbTypeEnum) {
contextHolder.set(dbTypeEnum.getValue());
}
/**
* 取得当前数据源
*
* @return
*/
public static String getDbType() {
return (String) contextHolder.get();
}
/**
* 清除上下文数据
*/
public static void clearDbType() {
contextHolder.remove();
}
}
6.写一个动态数据源类,继承AbstractRoutingDataSource,并重写determineCurrentLookupKey()方法。返回值就是要路由的数据源的名称
package com.example.dynamicdatasource.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
/**
* 取得当前使用哪个数据源
*
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
return DbContextHolder.getDbType();
}
}
7.写数据源配置类DataSourceConfig
package com.example.dynamicdatasource.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.example.dynamicdatasource.enums.DbTypeEnum;
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.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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@MapperScan("com.example.dynamicdatasource.*.dao")
public class DataSourceConfig {
@Bean(name = "t19DataSource")
@ConfigurationProperties(prefix = "spring.datasource.t100019")
public DataSource t19DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "t49DataSource")
@ConfigurationProperties(prefix = "spring.datasource.t100049")
public DataSource t49DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean(name = "t138DataSource")
@ConfigurationProperties(prefix = "spring.datasource.t100138")
public DataSource t138DataSource() {
return DruidDataSourceBuilder.create().build();
}
/**
* 动态数据源配置
*
* @return
*/
@Bean("multipleDataSource")
@Primary
public DynamicDataSource multipleDataSource(@Qualifier("t19DataSource") final DataSource t19DataSource,
@Qualifier("t49DataSource") final DataSource t49DataSource,
@Qualifier("t138DataSource") final DataSource t138DataSource) {
final DynamicDataSource dynamicDataSource = new DynamicDataSource();
final Map<Object, Object> targetDataSources = new HashMap<>(3);
targetDataSources.put(DbTypeEnum.T100019.getValue(), t19DataSource);
targetDataSources.put(DbTypeEnum.T100049.getValue(), t49DataSource);
targetDataSources.put(DbTypeEnum.T100138.getValue(), t138DataSource);
dynamicDataSource.setTargetDataSources(targetDataSources);
dynamicDataSource.setDefaultTargetDataSource(t19DataSource);
return dynamicDataSource;
}
@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(multipleDataSource(t19DataSource(), t49DataSource(), t138DataSource()));
//设置mapperLocation
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*/*Mapper.xml"));
//设置开启驼峰匹配
sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return sqlSessionFactoryBean.getObject();
}
@Bean("transactionManager")
public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("multipleDataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
}
8.AOP写数据源动态切换切面
package com.example.dynamicdatasource.aop;
import com.example.dynamicdatasource.config.DbContextHolder;
import com.example.dynamicdatasource.enums.DbTypeEnum;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
@Component
@Aspect
public class DataSourceSwitchAspect {
@Pointcut("execution(* com.example.dynamicdatasource.t100019.dao..*.*(..))")
private void t19Aspect() {
}
//切入点
@Pointcut("execution(* com.example.dynamicdatasource.t100049.dao..*.*(..))")
private void t49dbDictAspect() {
}
@Pointcut("execution(* com.example.dynamicdatasource.t100138.dao..*.*(..))")
private void t138dbPartnerAspect() {
}
//前置通知,调对应路径下方法时拦截并设置数据源
@Before("t19Aspect()")
public void t19() {
DbContextHolder.setDbType(DbTypeEnum.T100019);
}
@Before("t49dbDictAspect()")
public void t49() {
DbContextHolder.setDbType(DbTypeEnum.T100049);
}
@Before("t138dbPartnerAspect()")
public void t138() {
DbContextHolder.setDbType(DbTypeEnum.T100138);
}
}
9.为方便测试,写一个最简单的SSM。t100019包下的SSM代码 controller层,t100049和t100138如是。
package com.example.dynamicdatasource.t100019.controller;
import com.example.dynamicdatasource.t100019.service.T19Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class T19Controller {
@Autowired
private T19Service t19Service;
@GetMapping("/test/19")
public Integer getCount() {
return t19Service.getCount();
}
}
service层
package com.example.dynamicdatasource.t100019.service;
public interface T19Service {
Integer getCount();
}
package com.example.dynamicdatasource.t100019.service.impl;
import com.example.dynamicdatasource.t100019.dao.T19Dao;
import com.example.dynamicdatasource.t100019.service.T19Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class T19ServiceImpl implements T19Service {
@Autowired
private T19Dao t19Dao;
@Override
public Integer getCount() {
return t19Dao.getCount();
}
}
dao层
package com.example.dynamicdatasource.t100019.dao;
public interface T19Dao {
Integer getCount();
}
mapper
<?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.example.dynamicdatasource.t100019.dao.T19Dao">
<select id="getCount" resultType="java.lang.Integer">
SELECT COUNT(id) FROM `hy_floors`
</select>
</mapper>
t100049和t100138包下代码与t100019代码相同,只是名称第一不同。整体代码结构如下
测试:
t100019库
t100049库
t100138库
项目源码地址:
https://github.com/sidney189/dynamicDataSource.git
本质:
AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能在运行时, 根据某个key值来动态切换到真正的DataSource上。