基于dynamic-datasource实现多租户动态切换数据源

基于dynamic-datasource实现多租户动态切换数据源

一、添加pom配置

<!-- 动态切换数据源 --><dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot-starter</artifactId><version>3.5.1</version></dependency>

二、添加yaml配置

spring:datasource:dynamic:primary: master #设置默认的数据源或者数据源组,默认值即为masterstrict:false#设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.datasource:# 主库数据源master:url:username:password:driver-class-name: com.mysql.cj.jdbc.Driver
                # 从库数据源#                slave:#                    # 从数据源开关/默认关闭#                    enabled: false#                    url:#                    username:#                    password:

如果想整合druid连接池，可做如下配置

spring:autoconfigure:exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
    datasource:dynamic:primary: master #设置默认的数据源或者数据源组,默认值即为masterstrict:false#设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.datasource:# 主库数据源master:url:username:password:driver-class-name: com.mysql.cj.jdbc.Driver
                # 从库数据源#                slave:#                    # 从数据源开关/默认关闭#                    enabled: false#                    url:#                    username:#                    password:druid:# 初始连接数initialSize:5# 最小连接池数量minIdle:10# 最大连接池数量maxActive:20# 配置获取连接等待超时的时间maxWait:60000# 配置连接超时时间connectTimeout:30000# 配置网络超时时间socketTimeout:60000# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒timeBetweenEvictionRunsMillis:60000# 配置一个连接在池中最小生存的时间，单位是毫秒minEvictableIdleTimeMillis:300000# 配置一个连接在池中最大生存的时间，单位是毫秒maxEvictableIdleTimeMillis:900000# 配置检测连接是否有效validationQuery: SELECT 1 FROM DUAL
                testWhileIdle:truetestOnBorrow:falsetestOnReturn:falsewebStatFilter:enabled:truestatViewServlet:enabled:true# 设置白名单，不填则允许所有访问allow:url-pattern: /druid/*
                    # 控制台管理用户名和密码login-username: ruoyi
                    login-password:123456filter:stat:enabled:true# 慢SQL记录log-slow-sql:trueslow-sql-millis:1000merge-sql:truewall:config:multi-statement-allow:true

三、创建数据源表，储存数据源信息

