记录平常用到的小工具技巧

package com.sf.sfopen.facebook.action;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;

import java.io.*;
import java.text.SimpleDateFormat;

public class JsonBdusTest {
	public static void main(String[] args) {
		String bdusJson = getDatafromFile();
		JSONArray array = JSON.parseArray(bdusJson);
		JSONObject obj = new JSONObject();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Long createDate = 0L;
		Long updateDate = 0L;
		String str = "INSERT INTO table (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11) VALUES ";
		System.out.println(str);
		writeFile(str,0);
		for (int i = 0; i < array.size(); i++) {
			StringBuilder sb = new StringBuilder();
			obj = array.getJSONObject(i);
			sb.append("(");
			sb.append("'" + obj.getOrDefault("field1", "") + "',");
			sb.append("'" + obj.getOrDefault("field2", "") + "',");
			sb.append("'" + obj.getOrDefault("field3", "") + "',");
			sb.append("'" + obj.getOrDefault("field4", "") + "',");
			sb.append("'" + obj.getOrDefault("field5", "") + "',");
			sb.append("'" + obj.getOrDefault("field6", "") + "',");
			sb.append("'" + obj.getOrDefault("field7", "") + "',");
			sb.append("'" + obj.getOrDefault("field8", "") + "',");
			sb.append("'" + obj.getOrDefault("field9", "") + "',");
			if (StringUtils.isEmpty(obj.getString("field10")) && StringUtils.isEmpty(obj.getString("field11"))) {
				sb.append("'2020-11-01 00:00:10',");
				sb.append("'2020-11-01 00:00:10'");
			} else if (StringUtils.isEmpty(obj.getString("field10")) && StringUtils.isNotEmpty(obj.getString("field11"))) {
				updateDate = obj.getLong("field11");
				sb.append("'" + sdf.format(updateDate) + "',");
				sb.append("'" + sdf.format(updateDate) + "'");
			} else if (StringUtils.isNotEmpty(obj.getString("field10")) && StringUtils.isEmpty(obj.getString("field11"))) {
				createDate = obj.getLong("field10");
				sb.append("'" + sdf.format(createDate) + "',");
				sb.append("'" + sdf.format(createDate) + "'");
			} else if (StringUtils.isNotEmpty(obj.getString("field10")) && StringUtils.isNotEmpty(obj.getString("field11"))) {
				createDate = obj.getLong("field10");
				updateDate = obj.getLong("field11");
				sb.append("'" + sdf.format(createDate) + "',");
				sb.append("'" + sdf.format(updateDate) + "'");
			}
			if (array.size() - 1 == i) {
				sb.append(")");
			} else {
				sb.append("),");
			}
			writeFile(sb.toString(),1);
			System.out.println((i+1)+"  "+sb.toString());
		}
		String str1 = "ON DUPLICATE KEY UPDATE field1= VALUES(field1);";
		System.out.println(str1);
		writeFile(str1,1);


	}
	//获取文件内容并转换成JSON字符串
	private static String getDatafromFile() {
		String Path = "d:\\test.json";
		BufferedReader reader = null;
		String laststr = "";
		try {
			FileInputStream fileInputStream = new FileInputStream(Path);
			InputStreamReader inputStreamReader = new InputStreamReader(fileInputStream, "UTF-8");
			reader = new BufferedReader(inputStreamReader);
			String tempString = null;
			while ((tempString = reader.readLine()) != null) {
				laststr += tempString;
			}
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return laststr;
	}
	
	//追加写入文件
	public static void writeFile(String conent,int type) {
		String file = "d:\\test.sql";
		BufferedWriter out = null;
		try {
			out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file, true)));
			if(type == 0){
				out.write("\r\n\r\n\r\n"+conent + "\r\n");
			}else if(type == 1){
				out.write(conent + "\r\n");
			}else if(type ==2){
				out.write(conent);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}


}

1.代码

复制就可以使用了

