java读取分片msyql数据到csv文件中

java读取分片msyql数据到csv文件当中

public class demoTest {


    @Test
    public void testDemo1() {
        ApplicationContext context = new ClassPathXmlApplicationContext("conf/**/*Context*.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
        //获取数量
        String sqlCount = "select count(1) from payment_plat_instant_ts";
        int count = getCount(sqlCount, jdbcTemplate);
        //写入文件
        String sql = "select ts_id,ts_seq,serv_id from payment_plat_instant_ts limit " + String.valueOf(count);
        String csvPath = "E:\\123.csv";
        readWriteResultSetTOCsv(sql, csvPath, jdbcTemplate);
    }

    public void readWriteResultSetTOCsv(String query, String csvPath, JdbcTemplate jdbcTemplate) {
        ResultSet rs = null;
        try {
            rs = getRs(query, jdbcTemplate);

            List<List<String>> rows = new ArrayList<>();
            List<String> header = getColHeaderFromRS(rs);
            while (rs.next()) {
                List<String> row = new ArrayList<>();
                for (String h : header) {
                    row.add(rs.getString(h));
                }
                rows.add(row);
                if (rows.size() >= 2000) {
                    exportCsv(csvPath, rows);
                    rows.clear();
                }
            }
            if (!rows.isEmpty()) {
                exportCsv(csvPath, rows);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }

    private int getCount(String query, JdbcTemplate jdbcTemplate) {
        ResultSet rs = null;
        int count = 0;
        try {
            rs = getRs(query, jdbcTemplate);
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return count;
    }

    private ResultSet getRs(String query, JdbcTemplate jdbcTemplate) throws Exception {

        java.sql.Connection con = jdbcTemplate.getDataSource().getConnection();
        java.sql.Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(5000);
        ResultSet rs = stmt.executeQuery(query);
        return rs;
    }

    private List<String> getColHeaderFromRS(ResultSet rs) throws Exception {
        ResultSetMetaData data = rs.getMetaData();
        List<String> list = new ArrayList<>();
        for (int i = 1; i <= data.getColumnCount(); i++) {
            list.add(data.getColumnName(i));
        }
        return list;
    }

    private boolean exportCsv(String csvPath, List<List<String>> dataList) {
        boolean isSucess=false;
        try {
            BufferedWriter out =new BufferedWriter(new FileWriter(new File(csvPath),true));
            for(List<String> list:dataList){
                for (String data:list){
                    out.write(data);
                    out.write(",");
                }
                out.newLine();
            }
            out.flush();
            out.close();
            isSucess=true;
        }catch (Exception e){
            e.printStackTrace();
            isSucess=false;
        }
        return isSucess;
    }

}