在mysql中快速插入百万测试数据&&将百万数据写入本地Demo

在mysql中快速插入百万测试数据

public class BaseDao {
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //  获取数据库连接对象
    public static Connection getConn() {
        Connection conn = null;
        try {
            //  rewriteBatchedStatements=true
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/**?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true", "**", "**");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    //  释放资源
    public static void closeAll(AutoCloseable... autoCloseables) {
        for (AutoCloseable autoCloseable : autoCloseables) {
            if (autoCloseable != null) {
                try {
                    autoCloseable.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}

public static void main(String[] args) {
        long start = System.currentTimeMillis(); 
        Connection conn = BaseDao.getConn();
        String sql = "insert into ** values(?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql); 
            //  不断产生sql
            for (int i = 0; i < 6000000; i++) {
                ps.setString(1, i + "");
                ps.setInt(2, i);
                ps.setInt(3, i);  
                ps.addBatch();  //  将一组参数添加到此 PreparedStatement 对象的批处理命令中。
            }
            int[] ints = ps.executeBatch();//   将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。
            //  如果数组长度不为0，则说明sql语句成功执行，即百万条数据添加成功！
            if (ints.length > 0) {
                System.out.println("已成功添加6百万条数据！！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeAll(conn, ps);
        }
        long end = System.currentTimeMillis();
        System.out.println("所用时长:" + (end - start) / 1000 + "秒");
    }

将百万数据写入本地

600多w行实测2s左右

    File f = new File("xxx.txt");
    OutputStreamWriter writer = null;
    BufferedWriter bw = null;
    try {
        OutputStream os = new FileOutputStream(f);
        writer = new OutputStreamWriter(os);
        bw = new BufferedWriter(writer);
        bw.write(str);
        bw.flush();
    }catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            bw.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }