mysql(49) : 大数据按分区导出数据

代码

import com.alibaba.gts.flm.base.util.Mysql8Instance;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;

public class 数据导出_按分区 {

    // 分区字段
    private static List<Integer> partitions = new ArrayList<Integer>() {{
        add(765);
        add(769);
        add(782);
        add(787);
        add(796);
    }};

    private static Mysql8Instance m;

    private static Integer onCount = 10000;

    private static String fields;
    private static String table;

    public static void main(String[] args) throws Exception {
        m = new Mysql8Instance("127.0.0.1", 3306, "test", "root", "123456");
        m.setReturnColumnName(true);
        fields = "*";
        table = "test1";
        handle("test1.csv");
    }

    public static String getSql(Integer start, Integer date) {
        return "SELECT \n" + fields + " FROM    " + table + " where  sdate=" + date + "  limit " + start + "," + onCount;
    }

    public static void handle(String filePath) throws Exception {
        long currentTimeMillis = System.currentTimeMillis();
        File file = new File(filePath);
        // TODO 先删除再创建文件，避免文件有其他内容
        file.delete();
        file.createNewFile();
        BufferedWriter output = new BufferedWriter(new FileWriter(file, true));// true,则追加写入text文本
        long total = 0;
        boolean addTitle = false;
        for (Integer partition : partitions) {
            int start = 0;
            List<List<String>> rs;
            while ((rs = getData(m, getSql(start, partition))).size() > 1) {
                if (!addTitle) {
                    addTitle = true;
                } else {
                    rs.remove(0);
                }
                System.out.println("导出分区:[" + partition + "] ,limit:[" + start + "," + onCount + "]");
                for (List<String> r : rs) {
                    total += r.size();
                    String str = join(r, ",");
                    output.write(str);
                    output.write("\r\n");// 换行
                }
                start += onCount;
            }
            output.flush();
        }
        output.close();
        m.close();
        System.out.println("数据导出完成,总数量:" + total + ", 耗时:" + getHaoShi(System.currentTimeMillis() - currentTimeMillis));
    }

    public static List<List<String>> getData(Mysql8Instance m, String sql) {
        return m.query(sql);
    }

    /**
     * 计算耗时
     *
     * @param t 毫秒
     * @return
     */
    public static String getHaoShi(double t) {
        double d7 = t / 1000.0 / 60 / 60 / 24 / 30 / 12 / 100;
        if (d7 > 1) return round(d7, 1) + "纪元";
        double d6 = t / 1000.0 / 60 / 60 / 24 / 30 / 12;
        if (d6 > 1) return round(d6, 1) + "年";
        double d5 = t / 1000.0 / 60 / 60 / 24 / 30;
        if (d5 > 1) return round(d5, 1) + "月";
        double d4 = t / 1000.0 / 60 / 60 / 24;
        if (d4 > 1) return round(d4, 1) + "天";
        double d3 = t / 1000.0 / 60 / 60;
        if (d3 > 1) return round(d3, 1) + "小时";
        double d2 = t / 1000.0 / 60;
        if (d2 > 1) return round(d2, 1) + "分钟";
        double d1 = t / 1000.0;
        if (d1 > 1) return round(d1, 1) + "秒";
        return t + "毫秒";
    }

    public static String join(List<String> list, String separator) {
        Iterator<String> iterator = list.iterator();
        if (iterator == null) {
            return null;
        } else if (!iterator.hasNext()) {
            return "";
        } else {
            Object first = iterator.next();
            if (!iterator.hasNext()) {
                return Objects.toString(first, "");
            } else {
                StringBuilder buf = new StringBuilder(256);
                if (first != null) {
                    buf.append(first);
                }

                while (iterator.hasNext()) {
                    if (separator != null) {
                        buf.append(separator);
                    }

                    Object obj = iterator.next();
                    if (obj != null) {
                        buf.append(obj);
                    }
                }

                return buf.toString();
            }
        }
    }

    public static Double round(Double data, int amount) {
        if (data == null)
            return null;
        //利用BigDecimal来实现四舍五入.保留一位小数
        double result = new BigDecimal(data).setScale(amount, BigDecimal.ROUND_HALF_UP).doubleValue();
        //1代表保留1位小数,保留两位小数就是2,依此累推
        //BigDecimal.ROUND_HALF_UP 代表使用四舍五入的方式
        return result;
    }

    /**
     CREATE TABLE test.`test1` (
     `pkid` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
     `sys_create_time` datetime DEFAULT NULL COMMENT '创建时间',
     `sys_modify_time` datetime DEFAULT NULL COMMENT '修改时间',
     `lng` double DEFAULT NULL COMMENT '经度',
     `name` varchar(100) DEFAULT NULL COMMENT '名称',
     `time` bigint DEFAULT NULL COMMENT '时间',
     `age` varchar(100) DEFAULT NULL COMMENT '年龄',
     `speed` double DEFAULT NULL COMMENT '速度',
     `lat` double DEFAULT NULL COMMENT '维度',
     `sdate` int(11) NOT NULL COMMENT '小时(分区键)',
     PRIMARY KEY (`pkid`,`sdate`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表1';

     */
}

?Mysql8Instance类如下

mysql(30) : java管理mysql8(开发用轻量版)_java mysql8-CSDN博客