[Java知识库]【SpringBoot DB 系列】Mybatis-Plus 多数据源配置

MyBatis-Plus (opens new window)（简称 MP）是一个 MyBatis (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，既然做增强，那多数据源这种硬性场景，肯定是有非常简单的解决方案的

本文将实例演示 Mybatis-Plus 多数据源的配置

I. 环境准备

1. 数据库相关

一个oracle，一个sqlserver

2. 相关配置

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.springblade</groupId>
    <artifactId>spd-hisserview-ymf</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spd-hisserview-ymf</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--        多数据源配置-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>

        <!--        mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- 引入 redis 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
            <!-- 默认指定了lettuce,我们需要排除 ，并且引入jedis的客户端-->
            <exclusions>
                <exclusion>
                    <groupId>io.lettuce</groupId>
                    <artifactId>lettuce-core</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- jedis的客户端 -->
        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>
        <!--sqlserver数据库JDBC连接依赖-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>

        <!--MyBatisPlus启动依赖-->
        <dependency>

            <groupId>com.baomidou</groupId>

            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!--Oracle数据库JDBC连接依赖-->

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.2.0.1</version>
        </dependency>

        <!--JUNIT测试相关-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

配置文件信息application.yml，请注意下面的写法格式，如有疑问可以参考官方教程

server:
  port: 81

spring:
  redis:
    #Redis配置
    host: 127.0.0.1
    ##redis端口号
    port: 6379
    ##redis密码
    password:
    ##redis连接超时时间（毫秒）
    timeout: 60000
    ##Redis默认情况下有16个分片，这里配置具体使用的分片，默认时0
    database: 10

  application:
    name: viewService
  datasource:
    dynamic:
      primary: bdsz #设置默认的数据源或者数据源组,默认值即为master
      strict: true #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
      datasource:
        bdsz:
          driver-class-name: oracle.jdbc.driver.OracleDriver
          #    本地连接
          url: jdbc:oracle:thin:@//localhost:1521/orcl
          username: SYSTEM
          password: 961008Dyh
          #    远程连接
#          url: jdbc:oracle:thin:@//100.100.100.45:1521/PDBORCL
#          username: BDFPQS
#          password: BDFPQS123456
        szey:
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          #    本地连接
          url: jdbc:sqlserver://localhost:1433;databaseName=test
          username: sa
          password: 961008
          #    远程连接
#          url: jdbc:sqlserver://192.168.170.186:1433;databaseName=HERP
#          username: hrp_ymf
#          password: hrp_ymf

#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

tenantId:
  szey: 100000
  bdsz: 200000

II. 代码实现

1. 实体类

HisserVice

package org.springblade.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 com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;

import java.io.Serializable;

/**
 * 实体类
 *
 * @ClassName: HisserVice
 * @author: 〆、风神
 * @since: 2022/1/27 21:25
 */
@Data
@TableName("SPD_HISSERVICE_YMF")
@JsonInclude(JsonInclude.Include.NON_NULL)
public class HisserVice implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @TableId(value = "ID", type = IdType.AUTO)
    @JsonSerialize(using = ToStringSerializer.class)
    private Long id;

    /**
     * 采购订单号
     */
    @TableField(value = "ORDER_ID")
    private String orderId;

    /**
     * 采购订单明细号
     */
    @TableField(value = "ORDER_DETAIL_ID")
    private String orderDetailId;

    /**
     * 月结单号
     */
    @TableField(value = "LUNAR_KNOT_NO")
    private String lunarKnotNo;

    /**
     * 月结月份
     */
    @TableField(value = "LUNAR_KNOT_MONTH")
    private String lunarKnotMonth;
    /**
     * 发票号
     */
    @TableField(value = "INVOICE_NO")
    private String invoiceNo;
    /**
     * 供应商
     */
    @TableField(value = "SUPPLIER")
    private String supplier;
    /**
     * 品种编码
     */
    @TableField(value = "VARIETIES_CODE")
    private String varietiesCode;
    /**
     * 品种全称
     */
    @TableField(value = "VARIETIES_DESC")
    private String varietiesDesc;
    /**
     * 型号/规格
     */
    @TableField(value = "SPEC")
    private String spec;
    /**
     * 单位
     */
    @TableField(value = "UNIT")
    private String unit;
    /**
     * 单价
     */
    @TableField(value = "PRICE")
    private String price;
    /**
     * 数量
     */
    @TableField(value = "AMOUNT")
    private String amount;
    /**
     * 金额
     */
    @TableField(value = "TOTAL_MONTY")
    private String totalMonty;
    /**
     * 生产企业
     */
    @TableField(value = "MNUFACTURER")
    private String mnufacturer;
    /**
     * 批号
     */
    @TableField(value = "BATCH")
    private String batch;
    /**
     * 生产日期
     */
    @TableField(value = "PRODUCTION_DATE")
    private String productionDate;

    /**
     * 失效期
     */
    @TableField(value = "EXPIRATION_DATE")
    private String expirationDate;
    /**
     * 来源
     */
    @TableField(value = "SOURCE")
    private String source;
    /**
     * 高低值
     */
    @TableField(value = "CONSUMABLE_TYPE")
    private String consumableType;
    /**
     * 阳光产品码
     */
    @TableField(value = "PRODUCTION")
    private String production;
    /**
     * 阳光规格型号码
     */
    @TableField(value = "SPEC_NO")
    private String specNo;
    @TableField(exist = false)
    private String tenantId;
}

