Print Friendly, PDF & Email

前回は、JSTLとELについて学びました。

今回は、Javaプログラムとデータベースとの連携について学びます。

なぜ、Webアプリケーションにデータベースが必要なのか?

これまでに作成してきたWebアプリケーションのデータはアプリケーションサーバのメモリ上にだけ存在しています。

ということはアプリケーションサーバを再起動したり、電源を切るとデータは消えてしまいます。

そこで、何らかの方法でデータを外部記憶装置に保存する必要が出てきます。(難しい表現でデータの永続化 【perpetuation】といいます)

その際にデータベースとファイルという候補がありますが、データベースのほうが優れているということはこの研修でも学んだ通りです。

JDBCとは何か?

JDBC【Java DataBase Connectivity】とは、JavaプログラムからデータベースにアクセスするためのAPI【Application Programming Interface】です。

もしもJDBCが無かったら、以下のイメージ図のようにデータベースの種類ごとにJavaのプログラムを変えなくてはなりません。

図1.もしもJDBCが無かったら

JDBC がデータベースの違いを吸収するため、下図のようにどのようなデータベースに対しても、あるいはデータベースを変更しても同じ手順で接続し、データを読み書きすることができるのです。

図2.JDBC

JavaSEでJDBCを扱う

Javaプログラムからデータベースを利用するためには以下の図の通り、3つのオブジェクトが必要です。

①データベースとの接続(セッション)を表現するConnection

②SQL文を表すPreparedStatement

③データベースの結果セットを表すResultSet

図3.データベースを利用するための3つのオブジェクト

また、JavaにはJDBC 接続プールという仕組みがあり効率的にコネクションを利用する仕組みがあります。

余裕があればリンクをたどってみてください。

また、PreparedStatementは Prepared(予め準備された)Statement (SQLの記述)という意味があり、高速に動作する書き方になります。

また、プレースホルダを使用して安全にデータベース接続できるのですがこの点は後述します。

WebアプリケーションでMySQLを扱う前に、JavaSEでMySQLを扱ってみましょう。

この方法は、いちいちアプリケーション・サーバーを立ち上げる必要がないため動作検証には適しています。

さらに、常に以下の手順を踏めば、確実に動くプログラムを得ることができます。

  • MySQLでSQL実行
  • JavaSEでSQL実行
  • JavaWebアプリケーションでSQL実行

みなさんも、けっしていきなりWebアプリケーションで実行しようとしてはいけません。

急がば回れです。

SELECT文

1件のレコードを取得する

以下はCarsテーブルの車の数を取得するJavaプログラムです。

package p08;

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

public class Select1 {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "SELECT count(*) FROM cars";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) throws SQLException {

        con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);

        PreparedStatement ps = con.prepareStatement(SQL);

        ResultSet rs = ps.executeQuery();

        rs.next();

        System.out.println(rs.getInt("count(*)"));

        if (ps != null) {
            ps.close();
        }
        if (con != null) {
            con.close();
        }
    }
}
  • 23~27行目では何をしていると推測されますか?
あなたの答え:
  • 33~38行目では何をしていると推測されますか?
あなたの答え:

なお、上記の接続と切断の処理は決り文句ですので覚える必要はありません。

また、接続情報(CONNECT_STRING)の文字列には以下のような意味がありますが、これも覚える必要はありません。

rs.next();

この行が必要な理由は、以下の図にあるようなResultSetの構造にあります。

また、 ResultSet の一行から特定の列を取り出すには以下のように列名で取り出したい列を指定します。

rs.getInt("count(*)")

上記の例では count(*) がint型だったためgetIntメソッドを使いましたが、型にあわせて getStringメソッド やgetDoubleメソッドを使い分けないといけません

初学者がミスをしやすいところなので気をつけましょう。

例題1

上記の例を参考にcustomersテーブルの顧客数をコンソール出力するJavaプログラムを作成しなさい。

実験1

上記サンプルプログラムのCONNECT_STRING、USERIDやPASSWORD、SQLをわざと間違えて、どのようなエラーメッセージが出るかを観察しましょう。

観察結果のメモ:

CONNECT_STRING:
USERID:
PASSWORD:
SQL :

