服务端相互调用使用非对称加密鉴权实现数据库数据定时同步

需求背景

一个springboot框架写的SaaS系统的crm(客户管理)项目,需要对应做一个报表系统,主要功能有业绩管理(业绩排行榜/银行进件排行榜)和租户数据统计(渠道星级统计/客户转化率分析/租户用户统计).为了不影响原项目,数据库和redis等中间件全分开了.所以这里就有个功能要实现,就是数据库数据同步.

设计思路

1.想用Canel的,复杂度高,商量了下暂不考虑;
2.原项目已经集成了MybatisPlus,可以考虑其拦截器拦截指定表增删改操作,要注意事务回滚的处理;
3.本次报表项目不需要很高的实时性,选择了报表系统直接开启job定时http的方式进行数据拉取.参照的是数据的更新时间(项目要严格这个字段的时间填充).

代码实现(没有用appId和随机值,这个可以考虑加上)

报表系统(客户表字段减了很多)

package cn.iocoder.yudao.module.report.service.impl;

import cn.hutool.core.codec.Base64;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.SecureUtil;
import cn.hutool.crypto.asymmetric.KeyType;
import cn.hutool.crypto.asymmetric.RSA;
import cn.hutool.crypto.asymmetric.Sign;
import cn.hutool.crypto.asymmetric.SignAlgorithm;
import cn.hutool.http.HttpUtil;
import cn.iocoder.yudao.framework.common.util.date.DateUtil;
import cn.iocoder.yudao.framework.tenant.core.context.TenantContextHolder;
import cn.iocoder.yudao.module.report.dao.entity.*;
import cn.iocoder.yudao.module.report.service.*;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.service.IService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Component
@Slf4j
public class LamsDataSyncServiceImpl implements LamsDataSyncService {
    @Value("${lams.url}")
    private String lamsUrl;
    @Autowired
    private CustLogService custLogService;
    @Autowired
    private CustIncomingInfoService custIncomingInfoService;
    @Autowired
    private CustInfoService custInfoService;
    @Autowired
    private CustIncomingBanksService custIncomingBanksService;
    @Autowired
    private BaseChannelService baseChannelService;
    @Autowired
    private CustCommentRecordService custCommentRecordService;
    @Autowired
    private SystemTenantService systemTenantService;
    @Autowired
    private TenantSystemUsersService tenantSystemUsersService;
    @Value("${signature.publicKey}")
    private String publicKey;
    @Value("${signature.privateKey}")
    private String privateKey;
    private RSA rsa;
    private Sign sign;

    @PostConstruct
    public void init() {
        rsa = SecureUtil.rsa(privateKey, publicKey);
        sign = SecureUtil.sign(SignAlgorithm.SHA256withRSA, privateKey, publicKey);
    }


    @Override
    public void execute(Integer min) {
        min = min == null ? 60 : min;
        Date endTime = new Date();
        Date startTime = DateUtil.addMinute(endTime, -min);
        handleTable("cust_log", CustLogDO.class, custLogService, startTime, endTime, "*");
        handleTable("cust_incoming_info", CustIncomingInfoDO.class, custIncomingInfoService, startTime, endTime, "*");
        handleTable("cust_incoming_banks", CustIncomingBanksDO.class, custIncomingBanksService, startTime, endTime, "*");
        handleTable("base_channel", BaseChannelDO.class, baseChannelService, startTime, endTime, "*");
        handleTable("cust_comment_record", CustCommentRecordDO.class, custCommentRecordService, startTime, endTime, "*");
        handleTable("system_tenant", SystemTenantDO.class, systemTenantService, startTime, endTime, "*");
        handleTable("system_users", TenantSystemUsersDO.class, tenantSystemUsersService, startTime, endTime, "*");
        String custColumns = Arrays.stream(CustInfoDO.class.getDeclaredFields()).filter(field -> !"serialVersionUID".equals(field.getName())).map(field -> {
            String fieldName = field.getName();
            if (StringUtils.isBlank(fieldName)) {
                return null;
            } else {
                TableField annotation = field.getAnnotation(TableField.class);
                if (annotation != null && !annotation.exist()) {
                    return null;
                } else {
                    return StrUtil.toUnderlineCase(fieldName);
                }
            }
        }).filter(Objects::nonNull).collect(Collectors.joining(",")) + "," + String.join(",", "creator", "updater", "create_time", "update_time", "deleted");
        handleTable("cust_info", CustInfoDO.class, custInfoService, startTime, endTime, custColumns);
    }

    private <T extends BaseSyncDO, M extends IService<T>> void handleTable(String tableName, Class<T> clazz, M service, Date startTime, Date endTime, String columns) {
        Map<String, Object> paramMap = new HashMap<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        paramMap.put("tableName", tableName);
        paramMap.put("columns", columns);
        paramMap.put("startTime", sdf.format(startTime));
        paramMap.put("endTime", sdf.format(endTime));
        paramMap.put("timestamp", System.currentTimeMillis());
        String jsonParam = JSON.toJSONString(paramMap);
        String lamsSyncUrl = lamsUrl + "/reportsync/getTableData";
        try {
            String paramEnStr = rsa.encryptBase64(jsonParam, KeyType.PrivateKey);
            String signEnStr = Base64.encode(sign.sign(paramEnStr.getBytes(StandardCharsets.UTF_8)));
            Map<String, Object> secParam = new HashMap<>();
            secParam.put("paramEnStr", paramEnStr);
            secParam.put("signEnStr", signEnStr);
            String res = HttpUtil.post(lamsSyncUrl, secParam);
            if (StringUtils.isBlank(res)) {
                return;
            }
            String dataDe = rsa.decryptStr(res, KeyType.PrivateKey);
            if (StringUtils.isBlank(res) || "[]".equals(dataDe)) {
                return;
            }
            List<T> list = JSON.parseArray(dataDe, clazz);
            if (CollectionUtil.isNotEmpty(list)) {
                TenantContextHolder.setIgnore(true);
                service.saveOrUpdateBatch(list, 200);
            }
        } catch (Exception e) {
            log.error("lams同步数据失败", e);
        }
    }

}

