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

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

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

これまでに作成してきたWebアプリケーションのデータはアプリケーションサーバのメモリ上にだけ存在しています。ということはアプリケーションサーバを再起動したり、電源を切るとデータは消えてしまいます。

そこで、何らかの方法でデータを外部記憶装置に保存する必要が出てきます。(難しい表現ではデータの永続化 【perpetuation】ともいいます)その際にデータベースとファイルという候補がありますが、データベースのほうが優れているということはこの研修でも学んだ通りです。

2.JDBCとは何か?

JDBC【Java DataBase Connectivity】とは、JavaプログラムからデータベースにアクセスするためのAPI【Application Programming Interface】です。もしもJDBCが無かったら、以下の図8.1.のようにデータベースの種類ごとにJavaのプログラムを変えなくてはなりません。

図8.1 もしもJDBCが無かったら

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

下図8.2はその概念図です。

図8.2 JDBCがデータベースの違いを吸収する

3.JavaSEでJDBCを扱う

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

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

②SQL文を表すPreparedStatement

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

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

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

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

WebアプリケーションでMySQLを扱う前に、JavaSEでMySQLを扱ってみましょう。この方法は、いちいちアプリケーション・サーバーを立ち上げる必要がないためテストに要する時間を短縮できます。さらに、常に以下の手順を踏めば、確実に動くプログラムを得ることができます。

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

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

急がば回れです。

3.1 SELECT文

ここでは、SELECT文の実行結果であるResultSetの扱い方を学びます。

ResultSetの構造を理解する

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

なお、データベース(MySQL)講座の中でユーザーやパスワードは既に設定してあり、テーブルも作成済みであるという前提です。

なお、このサンプルプログラムは記述をシンプルに留めるためにmainメソッドで例外を呼び出し元(JVM)に投げています。

  • 23~27行目では何をしていると推測されますか?
あなたの答え:
  • 29目の出力結果は何でしたか?
あなたの答え:
  • 33~38行目では何をしていると推測されますか?
あなたの答え:

なお、上記の接続と切断(ResultSetは閉じる必要がないのは前述のとおりです)の処理は決り文句ですので覚える必要はありません。また、接続情報(CONNECT_STRING)の文字列には下図8.4のような意味がありますが、これも覚える必要はありません。

ただし、⑤のデータベース名だけは皆さんがMySQLで作成したスキーマ名になりますのでその点だけ忘れないように書き換えてください。

図8.4 接続情報の意味

29行目の出力結果はtrueでした。

rs.next();

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

【Result】= 結果、【Set】= セットですので、SQLを実行した結果のセットがResultSetなのです。このResultSetは構造上、カーソルを1つ進めないと1行目のデータが読めないのです。

8.5 カーソルの概念

また、 ResultSet の一行から特定の列(や集約関数の結果)を取り出すには以下のようにインスタンスメソッドgetXxxに列名(や集約関数名)を引数として渡します

rs.getInt("count(*)")

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

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

例題1

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

実験1

上記サンプルプログラムのUSERIDやPASSWORD、SQLの予約語(SELECT)、count(*)やテーブル名(cars)をわざと間違えて、どのようなエラーメッセージが出るかを観察しましょう。

観察結果のメモ:

USERID:
PASSWORD:
SELECT:
count(*):
cars:

ResultSetから複数件のレコードを取得するには

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

以下のSelect2.javaを読み込んで質問に答えてください。

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

  • 先の1件のレコードを取得したときと何が違いますか?
あなたの答え:

今回のResultSet rsのインスタンスの中身は下図8.6になります。そのため繰り返しが必要なのでした。

図8.6 ResultSet rsのインスタンスの中身 

例題2

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

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

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

以下のSelect3Injection.javaを実行してみましょう。

customer_idを指定してください

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

※このプログラムは1件のレコードを取得する仕様に対して、複数件のレコードを取得する繰り返しがあり不自然ですが、この後の解説につなげる意図ですので容赦ください。

  • 16行目でfinalキーワードを抜きました。これにはどのような意味がありますか?
あなたの答え:
  • 33行目では何をしていますか?
