我方每天在服务器上生成对账文件, 第三方来取.
1.需求:
2.思路
生成对账文件的程序是一个定时任务: 凌晨2点运行此程序
1.设置好 对账文件存放的路径(工程部门给外部平台一个地址,他们自己过来取) 2.在这个路径下新建一个文件 . 格式是.txt 3. 连接数据库,查询支付表(可能多表查询), 查询前一天的数据. 按照接口文档需要的字段 查询到对应字段 ,并拼接 4.把查出来的数据(eg:101486120190325083431|20190325|10.00|0|1|0042930413|SWShopMall), 放到 .txt文件里
3.代码
package com.seaway.open.open_bank_luoyang_check; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.SQLException; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.List; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import org.yaml.snakeyaml.Yaml; import com.seaway.open.open_bank_luoyang_check.configure.Configure; import io.vertx.core.AbstractVerticle; import io.vertx.core.Vertx; import io.vertx.core.cli.Argument; import io.vertx.core.cli.CLI; import io.vertx.core.cli.TypedOption; import io.vertx.core.json.JsonArray; import io.vertx.core.json.JsonObject; import io.vertx.ext.jdbc.JDBCClient; import io.vertx.ext.jdbc.spi.impl.HikariCPDataSourceProvider; import io.vertx.ext.sql.ResultSet; import io.vertx.ext.sql.SQLConnection; public class Runner extends AbstractVerticle { private static final Logger logger = LogManager.getLogger(Runner.class); private Configure gconfigure = null; private JDBCClient jdbcClient = null; // Vertx vertx = Vertx.vertx(); public Configure getGconfigure() { return gconfigure; } public void setGconfigure(Configure gconfigure) { this.gconfigure = gconfigure; } public synchronized JDBCClient getJdbcClientInstance() { if (jdbcClient == null) { try { HikariCPDataSourceProvider HikariCPDataSource = new HikariCPDataSourceProvider(); JsonObject jdbcobject = JsonObject.mapFrom(getGconfigure().getJdbcConfig()); jdbcobject.put("autoCommit", true); jdbcClient = JDBCClient.create(vertx, HikariCPDataSource.getDataSource(jdbcobject)); logger.info("初始化数据库连接池成功 {}", jdbcobject.toString()); } catch (SQLException e) { logger.error("连接数据库失败：{}", e); } } return jdbcClient; } public static void main(String[] args) { Runner runner = new Runner(); Vertx vertx = Vertx.vertx(); runner.start(vertx); } private void initConfigure() { CLI cli = CLI.create("open_bank_luoyang_check").setSummary("开放平台xx银行对账文件生成程序") .addOption(new TypedOption<Boolean>().setType(Boolean.class).setLongName("configure") .setShortName("cfg").setDescription("指定配置文件").setFlag(true).setRequired(true)) .addArgument(new Argument().setDescription("文件绝对路径(yaml格式)").setArgName("filename").setRequired(true)); StringBuilder builder = new StringBuilder(); cli.usage(builder); String ConfigureFile = null; // int liFile = this.context.processArgs().indexOf("-cfg"); // if (liFile == -1) { // // logger.error("指定配置文件: [-cfg <filename>]"); // logger.error(builder); // stop(); // } // try { // ConfigureFile = this.context.processArgs().get(liFile + 1); // if (!new File(ConfigureFile).exists()) { // // logger.error("配置文件[" + ConfigureFile + "]不存在"); // logger.error(builder); // stop(); // } // } catch (Exception e) { // // logger.error("指定配置文件: [-cfg <filename>]",e); // logger.error(builder); // stop(); // // } Yaml yaml = new Yaml(); ConfigureFile = "F:/Users/Administrator/git/open6/open_bank_check/open_bank_luoyang_check/src/main/resources/open_bank_luoyang_check.yaml"; try { InputStream in = new FileInputStream(new File(ConfigureFile)); setGconfigure(yaml.loadAs(in, Configure.class)); logger.info("读取配置文件[" + ConfigureFile + "]:[" + getGconfigure() + "]"); } catch (Exception e) { logger.error("解析文件错误", e); stop(); } } @Override public void stop() { logger.info("关闭系统!"); vertx.close(); System.exit(0); } // @Override public void start(Vertx vertx) { initConfigure(); HikariCPDataSourceProvider HikariCPDataSource = new HikariCPDataSourceProvider(); try { JsonObject jdbcobject = JsonObject.mapFrom(getGconfigure().getJdbcConfig()); jdbcobject.put("autoCommit", true); jdbcClient = JDBCClient.create(vertx, HikariCPDataSource.getDataSource(jdbcobject)); logger.info("初始化数据库连接池成功 {}", jdbcobject.toString()); } catch (SQLException e) { logger.error("连接数据库失败：", e); } /** * 功能描述: 当天对前一天生活圈发生的支付或退款交易进行校对和清算 对账文件的文件名：SWShopMall_yyyyMMdd.txt 文件内容格式： 订单号 * |交易日期|金额|交易状态|交易类型|客户号 |商户号 20160602 |20190218|9.01| 0 | 1 | 037894566 * |SWShopMall 文件格式是.txt 文件中不能出现空格，空行 每天早上7点之前必须将前一天的对账文件放置服务器上，否则将影响对账交易 交易状态 * 0:成功1：失败 交易类型 1：付款2：退款; */ // 每晚2点生成 vertx.setTimer(getGconfigure().getFileConfigure().getTaskTime(), id1 -> { executeTask(); vertx.setPeriodic(getGconfigure().getFileConfigure().getCycleTaskTime(), id2 -> { executeTask(); }); vertx.cancelTimer(id1); }); } private void executeTask() { logger.info("定时任务开始"); String dateTime = LocalDateTime.now().minusDays(1).format(DateTimeFormatter.ofPattern("yyyyMMdd")); // 如果dateTime日期20190324时间小于设置的时间,则取配置时间 if (!getGconfigure().getCheckConfigure().getCheckDate().equals("00000000")) dateTime = getGconfigure().getCheckConfigure().getCheckDate(); String checkPath = getGconfigure().getCheckConfigure().getCheckPath(); String filePath = checkPath + "/SWShopMall_" + dateTime + ".txt"; logger.info("filePath: {}", filePath); File file = new File(filePath); if (file.exists()) { logger.info("文件已经生成 退出"); return; } else { logger.info("文件不存在 新建文件"); try { file.createNewFile(); logger.info("创建成功"); if (jdbcClient == null) { logger.info("jdbcClient is null getJdbcClientInstance"); jdbcClient = getJdbcClientInstance(); } else { logger.info("jdbcClient not null"); } String times = dateTime; jdbcClient.getConnection(jdbcConnect -> { if (jdbcConnect.failed()) { logger.error("连接出错: {}", jdbcConnect.cause().getMessage()); return; } else { final SQLConnection connection = jdbcConnect.result(); logger.info("取得连接"); // 查询数据库 String beginTime = times + "000000"; String endTime = times + "235959"; // 金额保留两位小数,多余舍掉 cast(trunc(pay_amount/10000,2) AS NUMBER (10, 2)) // 金额保留两位小数,四舍五入 to_char(pa.pay_amount/10000,'fm99,999,990.00') // 该sql金额会至少保留两位小数(单位元) String querySql = "\n\nselect pay_id || '|' || substr(pay_time, 0, 8) || '|' || \n" + "case \n" + " when substr(to_char(pay_amount/10000,'fm99,999,990.0000')，-2) > 00\n" + " then\n" + " to_char(pay_amount/10000,'fm99,999,990.0000')\n" + " else\n" + " to_char(pay_amount/10000,'fm99,999,990.00')\n" + " end \n" + " || '|' ||\n" + "case when stats in(3,5,9) then\n" + " 0\n" + " else\n" + " 1\n" + " end || '|' || 1 || '|' || user_id || '|' || '" + getGconfigure().getFileConfigure().getMerchantCode() + "'\n" + "from payment_item where pay_time between '" + beginTime + "' and '" + endTime + "' and gateway_id = '" + getGconfigure().getFileConfigure().getGateway_id() + "' and channel_id = '" + getGconfigure().getFileConfigure().getChannel_id() + "' \n" + "union all\n" + "select r.refund_id || '|' || substr(r.refund_time, 0, 8)|| '|' || \n" + "case \n" + " when substr(to_char(r.refund_amount/10000,'fm99,999,990.0000')，-2) > 00\n" + " then\n" + " to_char(r.refund_amount/10000,'fm99,999,990.0000')\n" + " else\n" + " to_char(r.refund_amount/10000,'fm99,999,990.00')\n" + " end \n" + "|| '|' ||\n" + "case r.stats \n" + " when 1 then\n" + " 0\n" + " when 2 then\n" + " 1\n" + " else\n" + " 1\n" + " end || '|' || 2 || '|' || p.user_id || '|' || '" + getGconfigure().getFileConfigure().getMerchantCode() + "'\n" + "from refund_item r,payment_item p \n" + "where r.pay_id = p.pay_id and r.refund_time between '" + beginTime + "' and '" + endTime + "' and r.gateway_id = '" + getGconfigure().getFileConfigure().getGateway_id() + "' and r.channel_id = '" + getGconfigure().getFileConfigure().getChannel_id() + "'\n"; logger.info("querySql: {}", querySql); connection.query(querySql, queryresult -> { if (queryresult.succeeded()) { connection.close(); logger.info("queryresult succeeded"); ResultSet queryResultSet = queryresult.result(); List<JsonArray> jsonArraysList = queryResultSet.getResults(); // logger.info("jsonArraysList: {}", jsonArraysList); FileOutputStream outSTr = null; BufferedOutputStream buff = null; try { outSTr = new FileOutputStream(file); } catch (FileNotFoundException e1) { logger.error(e1); } buff = new BufferedOutputStream(outSTr); logger.info("size: " + jsonArraysList.size()); for (int i = 0; i < jsonArraysList.size(); i++) { /*** 把数据写入到磁盘 */ try { buff.write((jsonArraysList.get(i).getString(0) + "\n").getBytes()); // 每多少次清理一次 缓冲区 if (i % getGconfigure().getFileConfigure().getFlushCount() == 0 && buff != null) { buff.flush(); logger.info("flush: " + i); } } catch (IOException e) { logger.error("写入磁盘出错", e); } } if (buff != null) { try { buff.flush(); buff.close(); } catch (IOException e) { logger.error("关闭出错", e); } } logger.info("{}日对账文件写入磁盘成功,共{}条", times, jsonArraysList.size()); } else { connection.close(); logger.error("执行查询错误,关闭连接: {}", queryresult.cause().getMessage()); } }); } }); } catch (IOException e) { logger.error("程序异常: ", e); } } } }
控制台运行此程序,对账文件生成
.........................
......................
去这个路径下看看,发现对账文件已经生成了
有bug--回头看发现程序里面需要一个回车符
4.补充
这个程序重点:
1.要熟悉java基础.(建文件 /往文件写数据)
2.sql函数的运用.
公司数据库用的是oracle数据库. 下面是把生成对账文件内容的sql 从程序里摘出来.大家可以学习下
select pay_id || '|' || substr(pay_time, 0, 8) || '|' || case when substr(to_char(pay_amount/10000,'fm99,999,990.0000')，-2) > 00 then to_char(pay_amount/10000,'fm99,999,990.0000') else to_char(pay_amount/10000,'fm99,999,990.00') end || '|' || case when stats in(3,5,9) then 0 else 1 end || '|' || 1 || '|' || user_id || '|' || 'SWShopMall' from payment_item where pay_time between '20190326000000' and '20190326235959' and gateway_id = '10' and channel_id = '1' union all select r.refund_id || '|' || substr(r.refund_time, 0, 8)|| '|' || case when substr(to_char(r.refund_amount/10000,'fm99,999,990.0000')，-2) > 00 then to_char(r.refund_amount/10000,'fm99,999,990.0000') else to_char(r.refund_amount/10000,'fm99,999,990.00') end || '|' || case r.stats when 1 then 0 when 2 then 1 else 1 end || '|' || 2 || '|' || p.user_id || '|' || 'SWShopMall' from refund_item r,payment_item p where r.pay_id = p.pay_id and r.refund_time between '20190326000000' and '20190326235959' and r.gateway_id = '10' and r.channel_id = '1'
在数据库运行该sql, 这个就是对账文件里面放的内容. 已经用sql拼接好了.
多加思考,多加训练.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~补充二~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
上面的sql太长了, 是怎么要达到我想要的内容呢
1.先查出接口文档中字段(对账文件需要的字段).
标准的文件内容格式：
20160602|20190218|9.01|0|1|037894566|SWShopMal
2.然后再根据需求 改. 加函数/ 加判断
eg:时间格式 / 金额显示格式(保留几位小数) / 只显示渠道的支付记录/ 等等
2001
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
