Biegral Blog

首先关于Statement和PreparedStatement的基本概念我就不再叙述了，可以参考这篇文章，下面我们来看几个测试例子吧。

测试场景

现在我们向数据库中的一张表中插入100000（10万）条数据，测试使用Statement和PreparedStatement及PreparedStatement的Batch方式所需要的时间。

Dao基类

/**
 * 
 * Description: 数据库操作基类
 *
 * @author: crane-yuan
 * @date: 2016年9月27日 下午1:40:04
 */
public class BaseDao {
    protected Connection connection; // 访问数据库的句柄
    public void openConnection() throws Exception {
        if (this.connection == null || this.connection.isClosed()) {
            try {
                DbInfo dbinfo = DbInfo.instance();
                Class.forName(dbinfo.getDbdriver()); // 加载oracle的驱动
                connection = DriverManager.getConnection(dbinfo.getUrl(),
                        dbinfo.getUser(), dbinfo.getPassword());
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println("请检查驱动包是否正确");
                throw e;
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("请检查数据库的配置项是否正确");
                throw e;
            }
        }
    }
    public void closeConnection() throws Exception {
        if (this.connection != null) {
            this.connection.close();
        }
    }
} 

Statement方式

/**
 * 
 * Description: 测试Statement
 *
 * @throws Exception void
 */
public void testStatement() throws Exception {
    System.out.println("--------------Statement no batch----------------");
    String sql = "";
    this.openConnection();
    // 手动提交
    this.connection.setAutoCommit(false);
    Statement statement = this.connection.createStatement();
    Long beginTime = System.currentTimeMillis();
    System.out.println(new Date(beginTime));
    try {
        for (int i = 100001; i < 200000; i++) {
            sql = "insert into testStatement(code) values(" + i + ")";
            statement.executeUpdate(sql);
        }
    } catch (SQLException exception) {
        exception.printStackTrace();
    }
    this.connection.commit();
    statement.close();
    this.connection.close();
    Long endTime = System.currentTimeMillis();
    System.out.println(new Date(endTime));
    System.out.println("Statement：" + (endTime - beginTime) / 1000 + "秒");
}

结果：

--------------Statement no batch----------------
Wed Oct 19 16:42:22 CST 2016
Wed Oct 19 16:46:32 CST 2016
Statement：249秒 

PreparedStatement无Batch方式

/**
 * 
 * Description: 测试PreparedStatement，不开启Batch提交
 *
 * @throws Exception void
 */
public void testPreparedStatement() throws Exception {
    System.out
            .println("--------------PreparedStatement no batch----------------");
    String sql = "insert into testStatement(code) values(?)";
    this.openConnection();
    // 手动提交
    this.connection.setAutoCommit(false);
    PreparedStatement ps = this.connection.prepareStatement(sql);
    Long beginTime = System.currentTimeMillis();
    System.out.println(new Date(beginTime));
    try {
        for (int i = 0; i < 100000; i++) {
            String code = "" + i;
            ps.setString(1, code);
            ps.execute();
        }
    } catch (SQLException exception) {
        exception.printStackTrace();
    }
    this.connection.commit();
    ps.close();
    this.connection.close();
    Long endTime = System.currentTimeMillis();
    System.out.println(new Date(endTime));
    System.out.println("PreparedStatement：" + (endTime - beginTime) / 1000 + "秒");
}

结果

--------------PreparedStatement no batch----------------
Wed Oct 19 16:46:32 CST 2016
Wed Oct 19 16:48:37 CST 2016
PreparedStatement：125秒 

PreparedStatement有Batch方式

/**
 * 
 * Description: 测试使用PreparedStatement，并且开启Batch提交.
 *
 * @throws Exception void
 */
public void testPreparedStatementBatch() throws Exception {
    System.out
            .println("--------------PreparedStatement with batch----------------");
    String sql = "insert into testStatement(code) values(?)";
    this.openConnection();
    // 手动提交
    this.connection.setAutoCommit(false);
    PreparedStatement ps = this.connection.prepareStatement(sql);
    Long beginTime = System.currentTimeMillis();
    System.out.println(new Date(beginTime));
    int count = 0;
    try {
        for (int i = 200001; i < 300000; i++) {
            String code = "" + i;
            ps.setString(1, code);
            ps.addBatch();
            count++;
            if (count == 500) {
                count = 0;
                ps.executeBatch();
                this.connection.commit();
                ps.clearBatch();
            }
        }
    } catch (SQLException exception) {
        exception.printStackTrace();
    }
    ps.close();
    this.connection.close();
    Long endTime = System.currentTimeMillis();
    System.out.println(new Date(endTime));
    System.out.println("PreparedStatement+batch：" + (endTime - beginTime) / 1000 + "秒");
}

结果

--------------PreparedStatement with batch----------------
Wed Oct 19 16:48:37 CST 2016
Wed Oct 19 16:48:38 CST 2016
PreparedStatement+batch：1秒 

总结

一般来说，PreparedStatement的Batch方式执行效率比PreparedStatement和Statement的都高，PreparedStatement无Batch方式比Statement方式也要高。
在实际开发中一般推荐使用PreparedStatement，不过PreparedStatement也有一些缺点，在这篇文章中暂时不在叙述了，这方面的对比将在下篇文章中详细讲解。