あなたの答え:

3.2 SQLインジェクションとは?

SQLインジェクションはWebアプリケーションのデータベースを不正に操作する攻撃手法です。SQLインジェクションにより、個人情報やパスワード、クレジットカード情報などが盗まれることがあります。ここからのお話は想像力をたくましくしてお聞きください。つまり、サンプルコードはWebアプリケーションではないですが、もしも、Webアプリケーションで同じことが行われたらどうなるかと想像しながら聞いてください。

上記のSelect3Injection.javaの実行画面で、例えば以下のように入力するとどうなりますか?

1 or 1 = 1

全てのレコードが取得できたはずです。実はこれはSQLインジェクションとして知られた攻撃の簡略版なのです。パスワードが全て盗まれてしまいましたね。


おそらく最近もSQLインジェクションの被害事例があると思いますのでリンクをたどってみてください。

新しいプログラム言語にはこれから説明するようなSQLインジェクション対策が施されているにもかかわらず、未だに古い言語や古い書き方をして被害に合う組織が多いことは本当に残念です。

また、WAF【Web Application Firewall】の仕組みを導入することによりSQLインジェクション対策を施すことも有効です。

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

下図8.6の赤枠で囲った「1 = 1」の部分が常にtrueと評価されるために全てのレコードという意味になるのですね。このようにSQL文の組み立てを文字列連結によっているのは悪い書き方です。

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

図8.7 SQLインジェクション

実験2

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

実験結果のメモ:

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

SQLインジェクション対策はどうすればよいのでしょうか?

プレースホルダを使用するという方法があります。プレースホルダとは以下Select4Placeholder2.javaの16行目にある「?」です。この「?」は35行目の以下のコードによりキーボードから入力された値に置き換わるのです。

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

この setIntメソッドの第1引数はプレースホルダの番号です。この番号はSQL文の左から順に1,2…と数えられます。0からではない点に注意してください。

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

また、第2引数はセットしたい値です。気をつけないといけないのは、今回はint値であったため、setIntメソッドでしたが、文字列であればsetStringメソッドになるということです。

以下のSelect4Placeholder2.javaは車のidで検索できるプログラムです。

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

NumberFormatExceptionが発生して処理が止まってしまいました。(もちろん例外処理を適切にすれば処理を止めないことも可能です)

SQLインジェクションが成立しないのは、下図8.7のようにプレースホルダ全体が1つの文字列として解釈されるためです。プレースホルダ(?)が1つの文字列に置き換わるのです。"1 or 1 = 1"というidの車はありませんからね。

図8.8 ?が1つの文字列に置き換わる

例題3

Select3Injectionに対してもSQLインジェクション対策を施しなさい。

3.3 INSERT文

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

以下のInsert.javaを読み込んで質問に答えてください。

  • 15行目を見てこのSQL文を解説しなさい。
あなたの答え:
  • 28行目から30行目では何をしていると推測されますか?
あなたの答え:
  • 32行目のps.executeUpdate()の戻り値を答えなさい。
あなたの答え:

これまで学んだSELECT文の場合は、SQLを実行するメソッドがexecuteQuery()でしたね。しかし、今回のINSERT文では、executeUpdate()ですので気をつけましょう。【Query】=問い合わせ、【Update】=更新と意味から考えれば間違うことがなくなるでしょう。

  • このあと学ぶUPDATE文、DELETE文はexecuteQuery()とexecuteUpdate()どちらを使うと思いますか?
あなたの答え:

今回SQLに埋め込まれた“?”マークをプレースホルダといいました。このプレースホルダにps.setIntメソッドやps.setStringメソッドを使って実際の値を入れることでSQL文を完成させるのでした。メソッドの引数はそれぞれ、(位置,値)でしたね。

プレースホルダの「?」の位置は左から順に1から数えます。(0からでない点に注意するのでした)

例題4

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

3.4 UPDATE文

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

以下のUpdate.javaは何をしているプログラムですか?

  • このプログラムの処理内容は?
あなたの答え:

SQLのUPDATE文を実行するメソッドがexecuteUpdate()であるということは覚えやすいと思います。しかし、INSERT文やDELETE文もexecuteUpdate()であるということは忘れやすいので気をつけましょう。

