共被编辑 2 次

版本 更新时间 贡献者 编辑原因 操作
#r2 2017-11-28 Nerdyman9 更新问题 查看

测试JDBC批处理效率没变化

我在学习 JDBC 批处理时, 为比较 Statement, PreparedStatement 和 JDBC Batch 操作之间的效率,写一个例子:向数据库中插入 100,000 条记录,并记录消耗的时间。然后分别用这三种方式实现,可每次测试的结果却都是几乎相同没变化,请教大神这是为什么?

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class BatchTest {

    /**
     * 使用 Statement 进行批处理操作
     */
    @Test
    public void testBatchWithStatement(){
        Connection connection = null;
        Statement statement;
        String sql;

        try {
            connection = JDBCUtils.getConnection();
            JDBCUtils.beginTx(connection);

            statement = connection.createStatement();
            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                sql = "INSERT INTO batch VALUES(" + i +")";
                statement.executeUpdate(sql);
            }
            long end = System.currentTimeMillis();

            System.out.println(end - start);

            JDBCUtils.commit(connection);
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtils.rollback(connection);
        }
    }

    /**
     * 使用 PreparedStatement 进行批处理操作
     */
    @Test
    public void testBatchWithPreparedStatement(){
        Connection connection = null;
        PreparedStatement statement;
        String sql;

        try {
            connection = JDBCUtils.getConnection();
            JDBCUtils.beginTx(connection);

            sql = "INSERT INTO batch VALUES(?)";
            statement = connection.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                statement.setInt(1, i + 1);
                statement.executeUpdate();
            }
            long end = System.currentTimeMillis();

            System.out.println(end - start);

            JDBCUtils.commit(connection);
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtils.rollback(connection);
        }
    }

    /**
     * 使用 Batch 进行批处理操作
     */
    @Test
    public void testBatch(){
        Connection connection = null;
        PreparedStatement statement;
        String sql;
        try {
            connection = JDBCUtils.getConnection();
            JDBCUtils.beginTx(connection);

            sql = "INSERT INTO batch VALUES(?)";
            statement = connection.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                statement.setInt(1, i + 1);
                statement.addBatch();
                if ((i + 1) % 500 == 0){
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }
            if (100000 % 300 != 0){
                statement.executeBatch();
                statement.clearBatch();
            }
            long end = System.currentTimeMillis();

            System.out.println(end - start);
            JDBCUtils.commit(connection);
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtils.rollback(connection);
        }
    }

}
#r1 2017-11-28 Nerdyman9 创建问题 查看

测试JDBC批处理效率没变化

我在学习 JDBC 批处理时, 为比较 Statement, PreparedStatement 和 JDBC Batch 操作之间的效率,写一个例子:向数据库中插入 100,000 条记录,并记录消耗的时间。然后分别用这三种方式实现,可每次测试的结果却都是几乎相同没变化,请教大神这是为什么?

mport org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class BatchTest {

    /**
     * 使用 Statement 进行批处理操作
     */
    @Test
    public void testBatchWithStatement(){
        Connection connection = null;
        Statement statement;
        String sql;

        try {
            connection = JDBCUtils.getConnection();
            JDBCUtils.beginTx(connection);

            statement = connection.createStatement();
            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                sql = "INSERT INTO batch VALUES(" + i +")";
                statement.executeUpdate(sql);
            }
            long end = System.currentTimeMillis();

            System.out.println(end - start);

            JDBCUtils.commit(connection);
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtils.rollback(connection);
        }
    }

    /**
     * 使用 PreparedStatement 进行批处理操作
     */
    @Test
    public void testBatchWithPreparedStatement(){
        Connection connection = null;
        PreparedStatement statement;
        String sql;

        try {
            connection = JDBCUtils.getConnection();
            JDBCUtils.beginTx(connection);

            sql = "INSERT INTO batch VALUES(?)";
            statement = connection.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                statement.setInt(1, i + 1);
                statement.executeUpdate();
            }
            long end = System.currentTimeMillis();

            System.out.println(end - start);

            JDBCUtils.commit(connection);
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtils.rollback(connection);
        }
    }

    /**
     * 使用 Batch 进行批处理操作
     */
    @Test
    public void testBatch(){
        Connection connection = null;
        PreparedStatement statement;
        String sql;
        try {
            connection = JDBCUtils.getConnection();
            JDBCUtils.beginTx(connection);

            sql = "INSERT INTO batch VALUES(?)";
            statement = connection.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 0; i < 100000; i++) {
                statement.setInt(1, i + 1);
                statement.addBatch();
                if ((i + 1) % 500 == 0){
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }
            if (100000 % 300 != 0){
                statement.executeBatch();
                statement.clearBatch();
            }
            long end = System.currentTimeMillis();

            System.out.println(end - start);
            JDBCUtils.commit(connection);
        } catch (Exception e) {
            e.printStackTrace();
            JDBCUtils.rollback(connection);
        }
    }

}