学習13日目:Javaとデータベースを接続。

今日の学習時間。

  • Day:13
  • Today:4h
  • Total:88h

学習内容について。

Javaとデータベースを接続してみた。

JavaとSQLを繋ぐためにJDBCドライバのインストールを行う。今回使用するのはPleiadesに入っているMaven。pom.xmlを作ったりして、以下はデータベースに繋ぐための手順をメモしたもの。

package dbSample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbConnectSample01 {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver"); // STEP1

            con = DriverManager.getConnection( // STEP2
                    "jdbc:mysql://localhost/world?useSSL=false",
                    "root",
                    "password");

            stmt = con.createStatement(); // STEP3

            rs = stmt.executeQuery("select * from country limit 50"); // STEP4,5

            while (rs.next()) {
                System.out.println(rs.getString("Name")); // STEP6
            }

        } catch (SQLException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } finally { //STEP7
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
STEP
MySQLのJDBCドライバを読み込む。
  • Class.forName(“com.mysql.jdbc.Driver”);と記述
  • 別のデータベースソフトウェアを利用する場合は()内が変わる
  • JDBC4.0以降では明示的にドライバを読み込む処理は無くても良い
STEP
 DBと接続。
  • DriverManager.getConnection(url, user, password);と記述
    • DBのURL、DBのユーザー名、パスワードを引数に指定
  • 自分のPCにあるDBであればlocalhostになることがほとんど
  • ネットワーク越しにDBと接続するならそのIPアドレスを指定
  • ユーザー名のrootは変えた方が良い
STEP
 DBとやりとりする窓口を用意。

con.createStatement();と記述

STEP
SQLを実行。
  • stmt.executeQuery("select * from country limit 50");と記述
    • 引数内に実行したいSQLを記載
    • stmt.executeQuery()でJava側からSQLを実行
  • 検索時は executeQuery()
  • 新規追加、更新、削除は executeUpdate()
  • SQLが正しいか先にターミナルで確かめると安心
  • 検索時はstmt.executeQuery(SQLをここに記述)
  • CRUDのうち他の3つ(データに変更が発生するもの)はstmt.executeUpdate(SQLをここに記載)

下記コードでexecuteUpdateを実行。

STEP
結果を受け取る。
  • ResultSet rsと記述
    • SQLを実行した結果を詰め込むためのJavaに用意された仕組み
  • executeQuery()と記述
    • DBが返してくれたデータを詰めるためのもの
STEP
結果を表示。
  • rs.getString("Name")と記述
    • 引数に指定したDBのカラム名のデータを取得できる
    • 文字列を取り出したい時は、getString("取り出したいカラム名")
    • 数値を取り出したい時は、rs.getInt("取り出したいカラム名")
    • カラム名で指定する方が可読性が高い
  • while文を使用
    • DBから取得したレコード数が何行かは瞬時に判らないため
  • rs.next()と記述
    • 表の中にデータがあるかどうかを指し示すための処理
    • カーソルという考え方
      • DBから取り出してきたデータは1行目から1行ずつ処理される
STEP
 接続を閉じる。
  • 処理が終わったら必ず接続を閉じる処理が必要
    • 複数人がデータベースに接続すると繋がらなくなるため

STEP4のexecuteUpdateを実行(下記コード)。

package dbSample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbConnectSample02 {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/world?useSSL=false",
                    "root",
                    "password");

            stmt = con.createStatement();

            rs = stmt.executeQuery("select * from country limit 50");

            while (rs.next()) {
                System.out.println(rs.getString("Name"));
                System.out.println(rs.getInt("Population"));
            }

            int count = stmt.executeUpdate("update country set Population = 105000 where Code = 'ABW'");
            System.out.println(count);

        } catch (SQLException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

executeUpdate()の引数内のSQLが実行されたかMySQLで確認してみた(下記コード)。

mysql> select * from country where Code = 'ABW';
+------+-------+---------------+-----------+-------------+-----------+------------+----------------+--------+--------+-----------+----------------------------------------------+-------------+---------+-------+
| Code | Name  | Continent     | Region    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP    | GNPOld | LocalName | GovernmentForm                               | HeadOfState | Capital | Code2 |
+------+-------+---------------+-----------+-------------+-----------+------------+----------------+--------+--------+-----------+----------------------------------------------+-------------+---------+-------+
| ABW  | Aruba | North America | Caribbean |      193.00 |      NULL |     105000 |           78.4 | 828.00 | 793.00 | Aruba     | Nonmetropolitan Territory of The Netherlands | Beatrix     |     129 | AW    |
+------+-------+---------------+-----------+-------------+-----------+------------+----------------+--------+--------+-----------+----------------------------------------------+-------------+---------+-------+
1 row in set (0.00 sec)

ちゃんと変更されていた。

戻り値を確かめたいときはコンソールでUpdate文を実行してみる(下記コード例)。

mysql> update country set Population = 105000 where Code = 'ABW';
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この数値の「1」が戻り値。Update文は一行しか変更をかけていないので1でなければ正常に更新できていないと判断する。

DB操作の際に必ず気をつけること
  • close()で閉じる
    • コネクション、statement、resultsetの3つ
  • 閉じる順番にも気をつける
    • resultset、statement、connectionの順
      • 更新系のみを実行している時は、resultsetがない
    • 例外発生時でも閉じる
  • 更新系のSQL実行時は値を確定させる
    • commit();で確定させる
    • コミットすると、その前の値には戻せなくなるので注意

動的なSQLの作成してみる。

package dbSample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbConnectSample03 {

    public static void main(String[] args) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/world?useSSL=false",
                    "root",
                    "password");

            stmt = con.createStatement();

            String name = "Aruba";
            rs = stmt.executeQuery("select * from country where Name = '" + name + "'");

            while (rs.next()) {
                System.out.println(rs.getString("Name"));
                System.out.println(rs.getInt("Population"));
            }

        } catch (SQLException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

/* 実行結果

Aruba
105000

*/

上記コードのようにSQL文の文字列と変数を連結させる記述をすることで、動的なSQLを作成できる。しかし、name の値をString name = "' or 'a' = 'a";に変更すると全てのデータが出力される。これでは機密情報が漏洩してしまう可能性もあるので絶対にしないように。この脆弱性を利用した攻撃をSQLインジェクションという。

動的なSQLを活用しつつSQLインジェクション対策ができるプリペアードステートメントという方法がある。下記はプリコンパイル(プリペアードステートメントを活用して組み立てること)の例。

package dbSample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbConnectSample04 {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/world?useSSL=false",
                    "root",
                    "password");

            pstmt = con.prepareStatement("select * from country where Name = ?");

            String name = "Aruba";
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("Name"));
                System.out.println(rs.getInt("Population"));
            }

        } catch (SQLException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

/* 実行結果

Aruba
105000

*/

また、プリペアードステートメントはexecuteUpdate()(更新系)の命令でも使用可能(下記コード例)。

package dbSample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbConnectSample05 {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost/world?useSSL=false",
                    "root",
                    "password");

            pstmt = con.prepareStatement(
                    "insert into city (Name,CountryCode,District,Population) values ('Rafah',?,'Rafah',?)");

            String str1 = "PSE"; // 画面から出力
            int num1 = 123456; // 画面から出力
            pstmt.setString(1, str1);
            pstmt.setInt(2, num1);

            int count = pstmt.executeUpdate();
            System.out.println(count);

        } catch (SQLException e) {
            e.printStackTrace();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
変更点
  • Statement stmt = null;PreparedStatement pstmt = null;
    • import文もStatementからPreparedStatementに変えておく
    • Statementは不要なので削除
  • 動的に変化させたいところは?を利用
    • 例:下記コード
    • pstmt = con.prepareStatement();の引数を指定することで先に確定させておく
      • 値は後から指定
"insert into city (Name,CountryCode,District,Population) values ('Rafah',?,'Rafah',?)";

pstmt.setString(1, str1);pstmt.setInt(2, num1);のSQLの値を示している。中身の数字は"insert into city (Name,CountryCode,District,Population) values ('Rafah',?,'Rafah',?)"; における ? の順番を表す。

  • 文字列を設定する時は、setStringを利用
  • 数値を設定する時はsetIntを利用
  • pstmt.executeUpdate();で実行
    • 更新系はexecuteUpdate()
    • 参照系はexecuteQuery()

今日の反省と明日の目標。

今日はJDBCの基礎について学習。今までに学習してきたJavaとMySQLを組み合わせるだけだけど、点と点が繋がるようで面白い体験でした。確かに、コンソールを一々開くよりもEclipseで一括管理した方が楽ではありますね。他にもセキュリティに優れたりするようだけど現時点ではあまりピンとこないのが実情。

そういえば、テックアカデミーのカリキュラムには目安時間がついてるんですが、あまり当てにならないです。今日した内容は目安時間10時間だけど、3時間ほどで終わって残り時間は復習ができたので。しかし、オブジェクト指向のところは倍近くの時間がかかりました。残りカリキュラムの目安時間を計算してみると89時間。となると、時間的にはちょうど半分くらい、あと13日で終了するかと思います。

明日はJDBCでデータベースをオブジェクト指向っぽく書いていく内容になります。それにはフレームワークの一種であるHibernateを使っていくのですが、楽しみですね。

閉じる