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

MySQLのインストール(Mac)

参考: MacでMySQLを扱う方法
Mac環境でMySQLを利用する場合は、Homebrewを通じてインストールを行う。

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ドライバのインストール
参考2: MySQLでのデータベース作成
参考3: Kotlin研修7日目
JVMからデータベースへの接続過程は、以下の通り。

ターミナルを通じたMySQLの起動・テーブルの作成

MySQLの基本操作コマンドは、以下の通り。

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節で明示的にロールバックが実行できるようにするのが望ましい。

サンプルコード

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();
        }
    }
}

実行結果

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

ターミナルからテーブルデータを確認

> 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つの処理」として扱う手法。 一連のトランザクション処理が正常終了した場合はコミット、 途中で失敗した場合はロールバックが行われる。
KVS(Key-Value Store)	キーと値のペアで構成されたデータを、ネットワーク上に分散させて管理するDBMS。