SpringBoot 动态切换数据源

一、不同连接少量库切换

不同连接的不同库和表,库名已知且数量少,表名不一致且不能直接获取

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 中可自定义字段存储数据库连接相关配置,如: &currentSchema=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);
    }
}
  • 2
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值