実験2

rs.next()の戻り値を標準出力に表示してみましょう。

実験結果のメモ:

複数件のレコードを取得する

複数件のレコードを取得するJavaプログラムを作成してみます。

※今度はメインメソッドで例外を投げるのではなく、例外をキャッチするように書き換えています。

package p08;

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

public class Select2 {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "SELECT * FROM cars";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);

            PreparedStatement ps = con.prepareStatement(SQL);
            System.out.println("データベースへの接続に成功しました。");

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                System.out.print(rs.getInt("car_id") + ":");
                System.out.print(rs.getString("name") + ":");
                System.out.print(rs.getInt("price") + ":");
                System.out.print(rs.getString("end_of_life_dates"));
                System.out.println();
            }
        } catch (SQLException e) {
            System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
                System.out.println("データベースからの切断に成功しました。");
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}
  • 先の1件のレコードを取得したときと何が違いますか?
あなたの答え:

例題2

上記の例を参考にcustomersテーブルの全ての顧客をコンソール出力するJavaプログラムを作成しなさい。

特定のレコードを取得する

次にユーザーが選択した特定のレコードを取得してみます。

package p08;

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

public class Select3Injection {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static String SQL = "SELECT * FROM sip_a.cars where car_id = ";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);
            System.out.println("データベースへの接続に成功しました。");

            System.out.println("car_idを指定してください");

            Scanner sc = new Scanner(System.in);
            String car_id = sc.nextLine();

            SQL += car_id;

            PreparedStatement ps = con.prepareStatement(SQL);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                System.out.print(rs.getInt("car_id") + ":");
                System.out.print(rs.getString("name") + ":");
                System.out.print(rs.getInt("price") + ":");
                System.out.print(rs.getString("end_of_life_dates"));
                System.out.println();
            }
        } catch (SQLException e) {
            System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
                System.out.println("データベースからの切断に成功しました。");
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}
  • 16行目でfinalキーワードを抜きました。これにはどのような意味がありますか?
あなたの答え:
  • 33行目では何をしていますか?
あなたの答え:

car_idを指定してください

につづけて適当な数値を入力してエンターするとその番号の車の情報が標準出力に表示されます。

しかし、ここで例えば以下のように入力するとどうなりますか?

1 or 1 = 1

全てのレコードが取得できたはずです。

実はこれはSQLインジェクションとして知られた攻撃なのです。

今回は実害のないデータでしたが、これがもし、パスワードなどの機密性の高いデータだったらどうでしょうか?

ここでもう一度SQL文をよく見てみます。

赤枠で囲った「1 = 1」の部分が常にtrueと評価されるために全てのレコードという意味になるのですね。

したがって 「1 = 1」でなくてもtrueと評価される式であれば何でもSQLインジェクション攻撃になります。

実験3

「1 = 1」 以外の攻撃パターンを試してみましょう。

実験結果のメモ:

では、どうすればよいのでしょうか?

プレースホルダを使用するという方法があります。

プレースホルダを使用してSQLインジェクション攻撃を避ける

プレースホルダとは以下のサンプルプログラムの16行目にある「?」です。

この「?」は35行目の以下のコードによりキーボードから入力された値に置き換わるのです。

ps.setInt(1, Integer.parseInt(car_id));

この setIntメソッドの第1引数はプレースホルダの番号です。

この番号はSQL文の左から順に1,2…と数えられます。

0からではない点に注意してください。

配列の添字やコレクションフレームワークのインデックスのようにJavaは0から数えるのが一般的ですが、プレースホルダの番号はそうでない点は初学者が間違えやすい点です。

また、第2引数はセットしたい値です。

また、今回はint値であったため、 setIntメソッド でしたが文字列であればsetStringメソッドになることは言うまでもありません。

ということで次は車の名前で検索できるプログラムです。

プレースホルダでSQLインジェクション対策をしてありますので確かめてください。

package p08;

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