例題5

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

3.5 DELETE文

以下のDelete.javaは何をしているプログラムですか?

  • このプログラムの処理内容は?
あなたの答え:

例題6

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

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

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

4. トランザクションを使いデータの不整合を防ぐ

トランザクションとは、複数の SQL文によるデータ更新を1つの処理としてまとめてデータベースに反映させる仕組みです。

もしも、トランザクションが無いと以下のように困ったことが起こりえます。

例えば、借金返済のために私の銀行口座から皆さんの銀行口座に100万円を振り込むとします。つまり、私の銀行の残高を-100万円して、皆さんの銀行の残高を+100万円します。この2つの処理は1セットですね。もしも、私の銀行の残高を-100万円することには成功して、皆さんの銀行の残高を+100万円することに失敗したとなると困ったことになります。そうならないように、複数の SQL文によるデータ更新を1つの処理として、「全て成功」か「すべて失敗」にするための仕組みがトランザクションです。

データの不整合を防ぐのがトランザクションの役割です。


ここからの操作は、まずはMySQL単体で行います。

また、その際、下図8.8のようにAuto Commitモードをオフにして行います。なぜなら、Auto Commitモードがオンですと、1行実行するたびに処理がコミット(確定)してしまうからです。

図8.9 オートコミットモード

まずはMySQLの復習になりますが、以下transaction.sqlのソースコードを各自解析してください。

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

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

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

そうならないようにするのがトランザクションでした。

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

5. DAOパターンでデータベース処理を専門のクラスに任せる

これまでは、1つのクラスのmainメソッドの中にデータベース処理を書いてきました。しかし、このままではmainメソッドが肥大化して理解にしにくく、メンテナンスしにくいプログラムになってしまいます。そこで、データベース処理を専門のクラスに任せることにします。ビジネスロジックとデータベース処理をそれぞれ別のクラスに担当させるのです。

この考え方をDAOパターン【Data Access Object Pattern】といいます。

パターンというのは以前、MVCパターンのところでもでてきたデザインパターンのことです。

その概念を図示すると以下の図8.9になります。

図8.10 DAOパターン

以下Select1DAO.javaは、先に作成したSelect1クラスをselect1DAOクラスとしてビジネスロジッククラスのmainメソッドから呼び出すサンプルコードです。

クラスが2つになりますが、個々のクラスの役割ははっきりしてメンテナンスしやすくなりました。また、このDAOクラスを部品として他のクラスから再利用しやすくもなりました。

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

これまでは、個々のクラスでデータベースに接続する処理とデータベースから切断する処理を書いてきました。しかし、これは、DRY【Don't Repeat Your Self】原則に反しています。つまり、同じ処理をアチラコチラに書くとメンテナンス性が低下します。例えば、MySQLのパスワードを変更した際にその全てのファイルの記述箇所を探して変更する必要が生じてしまいますね。

そこで、本書では下図8.10のようにデータベースの接続と切断を行うSuperDAOクラスを作成して、carsやcustomersなどの各テーブルとの操作はSuperDAOクラスのサブクラスとして作成することにします。

「全てのサブクラスは一種のSuperDAOクラス」と言えますか?

言えますね。

ですので、このクラス設計で問題ないでしょう。

また、テーブルごとにSELECT、INSERT、UPDATE、DELETEの処理がまとまっているのが直感的にわかりやすいでしょうね。

(なお、データベースの接続情報は後から変更したくなることもあります。ところがソースコードに接続情報を記述すると変更後に再コンパイルが必要になってしまいます。そのため、実務上はデータベースへの接続情報は別途ファイルを用意して、そのファイルの情報を読み込むようにすることで、より柔軟にデータベースへの接続情報を変更できるようにする場合があります。詳細はキーワードJNDIで検索してください)

superDAO
図8.11 superDAOクラス

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

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

例題7

CustomersDAO.javaクラスを作成し、顧客情報をすべて表示するselectメソッドを作成しなさい。

テストクラスから動作を確認すること。