建表语句如下

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS =0;-- ------------------------------ Table structure for sys_database-- ----------------------------DROPTABLEIFEXISTS`sys_database`;CREATETABLE`sys_database`(`database_id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键',`pool_name`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULLCOMMENT'连接池名称',`driver_class_name`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULLCOMMENT'驱动名',`url`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULLCOMMENT'数据库地址',`username`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULLCOMMENT'数据库用户名',`password`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_general_ci NULLDEFAULTNULLCOMMENT'数据库密码',PRIMARYKEY(`database_id`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=3CHARACTERSET= utf8mb4 COLLATE= utf8mb4_general_ci ROW_FORMAT = DYNAMIC;SET FOREIGN_KEY_CHECKS =1;

数据源实体类如下

packagecom.ruoyi.common.core.domain.entity;importorg.apache.commons.lang3.builder.ToStringBuilder;importorg.apache.commons.lang3.builder.ToStringStyle;importcom.ruoyi.common.annotation.Excel;importcom.ruoyi.common.core.domain.BaseEntity;/**
 * 数据库管理对象 sys_database
 * 
 * @author ruoyi
 * @date 2023-03-01
 */publicclassSysDatabaseextendsBaseEntity{privatestaticfinallong serialVersionUID =1L;/** 主键 */privateLong databaseId;/** 连接池名称 */@Excel(name ="连接池名称")privateString poolName;/** 驱动名 */@Excel(name ="驱动名")privateString driverClassName;/** 数据库地址 */@Excel(name ="数据库地址")privateString url;/** 数据库用户名 */@Excel(name ="数据库用户名")privateString username;/** 数据库密码 */@Excel(name ="数据库密码")privateString password;publicvoidsetDatabaseId(Long databaseId){this.databaseId = databaseId;}publicLonggetDatabaseId(){return databaseId;}publicvoidsetPoolName(String poolName){this.poolName = poolName;}publicStringgetPoolName(){return poolName;}publicvoidsetDriverClassName(String driverClassName){this.driverClassName = driverClassName;}publicStringgetDriverClassName(){return driverClassName;}publicvoidsetUrl(String url){this.url = url;}publicStringgetUrl(){return url;}publicvoidsetUsername(String username){this.username = username;}publicStringgetUsername(){return username;}publicvoidsetPassword(String password){this.password = password;}publicStringgetPassword(){return password;}@OverridepublicStringtoString(){returnnewToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE).append("databaseId",getDatabaseId()).append("poolName",getPoolName()).append("driverClassName",getDriverClassName()).append("url",getUrl()).append("username",getUsername()).append("password",getPassword()).toString();}}

四、dynamic-datasource基础操作

（1）查看数据源

importjavax.sql.DataSource;importcom.baomidou.dynamic.datasource.DynamicRoutingDataSource;@AutowiredprivateDataSource dataSource;//这是官方示例，直接返回PoolName（就是yaml配置中的数据源名称）的Set@GetMapping@ApiOperation("获取当前所有数据源")publicSet<String>now(){DynamicRoutingDataSource ds =(DynamicRoutingDataSource) dataSource;return ds.getDataSources().keySet();}//如果只是想知道具体的数据源，输出查看PoolName即可，如下publicvoidgetDataSources(){DynamicRoutingDataSource ds =(DynamicRoutingDataSource) dataSource;for(String poolName : ds.getDataSources().keySet()){System.out.println("poolName:"+ poolName);}}

（2）添加数据源

importcom.ruoyi.system.service.ISysDatabaseService;importjavax.sql.DataSource;importcom.baomidou.dynamic.datasource.DynamicRoutingDataSource;importorg.springframework.beans.BeanUtils;@AutowiredprivateISysDatabaseService sysDatabaseService;@AutowiredprivateDataSource dataSource;//通用数据源会根据maven中配置的连接池根据顺序依次选择。//默认的顺序为druid>hikaricp>beecp>dbcp>spring basic@PostMapping("/add")@ApiOperation("通用添加数据源（推荐）")publicSet<String>add(@Validated@RequestBodyDataSourceDTO dto){DataSourceProperty dataSourceProperty =newDataSourceProperty();// 这里主要是将dto的属性赋值给dataSourceProperty//所以dataSourceProperty中必要的参数，dto都要提供BeanUtils.copyProperties(dto, dataSourceProperty);DynamicRoutingDataSource ds =(DynamicRoutingDataSource) dataSource;DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);// PoolName就是我们yaml配置中说的数据源名称
    ds.addDataSource(dto.getPoolName(), dataSource);return ds.getDataSources().keySet();}

（3）更新数据源

DynamicRoutingDataSource中记录数据源是以map形式，dataSourceMap的k就是poolName，所以如果想要更改poolName对应的数据源，直接覆盖同k的value即可。或者更为保险的做法可以先删除poolName对应的数据源，后续再次添加同名poolName数据源

（4）删除数据源

importcom.ruoyi.system.service.ISysDatabaseService;importjavax.sql.DataSource;importcom.baomidou.dynamic.datasource.DynamicRoutingDataSource;@AutowiredprivateDataSource dataSource;@DeleteMapping@ApiOperation("删除数据源")publicStringremove(String poolName){DynamicRoutingDataSource ds =(DynamicRoutingDataSource) dataSource;
    ds.removeDataSource(poolName);return"删除成功";}

（5）切换数据源

importcom.baomidou.dynamic.datasource.DynamicRoutingDataSource;publicstaticvoidswitchDataSource(String poolName){//需要注意的是手动切换的数据源，最好自己在合适的位置//调用DynamicDataSourceContextHolder.clear()清空当前线程的数据源信息。DynamicDataSourceContextHolder.clear();//切换到对应poolName的数据源DynamicDataSourceContextHolder.push(poolName);}

编写一个拦截器，根据用户，选择对应的数据源

packagecom.ruoyi.framework.interceptor;importcom.baomidou.dynamic.datasource.DynamicRoutingDataSource;importcom.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;importcom.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;importcom.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;importcom.ruoyi.common.core.domain.entity.SysDatabase;importcom.ruoyi.common.core.domain.entity.SysUser;importcom.ruoyi.common.utils.ShiroUtils;importorg.springframework.beans.BeanUtils;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.servlet.HandlerInterceptor;importorg.springframework.web.servlet.ModelAndView;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjavax.sql.DataSource;importjava.util.Objects;publicclassDynamicDatasourceInterceptorimplementsHandlerInterceptor{@OverridepublicbooleanpreHandle(HttpServletRequest request,HttpServletResponse response,Object handler){SysUser sysUser =ShiroUtils.getSysUser();//获取当前登录用户信息if(Objects.nonNull(sysUser)&&Objects.nonNull(sysUser.getDatabase())){SysDatabase database = sysUser.getDatabase();//获取用户对应的数据源信息DynamicDataSourceContextHolder.push(database.getPoolName());//切换数据源returntrue;}returntrue;}@OverridepublicvoidpostHandle(HttpServletRequest request,HttpServletResponse response,Object handler,ModelAndView modelAndView){}//    @Override//    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) {//        DynamicDataSourceContextHolder.clear();//    }}

将拦截器，注入到spring

packagecom.ruoyi.framework.config;importcom.ruoyi.framework.interceptor.DynamicDatasourceInterceptor;importorg.springframework.context.annotation.Configuration;importorg.springframework.web.servlet.config.annotation.InterceptorRegistry;importorg.springframework.web.servlet.config.annotation.WebMvcConfigurer;@ConfigurationpublicclassDynamicDatasourceInterceptorConfigimplementsWebMvcConfigurer{@OverridepublicvoidaddInterceptors(InterceptorRegistry registry){String[] path ={"/system/**"};// 如果拦截全部可以设置为 /**String[] excludePath ={};// 不需要拦截的接口路径DynamicDatasourceInterceptor dynamicDatasourceInterceptor =newDynamicDatasourceInterceptor();

        registry.addInterceptor(dynamicDatasourceInterceptor).addPathPatterns(path).excludePathPatterns(excludePath);}}

五、springboot启动时，加载所有数据源表中的数据源

packagecom.ruoyi.framework.shiro.service;importcom.baomidou.dynamic.datasource.DynamicRoutingDataSource;importcom.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;importcom.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;importcom.ruoyi.common.config.datasource.DynamicDataSourceContextHolder;importcom.ruoyi.common.core.domain.entity.SysDatabase;importcom.ruoyi.system.service.ISysDatabaseService;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.beans.BeanUtils;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importjavax.annotation.PostConstruct;importjavax.sql.DataSource;importjava.util.List;@ServicepublicclassInjectionAllDatasourceService{publicstaticfinalLogger log =LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);@AutowiredprivateDataSource dataSource;@AutowiredprivateDefaultDataSourceCreator dataSourceCreator;@AutowiredprivateISysDatabaseService databaseService;@PostConstructpublicvoidinit(){
        log.info("项目启动中，加载用户数据");List<SysDatabase> sysDatabases = databaseService.selectSysDatabaseList(newSysDatabase());for(SysDatabase database:sysDatabases){try{DataSourceProperty dataSourceProperty =newDataSourceProperty();// 这里主要是将dto的属性赋值给dataSourceProperty//所以dataSourceProperty中必要的参数，dto都要提供BeanUtils.copyProperties(database, dataSourceProperty);DynamicRoutingDataSource ds =(DynamicRoutingDataSource) dataSource;DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);// PoolName就是我们yaml配置中说的数据源名称
                ds.addDataSource(database.getPoolName(), dataSource);}catch(Exception e){
                e.printStackTrace();}}

        log.info("项目启动中，加载用户数据完成");}}