【JDBC ③】プレースホルダと引数を使ってmainメソッドから入力してみた。

前回の記事では、SQLのINSERT文・UPDATE文・DELETE文・SELECT文ごとにメソッドで分けて、mainメソッドでSQL文を実行することに成功しました。
追加や変更・削除したいデータをSQL文の中で固定していたため実用的ではありませんでした。

そこで、mainメソッドからSQL文の各メソッドを呼び出す際に追加したいデータ等も渡すことができれば、より有用性の高いソースコードになると考えたので挑戦してみました。

環境

今回の環境については以下のとおりです。

• Ubuntu 18.04 LTS
• OpenJDK 11.0.8
• SQLite3 3.20.0

---

前回の振り返り

まずは前回のコードのINSERT文のメソッドを見てみます

前回のINSERT文のメソッド

/**
 * INSERT文
 */
public static void insertData() {
    try {
        // create a database connection
        connection = DriverManager.getConnection(URL);
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);  // set timeout to 30 sec.

        statement.executeUpdate("INSERT INTO person VALUES(1, 'Satou')");
        statement.executeUpdate("INSERT INTO person VALUES(2, 'Tanaka')");
        statement.executeUpdate("INSERT INTO person VALUES(3, 'Suzuki')");
    } catch(SQLException e) {
        // if the error message is "out of memory", 
        // it probably means no database file is found
        System.err.println(e.getMessage());
    } finally {
        try {
            if(connection != null)
            connection.close();
        } catch(SQLException e) {
            // connection close failed.
            System.err.println(e);
        }
    }
}

ご覧の通り、SQL文の中で追加したいデータが固定で入っているため、mainメソッドから呼び出すだけでは任意のデータを追加できません。

---

任意のデータを渡すために必要なこと

mainメソッドから呼び出す際に追加したいデータを渡すためには、次の②つを満たす必要があリます。

• プレースホルダを使う
• Statementの代わりにPreparedStatementを使う

プレースホルダを使うことでSQL文に任意の文字列を渡すことができるようになります。またプレースホルダを使う際、予めSQL文を定義しておく必要があるのでStatementの代わりにPreparedStatementを使って以下の要領でデータを渡すことができるようになりました。

Statementを用いたコード

// ...

Statement statement = connection.createStatement();
statement.setQueryTimeout(30);  // set timeout to 30 sec.

statement.executeUpdate("INSERT INTO person VALUES(1, 'Satou')");
statement.executeUpdate("INSERT INTO person VALUES(2, 'Tanaka')");
statement.executeUpdate("INSERT INTO person VALUES(3, 'Suzuki')");

// ...

PreparedStatementを用いたコード

// ...

String sql = "INSERT INTO person (name) VALUES(?)";
try {
PreparedStatement ps = null;
ps = connection.prepareStatement(sql);
ps.setString(1, "Satou");
ps.executeUpdate();
connection.commit();
ps.close();
} catch (SQLException e){

// ...

注）この時点で、ソースコードを少しでもわかりやすくするためにidにPRIMARY KEY AUTOINCREMENTを付与しています。

---

メソッドの再利用性を高める

次に、mainメソッドからデータを渡すために、ps.setString(1, "Satou");のデータの部分を引数として渡すことで、mainメソッドからの操作でSQL文のデータ部分を任意に変えることができました。

ソースコードと、実行結果を以下に記しておきます。

今回のゴール

import java.sql.*;

/**
 * TestDataBaseAccess
 */
public class TestDataBaseAccess {
    static Connection connection;
    static PreparedStatement ps;
    static String URL = "jdbc:sqlite:sample.db";

    public static void main(String[] args) throws ClassNotFoundException {
       // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");
        connection = null;
        ps = null;

        dropTable();
        createTable();
        insertData("Satou");
        insertData("Tanaka");
        insertData("Suzuki");
        loadData();

        System.out.println("---------");

        updateData(1, "Takahashi");
        loadData();

        System.out.println("---------");

        deleteData(3);
        loadData();
    }
    /**
     * SELECT文
     */
    public static void loadData() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            ResultSet rs = statement.executeQuery("SELECT * FROM person");
            while(rs.next()){
                // read the result set
                System.out.println("id = " + rs.getInt("id") + " | name = " + rs.getString("name"));
            }
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * INSERT文
     */
    public static void insertData(String name) {
        String sql = "INSERT INTO person (name) VALUES(?)";

        try {
            connection = DriverManager.getConnection(URL);
            connection.setAutoCommit(false);

            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.executeUpdate();
            connection.commit();
            ps.close();
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * UPDATE文
     */
    public static void updateData(int id, String name) {
        try {
            String sql = "UPDATE person SET name = ? WHERE id = ?";
            // create a database connection
            connection = DriverManager.getConnection(URL);
            connection.setAutoCommit(false);

            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.setInt(2, id);
            ps.executeUpdate();
            connection.commit();
            ps.close();
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * DELETE文
     */
    public static void deleteData(int id) {
        try {
            String sql = "DELETE FROM person WHERE id = ?";
            // create a database connection
            connection = DriverManager.getConnection(URL);
            connection.setAutoCommit(false);

            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
            connection.commit();
            ps.close();
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * テーブル作成
     */
    public static void createTable() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)");
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

    /**
     * テーブル削除
     */
    public static void dropTable() {
        try {
            // create a database connection
            connection = DriverManager.getConnection(URL);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("DROP TABLE IF EXISTS person");
        } catch(SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null)
                connection.close();
            } catch(SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }
}

javac TestDataBaseAccess.java && java -cp .:sqlite-jdbc-3.30.1.jar TestDataBaseAccess 

id = 1 | name = Satou
id = 2 | name = Tanaka
id = 3 | name = Suzuki
---------
id = 1 | name = Takahashi
id = 2 | name = Tanaka
id = 3 | name = Suzuki
---------
id = 1 | name = Takahashi
id = 2 | name = Tanaka

まとめ

今回の変更で再利用性が高まっていいコードに近づいてきたのではないでしょうか。
今後は実際にソフトウェアで使ってみて、より良いソースコードにしていきたいと思います。

---

本記事目次ページ

【データベース】SQLite3・JDBCまとめ

---

参考サイト

• SQLite用JDBCのリポジトリ
• TECHSCORE BROG/3分で構築！SQLiteを色々な環境で動かしてみる
• Java SQLiteでinsert/update/deleteするサンプル