package com.har.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class CsvUtil {

	/**
	 * CSV文件生成方法  不会自动关闭流
	 *
	 * @param headLabel 头部标签
	 * @param dataList  数据列表
	 */
	public static void writeToCsv(String headLabel, List<String> dataList, BufferedWriter buffWriter) {
		try {
			//头部不为空则写入头部，并且换行
			if (StringUtils.isNotBlank(headLabel)) {
				buffWriter.write(headLabel);
				buffWriter.newLine();
			}
			//遍历list
			for (String rowStr : dataList) {
				//如果数据不为空，则写入文件内容,并且换行
				if (StringUtils.isNotBlank(rowStr)) {
					buffWriter.write(rowStr);
					buffWriter.newLine();//文件写完最后一个换行不用处理
				}
			}
			//刷新流，也就是把缓存中剩余的内容输出到文件
			buffWriter.flush();
		} catch (Exception e) {
			log.info("写入csv出现异常");
			e.printStackTrace();
		}
	}

    /**
     * CSV文件生成方法  字符流追加：FileWriter writer = new FileWriter(file，true)
     *
     * @param headLabel 头部标签
     * @param dataList  数据列表
     * @param filePath  文件路径
     * @param addFlag   是否追加
     */
    public static void writeToCsv(String headLabel, List<String> dataList, String filePath, boolean addFlag) {
        BufferedWriter buffWriter = null;
        try {
            //根据指定路径构建文件对象
            File csvFile = new File(filePath);
            //文件输出流对象，第二个参数时boolean类型,为true表示文件追加（在已有的文件中追加内容）
            FileWriter writer = new FileWriter(csvFile, addFlag);
            //构建缓存字符输出流（不推荐使用OutputStreamWriter）
            buffWriter = new BufferedWriter(writer, 1024);
            //头部不为空则写入头部，并且换行
            if (StringUtils.isNotBlank(headLabel)) {
                buffWriter.write(headLabel);
                buffWriter.newLine();
            }
            //遍历list
            for (String rowStr : dataList) {
                //如果数据不为空，则写入文件内容,并且换行
                if (StringUtils.isNotBlank(rowStr)) {
                    buffWriter.write(rowStr);
                    buffWriter.newLine();//文件写完最后一个换行不用处理
                }
            }
            //刷新流，也就是把缓存中剩余的内容输出到文件
            buffWriter.flush();
        } catch (Exception e) {
            log.info("写入csv出现异常");
            e.printStackTrace();
        } finally {
            try {
                //关闭流
                if (buffWriter != null) {
                    buffWriter.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 根据文件路径读取csv文件的内容
     *
     * @param filePath
     * @return
     */
    public static List<String> readFromCsv(String filePath) {
        ArrayList<String> dataList = new ArrayList<>();
        BufferedReader buffReader = null;
        try {
            //构建文件对象
            File csvFile = new File(filePath);
            //判断文件是否存在
            if (!csvFile.exists()) {
                log.info("文件不存在");
                return dataList;
            }
            //构建字符输入流
            FileReader fileReader = new FileReader(csvFile);
            //构建缓存字符输入流
            buffReader = new BufferedReader(fileReader);
            String line = "";
            //根据合适的换行符来读取一行数据,赋值给line
            while ((line = buffReader.readLine()) != null) {
                if (StringUtils.isNotBlank(line)) {
                    //数据不为空则加入列表
                    dataList.add(line);
                }
            }
        } catch (Exception e) {
            log.info("读取csv文件发生异常");
            e.printStackTrace();
        } finally {
            try {
                //关闭流
                if (buffReader != null) {
                    buffReader.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return dataList;
    }

}

2.测试代码

					//这个改成你查数据库的SQL
					List<FinancialReconciliationRecord> recordList = financialReconciliationRecordRepository.findByYearMonthAndFlowIdMoreThanLimit1000(yearMonth, maxFlowId);
					List<String> dataList = new ArrayList<>();
					String title = "业务类型,转支付订单号,转支付支付宝流水号,操作金额（元）,订单号,应用id,支付时间,处理状态";
					if (!CollectionUtils.isEmpty(recordList)){
						for (FinancialReconciliationRecord record : recordList) {
							StringBuilder sb = new StringBuilder();
							sb.append(record.getBiz()).append(",")
									.append(record.getFlowId()).append("\t").append(",")
									.append(record.getAlipayOrderNo()).append("\t").append(",")
									.append(record.getAmount()).append(",")
									.append(record.getOrderNo()).append("\t").append(",")
									.append(record.getAlipayAppId()).append("\t").append(",")
									.append(record.getGmtPayment()).append(",")
									.append(record.isStatus()).append("\t").append(",")
							;
							dataList.add(sb.toString());
						}
					}
					
					CsvUtil.writeToCsv(title,dataList,"D:/test/111.csv",true);
					recordList.clear();