MateOrderMainYmf

package org.springblade.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;

import java.io.Serializable;

/**
 * 实体类
 *
 * @ClassName: MateOrderMainYmf
 * @author: 〆、dyh
 * @since: 2022/4/1 18:27
 */
@Data
@TableName("mate_order_main_ymf")
@JsonInclude(JsonInclude.Include.NON_NULL)
public class MateOrderMainYmf implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableField(value = "order_id")
    private String orderId;

    @TableField(value = "distributeAddress")
    private String distributeAddress;

    @TableField(value = "remarks")
    private String remarks;

    @TableField(value = "order_detail_id")
    private String orderDetailId;

    @TableField(value = "goodsID")
    private String goodsID;
    @TableField(value = "goodsname")
    private String goodsname;
    @TableField(value = "companyIdPs")
    private String companyIdPs;
    @TableField(value = "purchaseCount")
    private String purchaseCount;
    @TableField(value = "subcodeId")
    private String subcodeId;
    @TableField(value = "orderDetailRemark")
    private String orderDetailRemark;
    @TableField(value = "orderCustomInfo")
    private String orderCustomInfo;
    @TableField(value = "sunPlatCode")
    private String sunPlatCode;
    @TableField(value = "sunPlatSpec")
    private String sunPlatSpec;
    @TableField(value = "orderDept")
    private String orderDept;
    @TableField(value = "updateDate")
    private String updateDate;
    @TableField(value = "updateTime")
    private String updateTime;
    @TableField(exist = false)
    private String tenantId;

}

2. Mapper 接口

HisserViceMapper

package org.springblade.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springblade.entity.HisserVice;

/**
 * @ClassName: HisserViceMapper
 * @author: 〆、风神
 * @since: 2022/1/27 23:09
 */
@Mapper
public interface HisserViceMapper extends BaseMapper<HisserVice> {

}

MateOrderMainYmfMapper

package org.springblade.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springblade.entity.MateOrderMainYmf;

/**
 * @InterfaceName: MateOrderMainYmfMapper
 * @author: 〆、dyh
 * @since: 2022/4/1 18:51
 */
@Mapper
public interface MateOrderMainYmfMapper extends BaseMapper<MateOrderMainYmf> {
}

3. Service 接口与实现

@DS注解:value 为前面数据源配置文件中的 key(spring.datasource.dynamic.datasource下面的bdsz+ szey)

这个注解可以放在类上也可以放在方法上，方法上的优先级 > 类

HisserViceService

package org.springblade.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.entity.HisserVice;

import java.util.List;

/**
 * @ClassName: HisserViceService
 * @author: 〆、风神
 * @since: 2022/1/27 23:07
 */