public class Select4Placeholder2 {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "SELECT * FROM sip_a.cars where name = ?";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);
            System.out.println("データベースへの接続に成功しました。");

            System.out.println("nameを指定してください");

            Scanner sc = new Scanner(System.in,"MS932");//,"MS932"は日本語の文字化け対策です。Windows環境で必要になります。
            String name = sc.nextLine();

            PreparedStatement ps = con.prepareStatement(SQL);
            
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                System.out.print(rs.getInt("car_id") + ":");
                System.out.print(rs.getString("name") + ":");
                System.out.print(rs.getInt("price") + ":");
                System.out.print(rs.getString("end_of_life_dates"));
                System.out.println();
            }
        } catch (SQLException e) {
            System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
                System.out.println("データベースからの切断に成功しました。");
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}

以下のように全体を文字列として解釈するためSQLインジェクションは成立しないのです。

INSERT文

次にレコードの挿入です。

package p08;

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

public class Insert {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "INSERT INTO cars(name, price, end_of_life_dates) VALUES(?, ?, ? )";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);

            PreparedStatement ps = con.prepareStatement(SQL);

            ps.setString(1, "ジープ");
            ps.setInt(2, 2000000);
            ps.setString(3, null);

            System.out.println(ps.executeUpdate());

        } catch (SQLException ex) {
            System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}
  • SELECT文のときとSQL文は何が違いますか?
あなたの答え:
  • 28行目から30行目では何をしていると推測されますか?
あなたの答え:
  • 32行目のps.executeUpdate()の戻り値を答えなさい。
あなたの答え:

今回SQLに埋め込まれた?マークをプレースホルダといいます。

このプレースホルダにps.setIntメソッドやps.setStringメソッドを使って実際の値を入れることでSQL文を完成させます。

メソッドの引数はそれぞれ、(位置,値)でしたね。

位置は左から順に1,2,...となっています。

実はPreparedStatementは、の最大の特徴はSQL文の解析と実行の処理を別々に行う点で、これによって処理速度を向上させることができます。

例題3

上記の例を参考にcustomersテーブルにあなたのチームメンバー一人を追加するJavaプログラムを作成しなさい。(結果は先の例題2のプログラムで確認のこと)

UPDATE文

次にレコードの更新を見てみましょう。

package p08;

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

public class Update {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "UPDATE cars SET end_of_life_dates = ? WHERE car_id = ?";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args)throws Exception {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);
            con.setAutoCommit(false);

            PreparedStatement ps = con.prepareStatement(SQL);

            ps.setString(1, "2021-12-19");
            ps.setInt(2, 9);

            System.out.println(ps.executeUpdate());
            
            con.commit();

        } catch (SQLException ex) {
            con.rollback();
             System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}

例題4

上記の例を参考にcustomersテーブルのチームメンバーのレコードを更新するJavaプログラムを作成しなさい。(結果は先の例題2のプログラムで確認のこと)

DELETE文

package p08;

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

public class Delete {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "DELETE FROM cars WHERE car_id = ?";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) throws Exception {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);
            con.setAutoCommit(false);

            PreparedStatement ps = con.prepareStatement(SQL);

            ps.setInt(1, 10);
            System.out.println(ps.executeUpdate());

            con.commit();

        } catch (SQLException ex) {
            con.rollback();
            System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}

例題4

上記の例を参考にcustomersテーブルのチームメンバーのレコードを削除するJavaプログラムを作成しなさい。(結果は先の例題2のプログラムで確認のこと)

CRUD【Create, Read, Update, Delete】処理を全て紹介し終わったところで、SQLを実行する2つのメソッドについてまとめておきます。

SQLの実行メソッド対応SQL文戻り値考え方
executeQuery()SELECT文ResultSetオブジェクト(必要に応じてプレースホルダをsetXXXメソッドで埋めてから)SQLを実行してResultSetを取得、
nextメソッドでカーソルを移動し、そのレコードから必要な列をgetXXXメソッドで取得する
executeUpdate()INSERT文
UPDATE文
DELETE文
主として処理したレコード数SQLのプレースホルダをsetXXXメソッドで埋めてから実行する

トランザクションとは何か?

ここからの操作は、Auto Commitモードをオフにして行います。

START TRANSACTION;
    
INSERT INTO sales(sale_id, car_id, customer_id, saleDateTime) VALUES(51, 5, 1, '2021-12-27 12:00:00');
    
UPDATE customers SET points = 100 WHERE customer_id = 1;

