Spring Boot MyBatis-Plus跨数据库连接进行数据传输

1 项目：效果图

2 项目背景：适应灵活多变的业务数据需要

3 项目说明：根据生产厂家提取正式业务系统账套的数据，经过调整后，推送到流向系统账套

4 细节说明：

• a 生产厂家input框必填，适应业务需要，精准检索有效数据
• b 表格空数据时的，tbody区域的展示

• c 点击查看历史库存、保存并提交按钮的空数据检查
  
  

• d 拉取正式业务系统数据后，调整数据（可删、可改）
  

• e 保存提交时，确认数据提醒，推送数据结果展示，可以进行数据二次核查，另外发现问题可以追悔删除
  
  

5 技术范围：HTML、CSS、JavaScript、jQuery、Maven、SpringBoot、MyBatis、springMVC

6 项目代码展示：

配置文件多数据库

spring:
  datasource:
    dynamic:
      primary: master
      strict: false
      datasource:
        master:
          url: jdbc:sqlserver://192.168.0.200:1433;DatabaseName=***
          username: sa
          password: *
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          type: com.zaxxer.hikari.HikariDataSource
        slave_1:
          url: jdbc:sqlserver://192.168.0.201:1433;DatabaseName=***
          username: sa
          password: *
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          type: com.zaxxer.hikari.HikariDataSource

Controller

@Controller
public class StockController {
    @Autowired
    private StockService stockService;
    @Autowired
    private ZlxStockMapper zlxStockMapper;
//拉取数据
    @RequestMapping("/stock")
    public String toHtml(@RequestParam(value = "factoryName", required = false) String factoryName, @RequestParam(value = "goodsName", required = false) String goodsName, Model model) {
        if (StringUtils.isNotBlank(factoryName)) {
            QueryWrapper<Stock> queryWrapper = new QueryWrapper<>();
            queryWrapper.like("shengccj", factoryName).like(StringUtils.isNotBlank(goodsName), "spmch", goodsName).orderByAsc("spid");
            List<Stock> list = stockService.list(queryWrapper);
            model.addAttribute("stockList", list);
        }
        return "stock";
    }
//保存数据
    @PutMapping("/stock/data")
    @ResponseBody
    public List<Map<String, Object>> getData(@RequestBody List<Map<String, Object>> list) {
        ArrayList<String> spids = new ArrayList<>();
        String goodsIdTemp = "";
        for (Map<String, Object> map : list) {
            String spid = (String) map.get("spid");
            if (spid != goodsIdTemp) {
                goodsIdTemp = spid;
                spids.add(spid);
            }
            System.out.println(spid);
            String hw = (String) map.get("hw");
            String rkdjsn = (String) map.get("rkdjsn");
            BigDecimal shl = new BigDecimal((Integer) map.get("shl"));
            zlxStockMapper.insert(spid, hw, rkdjsn, shl);
        }
        return zlxStockMapper.getZlxSphwph(spids, "", "");
    }
//查看历史库存
    @GetMapping(value = "/stock/hisQty")
    @ResponseBody
    public List<Map<String, Object>> getHistoryQty(HttpServletRequest request) {
        String goodsName = request.getParameter("goodsName");
        String factoryName = request.getParameter("factoryName");
        List<Map<String, Object>> zlxSphwph = zlxStockMapper.getZlxSphwph(null, goodsName, factoryName);
        for (Map<String, Object> map : zlxSphwph) {
            System.out.println("=====>" + map);
        }
        return zlxSphwph;
    }
//发现问题，追悔删除
    @DeleteMapping(value = "/stock/uploadData")
    @ResponseBody
    public int delUploadData(String spids) {
        if (spids.endsWith(",")) {
            spids = spids.substring(0, spids.length() - 1);
            return zlxStockMapper.delHistoryData(spids);
        }
        return 0;
    }
}

Mapper

-- sql片段
<sql id="columns2">
        spid
        , hw, pihao, baozhiqi, dangqzht,  gebjj, miejph, sxrq, bukxshl, yanghriqi, xsshl, Bkxshl, ykpshl, jh_kfp, rkdwbh, rkrq, th_spdj, th_spdjb, hscksj, wscksj, cgywy, rkdjsn, qylx, hdzc, xxkh, kxxq
    </sql>
    
    -- 保存数据
    <insert id="insert">
        delete
        from sphwph
        where spid = #{spid}
        and hw = #{hw}
        and rkdjsn = #{rkdjsn}
        insert
        into sphwph( <include refid="columns2"></include>,shl)
        SELECT
        <include refid="columns2"></include>,#{shl}
        FROM hsdjt.ksoadjt.dbo.sphwph
        where spid = #{spid}
        and hw = #{hw}
        and rkdjsn = #{rkdjsn}
    </insert>
    
    -- 发现问题追悔删除
    <delete id="delHistoryData">
        delete
        from sphwph
        where spid in (#{spids})
    </delete>
    
    -- 查看历史库存数据
    <select id="getZlxSphwph" resultType="map">
        select b.spid,b.spbh,b.spmch,b.shpgg,b.shengccj,rtrim(a.pihao) pihao,a.shl,a.hw,rtrim(a.rkdjsn)
        rkdjsn,a.baozhiqi,a.sxrq
        from sphwph a,
        spkfk b
        where a.spid = b.spid
        <if test="spids !=null and spids != ''">
            and b.spid in
            <foreach collection="spids" item="spid" open="(" separator="," close=")">
                #{spid}
            </foreach>
        </if>
        <if test="goodsName!=null and goodsName!=''">
            and b.spmch like '%${goodsName}%'
        </if>
        <if test="factoryName!=null and factoryName!=''">
            and b.shengccj like '%${factoryName}%'
        </if>
        order by a.spid
    </select>