JDBC - 学习6 -Betach - 批处理测试

文章目录

• • • 1. 不使用Betach、使用Statement
    • 2. 不使用Betach、使用PreparedStatement
    • 3. 使用Betach、PreparedStatement

运行时间以我自己的手提电脑为例

测试：执行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);
}