また、余裕があれば、他のメソッドも実装しなさい。

例題8

CarsDAO.javaクラスを作成し、車情報をすべて表示するselectメソッドを作成しなさい。

テストクラスから動作を確認すること。

余裕があれば、他のメソッドも実装しなさい。

7.Webアプリケーションとデータベースを連携させる

これまでの知識を応用してデータベースに入っているユーザー情報を使ってシステムにログインする処理を書いてみましょう。できるだけ過去に作成したプログラムを再利用することにします。

また、前提として下図8.11、12のようなlogin_userテーブルがsip_aスキーマにあることとします。

login_user1
図8.11 login_userテーブルのデータ型
login_user2
図8.12 login_userテーブルのデータ

新規作成クラス

<LoginDAO.java>

継承するクラス:SuperDAO

属性

属性名可視性static初期値説明
SQLprivateString"SELECT * FROM login_user where login_id = ? and password = ?"login_idとpassword がテーブルに存在するかどうかを問い合わせる
psprivatePreparedStatement-null PreparedStatement インタフェースのオブジェクト

操作

操作名可視性引数リスト返却値static説明
loginpublicString id, String passwordboolean-SQL を実行して、レコードが0件を超えていたらtrueを返す、超えていなければfalseを返す。

<LoginServlet.java>

継承するクラス:HttpServlet

urlPatterns:/LoginServlet

属性:なし

操作

操作名可視性引数リスト返却値static説明
doPostprotectedHttpServletRequest request, HttpServletResponse responsevoid-フォームから送信されたidとpassを元に、LoginDAOを使ってログインを試みる。ログインできた場合は、m05Login/ember_only2.jspにフォワードする。ログインできなかった場合は05Login/login_error3.jspにリダイレクトする。

必要なソースコードは以下のとおりです。

ただし、superDAO、05Login/member_only2.jspと05Login/login_error3.jspは再利用しているため掲載を割愛します。

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">

        <title>login_post3.jsp</title>
        <style>
            #center {
                height: 200px;
                width: 300px;
                position: absolute;
                left: 50%;
                top: 50%;
                margin-top: -100px;
                margin-left: -150px;
                text-align: center;
            }
        </style>
    </head>
    <body>
        <div id="center">
            <form action="/03_JavaWebText/LoginServlet" method="post">
                ユーザーID:<input type="text" name="id" required><br>
                パスワード:<input type="password" name="pass" required><br>
                <input type="submit" value="ログイン"><br>
            </form>
        </div>
    </body>
</html>

今回は、JDBCでデータベースと接続する方法を見てきました。これでJavaWebアプリケーションとMySQLを組み合わせることができました。ここまでの学習内容でこのあと受講者の皆さんに配布するサンプルWebアプリケーションを自力で読み取れるようになったことでしょう。

ただし、システムはあらゆる事態を想定していないといけません。

例えば、想定していないデータを入れられた場合にも優しくそれをたしなめる様なシステムが求められます。(フールプルーフといいます)そのためにはバリデーションという考え方が必要です。次回は、正規表現を使いバリデーションをする方法を学びます。

<まとめ:隣の人に正しく説明できたらチェックを付けましょう>

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

□ Javaプログラムからデータベースを利用するためには、Connection、PreparedStatement、ResultSetの3つのオブジェクトが必要である

□ ResultSet の一行から特定の列(や集約関数の結果)を取り出すにはrs.getInt("count(*)")のように列名(や集約関数名)で取り出したい列を指定する。ただし、型にあわせて getStringメソッド やgetDoubleメソッドを使い分けないといけない

□ プレースホルダを使用することでSQLインジェクション攻撃を避けることができる

□ プレースホルダの?の位置は左から順に1から数える。0からでない点に注意する。

□ SELECT文にはexecuteQuery()が、INSERT文・UPDATE文・DELETE文にはexecuteUpdate()がそれぞれ対応する。

□ トランザクションとは、複数の SQL文によるデータ更新を1つの処理としてまとめてデータベースに反映させることでありデータの不整合を防ぐ効果がある

□ データベース処理を専門のクラスに任せるのがDAOパターンである

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