public interface HisserViceService extends IService<HisserVice> {

    /**
     * 分页查询
     *
     * @param page
     * @param lunarKnotMonth
     * @return
     */
    IPage<HisserVice> selectList(Page<HisserVice> page, String lunarKnotMonth);

    /**
     * 不分页查询
     *
     * @param lunarKnotMonth
     * @return
     */
    List<HisserVice> selectList(String lunarKnotMonth);
}

MateOrderMainYmfService

package org.springblade.service;

import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.entity.MateOrderMainYmf;

import java.util.List;

/**
 * @InterfaceName: MateOrderMainYmfService
 * @author: 〆、dyh
 * @since: 2022/4/2 9:14
 */

public interface MateOrderMainYmfService extends IService<MateOrderMainYmf> {

    /**
     * 不分页查询
     *
     * @return
     */
    List<MateOrderMainYmf> selectList();
}

HisserViceServiceImpl

package org.springblade.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springblade.entity.HisserVice;
import org.springblade.mapper.HisserViceMapper;
import org.springblade.service.HisserViceService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * @ClassName: HisserViceServiceImpl
 * @author: 〆、风神
 * @since: 2022/1/27 23:09
 */
@Service
@DS("bdsz")
public class HisserViceServiceImpl extends ServiceImpl<HisserViceMapper, HisserVice> implements HisserViceService {

    @Value("${tenantId.bdsz}")
    private String tenantId;

    private static Logger logger = LoggerFactory.getLogger(HisserViceServiceImpl.class);

    @Override
    public IPage<HisserVice> selectList(Page<HisserVice> page, String lunarKnotMonth) {
        QueryWrapper<HisserVice> queryWrapper = new QueryWrapper();
        if (StringUtils.hasText(lunarKnotMonth)) {
            queryWrapper.eq("LUNAR_KNOT_MONTH", lunarKnotMonth);
        }
        return page(page, queryWrapper);
    }

    @Override
    public List<HisserVice> selectList(String lunarKnotMonth) {
        QueryWrapper<HisserVice> queryWrapper = new QueryWrapper();
        if (StringUtils.hasText(lunarKnotMonth)) {
            queryWrapper.eq("LUNAR_KNOT_MONTH", lunarKnotMonth);
        }
        return list(queryWrapper);
    }
}

MateOrderMainYmfServiceImpl

package org.springblade.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springblade.constant.Constant;
import org.springblade.entity.MateOrderMainYmf;
import org.springblade.mapper.MateOrderMainYmfMapper;
import org.springblade.service.MateOrderMainYmfService;
import org.springblade.util.RedisUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.List;

/**
 * @ClassName: MateOrderMainYmfServiceImpl
 * @author: 〆、dyh
 * @since: 2022/4/2 9:20
 */
@Service
@DS("szey")
public class MateOrderMainYmfServiceImpl extends ServiceImpl<MateOrderMainYmfMapper, MateOrderMainYmf> implements MateOrderMainYmfService {

    private static Logger logger = LoggerFactory.getLogger(MateOrderMainYmfServiceImpl.class);

    @Value("${tenantId.szey}")
    private String tenantId;

    @Autowired
    RedisUtil redisUtil;

    @Override
    public List<MateOrderMainYmf> selectList() {
        QueryWrapper<MateOrderMainYmf> queryWrapper = new QueryWrapper();

        if (redisUtil.exists(Constant.MOMYUT)) {
            logger.info("redis缓存的最近的修改时间：{}", redisUtil.get(Constant.MOMYUT));
            queryWrapper.gt("updateTime", redisUtil.get(Constant.MOMYUT));
        }
        queryWrapper.orderByDesc("updateTime");
        List<MateOrderMainYmf> list = list(queryWrapper);
        if (!CollectionUtils.isEmpty(list)) {
            logger.info("视图中最近的修改时间：{}", list.get(0).getUpdateTime());
            redisUtil.set(Constant.MOMYUT, list.get(0).getUpdateTime());
        }
        return list;
    }

}