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

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

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

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

- Ubuntu 18.04 LTS
- OpenJDK 11.0.8
- SQLite3 3.20.0

---

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

```java:前回の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`を使って以下の要領でデータを渡すことができるようになりました。

```java: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')");

// ...

```

```java: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文のデータ部分を任意に変えることができました。

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

```java:今回のゴール
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);
            }
        }
    }
}
```

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

```sql
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まとめ](https://qiita.com/tsweblabo/items/8fa21f3e9e30885be034)

---

### 参考サイト

- [SQLite用JDBCのリポジトリ](https://bitbucket.org/xerial/sqlite-jdbc/src/default/)
- [TECHSCORE BROG/3分で構築！SQLiteを色々な環境で動かしてみる](https://www.techscore.com/blog/2015/03/27/3%E5%88%86%E3%81%A7%E6%A7%8B%E7%AF%89%EF%BC%81sqlite%E3%82%92%E8%89%B2%E3%80%85%E3%81%AA%E7%92%B0%E5%A2%83%E3%81%A7%E5%8B%95%E3%81%8B%E3%81%97%E3%81%A6%E3%81%BF%E3%82%8B/)
- [Java SQLiteでinsert/update/deleteするサンプル](https://itsakura.com/java-sqlite-insert)