JDBCのStatementとPreparedStatementの単純なパフォーマンスの比較

まず、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にもいくつかの欠点があり、この文章ではしばらく述べていないが、この方面の対比は次の文章で詳しく説明する.