Java需求：SQL查询结果生成指定格式的本地.txt 文件并上传远程服务器

Java需求：SQL查询结果生成指定格式的本地.txt 文件并上传远程服务器（DB2数据库）

1. 文件格式

格式：
name(姓名)_|idNo(证件号)_|idType(证件类型)_|prodNo(产品号)_|money(金额)_|serialNo(流水号)

例：
张三_|123123123123123123_|01_|36_|10000.00_|********

注意：
1.严格按照字段顺序赋值
2.字段之间使用_|分隔
3.首位无分隔符

4.产品号两个固定产品（zhu1,zhu2）

5.查询结果中证件类型需要转换处理再输出

2. 查询结果生成文件内容方法

private File createFileBySql(SqlBeanManager sqlbeanManger,String changeNo) throws Exception{
    //根据自己框架获取数据库连接
    StateMent stmt = sqlBeanManger.getConn().createStatement();
    String querySql = "select name,idNO,idType,'zhu1' as prodNo,'money' as money,serialNo from table1" + "union" + "select name,idNO,idType, 'zhu2' as prodNo,money,serialNo from table2";
    //查询并计算金额
    String querySql1 = "查询金额等信息的sql";
    ResultSet resultSet = stmt.executeQuery(querySql1);
    //存放金额列表
    List<String> list new ArrayList<>();
    //循环遍历
    while(resultSet.next()) {
        
        //获取流水号
        String serialNo1 = result.getString("查询字段1");
        //获取其他表关联流水号
        String serialNo2 = result.getString("查询字段2");
        //总金额（根据数据库表字段类型获取）
        BigDecimal countMoney = result.getBigDecimal("查询字段总金额");
        //成本
        BigDecimal cb = result.getBigDecimal("查询字段成本");
        //假设查询利润金额 = 总金额 - 花费金额之和 - 总成本
        //省去金额查询，只做金额处理
       	...
        //花费金额之和nvl(sum(nvl(money,0)),0) = 判断总花费金额是否为空，如果为空，设置为0
        String sumMoney = "select nvl(sum(nvl(money,0)),0) from table3 where 条件";
        //String 转 BigDecimal
        BigDecimal sumMoneyBigDecimal = new BigDecimal(sumMoney);
        //获取利润金额
        BigDecimal lr = sumMoney.subtract(cb).subtract(sumMoneyBigDecimal);
        //转成字符串添加到列表
        list.add(lr.toString());
    }
    
    //建立输出路径
    String outPath = "./temp_file/upload" + StringFunction.getToday().replace("/","")
        +StringFunction.getNow().replace(":","")+".txt";
    
    return createFileBySqlAndFmt(stmt,querySql,"~|~","UTF-8",outPath,"|\n",list);
   
    
}

3.将查询结果生成格式文件

/**
querySql:查询输出结果
colSeparate：字段之前分隔符
fileEncoding：文件编码格式
outPath：文件输出路径
rowEnd：文件输出内容
list : 利润金额拼接到文件中
*/
public static File createFileBySqlAndFmt(Statement stmt,String querySql,String colSeparate,String fileEncoding,String outPath,String rowEnd,List<String> list) throws Excption {
    if (Objects.isEmpty(querySql)) {
        throw new SQLException("querySql is empty");
    }
    
    if (Objects.isEmpty(outPath)) {
        throw new SQLException("querySql is empty");
    }
    
    if (Objects.isEmpty(colSeparate)) {
        colSeparate = "~|~";
    }
    
    if (Objects.isEmpty(fileEncoding)) {
       fileEncoding = "UTF-8" ;
    }
    if (Objects.isEmpty(rowEnd)) {
        rowEnd = "/n";
    }
    
    if (list.size == 0 || list == null) {
        throw new SQLException("querySql is empty");
    }
    //证件类型转换
    HashMap<String,String> stringMap = new HashMap<String,String>();
    String sql = "select bankNo,itemNo from code_library where codeNo = 'type1'";
    ResultSet resultSet = stmt.executeQuery(sql);
    while() {
        stringMap.put(resultSet.getString("bankNo"),resultSet.getString("itemNo"));
    }
    
    File outDirFile = new File(outPath);
    if(!outDirFile.getParentFile().exists())) {
        boolean result = outDirFile.getParentFile().mkdirs();
        log.info(outDirFile.getParentFile().getPath + "不存在，建立新目录，创建结果：" + result);
    }
    
    BufferedWriter bw = null;
    ResultSet rs = null;
    try {
        
        bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outPath),fileEncoding));
        int recordCount = 0;
        rs = stmt.executeQuery(querySql);
        //获取查询结果集表结构信息
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取列数
        int iColCount = rsmd.getColumnCount();
        String value;
        StringBuilder rowValue;
        while(rs.next()) {
            recordCount++;
            rowValue = new StringBuilder();
            for(int i = 1; i <= iColCount; i++) {
                //获取每一行数据并去除空格
                value = Objects.notNull(rs.getString(i)).trim();
                //替换回车换行符
                value = StringUtils.replace(value,"\r\n","");
                if(i == 1) {
                    rowValue = new StringBuilder(value);
                } else if (i == iColCount) {
                    rowValue.append(colSeparate).append(value).append(rowEnd);
                } else if (i == 3) {
                	rowValue.append(colSeparate).append(StringMap.get(value));
                } else if (i == 5) {
                    for (String money:list) {
                        rowValue.append(colSeparate).append(money);
                    }
                } else {
                    rowValue.append(colSeparate).append(value);
                }
            }
            bw.write(rowValue.toString());
        }
        log.info("任务完成，导出记录数：" + recordCount + "导出路径： " + outDirFile.getAbsolutePath());
        return outDirFile;
        
    } catch(Exception e) {
        e.printStackTrace();
        log.error(outDirFile + "导出文件出错！" , e);
    } finally {
        rs.close();
        bw.close();
        stmt.close();
    }
    
}