springboot整合mybatis-plus多数据源分别连接mysql和oracle

一、引入依赖

<?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>
    <packaging>jar</packaging>

    <!-- 配置文件没有生效将此处的pom改为jar就生效了-->
    <!--<packaging>jar</packaging>-->

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.example</groupId>
    <artifactId>readinglist</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>readinglist</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <mybatis-plus.version>3.0.7</mybatis-plus.version>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

<!-- MYBATIS PLUS -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.2.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-annotation</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
    <dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <!-- 这个配置很关键，编译的时候不会漏掉xml文件-->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>
</project>

server:
  port: 8080

spring:
  #mysql数据源配置
  datasource:
    dynamic:
      druid:
        validation-query: SELECT 1 FROM DUAL
      primary: db1
      datasource:
      # 强烈注意：Spring Boot 2.X 版本不再支持配置继承，多数据源的话每个数据源的所有配置都需要单独配置，否则配置不会生效
        db1:
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8
          username: root
          password: 123456
        db2:
          driver-class-name: oracle.jdbc.OracleDriver
          url: jdbc:oracle:thin:@localhost:1521:orcl
          username: system
          password: 123456
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

#kotime配置
  profiles:
    active: koTime
koTime:
  log:
    enable: false  # 是否开启控制台输出，非必填，默认false
    language: chinese # 控制台输出语言（english/chinese）非必填，默认chinese
  time:
    threshold: 800.0 # 时间阈值，用于前端展示，大于阈值显示红色，小于阈值显示绿色，非必填，默认800

  #redis配置
  redis:
    database: 0
    lettuce:
      pool:
        max-active: 8
        max-wait: -1ms
        max-idle: 8
        min-idle: 0
    host: localhost
    port: 6379
    password: 123456

#mybatis数据库操作配置文件,缺少这个找不到xml文件
mybatis-plus:
  mapper-locations: springboot/dao/mapping/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

package springboot;


import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;

//@EnableScheduling
@SpringBootApplication
@EnableAsync //使用异步注解@Async 需要在这里加上@EnableAsync
@MapperScan("springboot.dao") //不可或缺作用是扫描dao包下面的所有mapper装配
public class HelloApplication {
    public static void main(String[] args) {
        SpringApplication.run(HelloApplication.class,args);
    }
}

TStudentMapper.java 通过@DS注解来选择数据源

package springboot.dao;
import com.baomidou.dynamic.datasource.annotation.DS;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 后台管理用户表 Mapper 接口
 * </p>
 *
 * @author cruder
 * @since 2020-07-16
 */
@DS("db2")
public interface TStudentMapper{

    List<Map<String,Object>> test01(Map<String,Object> hashParam);

}

<?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="springboot.dao.TStudentMapper">

    <select id="test01" parameterType="hashMap" resultType="hashMap">
        select student_id,student_name,speciality,grade,
        TO_CHAR(BIRTH_DAY,'yyyy-MM-dd') AS birthDay,age
         from t_student
        <if test="age!=null">
        where age = #{age}
    </if>
    </select>

</mapper>

TUserMapper.ava
，不加@DS注解则使用默认数据库

package springboot.dao;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import springboot.domain.TUser;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 后台管理用户表 Mapper 接口
 * </p>
 *
 * @author cruder
 * @since 2020-07-16
 */
public interface TUserMapper extends BaseMapper<TUser> {

    List<TUser> findUser(@Param("tUser") TUser tUser,Integer startIndex,Integer endIndex);
}

<?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="springboot.dao.TUserMapper">

    <select id="findUser" parameterType="springboot.domain.TUser" resultType="springboot.domain.TUser">
        select * from t_user
        <where>
            <if test="tUser.username!=null and tUser.username!=''">
                username = #{tUser.username}
            </if>
        </where>
        <if test="startIndex!=null and endIndex!=null">
            limit #{startIndex},#{endIndex}
        </if>
    </select>

</mapper>

package springboot.domain;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.util.Date;
import java.util.Set;

/**
 * <p>
 * 后台管理用户表
 * </p>
 *
 * @author cruder
 * @since 2020-07-16
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class TUser extends Model {

    private static final long serialVersionUID = 1L;

    /**
     * 账号
     */
    @TableId
    @ExcelProperty("姓名")
    private String username;

    /**
     * 密码
     */
    @ExcelProperty({"个人信息","密码"})
    private String password;


    /**
     * 创建时间
     */
    @ExcelProperty({"个人信息","创建时间"})
    private Date createTime;

    /**
     * 更新时间
     */
    @ExcelProperty("修改时间")
    private Date updateTime;

    /**
     * 表的后缀（年月) 按月分表
     */
    @TableField(exist = false)
    @ExcelIgnore
    private String date;

    /**
     * 用户对应的角色集合
     */
    @ExcelIgnore
    @TableField(exist = false)
    private Set<Role> roles;

}

测试接口

package springboot.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import springboot.dao.TStudentMapper;
import springboot.dao.TUserMapper;
import springboot.domain.Person;
import springboot.domain.TUser;
import springboot.dto.DataRequestDTO;
import springboot.dto.ResponseDTO;
import test.entity.DataOutput;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
public class TestController {

    @Resource
    private TUserMapper tUserMapper;
    @Resource
    private TStudentMapper tStudentMapper;


    @GetMapping("hhh")
    public ResponseDTO hhh(@RequestParam MultipartFile file){

        return ResponseDTO.success();
    }

    @GetMapping("test")
    public ResponseDTO test(TUser tUser){
        return ResponseDTO.success(tUserMapper.findUserName(new QueryWrapper<>(tUser).eq("username",tUser.getUsername())));
    }

    @CrossOrigin
    @GetMapping("test01")
    public ResponseDTO test01(TUser tUser,Integer startIndex,Integer endIndex){
        return ResponseDTO.success(tUserMapper.findUser(tUser,startIndex,endIndex));
    }

    @GetMapping("test02")
    public void test02(TUser tUser, HttpServletResponse response) throws IOException {
        List<TUser> tUserList = tUserMapper.findUser(tUser, 0, 10);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), TUser.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("汇总").build();
        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        excelWriter.write(tUserList, writeSheet);
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }

    @GetMapping("test03")
    public ResponseDTO test03(TUser tUser, Page<TUser> page){
        return ResponseDTO.success(tUserMapper.findUserPage(page,tUser));
    }

    @PostMapping("test04")
    public ResponseDTO test04(@RequestBody TUser tUser){
        tUser.setCreateTime(new Date());
        return ResponseDTO.success(tUserMapper.insert(tUser));
    }

    @PostMapping("test05")
    public ResponseDTO test05(@RequestBody Map<String,Object> param){
        List<Map<String, Object>> maps = tStudentMapper.test01(param);
//        List<Map<String, Object>> maps2 = tUserMapper.findMap(param);
        return ResponseDTO.success(maps);
    }

}