Java微信支付对帐，微信账单下载并读取到实体Bean，并保存至数据库

最近公司的项目需要微信对帐功能，这里展示了简单的微信账单下载并读取到数据库方法，有问题或者更好的想法的可以在评论区交流哟。

一、依赖

<!-- 微信支付 -->
<dependency>
    <groupId>com.github.wechatpay-apiv3</groupId>
    <artifactId>wechatpay-java</artifactId>
    <version>0.2.12</version>
</dependency>

<!-- csv处理 -->
<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.9</version>
</dependency>

二、参考链接

https://pay.weixin.qq.com/docs/merchant/products/bill-download/development.html

https://opencsv.sourceforge.net/

三、详解

1. 下载微信账单

    public boolean downloadTradeBill(String time, String filePath) {
        // time = "2023-11-16"; 事例
        // 获取交易账单
        RSAAutoCertificateConfig config = this.configManager.getConfig("payOrder");
        BillDownloadServiceExtension service = new BillDownloadServiceExtension.Builder().config(config).build();
        GetTradeBillRequest request = new GetTradeBillRequest();
        request.setBillDate(time);
        request.setBillType(BillType.ALL);
        request.setTarType(TarType.GZIP);
        DigestBillEntity bill = service.getTradeBill(request);

        try (InputStream inputStream = bill.getInputStream()) {
            // 使用有缓存的 BufferedOutputStream
            try (BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(filePath))) {
                // 处理大文件时，常用的缓冲区大小为 8192 或 16384。
                // 不过，最佳缓冲区大小可能取决于具体的硬件和系统配置。
                byte[] buffer = new byte[16384];
                int bytesRead;
                while ((bytesRead = inputStream.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, bytesRead);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        // 验证数据流中已读取数据的摘要
        if (bill.verifyHash()) {
            // 账单是完整准确，可以开启后续操作，例如完成每日对账。
            log.info("微信账单下载验证成功！");
            return true;
        } else {
            // 账单不完整或者被篡改，应清理之前保存的文件
            try {
                Path path = Paths.get(filePath);
                Files.delete(path);
            } catch (IOException e) {
                log.error(e.toString());
            }
            return false;
        }
    }

注意：RSAAutoCertificateConfig config = this.configManager.getConfig(“payOrder”);

请根据自身项目按照实际获取微信支付的RSAAutoCertificateConfig 。

注意：下载的文件格式为UTF-8 BOM 这里有坑，请一定小心

2. 过滤微信账单的无用部分（完整详细账单格式请见：https://pay.weixin.qq.com/docs/merchant/products/bill-download/format-trade.html）
   

	// 微信账单去除最后无用部分(最后两行汇总信息)
	private void removeUselessParts(String inputFilePath, String outputFilePath) {
		try (
				BufferedReader reader = new BufferedReader(new FileReader(inputFilePath, StandardCharsets.UTF_8));
				FileWriter writer = new FileWriter(outputFilePath, StandardCharsets.UTF_8)
		) {
			String line = null, line1 = null, line2 = null, line3 = null;
			int count = 0;
			while ((line = reader.readLine()) != null) {
				line3 = line2;
				line2 = line1;
				line1 = line;
				if (count < 2) {
					count++;
				} else {
					writer.write(line3 + "\n");
				}
			}
		} catch (IOException e) {
			logger.error(e.toString());
		}
	}

3. 读取微信账单

微信支付账单实体Bean（可以按需获取，本例只引用了部分）

public class Bill extends BaseEntity {
    // 交易时间
    @CsvBindByPosition(position = 0, capture = "`(.*)")
    @CsvDate("yyyy-MM-dd HH:mm:ss")
    private Date tradingHours;

    // 微信订单号
    @CsvBindByPosition(position = 5, capture = "`(.*)")
    private String weChatOrderNumber;

    // 商户订单号
    @CsvBindByPosition(position = 6, capture = "`(.*)")
    private String merchantOrderNumber;

    // 交易状态
    @CsvBindByPosition(position = 9, capture = "`(.*)")
    private String transactionStatus;

    // 应结订单金额
    @CsvBindByPosition(position = 12, capture = "`(.*)")
    private String payableOrderAmount;

    // 微信退款单号
    @CsvBindByPosition(position = 14, capture = "`(.*)")
    private String weChatRefundNumber;

    // 商户退款单号
    @CsvBindByPosition(position = 15, capture = "`(.*)")
    private String merchantRefundNumber;

    // 退款金额
    @CsvBindByPosition(position = 16, capture = "`(.*)")
    private BigDecimal refundAmount;

    // 订单金额
    @CsvBindByPosition(position = 24, capture = "`(.*)")
    private BigDecimal amount;

    // 申请退款金额
    @CsvBindByPosition(position = 25, capture = "`(.*)")
    private BigDecimal requestRefundAmount;
}

读取微信账单csv文件

	public void readAndSaveWeChatTradeBill(String filePath) {
        InputStreamReader reader = null;
		try {
			reader = new InputStreamReader(new FileInputStream(filePath), StandardCharsets.UTF_8);
			CsvToBean<WeChatTradeBill> csvToBean = new CsvToBeanBuilder<WeChatTradeBill>(reader)
					.withType(WeChatTradeBill.class)
					.withSkipLines(1)
					.build();
			// 使用迭代器读取并分步保存
			int count = 0;
			List<WeChatTradeBill> list = new ArrayList<>();
			for (WeChatTradeBill next : csvToBean) {
				count++;
				if (count % 100 == 0) {
					weChatTradeBillDao.saveAll(list);
					list = new ArrayList<>();
				} else {
					list.add(next);
				}
			}

			if (!CollectionUtils.isEmpty(list)) {
				weChatTradeBillDao.saveAll(list);
			}

		} catch (FileNotFoundException e) {
			throw new RuntimeException(e);
		} finally {
			try {
                assert reader != null;
                reader.close();
			} catch (IOException e) {
				logger.error(e.toString());
			}
		}
    }

四、完整参考

public void autoImportWeChatTradeBill(String date) {
		String time;
		if (date == null) {
			LocalDate threeDayAgo = LocalDate.now().minusDays(3L);
			time = threeDayAgo.format(DateTimeFormatter.ISO_LOCAL_DATE);
		} else {
			time = LocalDate.parse(date, DateTimeFormatter.ISO_DATE).format(DateTimeFormatter.ISO_DATE);
		}

		String downloadFilePath = System.getProperty("user.dir") + "\\" + time + "-wechat.csv";
		String updateFilePath = System.getProperty("user.dir") + "\\" + time + "-wechat-update.csv";
		// 尝试下载账单文件，并且去除最后无用部分
		int count = 0;
		while (count < 10) {
			count++;
			boolean b = wechatUtils.downloadTradeBill(time, downloadFilePath);
			if (b) {
				break;
			} else if (count >= 10) {
				logger.error("多次尝试，账单仍下载失败！！！");
			}
		}
		this.removeUselessParts(downloadFilePath, updateFilePath);
		// 解决this自调用事务失效问题
		WeChatTradeBillServiceImpl bean = applicationContext.getBean(WeChatTradeBillServiceImpl.class);
		bean.readAndSaveWeChatTradeBill(updateFilePath);
		// 删除之前下载的账单文件。
		removeOldDownloadFile(downloadFilePath);
		removeOldDownloadFile(updateFilePath);
	}

// 删除下载的微信账单文件
private void removeOldDownloadFile(String filePath) {
    try {
        Path path = Paths.get(filePath);
        Files.delete(path);
    } catch (IOException e) {
        logger.error(e.toString());
    }
}