jdbc批处理+手动事务实现170秒插入1000万数据（单线程版）

最新推荐文章于 2024-04-15 08:24:48 发布

guandezhi

最新推荐文章于 2024-04-15 08:24:48 发布

阅读量868

点赞数 2

分类专栏： java 文章标签： jdbc批处理

本文链接：https://blog.csdn.net/qq_35859844/article/details/89286051

版权

java 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

jdbc批处理加上手动开启事务，能够实现170秒左右插入千万级别的数据

/**
 * @Author: guandezhi
 * @Date: 2019/4/13 15:35
 */
public class JdbcUtils {

    private static String url = "jdbc:mysql://localhost:3306/user?useSSL=false&rewriteBatchedStatements=true";
    private static String user = "root";
    private static String password = "root";


    public static void executeBatch() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
            long beginTime = System.currentTimeMillis();
            String sql = "insert into user(name, mobile) values(?,?)";
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            for (int j = 0; j < 10000000; j++) {
                String mobile = "13356544556";
                Integer randNum = 0;
                Random random = new Random();
                for (int i = 0; i < 1000; i++) {
                    randNum = random.nextInt(10);
                }
                ps.setString(1, "官德志");
                ps.setString(2, mobile + String.valueOf(randNum));
                ps.addBatch();
                //此处避免内存溢出
                if (j % 1000000 == 0) {
                    ps.executeBatch();
                    conn.commit();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println("插入一千万数据用时：" + (endTime - beginTime) / 1000 + " 秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }