解答例 - 実習課題２ - ３．トランザクション処理

（実習課題２）

以下のプログラムを作成しなさい。

• 「product」テーブルに複数データを挿入するコンソールプログラム。
• データの挿入はトランザクションで実行する事。
• 以下の4通りでプログラムを形成し、実行速度を比較する事。
  • 1節の方法で、「java.sql.Statement」を利用する方法
  • 2節の方法で、「java.sql.Statement」を利用する方法
  • 1節の方法で、「java.sql.PreparedStatement」を利用する方法
  • 「java.sql.PreparedStatement」で「addBatch」を利用する方法

解答例

▼データベースアクセス用クラスのソース

package com.techscore.jdbc.chapter3.exercise2;
/**
 * ProductDAO.java
 * TECHSCORE JDBC3章 実習課題2
 *
 * Copyright (c) 2004 Four-Dimensional Data, Inc.
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.DriverManager;

public class ProductDAO{
    
    private Connection getConnection() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection("jdbc:postgresql://dbserver:5432/Training"
                                           ,"postgres"  //ユーザ名
                                           ,"");        //パスワード
        conn.setAutoCommit(false);  //自動コミットモード解除
        return conn;
    }
    
    public void delete() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        try{
            conn = getConnection();
            PreparedStatement statement = null;
            final String sql = "delete from product";
            statement = conn.prepareStatement(sql);
            statement.executeUpdate(sql);
            conn.commit();
            statement.close();
        }finally{
            if (conn != null){
                conn.close();
            }
        }
    }
    public void insert1() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        int key;
        try{
            conn = getConnection();
            Statement statement = conn.createStatement();
            for (key = 0; key < 1000; key++){
                String sql = "insert into product " + 
                             "values(" +
                             key + ","+ 
                             "\'DefaultPName\'," +
                             "\'DefaultType\'," +
                             "0)";
                statement.executeUpdate(sql);
            }
            conn.commit();
            statement.close();
        }finally{
            if (conn != null){
                conn.close();
            }
        }
    }
    public void insert2() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        int key;
        String sql = "begin;";
        try{
            conn = getConnection();
            Statement statement = conn.createStatement();
            for (key = 0; key < 1000; key++){
                sql += "insert into product " + 
                       "values(" +
                       key + ","+ 
                       "\'DefaultPName\'," +
                       "\'DefaultType\'," +
                       "0);";
            }
            sql += "commit;";
            statement.executeUpdate(sql);
            statement.close();
        }finally{
            if (conn != null){
                conn.close();
            }
        }
    }
    public void insert3() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        int key = 0;
        try{
            conn = getConnection();
            PreparedStatement statement = null;
            final String sql = "insert into product " + 
                               "values(?,?,?,?)";
            statement = conn.prepareStatement(sql);
            for(key = 0; key < 1000; key++){
                statement.setInt(1,key);
                statement.setString(2,"DefaultPName");
                statement.setString(3,"DefaultType");
                statement.setInt(4,0);
                statement.executeUpdate();
            }
            conn.commit();
            statement.close();
        }finally{
            if (conn != null){
                conn.close();
            }
        }
    }
    public void insert4() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        int key = 0;
        try{
            conn = getConnection();
            PreparedStatement statement = null;
            final String sql = "insert into product " + 
                               "values(?,?,?,?)";
            statement = conn.prepareStatement(sql);
            for(key = 0; key < 1000; key++){
                statement.setInt(1,key);
                statement.setString(2,"DefaultPName");
                statement.setString(3,"DefaultType");
                statement.setInt(4,0);
                statement.addBatch();
            }
            statement.executeBatch();
            conn.commit();
            statement.close();
        }finally{
            if (conn != null){
                conn.close();
            }
        }
    }
}

▼データ挿入測定クラスのソース

package com.techscore.jdbc.chapter3.exercise2;
/**
 * MeasureInsertingRows.java
 * TECHSCORE JDBC3章 実習課題2
 *
 * Copyright (c) 2004 Four-Dimensional Data, Inc.
 */
import java.sql.SQLException;
import java.util.Calendar;

public class MeasureInsertingRows {
    protected static final int CHAPTER1 = 1;
    protected static final int CHAPTER2 = 2;
    protected static final int CHAPTER3 = 3;
    protected static final int CHAPTER4 = 4;
    
    public static void main(String[] args) {
        ProductDAO productDAO = new ProductDAO();
        measureTime(productDAO,CHAPTER1);
        measureTime(productDAO,CHAPTER2);
        measureTime(productDAO,CHAPTER3);
        measureTime(productDAO,CHAPTER4);
    }

    private static void measureTime(ProductDAO productDAO,int method){
        long start;
        long end;
        long time;
        try{
            start = Calendar.getInstance().getTimeInMillis();
            if(method == CHAPTER1){
                System.out.println("■第１節の処理■");
                productDAO.insert1();
            }
            if(method == CHAPTER2){
                System.out.println("■第２節の処理■");
                productDAO.insert2();
            }
            if(method == CHAPTER3){
                System.out.println("■第１節＋PreparedStatementの処理■");
                productDAO.insert3();
            }
            if(method == CHAPTER4){
                System.out.println("■第１節＋PreparedStatement＋addBatchの処理■");
                productDAO.insert4();
            }
            productDAO.delete();
            end   = Calendar.getInstance().getTimeInMillis();
            time = end - start;
            System.out.println("処理時間は " + time + "[msec] です。");
        }catch(SQLException e){
            e.printStackTrace();
            System.exit(1);
        }catch(ClassNotFoundException e){
            e.printStackTrace();
            System.exit(1);
        }
    }
}

▼データベースの指定

JDBC　1章　実習課題1を参照