【Java発展学習10日目】MySQLを用いたデータベースアクセス

# MySQLのインストール(Mac)
[参考: MacでMySQLを扱う方法](https://qiita.com/fuwamaki/items/194c2a82bd6865f26045)
Mac環境で`MySQL`を利用する場合は、`Homebrew`を通じてインストールを行う。

```shell:MySQLのインストール(Mac)
# Homebrewを用いたMySQLのインストール
% brew install mysql

# MySQLのバージョン情報
% mysql --version
> mysql  Ver 8.0.25 for macos11.3 on x86_64 (Homebrew)
```

# JVM ⇔ データベースの連携
[参考1: JDBCドライバのインストール](https://style.potepan.com/articles/19389.html)
[参考2: MySQLでのデータベース作成](https://prog-8.com/docs/mysql-database-setup)
[参考3: Kotlin研修7日目](https://qiita.com/b150005/items/0d9ab381c1c6f3619cc1#db%E6%8E%A5%E7%B6%9A%E3%82%AA%E3%83%96%E3%82%B8%E3%82%A7%E3%82%AF%E3%83%88%E3%81%AE%E7%94%9F%E6%88%90)
`JVM`から`データベース`への接続過程は、以下の通り。

![JDBC.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673880/cf101584-8641-a191-714c-3fbcb2991c05.png)

## ターミナルを通じたMySQLの起動・テーブルの作成
`MySQL`の基本操作コマンドは、以下の通り。

```shell:MySQLの起動・テーブルの作成
# MySQLの起動
% mysql.server start
Starting MySQL
. SUCCESS!

# MySQLの実行
mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 Homebrew
...

# 設定情報の確認
> status;
--------------
mysql  Ver 8.0.25 for macos11.3 on x86_64 (Homebrew)

Connection id:		8
Current database:	test
Current user:		root@localhost  # Javaプログラムで「ユーザ名」として利用
...

# データベースの作成
> create database <データベース名>;
Query OK, 1 row affected (0.00 sec)

# データベース一覧の表示
> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

# 利用するデータベースの指定
> use <データベース名>;
Database changed

# テーブルの作成
 > create table <テーブル名>(
-> id int auto_increment,  # 「<カラム名> <データ型> <オプション>」の順に指定
-> data text,
-> primary key (id)        # 主キーの指定
-> );
Query OK, 0 rows affected (0.01 sec)

# テーブル一覧の表示
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| testTable      |
+----------------+
1 row in set (0.00 sec)

# テーブルのデータ構造の参照
> describe <テーブル名>;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| data  | text | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

# データの挿入(INSERT文)
> insert into <テーブル名>(<カラム名>) values (<挿入するデータ>);
Query OK, 1 row affected (0.01 sec)

# テーブルの参照
> select * from <テーブル名>;
+----+----------+
| id | data     |
+----+----------+
|  1 | Data 1   |
|  2 | Data 2   |
|  3 | Data 3   |
|  4 | new Data |
|  5 | new Data |
+----+----------+
5 rows in set (0.00 sec)

# MySQLモードの終了
> exit
Bye

# MySQLの終了
% mysql.server stop
Shutting down MySQL
. SUCCESS! 

# 補足
# カラムの追加(ALTER TABLE文)
> alter table <テーブル名> add <追加カラム名> <データ型> <オプション>;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

# データの更新(UPDATE文)
> update <テーブル名> set <変更カラム名>=<値> where <対象カラム名>=<対象データ>;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

## JVM → データベースの接続
`JVM`から`JDBCドライバ`に接続する場合は、Java標準ライブラリである`JDBC API`を利用する。
利用機会の多い主なクラスは、以下の通り。

| API | 内容 |
|:--|:--|
| `DriverManager` | `JDBCドライバ`の管理 |
| `Connection` | `JDBCドライバ`への接続 |
| `PreparedStatement` | `SQL文`の保持 |
| `ResultSet` | `SQL文`の実行結果の保持 |

なお、データベースを操作する処理をトランザクション制御で実行する場合、
`try-with-resource文`を用いて`Connection`・`PreparedStatement`オブジェクトの解放処理を行うのではなく、
`try-catch文`を用いて`catch節`で明示的に`ロールバック`が実行できるようにするのが望ましい。

### サンプルコード

```java:MySQL.java
import java.sql.*;
import java.util.Date;

public class MySQL {
    public static void main(String[] args) {
        // -- JDBCドライバの読み込み --
        try {
            // JDBCドライバの読み込み
            Class.forName("com.mysql.cj.jdbc.Driver");
        }
        catch (ClassNotFoundException e) {
            // 例外の再送出
            throw new IllegalStateException("JDBC Driver is not found.");
        }

        // -- テーブルデータの更新 --
        // SQL文を保持するPreparedStatementオブジェクト
        PreparedStatement psInsert = null;
        Connection con1 = null;
        try {
            // JDBCドライバへの接続を行うConnectionオブジェクトの生成
            // -> JDBC URLは"jdbc:mysql://<ユーザ名>/<DB名>"で指定
            con1 = DriverManager.getConnection("jdbc:mysql://root@localhost/test");

            // トランザクション制御として設定
            con1.setAutoCommit(false);

            // -- SQL文(更新系) --
            // バインド変数を利用したINSERT文
            psInsert = con1.prepareStatement(
                "insert into testTable(data, date) values (?, \"2000-01-01 00:00:00\")"
            );

            // バインド変数への値のバインディング
            psInsert.setString(1, "new Data");

            // データを操作するDML文の実行
            // -> 更新したレコード数がint型の値で返却
            int r = psInsert.executeUpdate();

            if (r != 0) {
                System.out.println("Query OK, " + r + " row affected.");
            }
            else {
                System.out.println("Query NG.");
            }

            // 処理が正常終了した場合はコミット
            con1.commit();
        }
        catch (SQLException e) {
            try {
                // 処理が途中で失敗した場合はロールバック
                con1.rollback();
            }
            catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        finally{
            // Connectionオブジェクトがnullでないことを保証
            if (con1 != null) {
                try {
                    // Connectionオブジェクトの解放
                    con1.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            // PreparedStatementオブジェクトがnullでないことを保証
            if (psInsert != null) {
                try {
                    // PreparedStatementオブジェクトの解放
                    psInsert.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

        // -- テーブルデータの取得 --
        try (
            // JDBCドライバへの接続を行うConnectionオブジェクトの生成
            Connection con = DriverManager.getConnection("jdbc:mysql://root@localhost/test");

            // -- SQL文(検索系) --
            // バインド変数を利用したSELECT文
            PreparedStatement psSelect1 = con.prepareStatement(
                "select * from testTable where id >= ?"
            );
        ) {
            // バインド変数への値のバインディング
            psSelect1.setInt(1, 2);

            // データを抽出するクエリ文の実行
            // -> 抽出結果を保持するResultSetオブジェクトが返却
            // <- 本来はResultSetオブジェクトも解放処理が必要であるが、
            //    Connectionオブジェクトの解放処理によって連鎖的に解放される
            ResultSet res = psSelect1.executeQuery();

            // 抽出結果をコンソールに出力
            while (res.next()) {
                // 「カラム名」または「カラムのインデックス番号」を指定して値を取得
                int id = res.getInt("id");
                String data = res.getString(2);

                System.out.println("id: " + id + ", data: " + data);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        // -- 「日時」を表すTIMESTAMP型データ(=JavaではDate型)の取得 --
        try (
            // JDBCドライバへの接続を行うConnectionオブジェクトの生成
            Connection con = DriverManager.getConnection("jdbc:mysql://root@localhost/test");

            // -- SQL文(検索系) --
            // バインド変数を利用したSELECT文
            PreparedStatement psSelect2 = con.prepareStatement(
                "select * from testTable where id >= ? and date >= ?"
            );
        ) {
            // バインド変数への値のバインディング
            psSelect2.setInt(1, 2);
            psSelect2.setString(2, "2000-01-01 00:00:00");

            // データを抽出するクエリ文の実行
            // -> 抽出結果を保持するResultSetオブジェクトが返却
            ResultSet res = psSelect2.executeQuery();

            // 抽出結果をコンソールに出力
            while (res.next()) {
                int id = res.getInt(1);
                Timestamp resTime = res.getTimestamp(3);

                // Timestamp型 -> long型 -> Date型 への変換
                long lon = resTime.getTime();
                Date date = new Date(lon);
                
                System.out.println("id: " + id + ", date: " + date);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}
```

```java:実行結果
Query OK, 1row affected.
id: 2, data: Data 2
id: 3, data: Data 3
id: 4, data: new Data
id: 3, date: Sat Jun 14 12:47:33 JST 2003
id: 4, date: Sat Jan 01 00:00:00 JST 2000
```

```shell:ターミナルからテーブルデータを確認
> select * from <テーブル名>;
+----+----------+---------------------+
| id | data     | date                |
+----+----------+---------------------+
|  1 | Data 1   | 1993-02-09 23:11:49 |
|  2 | Data 2   | 1997-12-21 07:45:20 |
|  3 | Data 3   | 2003-06-14 12:47:33 |
|  4 | new Data | 2000-01-01 00:00:00 |   # Javaプログラムによって追加されたレコード 
+----+----------+---------------------+
4 rows in set (0.00 sec)
```

***

# 用語集

| 用語 | 内容 |
|:--|:--|
| JDBC(Java DataBase Connectivity) | `データベース操作`を行うAPI。 |
| トランザクション(transaction) | 複数の`SQL文`を「1つの処理」として扱う手法。<br>一連のトランザクション処理が正常終了した場合は`コミット`、<br>途中で失敗した場合は`ロールバック`が行われる。 |
| KVS(Key-Value Store) | `キー`と`値`のペアで構成されたデータを、ネットワーク上に分散させて管理する`DBMS`。 |