crm系统

package cn.iocoder.yudao.module.system.service.reportsync;

import cn.hutool.core.codec.Base64;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.crypto.asymmetric.KeyType;
import cn.hutool.crypto.asymmetric.RSA;
import cn.hutool.crypto.asymmetric.Sign;
import cn.hutool.crypto.asymmetric.SignAlgorithm;
import cn.iocoder.yudao.module.system.api.dict.DictDataApi;
import cn.iocoder.yudao.module.system.api.dict.dto.DictDataRespDTO;
import com.alibaba.fastjson.JSONObject;
import com.beust.jcommander.internal.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;


/**
 * @author ldq
 * @version 1.0
 * @date 2024/4/1 11:15
 */
@Service
@Slf4j
public class ReportSyncServiceImpl implements ReportSyncService {

    @Value("${signature.publicKey}")
    private String publicKey;
    @Value("${signature.privateKey}")
    private String privateKey;

    private RSA rsa;
    private Sign sign;

    @Autowired
    private DictDataApi dictDataApi;

    @PostConstruct
    public void init() {
        rsa = new RSA(privateKey, publicKey);
        sign = new Sign(SignAlgorithm.SHA256withRSA, privateKey, publicKey);
    }

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public String getTableData(String paramEnStr, String signEnStr) {

        //验签加解密
        String dataEn = validSignData(paramEnStr, signEnStr);
        if (StringUtils.isBlank(dataEn)) {
            return null;
        }
        JSONObject jsonObject = JSONObject.parseObject(dataEn);
        String tableName = jsonObject.getString("tableName");
        String columns = jsonObject.getString("columns");
        String startTime = jsonObject.getString("startTime");
        String endTime = jsonObject.getString("endTime");
        Long timestamp = jsonObject.getLong("timestamp");
        if (StringUtils.isAnyBlank(tableName, columns, startTime, endTime) || timestamp == null) {
            log.info("参数错误");
            return null;
        }
        List<DictDataRespDTO> reportTableList = dictDataApi.getListByDictType("report_table_list");
        if (CollectionUtil.isEmpty(reportTableList)) {
            return null;
        }
        Set<String> tableNames = reportTableList.stream().map(DictDataRespDTO::getValue).collect(Collectors.toSet());
        if (!tableNames.contains(tableName)) {
            log.info("表名非法");
            return null;
        }
        //判断请求时间不能超过5秒钟
        if (System.currentTimeMillis() - timestamp > 5000) {
            log.info("请求时间超过5秒");
            return null;
        }

        try {
            List<Map<String, Object>> result = Lists.newArrayList();
            String sql = "select " + columns + " from " + tableName + " where update_time between '" + startTime + "' and '" + endTime + "'" + " limit %s, %s";
            int current = 0;
            int size = 200;
            do {
                String sqlLimit = String.format(sql, current, size);
                List<Map<String, Object>> maps = jdbcTemplate.queryForList(sqlLimit);
                if (CollectionUtil.isEmpty(maps)) {
                    break;
                }
                result.addAll(maps);
                current += maps.size();
            } while (true);
            if (CollectionUtil.isEmpty(result)) {
                log.info("查询结果为空");
                return null;
            }
            return rsa.encryptBase64(JSONObject.toJSONString(result), KeyType.PublicKey);
        } catch (Exception e) {
            log.error("ReportSyncServiceImpl-查询失败,tableName:{},startTime:{},endTime:{}", tableName, startTime, endTime, e);
        }
        return null;
    }

    private String validSignData(String paramEnStr, String signEnStr) {
        String dataDe = null;
        try {
            boolean verify = sign.verify(paramEnStr.getBytes(StandardCharsets.UTF_8), Base64.decode(signEnStr.getBytes(StandardCharsets.UTF_8)));
            if (!verify) {
                log.info("验签失败");
                return null;
            }
            dataDe = rsa.decryptStr(paramEnStr, KeyType.PublicKey);
        } catch (Exception e) {
            log.error("解密或验签失败", e);
        }
        return dataDe;
    }
}

注意点

1.加解密和base64都是统一用的hutool工具,这里最好统一;
2.在数据请求的时候使用的不是get请求而是post请求,因为get请求会转义参数;
3.定时任务的参数和频率一定要配合使用.限制数据传输的多少,太多还要考虑分段加解密;
4.本次数据的插入使用了mybatisplus的Iservice插件里的批量更新或者删除的方法,并且这里数据映射对象的deleted字段不要加逻辑删除注解;
5.本次是依赖表的更新时间,要对查询和插入的sql提前做好性能优化,比如索引等.