Java JDBC

Java 11 Gold 取得に向けた学習記録
# Java JDBC
Javaプログラムから
- データベースへの接続
- SQLクエリの実行
- 結果操作

を行うためのAPIを**JDBC**（Java Database Connectivity）と言う。

データベースには、

- Oracle Database
- SQL Server
- PostgreSQL
- MySQL

など各社から開発された複数の製品があるが、JDBCは特定の製品に依存しない作りになっている。そのため利用時に特定の製品を意識する必要がない。

具体的にはインターフェース、例外クラスが`java.sql`パッケージ、`javax.sql`パッケージから提供される。これらはどちらも`java.sql`モジュールに属している。

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/package-summary.html

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/javax/sql/package-summary.html

## ドライバ
`java.sql`パッケージ、`javax.sql`パッケージから提供されるインターフェースの実装クラスは、RDBMS製品の開発元であるベンダから**ドライバ**として提供されている。

そのため、開発時は使用したいDBのベンダからドライバをダウンロードなどによって取得し、アプリケーションに組み込む必要がある。

## データベースとの接続

### *`Connection`*

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/Connection.html

データベースとの接続を表現するインターフェース。

インターフェースを実装したクラスは[DriverManager](#drivermanager)クラスの`static`メソッドを介して取得される。

データベース操作のためのセッション（`接続 ▶️ 操作 ▶️ 切断`）を確立し、トランザクション制御を行うために利用される。

主に次のような抽象メソッドが定義されている。

- `Statement createStatement()`
    - SQLを送信するための[`Statemant`](#sql文の送信)インターフェースを実装したクラスのオブジェクトを生成する
- `PreparedStatement prepareStatement​(String sql)`
    - パラメータ付きのSQl文を送信するための[`PreparedStatement`](#preparedstatement)インターフェースを実装したクラスのオブジェクトを生成する
- `void	setAutoCommit​(boolean autoCommit)`
    - 自動コミットモードの ON / OFF を設定する
- `void commit()`
    - コミットする
- `void rollback()`
    - ロールバックする
- `void close()`
    - 接続を閉じる


### `DriverManager`

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/DriverManager.html

[ドライバ](#ドライバ)をロード、管理し、データベース接続を確立するための`static`メソッドが定義されたクラス。

下記のメソッドを使用することで *`Connection`* インターフェースの実装クラスのオブジェクトを取得することができる。

- `static Connection getConnection​(String url)` 
    - `url` : [接続文字列](#接続文字列)
- `static Connection getConnection​(String url, String user, String password)`
    - `user` : データベースのユーザー名
    - `password` : データベースのユーザーパスワード
- `static Connection getConnection​(String url, Properties info)`
    - `info` : パラメータをまとめた`java.util.Properties`オブジェクト（ユーザ名、パスワードは最低限必要）

```java:DriveManager
String url = "jdbc:mysql://localhost:3306/mydatabase";
try (Connection conn = DriverManager.getConnection(url)) {
    // 処理

} catch (SQLException e) {
    throw new RuntimeException(e);
}
```

[ドライバ](#ドライバ)をロードする際に、ドライバの実体がクラスパス上に配置されていないと例外が発生する。

#### 接続文字列
Connection String

データベースに接続するための情報を含む文字列を**接続文字列**と言う。

接続文字列にはDBの種類、ホスト名、ポート番号、DB名、ユーザ名、パスワード等が含まれる。

```java:接続文字列
String url = "jdbc:<データベース種類>://<ホスト名>:<ポート番号>/<データベース名>?<オプション>";
```

データベース種類には`mysql`、`postgresql`、`oracle`、`sqlserver`などがある。

## SQL文の送信

SQLクエリをdデータベースに送信するためのインターフェースには、クエリの種類に応じて *`Statement`* 、*`PreparedStatement`* 、*`CallableStatement`* の3種類がある。


![statement.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1069520/fe162d9d-a65a-a398-15f3-5e6834eb5437.png)

- *`Statement`*
    - パラメータなしのSQL
    - 文字列としてDBに送信後、DB側でコンパイルを行う

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/Statement.html

- *`PreparedStatement`*
    - パラメータ付きのSQL
    - プリコンパイル（prepared=準備）してからDBに送信される（パフォーマンスが良い）

 https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/PreparedStatement.html

- *`CallableStatement`*
    - ストアドプロシージャ用

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/CallableStatement.html

### *`PreparedStatement`*

パラメータ付きでSQL文を実行するためのインターフェース。SQL文にはパラメータ（`?`）を含めることができる。

```java:PreparedStatement
String sql = "INSERT INTO table_1 (column_1, column_2, column_3) VALUES (?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url);
     PreparedStatement preparedStatement = conn.prepareStatement(sql)) {

    // sql に含まれる1つ目のパラメータ（?）に値を設定する
    preparedStatement.setInt(1, 100);
    
    // sql に含まれる2つ目のパラメータ（?）に値を設定する
    preparedStatement.setString(2, "aaa");
    
    // sql に含まれる3つ目のパラメータ（?）に値を設定する
    preparedStatement.setDouble(3, 1.23);

    // SQLクエリの実行
    preparedStatement.execute();

} catch (SQLException e) {
}
```

#### SQL実行

*`PreparedStatement`* に定義されている実行系メソッド。

#### `execute()`

> `boolean execute()`

- あらゆるSQL文を実行できる
- 戻り値が`ResultSet`の場合、`true`を返す

#### `executeQuery()`

> `ResultSet executeQuery()`

- `SELECT`文の実行によって生成された`ReseultSet`オブジェクトを返す

#### `executeUpdate()`

> `int executeUpdate()`

- `INSERT`文、`UPDATE`文、`DELETE`文を実行する
- 操作を行った行数を返す

## クエリ結果の取得

### *`ResultSet`*

クエリの結果を表すインターフェース。

データベースから情報を取得した後、Javaプログラム内で操作するためのメソッドが定義されている。

https://docs.oracle.com/javase/jp/11/docs/api/java.sql/java/sql/ResultSet.html

*`ResultSet`* では、SQLクエリ結果を行ごとに操作するための**カーソル**を扱う。

カーソルは始め、最初の要素の一つ手前に位置していて `next()`メソッドによって1ずつ移動させることができる。それ以上行が存在しない場合、`next()` は `false` を返す。

```java:next()
try (Connection conn = DriverManager.getConnection(url);
     PreparedStatement preparedStatement = conn.prepareStatement(sql)) {

    ResultSet resultSet = preparedStatement.executeQuery();

    while (resultSet.next()) {

    }

} catch (SQLException e) {
}
```

カーソル移動後、特定の列の情報を取得するためには getter 系メソッドを使用する。引数には列のインデックスを指定する。インデックスの最小値は `1` （0列目は存在しない）。

```java:getXXX()
while (resultSet.next()) {
    int id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    int age = resultSet.getInt(3);
}
```

## 例外

### `SQLException`
JDBC操作中に発生する例外を表すクラス。データベースに関するエラーが発生した場合に`throw`される。

# 実践学習の記録

実際に手を動かしながら学んだ記録まとめ。

環境
OS : macOS
PostgreSQL version: 16.3
Java version : 11

## データベースをインストールする

インストール可能な PostgreSQL のバージョンを確認する。インストール済みの場合、バージョンの横に✅が表示される。

```console
$ brew search postgresql

==> Formulae
postgresql@10             postgresql@12             postgresql@14             postgresql@16             postgrest
postgresql@11             postgresql@13             postgresql@15             qt-postgresql

==> Casks
navicat-for-postgresql                                           posture-pal
```

一番新しいバージョンを指定してインストールを行う。

```console
$ brew install postgresql@16
```

インストールが成功していれば、バージョンが出力される。

```console
$ psql --version

zsh: command not found: psql
```

見つからないため、確かにインストールされたことを確認する。

```console
# brew list | grep postgresql

postgresql@16
```

確かにインストールされている。

インストール先を確認する。

```console
$ find /opt/homebrew -name psql

/opt/homebrew/Cellar/postgresql@16/16.3/bin/psql
```

環境変数 $PATH を確認し、インストール先のパスが含まれているかを確認する。

```console
$ echo $PATH
/opt/homebrew/bin:/opt/homebrew/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin:/bin:/usr/sbin:/sbin
```

直接パスを指定して実行してみる。

```console
$ /opt/homebrew/Cellar/postgresql@16/16.3/bin/psql --version

psql (PostgreSQL) 16.3 (Homebrew)
```

シンボリックリンクが正しく作成されていない場合、手動で作成する。

```console
$ ln -s /opt/homebrew/Cellar/postgresql@16/16.3/bin/psql /opt/homebrew/bin/psql
```

実際のインストール先のパスに対して、Homebrewが使用している`$PATH`へのシンボリックリンクを作成したことで解決した。（`/opt/homebrew/bin/psql`はHomebrewのインストールしたパッケージのダウンロード先。）

```console
$ psql --version

psql (PostgreSQL) 16.3 (Homebrew)
```

## ドライバをプロジェクトに追加する

ビルドツールとして Gradle を使用しているため、プロジェクトフォルダ直下の`build.gradle`の`dependencies`ブロックに追加する。

https://mvnrepository.com/artifact/org.postgresql/postgresql/42.1.4

```gradle
dependencies {
    implementation group: 'org.postgresql', name: 'postgresql', version: '42.7.3'
}
```

## サービスを起動する

```console
$ brew services start postgresql@16
```

## パスワードの設定

PostgreSQLのデフォルトのユーザー名は`postgres`であり、パスワードを設定するにはログインが必要。

ログインは以下のコマンドによって行う。パスワードは2回入力が求められる。1回目はOSのユーザーパスワード、2回目が PostgreSQL のパスワード。パスワードは`password`で入力した。

```console
$ sudo -u postgres psql

Password: OSのパスワード
Password for user postgres: password
psql (16.3 (Homebrew), server 12.9)
Type "help" for help.
postgres=# 
```

ログインに成功すると、

```console
postgres=# 
```

という形式になる。

```console
postgres=# \q
```

でログアウトできる。

## データベースとテーブルを作成する

```java
String url = "jdbc:postgresql://localhost:5432/";
String user = "postgres";
String password = "password";

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);

String dbname = "mydatabase";
// データベースの作成
try (Connection conn = DriverManager.getConnection(url, props);
    Statement statement = conn.createStatement()) {

    String sql = "CREATE DATABASE " + dbname;
    statement.executeUpdate(sql);
    System.out.println("データベース作成の完了");
    
} catch (SQLException e) {
    System.err.println("データベース作成に失敗: " + e.getMessage());
}

// テーブルの作成
url = "jdbc:postgresql://localhost:5432/" + dbname;

try (Connection conn = DriverManager.getConnection(url, props);
     Statement statement = conn.createStatement()) {

    String sql = "CREATE TABLE mytable ("
            + "id SERIAL PRIMARY KEY, "
            + "name VARCHAR(20) NOT NULL, "
            + "age INTEGER NOT NULL"
            + ")";

    statement.execute(sql);
    System.out.println("テーブル作成の完了");
    
} catch (SQLException e) {
    System.err.println("テーブル作成に失敗: " + e.getMessage());
}
```

## サンプルデータを登録する

```java
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "postgres";
String password = "password";

Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);

try (Connection conn = DriverManager.getConnection(url, props);
     PreparedStatement preparedStatement = conn.prepareStatement(
             "INSERT INTO mytable (id, name, age) VALUES (?, ?, ?)")) {

    String[] names = new String[]{
            "name_1",
            "name_2",
            "name_3",
            "name_4",
            "name_5",
            "name_6",
            "name_7",
            "name_8",
            "name_9",
            "name_10",
    };

    for (int i = 0; i < names.length; i++) {
        preparedStatement.setInt(1, i);
        preparedStatement.setString(2, names[i]);
        preparedStatement.setInt(3, 18 + i * 2);
        preparedStatement.executeUpdate();
    }

    System.out.println("サンプルデータ作成に成功");
} catch (SQLException e) {
    System.out.println("サンプルデータ作成に失敗: " + e.getMessage());
}
```





# 参考
- [徹底攻略Java SE 11 Gold問題集[1Z0-816]対応](https://amzn.asia/d/0eV9L1Wp)