Springboot+MybatisPlus多数据源比对数据

欢迎关注博主公众号「Java大师」, 专注于分享Java领域干货文章https://www.javaman.cn/

基于不同的数据源进行数据比对，比较两个数据库的表之间数据的总量，以及处理后的总量，按天进行比对，将比对结果输出到核对表中。

一、工程目录

二、Mybatisplus多数据源配置

1、pom.xml

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>

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

2、application.yml

spring:
  datasource:
    dynamic:
      primary: tfss   #默认数据源
      strict: false
      datasource:
        ttba: #数据源1
          username: ttba
          password: ttba
          url: jdbc:oracle:thin:@127.0.0.1:1521:ORCL
          driver-class-name: oracle.jdbc.driver.OracleDriver
        tfss: #数据源2
          username: tfss
          password: tfss
          url: jdbc:oracle:thin:@127.0.0.1:1521:ORCL
          driver-class-name: oracle.jdbc.driver.OracleDriver
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  #开启MybatisPlus输出

三、业务代码

1、MybatisPlusConfig

package com.ds.check.config;

import com.baomidou.mybatisplus.extension.incrementer.OracleKeyGenerator;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
	添加oracle的主键策略
*/
@Configuration
public class MybatisPlusConfig {
    @Bean
    public OracleKeyGenerator oracleKeyGenerator(){
        return new OracleKeyGenerator();
    }
}

2、实体类

核对结果表

@Data
@TableName("ST_ZFQLC_CHECK_DATA")
@KeySequence("SEQ_ZFQLC_CHECK_DATA")
public class ZfqlcCheckData {
    @TableId(value = "Id",type = IdType.INPUT)
    private Integer id;
    private String xtbaTable;
    private String zfqlcTable;
    private String transTable;
    private Integer zfqlcSl;
    private Integer transSl;
    private Integer xtbaSl;
    private String cycle;

配置表

package com.ds.check.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.KeySequence;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("ST_ZFQLC_CHECK_TABLE")
public class ZfqlcCheckTable {
    private String xtbaTable;
    private String zfqlcTable;
    private String transTable;
}

3、mapper接口

package com.ds.check.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
@DS("tfss")  //DS关键字配置数据源
public interface TransMapper extends BaseMapper {
    Integer selectTotal(@Param("tableName") String tableName);
}

package com.ds.check.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
@DS("ttba") //DS关键字配置数据源
public interface XtbaMapper extends BaseMapper {
    Integer selectTotal(@Param("tableName") String tableName);
}

package com.ds.check.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ds.check.entity.ZfqlcCheckData;
import org.apache.ibatis.annotations.Mapper;

@Mapper
@DS("ttfs")
public interface ZfqlcCheckDataMapper extends BaseMapper<ZfqlcCheckData> {
}

package com.ds.check.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ds.check.entity.ZfqlcCheckData;
import com.ds.check.entity.ZfqlcCheckTable;
import org.apache.ibatis.annotations.Mapper;

@Mapper
@DS("ttfs")
public interface ZfqlcCheckTableMapper extends BaseMapper<ZfqlcCheckTable> {
}

package com.ds.check.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
@DS("ttfs")
public interface ZfqlcMapper extends BaseMapper {
    Integer selectTotal(@Param("tableName") String tableName);
}

4、mapper.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.ds.check.mapper.ZfqlcMapper">

    <!-- selectByName -->
    <select id="selectTotal" resultType="java.lang.Integer">
        select count(*) from ${tableName} where to_char(zxgxsj,'yyyymmdd')=to_char(sysdate-1,'yyyymmdd')
    </select>

</mapper>

四、主程序入口

package com.ds.check;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import com.ds.check.entity.ZfqlcCheckData;
import com.ds.check.entity.ZfqlcCheckTable;
import com.ds.check.mapper.*;
import lombok.extern.slf4j.Slf4j;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
@MapperScan(value = "com.ds.check.mapper")
@Slf4j
//实现CommandLineRunner类似于普通java程序的main函数入口
public class CheckApplication implements CommandLineRunner {

    @Autowired
    private ZfqlcCheckDataMapper zfqlcCheckDataMapper;
    @Autowired
    private ZfqlcCheckTableMapper zfqlcCheckTableMapper;

    @Autowired
    private XtbaMapper xtbaMapper;
    @Autowired
    private ZfqlcMapper zfqlcMapper;
    @Autowired
    private TransMapper transMapper;


    public static void main(String[] args) {
        SpringApplication.run(CheckApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE,-1);
        String yesterday = new SimpleDateFormat( "yyyyMMdd").format(cal.getTime());
        System.out.println(yesterday);
        List<ZfqlcCheckTable> list = zfqlcCheckTableMapper.selectList(null);
        for (ZfqlcCheckTable zfqlcCheckTable : list) {
            log.info("开始处理记录:"+zfqlcCheckTable.getXtbaTable()+","+zfqlcCheckTable.getZfqlcTable()+","+zfqlcCheckTable.getTransTable());
            Integer totalXtba = xtbaMapper.selectTotal(zfqlcCheckTable.getXtbaTable());
            Integer totalZfqlc = zfqlcMapper.selectTotal(zfqlcCheckTable.getZfqlcTable());
            Integer totaltrans = transMapper.selectTotal(zfqlcCheckTable.getTransTable());
            ZfqlcCheckData zfqlcCheckData = new ZfqlcCheckData();
            zfqlcCheckData.setXtbaTable(zfqlcCheckTable.getXtbaTable());
            zfqlcCheckData.setZfqlcTable(zfqlcCheckTable.getZfqlcTable());
            zfqlcCheckData.setTransTable(zfqlcCheckTable.getTransTable());
            zfqlcCheckData.setXtbaSl(totalXtba);
            zfqlcCheckData.setZfqlcSl(totalZfqlc);
            zfqlcCheckData.setTransSl(totaltrans);
            zfqlcCheckData.setCycle(yesterday);
            zfqlcCheckDataMapper.insert(zfqlcCheckData);
        }
    }
}