一、不同连接少量库切换
不同连接的不同库和表,库名已知且数量少,表名不一致且不能直接获取
1. 数据库
database -> user_one -> t_user_one
id name
1 张三
database -> user_two -> t_user_two
id name
2 李四
2. 项目目录
3. pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>
4. application.yml
spring:
datasource:
dynamic:
# 设置默认的数据源或者数据源组,默认值即为 master
primary: master
# 严格匹配数据源,默认false. true -> 未匹配到指定数据源时抛异常 ; false -> 使用默认数据源
strict: false
datasource:
# 默认的数据源
master:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/user_one?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified
username: postgres
password: postgres
slave_1:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/user_two?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified
username: postgres
password: postgres
mybatis-plus:
global-config:
db-config:
id-type: input
db-column-underline: true
refresh-mapper: true
mapper-locations: classpath:/mapper/**/*.xml
type-aliases-package: com.cnbai.*.*
configuration:
map-underscore-to-camel-case: true
call-setters-on-nulls: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
5. 创建测试类
TestController
package com.cnbai.controller;
import com.cnbai.service.UserOneService;
import com.cnbai.service.UserTwoService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
@RestController
public class TestController {
@Resource
private UserOneService userOneService;
@Resource
private UserTwoService userTwoService;
/**
* 获取 user_one -> t_user_one 表数据
*/
@RequestMapping("userOne")
public String userOne() {
// [User{id=1,name='张三'}]
return userOneService.list().toString();
}
/**
* 获取 user_two -> t_user_two 表数据
*/
@RequestMapping("userTwo")
public String userTwo() {
// [User{id=2,name='李四'}]
return userTwoService.list().toString();
}
}
6. 创建 Mapper
UserOneMapper
package com.cnbai.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cnbai.entity.UserOne;
import org.springframework.stereotype.Repository;
/**
* user_one
*/
@Repository
public interface UserOneMapper extends BaseMapper<UserOne> {
}
UserTwoMapper
package com.cnbai.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cnbai.entity.UserTwo;
import org.springframework.stereotype.Repository;
/**
* user_two
*/
@Repository
public interface UserTwoMapper extends BaseMapper<UserTwo> {
}
7. 创建实体类
UserOne
package com.cnbai.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
@TableName(value = "t_user_one")
public class UserOne implements Serializable {
@TableId(value = "id", type = IdType.INPUT)
private Integer id;
@TableField(value = "name")
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "UserOne{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
UserTwo
package com.cnbai.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
@TableName(value = "t_user_two")
public class UserTwo implements Serializable {
@TableId(value = "id", type = IdType.INPUT)
private Integer id;
@TableField(value = "name")
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "UserTwo{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
8. 创建 Service
UserOneService
package com.cnbai.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.cnbai.entity.UserOne;
/**
* user_one
*/
public interface UserOneService extends IService<UserOne> {
}
UserTwoService
package com.cnbai.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.cnbai.entity.UserTwo;
/**
* user_two
*/
public interface UserTwoService extends IService<UserTwo> {
}
UserOneServiceImpl
package com.cnbai.service.impl;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cnbai.dao.UserOneMapper;
import com.cnbai.entity.UserOne;
import com.cnbai.service.UserOneService;
import org.springframework.stereotype.Service;
/**
* 指定 yml 中 master 对应的数据库
*/
@DS("master")
@Service
public class UserOneServiceImpl extends ServiceImpl<UserOneMapper, UserOne> implements UserOneService {
}
UserTwoServiceImpl
package com.cnbai.service.impl;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cnbai.dao.UserTwoMapper;
import com.cnbai.entity.UserTwo;
import com.cnbai.service.UserTwoService;
import org.springframework.stereotype.Service;
/**
* 指定 yml 中 slave_1 对应的数据库
*/
@DS("slave_1")
@Service
public class UserTwoServiceImpl extends ServiceImpl<UserTwoMapper, UserTwo> implements UserTwoService {
}
9. 创建启动类
application
package com.cnbai;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class application {
public static void main(String[] args) {
SpringApplication.run(application.class, args);
}
}
二、同连接少量库切换
相同连接的不同库和表,库名已知且数量少,表名不一致且能直接获取(根据一定规律拼接成的)
实现方法也可和《不同连接少量库切换》一致,此处介绍另一种实现方法
1. 数据库
database -> user_one -> t_user_one
id name
1 张三
database -> user_two -> t_user_two
id name
2 李四
database -> user_three -> t_user_three
id name
3 王五
2. 项目目录
3. pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.8.1</version>
</dependency>
4. application.yml
spring:
datasource:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/user_one?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified
username: postgres
password: postgres
mybatis-plus:
global-config:
db-config:
id-type: input
db-column-underline: true
refresh-mapper: true
mapper-locations: classpath:/mapper/**/*.xml
type-aliases-package: com.cnbai.*.*
configuration:
map-underscore-to-camel-case: true
call-setters-on-nulls: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
5. 创建配置类
DataSourceConfig
package com.cnbai.config;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
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.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.Properties;
/**
* 数据源配置
*/
@Configuration
public class DataSourceConfig {
/**
* 创建默认数据源
*/
@Bean(name = "hikariConfig")
@ConfigurationProperties("spring.datasource")
public HikariConfig hikariConfig(Environment environment) {
return createHikariConfig(environment);
}
/**
* 配置默认数据源 -> user_one
*/
@Primary
@Bean(name = "defaultDataSource")
public DataSource defaultDataSource(@Qualifier(value = "hikariConfig") HikariConfig config) {
return new HikariDataSource(config);
}
/**
* 配置数据源 -> user_two
*/
@Bean(name = "userTwoDataSource")
public DataSource userTwoDataSource(Environment environment) {
HikariConfig config = createHikariConfig(environment);
String jdbcUrl = config.getJdbcUrl();
int start = jdbcUrl.lastIndexOf("/") + 1;
int end = jdbcUrl.lastIndexOf("?");
String dbName = jdbcUrl.substring(start, end);
config.setJdbcUrl(jdbcUrl.replace(dbName, "user_two"));
return new HikariDataSource(config);
}
/**
* 配置数据源 -> user_three
*/
@Bean(name = "userThreeDataSource")
public DataSource userThreeDataSource(Environment environment) {
HikariConfig config = createHikariConfig(environment);
String jdbcUrl = config.getJdbcUrl();
int start = jdbcUrl.lastIndexOf("/") + 1;
int end = jdbcUrl.lastIndexOf("?");
String dbName = jdbcUrl.substring(start, end);
config.setJdbcUrl(jdbcUrl.replace(dbName, "user_three"));
return new HikariDataSource(config);
}
/**
* 读取数据源配置
*/
private HikariConfig createHikariConfig(Environment environment) {
HikariConfig config = new HikariConfig();
config.setDriverClassName(environment.getProperty("spring.datasource.driver-class-name"));
config.setJdbcUrl(environment.getProperty("spring.datasource.url"));
config.setUsername(environment.getProperty("spring.datasource.username"));
config.setPassword(environment.getProperty("spring.datasource.password"));
Properties properties = new Properties();
properties.setProperty("defaultRowFetchSize", "10000");
config.setDataSourceProperties(properties);
return config;
}
/**
* 配置 Mybatis
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier(value = "defaultDataSource") DataSource dataSource
, @Qualifier(value = "mybatisPlusProperties") MybatisPlusProperties mybatisPlusProperties
, @Qualifier(value = "mybatisPlusInterceptor") MybatisPlusInterceptor mybatisPlusInterceptor) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(mybatisPlusProperties.resolveMapperLocations());
sqlSessionFactoryBean.setConfiguration(mybatisPlusProperties.getConfiguration());
sqlSessionFactoryBean.setTypeAliasesPackage(mybatisPlusProperties.getTypeAliasesPackage());
sqlSessionFactoryBean.setGlobalConfig(mybatisPlusProperties.getGlobalConfig());
sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);
return sqlSessionFactoryBean.getObject();
}
/**
* 事务管理
*/
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier(value = "defaultDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
MybatisPlusConfig
package com.cnbai.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
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;
/**
* Mybatis plus 配置
*/
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
return interceptor;
}
@Primary
@Bean
@ConfigurationProperties("mybatis-plus")
public MybatisPlusProperties mybatisPlusProperties() {
return new MybatisPlusProperties();
}
}
6. 创建 Service
CustomDataSourceService
package com.cnbai.service;
import org.apache.commons.dbutils.ResultSetHandler;
import javax.sql.DataSource;
public interface CustomDataSourceService {
Object[] insert(DataSource dataSource, String sql);
int update(DataSource dataSource, String sql);
<T> T findAll(DataSource dataSource, String sql, ResultSetHandler<T> resultType);
int delete(DataSource dataSource, String sql);
}
CustomDataSourceServiceImpl
package com.cnbai.service.impl;
import com.cnbai.service.CustomDataSourceService;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
@Service
public class CustomDataSourceServiceImpl implements CustomDataSourceService {
@Override
public Object[] insert(DataSource dataSource, String sql) {
QueryRunner runner = new QueryRunner(dataSource);
try {
return runner.insert(sql, new ArrayHandler());
} catch (Exception e) {
return new Object[0];
}
}
@Override
public int update(DataSource dataSource, String sql) {
QueryRunner runner = new QueryRunner(dataSource);
try {
return runner.update(sql);
} catch (Exception e) {
return 0;
}
}
@Override
public <T> T findAll(DataSource dataSource, String sql, ResultSetHandler<T> resultType) {
QueryRunner runner = new QueryRunner(dataSource);
try {
return runner.query(sql, resultType);
} catch (Exception e) {
return null;
}
}
@Override
public int delete(DataSource dataSource, String sql) {
QueryRunner runner = new QueryRunner(dataSource);
try {
return runner.execute(sql);
} catch (Exception e) {
return 0;
}
}
}
UserService
package com.cnbai.module.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.cnbai.module.entity.User;
public interface UserService extends IService<User> {
}
UserServiceImpl
package com.cnbai.module.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cnbai.module.dao.UserDao;
import com.cnbai.module.entity.User;
import com.cnbai.module.service.UserService;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {
}
7. 创建测试类
UserController
package com.cnbai.module.controller;
import com.cnbai.module.service.UserService;
import com.cnbai.service.CustomDataSourceService;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
@RestController
public class UserController {
@Resource
private UserService userService;
@Resource(name = "userTwoDataSource")
private DataSource userTwoDataSource;
@Resource(name = "userThreeDataSource")
private DataSource userThreeDataSource;
@Resource
private CustomDataSourceService customDataSourceService;
/**
* 获取 user_one -> t_user_one 表数据
*/
@RequestMapping("userOne")
public String userOne() {
// [User{id=1,name='张三'}]
return userService.list().toString();
}
/**
* 获取 user_two -> t_user_two 表数据
*/
@RequestMapping("userTwo")
public String userTwo() {
String tableName = "t_user_two";
String sql = "select * from %s where id = %s and name = '%s'";
List<Map<String, Object>> list = customDataSourceService.findAll(userTwoDataSource, String.format(sql, tableName, 2, "李四"), new MapListHandler());
// [{id=2,name=李四}]
return list.toString();
}
/**
* 获取 user_three -> t_user_three 表数据
*/
@RequestMapping("userThree")
public String userThree() {
String tableName = "t_user_three";
String sql = "select * from %s where id = %s";
List<Map<String, Object>> list = customDataSourceService.findAll(userThreeDataSource, String.format(sql, tableName, 3), new MapListHandler());
// [{id=3,name=王五}]
return list.toString();
}
/**
* 获取 user_three -> t_user_three 表数据量
*/
@RequestMapping("userThreeCount")
public String userThreeCount() {
String tableName = "t_user_three";
String sql = "select count(1) from %s";
Long count = customDataSourceService.findAll(userThreeDataSource, String.format(sql, tableName), new ScalarHandler<>());
return count.toString();
}
}
8. 创建 Mapper
UserDao
package com.cnbai.module.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cnbai.module.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserDao extends BaseMapper<User> {
}
9. 创建实体类
User
package com.cnbai.module.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
@TableName(value = "t_user_one")
public class User implements Serializable {
@TableId(value = "id", type = IdType.INPUT)
private Integer id;
@TableField(value = "name")
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
10. 创建启动类
Application
package com.cnbai;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
三、同连接动态库切换
相同连接的不同库和表,库名未知随业务增加,表名每个库相同
1. 数据库
database -> user -> t_user
id name
1 张三
database -> case_1 -> t_user
id name
2 李四
database -> case_2 -> t_user
id name
3 王五
2. 项目目录
3. pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.14.0</version>
</dependency>
4. application.yml
spring:
datasource:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/user?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified
username: postgres
password: postgres
mybatis-plus:
global-config:
db-config:
id-type: input
db-column-underline: true
refresh-mapper: true
mapper-locations: classpath:/mapper/**/*.xml
type-aliases-package: com.cnbai.*.*
configuration:
map-underscore-to-camel-case: true
call-setters-on-nulls: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
5. 创建配置类
DatabaseAop
package com.cnbai.aop;
import com.cnbai.config.DataSourceHolder;
import com.cnbai.config.DynamicDataSource;
import com.cnbai.utils.SpringUtils;
import org.apache.commons.lang3.ClassUtils;
import org.apache.commons.lang3.StringUtils;
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.annotation.Pointcut;
import org.aspectj.util.Reflection;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.util.Map;
/**
* 初始化数据源切面
*
* 扫描所有带 @TestDynamicDataSource(DataSource = "caseId") 注解的 dao,
* 没带注解表明只需要连接默认库,无需动态切换数据源
*/
@Aspect
@Component
public class DatabaseAop {
private static final String CASE_ANO_NAMES = "@org.apache.ibatis.annotations.Param(value=caseId)";
@Pointcut("execution(public * com.cnbai.dao.*.*(..))")
public void join() {
}
@Before("join()")
public void dbCheck(JoinPoint joinPoint) {
try {
Class<?> aClass = ClassUtils.getClass(joinPoint.getSignature().getDeclaringTypeName());
Annotation[] annotations = aClass.getAnnotations();
String caseIdValue = "";
for (int i = 0; i < annotations.length; i++) {
Annotation annotation = annotations[i];
if (annotation instanceof TestDynamicDataSource) {
// 根据自定义注解判断是否需要切换数据源的 dao
if ("caseId".equalsIgnoreCase(((TestDynamicDataSource) annotation).DataSource())) {
Method matchingMethod = Reflection.getMatchingMethod(aClass, joinPoint.getSignature().getName(), joinPoint.getArgs());
Annotation[][] methodAnnotations = matchingMethod.getParameterAnnotations();
if (methodAnnotations.length > 0) {
for (int j = 0; j < methodAnnotations.length; j++) {
Annotation[] parameterName = methodAnnotations[j];
for (int k = 0; k < parameterName.length; k++) {
Annotation methodAno = parameterName[k];
if (methodAno.toString().equalsIgnoreCase(CASE_ANO_NAMES)) {
Object[] args = joinPoint.getArgs();
caseIdValue = (String) args[j];
}
}
}
}
// 案件为空,使用默认连接
if (StringUtils.isBlank(caseIdValue)) {
DataSourceHolder.updateDataSource(caseIdValue);
} else {
// 不为空,切换数据源
DynamicDataSource dynamicDataSource = SpringUtils.getBean("dynamicDataSource", DynamicDataSource.class);
Map<Object, Object> targetDataSources = dynamicDataSource.getTargetDataSources();
if (!targetDataSources.containsKey(caseIdValue)) {
DataSource dataSource = dynamicDataSource.createDynamicDataSource(caseIdValue);
targetDataSources.put(caseIdValue, dataSource);
dynamicDataSource.setTargetDataSources(targetDataSources);
// 刷新配置使之生效
dynamicDataSource.afterPropertiesSet();
}
DataSourceHolder.updateDataSource(caseIdValue);
}
}
}
}
if (StringUtils.isBlank(caseIdValue)) {
DataSourceHolder.updateDataSource(caseIdValue);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
@After("join()")
public void afterDbCheck() {
DataSourceHolder.cleanDataSource();
}
}
TestDynamicDataSource
package com.cnbai.aop;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义动态数据源注解
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface TestDynamicDataSource {
String DataSource() default "user";
}
DataSourceConfig
package com.cnbai.config;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
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.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
/**
* 数据源配置
*/
@Configuration
public class DataSourceConfig {
/**
* 配置默认数据源
*/
@Bean(name = "hikariConfig")
@ConfigurationProperties("spring.datasource")
public HikariConfig hikariConfig(Environment environment) {
HikariConfig config = new HikariConfig();
config.setDriverClassName(environment.getProperty("spring.datasource.driver-class-name"));
config.setJdbcUrl(environment.getProperty("spring.datasource.url"));
config.setUsername(environment.getProperty("spring.datasource.username"));
config.setPassword(environment.getProperty("spring.datasource.password"));
Properties properties = new Properties();
properties.setProperty("defaultRowFetchSize", "10000");
config.setDataSourceProperties(properties);
return config;
}
/**
* 创建默认数据源
*/
@Primary
@Bean(name = "defaultDataSource")
public DataSource defaultDataSource(@Qualifier(value = "hikariConfig") HikariConfig config) {
return new HikariDataSource(config);
}
/**
* 配置动态数据源
*/
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource(@Qualifier(value = "defaultDataSource") DataSource dataSource) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map<Object, Object> dataSources = new HashMap<>();
dataSources.put("defaultDataSource", dataSource);
dynamicDataSource.setTargetDataSources(dataSources);
dynamicDataSource.setDefaultTargetDataSource(dataSource);
return dynamicDataSource;
}
/**
* 配置 Mybatis
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier(value = "dynamicDataSource") DataSource dataSource
, @Qualifier(value = "mybatisPlusProperties") MybatisPlusProperties mybatisPlusProperties
, @Qualifier(value = "mybatisPlusInterceptor") MybatisPlusInterceptor mybatisPlusInterceptor) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(mybatisPlusProperties.resolveMapperLocations());
sqlSessionFactoryBean.setConfiguration(mybatisPlusProperties.getConfiguration());
sqlSessionFactoryBean.setTypeAliasesPackage(mybatisPlusProperties.getTypeAliasesPackage());
sqlSessionFactoryBean.setGlobalConfig(mybatisPlusProperties.getGlobalConfig());
sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);
return sqlSessionFactoryBean.getObject();
}
/**
* 事务管理
*/
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier(value = "dynamicDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
DataSourceHolder
package com.cnbai.config;
import org.apache.commons.lang3.StringUtils;
/**
* 使用 ThreadLocal 线程隔离维护数据源
*/
public class DataSourceHolder {
private static final String DEFAULT_DATA_SOURCE = "defaultDataSource";
private static final ThreadLocal<String> HOLDER = ThreadLocal.withInitial(() -> DEFAULT_DATA_SOURCE);
/**
* 获取数据源
*/
public static String getDataSource() {
return HOLDER.get();
}
/**
* 缓存数据源
*/
public static void setDataSource(String key) {
HOLDER.set(key);
}
/**
* 清理数据源
*/
public static void cleanDataSource() {
HOLDER.remove();
}
/**
* 根据 数据库名称(案件Id)切换数据源
*/
public static void updateDataSource(String caseId) {
setDataSource(StringUtils.isBlank(caseId) ? DEFAULT_DATA_SOURCE : caseId);
}
}
DynamicDataSource
package com.cnbai.config;
import com.cnbai.utils.SpringUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 动态数据源管理
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private Map<Object, Object> targetDataSources;
@Override
protected Object determineCurrentLookupKey() {
return DataSourceHolder.getDataSource();
}
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
super.setTargetDataSources(this.targetDataSources);
}
public Map<Object, Object> getTargetDataSources() {
return targetDataSources;
}
/**
* 创建动态数据源
*/
public DataSource createDynamicDataSource(String caseId) {
HikariConfig config = SpringUtils.getBean("hikariConfig", HikariConfig.class);
config.setJdbcUrl(String.format("jdbc:postgresql://127.0.0.1:5432/%s?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified", caseId));
return new HikariDataSource(config);
}
}
MybatisPlusConfig
package com.cnbai.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
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;
/**
* Mybatis plus 配置
*/
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
return interceptor;
}
@Primary
@Bean
@ConfigurationProperties("mybatis-plus")
public MybatisPlusProperties mybatisPlusProperties() {
return new MybatisPlusProperties();
}
}
6. 创建测试类
UserController
package com.cnbai.controller;
import com.cnbai.service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
@RestController
public class UserController {
@Resource
private UserService userService;
/**
* 获取 user -> t_user 表数据
*/
@RequestMapping("userOne")
public String userOne() {
// [{id=1,name='张三'}]
return userService.getUserList().toString();
}
/**
* 获取 case_1 -> t_user 表数据
*/
@RequestMapping("userTwo")
public String userTwo() {
String caseId = "case_1";
// [{id=2,name=李四}]
return userService.getUserList(caseId).toString();
}
/**
* 获取 case_2 -> t_user 表数据
*/
@RequestMapping("userThree")
public String userThree() {
String caseId = "case_2";
// [{id=3,name=王五}]
return userService.getUserList(caseId).toString();
}
}
7. 创建 Service
UserService
package com.cnbai.service;
import com.baomidou.mybatisplus.extension.service.IService;
import java.util.List;
import java.util.Map;
public interface UserService extends IService<User> {
List<Map<String, Object>> getUserList();
List<Map<String, Object>> getUserList(String caseId);
}
UserServiceImpl
package com.cnbai.service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cnbai.dao.UserDao;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {
@Resource
private UserDao userDao;
@Override
public List<Map<String, Object>> getUserList() {
return userDao.getUserList();
}
@Override
public List<Map<String, Object>> getUserList(String caseId) {
return userDao.getUserList(caseId);
}
}
8. 创建 Mapper
UserDao
package com.cnbai.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cnbai.aop.TestDynamicDataSource;
import com.cnbai.service.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
@TestDynamicDataSource(DataSource = "caseId")
@Mapper
public interface UserDao extends BaseMapper<User> {
List<Map<String, Object>> getUserList();
List<Map<String, Object>> getUserList(@Param("caseId") String caseId);
}
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.cnbai.dao.UserDao">
<select id="getUserList" resultType="java.util.Map">
select * from t_user
</select>
</mapper>
9. 创建实体类
User
package com.cnbai.service;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
@TableName(value = "t_user")
public class User implements Serializable {
@TableId(value = "id", type = IdType.INPUT)
private Integer id;
@TableField(value = "name")
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
10. 创建工具类
SpringUtils
package com.cnbai.utils;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
/**
* 获取 bean 类
*/
@Component
public class SpringUtils implements ApplicationContextAware {
private static ApplicationContext context;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
SpringUtils.context = applicationContext;
}
public static <T> T getBean(String beanName, Class<T> tClass) {
return SpringUtils.context.getBean(beanName, tClass);
}
}
11. 创建启动类
Application
package com.cnbai;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
四、不同连接动态库切换
不同连接的不同库和表,库名未知随业务增加,表名每个库相同
实现方法和《同连接动态库切换》一致,只修改一个其中方法即可
DynamicDataSource
package com.cnbai.config;
import com.cnbai.utils.SpringUtils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 动态数据源管理
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private Map<Object, Object> targetDataSources;
@Override
protected Object determineCurrentLookupKey() {
return DataSourceHolder.getDataSource();
}
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
super.setTargetDataSources(this.targetDataSources);
}
public Map<Object, Object> getTargetDataSources() {
return targetDataSources;
}
/**
* 创建动态数据源
*/
public DataSource createDynamicDataSource(String caseId) {
HikariConfig config = SpringUtils.getBean("hikariConfig", HikariConfig.class);
// 1. 通过 caseId 获取默认数据库中存储的 ip, port, database 等
DataBaseConfig baseConfig = DataBaseConfigService.getByCaseId(caseId);
// 2. 替换连接,DataBaseConfig 中可自定义字段存储数据库连接相关配置,如: ¤tSchema=s%
config.setJdbcUrl(String.format("jdbc:postgresql://%s:%s/%s?useUnicode=true&characterEncoding=UTF-8&stringtype=unspecified", baseConfig.getIp(), baseConfig.getPort(), baseConfig.getDatabaseName()));
config.setUsername(baseConfig.getUserName());
config.setPassword(baseConfig.getPassword());
return new HikariDataSource(config);
}
}