JDBC批处理实现手动事务插入1000万数据（单线程 & 多线程）

JavaEE > 微服务开发

发布时间 : 2023-08-28 09:23

阅读 :

1、JDBC 单线程插入1000万数据
2、JDBC 多线程插入1000万数据

1、JDBC 单线程插入1000万数据

pom.xml 依赖如下：

<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.220</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.28</version>
</dependency>

import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.UUID;

/**
 * jdbc批处理+手动事务实现50秒左右插入1000万数据（单线程版）
 * 参考文献: https://blog.csdn.net/qq_35859844/article/details/89286051
 */
public class JdbcUtils1 {

    private static String JDBC_DRIVER = "org.h2.Driver";
    private static String JDBC_URL = "jdbc:h2:file:~/test";
    private static String JDBC_USER = "sa";
    private static String JDBC_PASSWORD = "";
    private static String CREATE_SQL = "CREATE TABLE IF NOT EXISTS USER_INFO(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(99),MOBILE VARCHAR(99))";
    private static String INSERT_SQL = "INSERT INTO USER_INFO(NAME, MOBILE) values(?,?)";

    public static void main(String[] args) throws Exception {
        // 先删除H2数据库文件
        Files.deleteIfExists(Paths.get(System.getProperty("user.home") + "/test.mv.db"));
        Files.deleteIfExists(Paths.get(System.getProperty("user.home") + "/test.trace.db"));
        executeBatch();
    }

    public static void executeBatch() throws Exception {
        // Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
        // 创建表
        conn.createStatement().execute(CREATE_SQL);
        long beginTime = System.currentTimeMillis();
        // 开启事务手动提交
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement(INSERT_SQL);
        for (int j = 1; j <= 10000000; j++) {
            String uuid = UUID.randomUUID().toString().replace("-", "");
            ps.setString(1, uuid.substring(0, 8));
            ps.setString(2, uuid.substring(8));
            ps.addBatch();
            // 此处避免内存溢出, 每一百万条数据提交一次
            if (j % 1000000 == 0) {
                ps.executeBatch();
                conn.commit();
                ps.clearBatch();
                System.out.println("已插入数据: " + j + "条");
            }
        }
        ps.executeBatch();
        conn.commit();
        long endTime = System.currentTimeMillis();
        System.out.println("插入一千万数据用时：" + (endTime - beginTime) / 1000 + " 秒");
        ps.close();
        conn.close();
    }
}

2、JDBC 多线程插入1000万数据

import lombok.SneakyThrows;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.UUID;
import java.util.concurrent.*;

/**
 * jdbc批处理+手动事务+多线程实现70秒左右插入1000万数据（多线程版)
 * 由于会重新建立一次jdbc连接, 所以多线程比单线程更久
 * 参考文献: https://blog.csdn.net/qq_35859844/article/details/89310153
 */
public class JdbcUtils2 {

    private static String JDBC_DRIVER = "org.h2.Driver";
    private static String JDBC_URL = "jdbc:h2:file:~/test";
    private static String JDBC_USER = "sa";
    private static String JDBC_PASSWORD = "";
    private static String CREATE_SQL = "CREATE TABLE IF NOT EXISTS USER_INFO(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(99),MOBILE VARCHAR(99))";
    private static String INSERT_SQL = "INSERT INTO USER_INFO(NAME, MOBILE) values(?,?)";

    public static void main(String[] args) throws Exception {
        // 先删除H2数据库文件
        Files.deleteIfExists(Paths.get(System.getProperty("user.home") + "/test.mv.db"));
        Files.deleteIfExists(Paths.get(System.getProperty("user.home") + "/test.trace.db"));
        // 初始化线程池和CountDownLatch工具类
        ExecutorService executorService = Executors.newFixedThreadPool(2);
        CountDownLatch latch = new CountDownLatch(10);
        long beginTime = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            executorService.execute(new Task(latch));
        }
        latch.await();
        long endTime = System.currentTimeMillis();
        System.out.println("插入一千万数据用时：" + (endTime - beginTime) / 1000 + " 秒");
        executorService.shutdown();
    }

    /**
     * 为了避免内存溢出问题,每100万条重新建立一次jdbc连接,并且每100万提交一次commit
     */
    public static class Task implements Runnable {
        CountDownLatch latch;
        public Task(CountDownLatch latch) {
            this.latch = latch;
        }
        @SneakyThrows
        @Override
        public void run() {
            // Class.forName(JDBC_DRIVER);
            Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
            // 创建表
            conn.createStatement().execute(CREATE_SQL);
            // 开启事务手动提交
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement(INSERT_SQL);
            for (int j = 0; j < 1000000; j++) {
                String uuid = UUID.randomUUID().toString().replace("-", "");
                ps.setString(1, uuid.substring(0, 8));
                ps.setString(2, uuid.substring(8));
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
            ps.close();
            conn.close();
            latch.countDown();
        }
    }
}

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 8629303@qq.com