springboot+mybatisplus+dynicDatasource 从数据库表中查询数据源 动态添加

1、pom依赖

  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.12</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
 <dependencies>
        <!-- Spring Boot Starter Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.33</version>
        </dependency>


        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.2</version>
        </dependency>


        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>4.3.0</version>
        </dependency>

        <!-- 可选：如果使用Lombok提高代码简洁性 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

2、配置文件

spring:
  datasource:
    dynamic:
      #设置默认的数据源或者数据源组,默认值即为master
      primary: master
      #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      strict: false
      datasource:
        master:
          url: jdbc:mysql://127.0.0.1:3306/tenant?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver

mybatis-plus:
  # ????
  global-config:
    db-config:
      # ????????mysql/oracle/h2/postgresql?
      db-type: mysql
      # ???????not_null/commented/underline_to_camel/to_lowercase?
      field-strategy: not_null
      # ??????????
      capital-mode: true
      # ????????
      column-underline: true
      # ???
      table-prefix:
      # ID?????id_WORKER/AUTO
      id-type: auto
      # SQL???
      logic-delete-value: 1
      logic-not-delete-value: 0
      logic-delete-field: deleted
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: com.example.mulittenantdemo.domain
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl


server:
  port: 8083

3、应用启动的时候动态添加数据源

package com.example.mulittenantdemo.util;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.DataSourceProperty;
import com.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;
import com.example.mulittenantdemo.domain.Tenant;
import com.example.mulittenantdemo.service.TenantService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.List;

@Component
public class MyDomic{
    @Autowired
    private DataSource dataSource;
    @Autowired
    private DefaultDataSourceCreator dataSourceCreator;

    @Autowired
    TenantService tenantService;
    @Autowired
    public void init(){

        List<Tenant> list = tenantService.list();
        for (Tenant tenant : list) {
            DataSourceProperty dataSourceProperty = new DataSourceProperty();
            dataSourceProperty.setUrl("jdbc:mysql://127.0.0.1:3306/"+tenant.getDb()+"?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false");
            dataSourceProperty.setUsername(tenant.getUser());
            dataSourceProperty.setPassword(tenant.getPwd());
            dataSourceProperty.setDriverClassName("com.mysql.cj.jdbc.Driver");

            DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
            DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
            ds.addDataSource(tenant.getCode(), dataSource);
        }


    }
}

4、动态切换数据源

package com.example.mulittenantdemo.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.mulittenantdemo.domain.AppVersion;
import com.example.mulittenantdemo.mapper.AppVersionMapper;
import com.example.mulittenantdemo.service.AppVersionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import  com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;



/**
 * @author 许贵林
 * @description 针对表【app_version(app版本管理)】的数据库操作Service实现
 * @createDate 2024-03-19 09:32:09
 */
@Service
public class AppVersionServiceImpl extends ServiceImpl<AppVersionMapper, AppVersion>
        implements AppVersionService {

    @Autowired
    private AppVersionMapper appVersionMapper;

    @Override
    public AppVersion queryById1(Long id) {
        DynamicDataSourceContextHolder.push("slave_1");
        return appVersionMapper.selectById(id);
    }

    @Override
    @DS("slave_2")
    public AppVersion queryById2(Long id) {

        return appVersionMapper.selectById(id);
    }

    @Override

    public AppVersion queryById3(Long id,String code) {
        DynamicDataSourceContextHolder.push(code);
        return appVersionMapper.selectById(id);
    }

    @Override
    @DS("primary")
    public AppVersion queryById(Long id) {

        return appVersionMapper.myqueryById111(id);
    }
}

public interface AppVersionService extends IService<AppVersion> {

    AppVersion queryById(Long id);
    AppVersion queryById1(Long id);

    AppVersion queryById2(Long id);

    AppVersion queryById3(Long id,String code);
}

    @GetMapping("/get3/{id}/{code}")
    public AppVersion sayHello3(@PathVariable("id") Long id,@PathVariable("code") String code) {
        AppVersion byId = appVersionService.queryById3(id,code);
        System.out.println(byId);
        return (byId);
    }

 租户库：tenant 表设计

CREATE TABLE `t_tenant`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '租户编码',
  `db` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `pwd` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_tenant
-- ----------------------------
INSERT INTO `t_tenant` VALUES (1, 'farm', 'farm', 'root', '123456');
INSERT INTO `t_tenant` VALUES (2, 'from1', 'from1', 'root', '123456');
INSERT INTO `t_tenant` VALUES (3, 'farm2', 'farm2', 'root', '123456');