ROLLBACK;
  • 3行目のINSERT文は何をしていますか?
あなたの答え:
  • 5行目のUPDATE文は何をしていますか?
あなたの答え:

この処理を1行ずつ実行する処理をやってみてください。(あるいは講師の実演を見てください)

車を購入いただいたお客様に100ポイント付けているという処理でした。

ここで例えば、自動車の購入処理だけが行われて、ポイントを付ける処理が失敗する、あるいはその逆があったとしたらどうでしょうか?

例えば、重複した商品(車)を登録しないようなJavaプログラムは以下のようになります。

package p08;

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

public class Insert2NoDuplication {

    public static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    public static final String USERID = "newuser";
    public static final String PASSWORD = "0";
    public static final String SQL = "INSERT INTO cars(name, price, end_of_life_dates) VALUES(?, ?, ? )";

    public static Connection con = null;
    public static PreparedStatement ps = null;
    public static ResultSet rs = null;

    public static void main(String[] args) {

        try {
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);
            
            con.setAutoCommit(false);

            PreparedStatement ps = con.prepareStatement(SQL);
            
            String carName = "ジープ";

            ps.setString(1, carName);
            ps.setInt(2, 2000000);
            ps.setString(3, null);
            
            ps.executeUpdate();
            
            ps=con.prepareStatement("SELECT * FROM sip_a.cars where name = ?");
            ps.setString(1,carName);
            
            rs = ps.executeQuery();
            int cnt = 0;
            while(rs.next()){
                cnt++;
            }
            
            if(cnt == 1){
                con.commit();
                System.out.println("商品を追加しました");
            }else{
                con.rollback();
                System.out.println("商品はすでに登録されています");
            }

            con.setAutoCommit(true);

        } catch (SQLException ex) {
            System.err.println("データベースへの接続時に問題が発生しました。");
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                System.err.println("データベースからの切断時に問題が発生しました。");
            }
        }
    }
}

データベースの接続・切断の処理をスーパークラスとして切り出す

これまでは、個々のクラスでデータベースに接続する処理とデータベースから切断する処理を書いてきました。

しかし、これは、DRY【Don't Repeat Your Self】原則に反しています。

つまり、同じ処理をアチラコチラに書くと、例えばMySQLのパスワードを変更した際にその全てを変更する必要が発生してメンテナンス性が低下します。

そこで、本書ではデータベースの接続と切断を行うSuperDAOクラスを作成して、各操作はSuperDAOクラスのサブクラスとして作成することにします。

「全てのサブクラスは一種のSuperDAOクラス」といえるのでこのクラス設計で問題ないでしょう。

(実務上はデータベースへの接続情報は別途ファイルを用意して、そのファイルの情報を読み込むようにすることでより柔軟にデータベースへの接続情報を変更できるようにする場合があります)

データベースへの接続と切断を担当するSuperDAOクラスは以下のようになりました。

package p08;

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

public class SuperDAO {

    private static final String CONNECT_STRING
            = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
    private static final String USERID = "newuser";
    private static final String PASSWORD = "0";
    protected Connection con = null;

    protected void connect() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection(CONNECT_STRING, USERID, PASSWORD);
            System.out.println("データベースへの接続に成功しました。");
        } catch (SQLException e) {
            System.err.println("データベースへの接続時に問題が発生しました。");
            System.err.println(e);
        } catch (ClassNotFoundException ex) {
            System.err.println(ex);
        }
    }

    protected void close(PreparedStatement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
            if (con != null) {
                con.close();
            }
            System.out.println("データベースからの切断に成功しました。");
        } catch (SQLException e) {
            System.err.println("データベースからの切断時に問題が発生しました。");
            System.err.println(e);
        }
    }

    public static void main(String[] args) {
        SuperDAO sd = new SuperDAO();

        PreparedStatement ps = null;

        sd.connect();

        sd.close(ps);

    }
}
  • 上記クラスにはフィールドはいくつあって、それぞれの役割はなんですか?
あなたの答え:
  • 上記クラスにはメソッドはいくつあって、それぞれの役割はなんですか?
あなたの答え:

まとめ

JavaWebアプリケーション目次に戻る