JDBC - 學習6 -Betach - 批處理測試

運行時間以我自己的手提電腦為例


批處理差異
MySQL:默認關閉批處理 -- 自行百度開啓批處理功能
Oracle:默認開啓批處理

測試:執行10K條同樣的SQL語句的運行時間 – oracle數據庫

1. 不使用Betach、使用Statement

  運行時間:15秒

@Test
public void test7() throws Exception {
    Connection conn = ConnectionTest.getConnection5();

    Statement statement = conn.createStatement();

    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        String sql = "insert into imgTable(name) values ('name_" + (10000 + i + 1) + "')";
        statement.execute(sql);
    }
    long end = System.currentTimeMillis();

    System.out.println((end - start) / 1000 + "秒");

    ConnectionTest.closeResource(conn, statement, null);
}

2. 不使用Betach、使用PreparedStatement

  運行時間:5秒

@Test
public void test6() throws Exception {
    Connection conn = ConnectionTest.getConnection5();

    String sql = "insert into imgTable(name) values ( ?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        ps.setObject(1, "name_" + i);
        ps.execute();
    }
    long end = System.currentTimeMillis();

    System.out.println((end - start) / 1000 + "秒");

    ConnectionTest.closeResource(conn, ps, null);
}

3. 使用Betach、PreparedStatement

  運行時間:257毫秒

@Test
public void test8() throws Exception {
    Connection conn = ConnectionTest.getConnection3();

    String sql = "insert into imgTable(name) values (?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        ps.setObject(1, "name_" + i);

        // 1. 將SQL語句先存起來
        ps.addBatch();

        // 2. 一旦存起來的SQL語句有500條,則一次性執行存起來的SQL語句,並且清空運行的SQL語句
        if ((i + 1) % 500 == 0) {
            ps.executeBatch();
            ps.clearBatch();
        }

    }

    // 3. 防止還有存起來的SQL語句沒有執行
    ps.executeBatch();
    ps.clearBatch();
    long end = System.currentTimeMillis();

    System.out.println((end - start) + "豪秒");

    ConnectionTest.closeResource(conn, ps, null);
}