前回は、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はあたかも「多言語を話す通訳者」のようなものです。

JDBCがデータベースの違いを吸収するため、どのようなデータベースに対しても、あるいはデータベースを変更しても、同じ手順で接続することができるのです。異なるデータベース製品は、DBMSの実装やSQLの方言などで差異がありますが、JDBCはそれらの差異を隠蔽し、Javaプログラマが統一的な方法でデータベースにアクセスできるようにします。

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

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

講師の指示に従い、MySQLのJDBCドライバー(mysql-connector-java-x.x.xx.jar)をダウンロードして、Javaプロジェクトのクラスパスに追加します。

Eclipseを使っている場合は、下図8.3のようにプロジェクトの中のWEB-INFディレクトリの中のlibディレクトリにダウンロードしたjarファイルをコピーするだけです。

JDBCがデータベースの違いを吸収する
図8.3 mysql-connector-javaをコピーするディレクトリ

3. JavaSEでJDBCを扱う

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

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

②SQL文を表すPreparedStatement

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

図8.4 SELET文を実行するための3つのオブジェクト

Connection

  • 役割: データベースへの接続を管理します。
  • 使用法: データベースへの接続を確立するために使用され、この接続を通じてSQL文を実行します。データベース接続はコストがかかるリソースです。以降のコードでは、接続を一度開いて、複数の異なる操作で再利用しています。これにより、接続と切断のオーバーヘッドが減少し、アプリケーションのパフォーマンスが向上します。

PreparedStatement:

  • 役割: パラメータ化されたSQL文を表します。
  • 使用法: SQLインジェクション攻撃(後述)を防ぐため、または同じSQL文を繰り返し実行する際に効率的に使用されます。PreparedStatementを使用することで、SQL文にパラメータを動的に挿入できます。

ResultSet:

  • 役割: SQLクエリから返されたデータを保持します。
  • 使用法: クエリの実行結果として得られたデータセットを操作し、データを読み取るために使用されます。

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

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

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

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

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

図8.5 DAOパターン

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

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

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

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

言えますね。

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

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

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

superDAO
図8.6 superDAOクラス

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

package model.dao;

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

/**
 * Daoクラス - 各daoの基底クラス データベースへの接続、切断、クエリ実行など各dao共通の機能を実装する
 * 
 * @author Say Consulting Group
 * @version 1.0.0
 */
public class SuperDao {

	/** DB接続オブジェクト */
	protected Connection con = null;

	/** DB接続情報 */
	private static final String DB_URI = "jdbc:mysql://localhost:3306/sip_a?characterEncoding=utf8&"
			+ "useSSL=false&serverTimezone=GMT%2B9&rewriteBatchedStatements=true";
	private static final String DB_USER = "newuser";
	private static final String DB_PASS = "0";

	/**
	 * openメソッド データベースへの接続を開始する
	 */
	protected void connect() {
		try {
			// データベース接続用ドライバをロード
			// javaSE8から不要な処理となったが、Tomcatを利用する場合は必要なので、記述
			Class.forName("com.mysql.cj.jdbc.Driver");
			// ドライバを用いてデータベースへの接続を開く
			con = DriverManager.getConnection(DB_URI, DB_USER, DB_PASS);
			System.out.println("データベースへの接続に成功しました。");
		} catch (SQLException e) {
			String errMsg = "データベースへの接続時に問題が発生しました。";
			System.err.println(errMsg);
			System.err.println(e);
		} catch (ClassNotFoundException e) {
			String errMsg = "データベース接続用クラスが見つかりません。";
			System.err.println(errMsg);
			System.err.println(e);
		}
	}

	/**
	 * closeメソッド DBとの接続を切断する 各オブジェクトを再初期化する
	 */
	protected void close() {
		try {
			if (con != null) {
				con.close();
				con = null;
			}
			System.out.println("データベースからの切断に成功しました。");
		} catch (SQLException e) {
			String errMsg = "データベースからの切断時に問題が発生しました。";
			System.err.println(errMsg);
			System.err.println(e);
		}
	}
}
  • SuperDAO.javaにはフィールドはいくつあって、それぞれの役割はなんですか?
あなたの答え:
  • メソッドはいくつあって、それぞれの役割はなんですか?
あなたの答え:
  • 上記のフィールドやメソッドはSuperDAOクラスのサブクラスで定義しなくても使えますか?
あなたの答え:

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

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

図8.7 接続情報の意味

上記のコードに便宜的に以下のmainメソッドを追加して実行してみて下さい。

	public static void main(String[] args) {
		SuperDao superDao = new SuperDao();
		superDao.connect();
		superDao.close();
	}

<実行結果>

データベースへの接続に成功しました。
データベースからの切断に成功しました。

このように接続と切断だけをするスーパークラスができました。

次にこのスーパークラスを継承したクラス(WithSuperClass.java)を作成します。データベースに格納されている車の数をコンソール出力するプログラムです。詳細はひとまず置いて行数(39行)に着目してください。

package model.dao;

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

public class WithSuperClass extends SuperDao {

	private PreparedStatement ps;

	public int countCars() {

		int ret = 0;

		this.connect();

		String SQL = "SELECT count(*) FROM cars";
		try {
			ps = con.prepareStatement(SQL);

			ResultSet rs = ps.executeQuery();

			rs.next();

			ret = rs.getInt("count(*)");

		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			this.close();
		}
		return ret;
	}

	public static void main(String[] args) {
		WithSuperClass wsc = new WithSuperClass();
		System.out.println(wsc.countCars());
	}
}

もしも、スーパークラスを継承しない場合は以下のWithoutSuperClass.javaのように61行と長くなります。(スーパークラスまで含めると上記のコードも長いのですが、サブクラスが増えるにつれコード削減効果がでることは理解いただけるものと思います)

また、設定情報などの決まり文句が一箇所に集まっているのもスーパークラスを継承するメリットです。

package model.dao;

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

public class WithoutSuperClass {

	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 USER_ID = "newuser";
	private static final String PASSWORD = "0";
	private static Connection con = null;
	private static PreparedStatement ps = null;

	public int countCars() {
		try {
			con = DriverManager.getConnection(CONNECT_STRING, USER_ID, PASSWORD);
			System.out.println("データベースへの接続に成功しました。");
		} catch (SQLException e) {
			System.err.println("データベースへの接続時に問題が発生しました。");
			System.err.println(e);
		}

		int ret = 0;

		String SQL = "SELECT count(*) FROM cars";
		try {
			ps = con.prepareStatement(SQL);

			ResultSet rs = ps.executeQuery();

			rs.next();

			ret = rs.getInt("count(*)");

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

	public static void main(String[] args) {
		WithoutSuperClass wosc = new WithoutSuperClass();
		System.out.println(wosc.countCars());
	}
}

ここからはSuperDAOを継承したCarsクラスの様々なメソッドを通じてJavaからデータベースを扱う方法を学んでいきます。

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

4. SELECT文

ここからいよいよ本格的にJavaからMySQLを扱うことにします。ただし、WebアプリケーションでMySQLを扱う前に、JavaSEでMySQLを扱ってみましょう。この方法は、いちいちアプリケーション・サーバーを立ち上げる必要がないためテストに要する時間を短縮できます。

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

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

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

急がば回れです。

4.1 ResultSetの構造を理解する(まずは1レコードを取得する)

以下CarsDAO.javaのcountCarsメソッドはcarsテーブルの車の数を取得するJavaSEプログラムです。これ以降のコードにはコメントを付けますので参考にして下さい。

以下の2点が前提です。

  • データベース(MySQL)講座の中でユーザーやパスワードは既に設定してあり、テーブルも作成済みである
  • modelパッケージに6章で作成したCarbeanクラスをコピーしてあり、以下のCarsBeanクラスも作成済みである。
package model;

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class CarsBean implements Serializable {

	private List<CarBean> carArrayList = new ArrayList<>();

	public CarsBean() {
	}

	public List<CarBean> getCarArrayList() {
		return carArrayList;
	}

	public void setCarArrayList(List<CarBean> carArrayList) {
		this.carArrayList = carArrayList;
	}

	public void addCar(CarBean aCar) {
		this.carArrayList.add(aCar);
	}

	public int getCarsSize() {
		return this.carArrayList.size();
	}

	@Override
	public String toString() {
		return "CarsBean{" + "carArrayList=" + carArrayList + '}';
	}
}

  • CarsBeanクラスにメインメソッドを加えて、CarBeanのインスタンスを複数作成し、CarsBeanにaddCarしてからコンソール出力してみなさい。
あなたの答え:
  • プログラムを実行する前にcarsテーブルの車の数を取得するSQL文をMySQLWorkBenchで実行し、結果を以下に書き入れなさい。
あなたの答え:
package model.dao;

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

import model.CarBean;
import model.CarsBean;

public class CarsDao extends SuperDao {

	// メソッドの開始:車の数をカウントするメソッド
	public int countCarsBean() {

		// カウントした車の数を保持するための整数変数retを初期化
		int ret = 0;

		// データベースに接続するメソッドを呼び出し
		connect();

		// 車の総数を取得するためのSQLクエリを定義
		String sql = "SELECT count(*) FROM cars";

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// クエリを実行し、結果をResultSetに格納
			ResultSet rs = ps.executeQuery();

			// ResultSetの最初の行に移動
			rs.next();

			// ResultSetから車の総数を取得してretに代入
			ret = rs.getInt("count(*)");

		} catch (SQLException e) {
			// SQL例外が発生した場合の処理
			// エラーメッセージをコンソールに出力
			System.err.println(e);
		}

		close();

		// 車の総数を返す
		return ret;
	}
(以下のメソッドはこの後順番に紹介します)
  • 「con.prepareStatement(sql)」これはインスタンスメソッド、スタティックメソッド、どちらですか?
あなたの答え:

ちなみに、クラス名は過去分詞形のPrepared、メソッド名は動詞のprepareです。

  • 「rs.next()」の戻り値は何ですか? コンソール出力してみましょう。
あなたの答え:
  • 「rs.getInt("count(*)")」では何をしていると推測されますか?
あなたの答え:

rs.next()の戻り値はtrueでした。

rs.next();

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

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

8.8 カーソルの概念

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

rs.getInt("count(*)")

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

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

例題1

CarsDAO.javaをコピーしてCustomers.javaとし、customersテーブルの顧客数をコンソール出力するcountCustomersメソッドを作成しなさい。

例題2

CarsDAO.javaのUSERIDやPASSWORD、SQLの予約語(SELECT)、列名のcount(*)やテーブル名(cars)のスペルをわざと間違えて、どのようなエラーメッセージが出るかを観察しましょう。

観察結果のメモ:

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

4.2 ResultSetから全件のレコードを取得する

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

  • carsテーブルの全ての車のレコードを取得するSQL文を以下に記述してから、MySQLWorkBenchで実行しなさい。
あなたの答え:

以下のCarsDAO.javaのgetCarsBeanメソッドを読み込んで質問に答えてください。

	// メソッドの開始:CarsBeanオブジェクトを取得するメソッド
	public CarsBean getCarsBean() {

		// CarsBeanオブジェクトのインスタンスを生成
		CarsBean carsBean = new CarsBean();

		// データベースに接続するメソッドを呼び出し
		connect();

		// 'cars'テーブルから全てのデータを選択するSQLクエリを定義
		String sql = "SELECT * FROM cars";

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// クエリを実行し、結果をResultSetに格納
			ResultSet rs = ps.executeQuery();

			// ResultSetを繰り返し処理し、各車のデータを取得
			while (rs.next()) {
				// CarBeanオブジェクトのインスタンスを生成
				CarBean car = new CarBean();

				// CarBeanにcar_id, name, price, deleted_atを設定
				car.setCarId(rs.getInt("car_id"));
				car.setName(rs.getString("name"));
				car.setPrice(rs.getInt("price"));
				car.setDeletedAt(rs.getString("deleted_at"));

				// 取得した車のデータをCarsBeanに追加
				carsBean.addCar(car);
			}
		} catch (SQLException e) {
			// SQL例外が発生した場合の処理
			// エラーメッセージをコンソールに出力
			System.err.println(e);
		}
		close();
		// 取得したCarsBeanを返す
		return carsBean;
	}

先の1件のレコードを取得したときとソースコードは何が違いますか?

  • SQL文の観点から違いを挙げてください。
あなたの答え:
  • 繰り返し処理の観点から違いを挙げてください。
あなたの答え:
  • 戻り値の観点から違いを挙げてください。
あなたの答え:
  • 適切なメインメソッドを加えて実行してみて下さい。
あなたの答え:

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

図8.9 ResultSet rsのインスタンスのイメージ 

例題3

CustomersDAO.javaにcustomersテーブルの全ての顧客のすべてのデータをコンソール出力するSelectAllCustomersメソッドを作成しなさい。

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

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

  • carsテーブルから特定のcar_id(例えば1)を持つ車のレコードを取得するSQL文を以下に記述してから、MySQLWorkBenchで実行しなさい。
あなたの答え:

以下のメソッドを実行してみましょう。

	// 特定のIDを持つ車の情報をデータベースから取得するメソッド
	public CarBean getACar(int id) {

		// CarBeanの新しいインスタンスを生成
		CarBean car = new CarBean();

		// データベースへの接続を行う
		this.connect();

		// 指定されたcar_idに基づいて車の情報を取得するSQLクエリ
		String sql = "select * from cars where car_id = ?";

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// クエリのパラメータ(car_id)を設定する
			ps.setInt(1, id);

			// クエリを実行し、結果をResultSetに格納する
			ResultSet rs = ps.executeQuery();

			// 結果セットにデータが存在する場合のみ処理を行う
			if (rs.next()) {

				// ResultSetから車の情報を取得し、CarBeanにセットする
				car.setCarId(rs.getInt("car_id"));
				car.setName(rs.getString("name"));
				car.setPrice(rs.getInt("price"));
				car.setDeletedAt(rs.getString("deleted_at"));
			}

		} catch (SQLException e) {
			// SQL例外が発生した場合、エラーをコンソールに出力する
			System.err.println(e);
		}
		close();

		// 取得した車の情報を含むCarBeanを返す
		return car;
	}
  • 上記のgetACarメソッドの引数と戻り値の型を答えなさい。
あなたの答え:
  • 仮に同じcar_idの車が複数台データベースに格納されていた場合はどうなりますか?
あなたの答え:
  • 適切なメインメソッドを加えて実行してみて下さい。
あなたの答え:

上記のコードではSQL文の最後が「car_id = ?」となっています。この「?」をプレースホルダといいます。このプレースホルダにはps.setString(1, id);のところでgetACarメソッドの引数のidが入ります。

今回はプレースホルダが1つです。しかし、プレースホルダの「?」は複数あっても良いです。その場合の数え方は左から1,2…となります。0始まりでない点は注意して下さい。

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

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

SQLインジェクションは、Webアプリケーションのデータベースを不正に操作する攻撃手法です。SQLインジェクションにより、個人情報やパスワード、クレジットカード情報などが盗まれることがあります。

ここからのお話は想像力をたくましくしてお聞きください。つまり、以下のサンプルコードSQLInjection.javaはWebアプリケーションではないですが、もしも、Webアプリケーションで同じことが行われたらどうなるかと想像しながら聞いてください。

package model.dao;

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

public class SQLInjection extends SuperDao {

	public static String SQL = "SELECT * FROM customers where customer_id = ";

	PreparedStatement ps = null;
	ResultSet rs = null;

	public void getACustomer() {

		try {
			connect();
			System.out.println("customer_idを整数値で指定してください");
			try (Scanner sc = new Scanner(System.in)) {
				String customer_id = sc.nextLine();
				SQL += customer_id;
			}

			ps = con.prepareStatement(SQL);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getInt("customer_id") + ":");
				System.out.print(rs.getString("name") + ":");
				System.out.print(rs.getString("mail") + ":");
				System.out.print(rs.getString("mobile") + ":");
				System.out.println(rs.getString("pass"));
			}
		} catch (SQLException e) {
			System.err.println("データベースへの接続時に問題が発生しました。");
			System.err.println(e);
		} finally {
			this.close();
		}
	}

	public static void main(String[] args) {
		SQLInjection sqlInjection = new SQLInjection();
		sqlInjection.getACustomer();
	}
}

上記プログラムを実行して、

customer_idを指定してください

につづけて適当な数値を入力してエンターするとその番号の顧客の個人情報が標準出力に表示されます。(このプログラムは1件のレコードを取得する仕様に対して、複数件のレコードを取得する繰り返しがあり、不自然ですが、SQLインジェクションの解説につなげる意図ですので容赦ください)

  • SQLの宣言からfinalキーワードを抜きました。これにはどのような意味がありますか?
あなたの答え:
  • 「SQL += customer_id;」ここでは何をしていますか?
あなたの答え:

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

1 or 1 = 1

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


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

下図8.10の赤枠で囲った「1 = 1」の部分が常にtrueと評価されるために全てのレコードという意味になるのですね。(SELECT * FROM customers where id = true と同じですので試してください )

このようにSQL文の組み立てを文字列連結によっているのは悪い書き方です。

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

図8.10 SQLインジェクション

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

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

また、この研修では扱いませんが、Webアプリケーションフレームワークを使って開発したり、WAF【Web Application Firewall】の仕組みを導入することによりSQLインジェクション対策を施すことも有効です。

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

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

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

プレースホルダとは以下のサンプルコードにある「?」です。この「?」は以下のコードによりキーボードから入力された値に置き換わるのです。

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

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

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

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

以下のPlaceholder.javaはcustomer_idで検索できる先ほど同様のプログラムです。ただし、プレースホルダでSQLインジェクション対策をしてありますので実行して確かめてください。

package model.dao;

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

public class Placeholder extends SuperDao {

	public static final String SQL = "SELECT * FROM customers where customer_id = ?";

	PreparedStatement ps = null;
	ResultSet rs = null;

	public void getACustomer() {

		try {
			connect();
			System.out.println("customer_idを整数値で指定してください");

			int id = 0;
			try (Scanner sc = new Scanner(System.in)) {
				String customer_id = sc.nextLine();
				id = Integer.parseInt(customer_id);
			}

			ps = con.prepareStatement(SQL);
			ps.setInt(1, id);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getInt("customer_id") + ":");
				System.out.print(rs.getString("name") + ":");
				System.out.print(rs.getString("mail") + ":");
				System.out.print(rs.getString("mobile") + ":");
				System.out.println(rs.getString("pass"));
			}
		} catch (SQLException e) {
			System.err.println("データベースへの接続時に問題が発生しました。");
			System.err.println(e);
		} finally {
			this.close();
		}
	}

	public static void main(String[] args) {
		Placeholder placeholder = new Placeholder();
		placeholder.getACustomer();
	}
}

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

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

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

なお、PreparedStatementのプレースホルダ(?)は、値の置き換えにのみ使用されるため、テーブル名やカラム名、SQLのキーワード(例:DESC)などのSQL文の構造部分を動的に変更するのには適していません。この点は次に触れます。

例題4

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

4.5 任意のフィールドで昇順または降順に並べ替える

  • carsテーブルからpriceの降順に全ての車のレコードを取得するSQL文を以下に記述してから、MySQLWorkBenchで実行しなさい。
あなたの答え:

詳細はコメントを読んで講師の解説をお聞きください。また、メインメソッドからこのメソッドを実行してみてください。

//	任意のフィールドで昇順または降順に並べ替えるメソッド
	public CarsBean getSortedCarsBean(String sortField, boolean isAscending) {
		CarsBean carsBean = new CarsBean();

		connect();

		// sortFieldが有効なフィールド名であるか確認
		if (!isValidSortField(sortField)) {
			throw new IllegalArgumentException("Invalid sort field: " + sortField);
		}

		String sortOrder = isAscending ? "ASC" : "DESC";
		String sql = "SELECT * FROM cars ORDER BY " + sortField + " " + sortOrder;

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// クエリを実行し、結果をResultSetに格納
			ResultSet rs = ps.executeQuery();

			// ResultSetを繰り返し処理し、各車のデータを取得
			while (rs.next()) {
				// CarBeanオブジェクトのインスタンスを生成
				CarBean car = new CarBean();

				// CarBeanにcar_id, name, priceを設定
				car.setCarId(rs.getInt("car_id"));
				car.setName(rs.getString("name"));
				car.setPrice(rs.getInt("price"));
				car.setDeletedAt(rs.getString("deleted_at"));

				// 取得した車のデータをCarsBeanに追加
				carsBean.addCar(car);
			}
		} catch (SQLException e) {
			// SQL例外が発生した場合の処理
			// エラーメッセージをコンソールに出力
			System.err.println(e);
		}
		close();

		// 取得したCarsBeanを返す
		return carsBean;
	}

	// 指定されたフィールド名が有効かどうかをチェックするメソッド
	private boolean isValidSortField(String field) {
		// 許可されたフィールド名の配列
		String[] validFields = { "car_id", "name", "price", "deleted_at" };

		// 与えられたフィールド名が配列内に存在するかどうかを確認
		for (String validField : validFields) {
			if (validField.equals(field)) {
				return true;
			}
		}
		return false;
	}

  • 「throw new IllegalArgumentException("Invalid sort field: " + sortField)」これは何を投げているのでしたか?
あなたの答え:
  • 「isAscending ? "ASC" : "DESC"」 こういう文をなんと呼ぶのでしたか?
あなたの答え:
  • 適切なメインメソッドを加えて実行してみて下さい。
あなたの答え:

4.6 あいまい検索を使ってレコードを取得する

  • carsテーブルからあいまい検索でnameに"車"を含む全てのレコードを取得するSQL文を以下に記述してから、MySQLWorkBenchで実行しなさい。
あなたの答え:

詳細はコメントを読んで講師の解説をお聞きください。また、メインメソッドからこのメソッドを実行してみてください。

	// キーワードに基づいて車を検索し、その結果をCarsBeanに格納して返すメソッド
	public CarsBean searchCarsBean(String keyword) {

		// CarsBeanの新しいインスタンスを生成
		CarsBean carsBean = new CarsBean();

		// データベースへの接続を行う
		connect();

		// 指定されたキーワードに基づいて車の情報を検索するSQLクエリ
		String sql = "select * from cars where name like ?";

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// クエリのパラメータ(nameの検索キーワード)をあいまい検索で設定する
			ps.setString(1, "%" + keyword + "%");

			// クエリを実行し、結果をResultSetに格納する
			ResultSet rs = ps.executeQuery();

			// 結果セットの各行を繰り返し処理し、車の情報を取得する
			while (rs.next()) {
				// CarBeanの新しいインスタンスを生成
				CarBean car = new CarBean();

				// ResultSetから車の情報を取得し、CarBeanにセットする
				car.setCarId(rs.getInt("car_id"));
				car.setName(rs.getString("name"));
				car.setPrice(rs.getInt("price"));
				car.setDeletedAt(rs.getString("deleted_at"));

				// 取得した車の情報をCarsBeanに追加する
				carsBean.addCar(car);
			}
		} catch (SQLException e) {
			// SQL例外が発生した場合、エラーをコンソールに出力する
			System.err.println(e);
		}
		close();

		// 検索結果を含むCarsBeanを返す
		return carsBean;
	}
  • 「"%" + keyword + "%"」なぜ、これであいまい検索になるのでしたか?
あなたの答え:

5. SELECT文以外の操作

5.1 INSERT文

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

  • carsテーブルにname=電気自動車 price=5000000というレコードを挿入するSQL文を以下に記述してから、MySQLWorkBenchで実行しなさい。
あなたの答え:

以下のaddACarメソッドを読み込んで質問に答えてください。

	// 新しい車のデータをデータベースに追加するメソッド
	public int addACar(CarBean aCar) {

		// 返り値として使用する整数型変数を初期化
		int ret = 0;

		// データベースへの接続を行う
		this.connect();

		// 新しい車を追加するためのSQL文を定義
		String sql = "INSERT INTO cars(name, price) VALUES( ?, ?);";

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// PreparedStatementに車の名前と価格をセット
			ps.setString(1, aCar.getName());
			ps.setInt(2, aCar.getPrice());

			// SQL文を実行し、処理された行数をretに代入
			ret = ps.executeUpdate();

		} catch (SQLException e) {
			// SQL処理中に例外が発生した場合のエラーハンドリング
			System.err.println(e);
		}

		close();

		// 処理された行数を返す
		return ret;
	}
  • レコードのInsertにResultSetオブジェクトは必要ですか?
あなたの答え:
  • このSQL文を解説しなさい。
あなたの答え:
  • 「ps.executeUpdate()」の戻り値を答えなさい。
あなたの答え:
  • 上記のINSERT文ではcar_idを指定していません。なぜ、これで良いのですか?MySQLの知識で答えてください。
あなたの答え:

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

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

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

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

例題5

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

※必ずMySQLで一度実行してからJavaで実行すること

5.2 UPDATE文

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

  • carsテーブルのcar_idが3のレコードのdeleted_atを今年の4月1日0時0分0秒に更新するSQL文を以下に記述してから、MySQLWorkBenchで実行しなさい。
あなたの答え:

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

//	データベース内の特定の車の情報を更新するためのメソッド	
	public int updateACar(CarBean aCar) {
		// 更新に成功したレコードの数を保持する変数を初期化
		int ret = 0;
		// データベースに接続
		this.connect();
		// 車の情報を更新するSQL文を定義
		String sql = "UPDATE cars SET name = ?, price = ?, deleted_at = ? WHERE car_id = ?";

		try (PreparedStatement ps = con.prepareStatement(sql)) {

			// SQL文のパラメータを設定
			ps.setString(1, aCar.getName()); // 車の名前
			ps.setInt(2, aCar.getPrice()); // 価格
			ps.setString(3, aCar.getDeletedAt()); // 削除日時
			ps.setInt(4, aCar.getCarId()); // 車のID

			// SQL文を実行し、影響を受けたレコードの数を取得
			ret = ps.executeUpdate();

		} catch (SQLException e) {
			// SQL例外が発生した場合、エラーを出力
			System.err.println(e);
		}

		close();

		// 更新に成功したレコードの数を返す
		return ret;
	}
  • このプログラムの処理内容は?
あなたの答え:

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

例題6

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

5.3 DELETE文

MySQLの時にお話ししたようにDELETE文はほとんど活躍の場がありませんのでサンプルコードも紹介しません。

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の実行メソッドのまとめ

例題8

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

テスト用のメインメソッドから動作を確認すること。

また、余裕があれば、他のメソッド(一人の顧客情報を表示するメソッド、データの挿入・更新メソッド)も実装しなさい。

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

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

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

login_user1
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:/Login

属性:なし

操作

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

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

ただし、superDAO、member-only2.jspとlogin-error.jsp、video.jspは再利用しているため掲載を割愛します。

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css"
	rel="stylesheet"
	integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC"
	crossorigin="anonymous">
<title>ログイン画面</title>
</head>

<body>
	<div class="container">
		<div class="row justify-content-center align-items-center"
			style="height: 100vh;">
			<div class="col-4">
				<form action="${pageContext.request.contextPath}/Login"
					method="post" class="form-signin">
					<div class="form-group mb-3">
						<label for="userId">ユーザーID:</label> <input type="text" id="userId"
							class="form-control" name="id" required>
					</div>
					<div class="form-group mb-3">
						<label for="password">パスワード:</label> <input type="password"
							id="password" class="form-control" name="pass" required>
					</div>
					<div class="form-group mb-3">
						<input type="submit" class="btn btn-primary" value="ログイン">
					</div>
				</form>
			</div>
		</div>
	</div>
	<script
		src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"
		integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM"
		crossorigin="anonymous"></script>
</body>
</html>
package p08.dao;

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

public class LoginDAO extends SuperDAO {

	private final String SQL 
    = "SELECT * FROM login_user where login_id = ? and password = ?";
	private PreparedStatement ps = null;

	public boolean login(String id, String password) {

		try {
			this.connect();

			ps = con.prepareStatement(SQL);

			ps.setString(1, id);
			ps.setString(2, password);

			ResultSet rs = ps.executeQuery();

			int cnt = 0;

			while (rs.next()) {
				cnt++;
			}

			if (cnt > 0) {
				return true;
			}
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			this.close();
		}
		return false;
	}
}
  • 上記LoginDaoにmainメソッドを追加して「id:imai password:p」でログインできること、それ以外ではログインできないことを確かめなさい。
確かめた結果:
package controller;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import model.dao.LoginDao;

@WebServlet(urlPatterns = { "/Login" })
public class LoginServlet extends HttpServlet {

	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String id = request.getParameter("id");
		String pass = request.getParameter("pass");

		HttpSession session = request.getSession();

		session.setAttribute("id", id);
		session.setAttribute("message", "こんにちは" + id + "さん。");

		LoginDao ld = new LoginDao();

		if (ld.login(id, pass)) {
			request.getRequestDispatcher("/view/member-only2.jsp").forward(request, response);
		} else {
			response.sendRedirect("view/login-error.jsp");
		}
	}

例題9

講師から受け取ったサンプルプロジェクトのトップページに以下の変更を加えなさい。

1.データベースに登録されている車の台数を表示する

2.各車の購入ボタンを押すと、その車の情報だけが記述されたページに遷移する


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

□ JDBC がデータベースの違いを吸収するため、データベースの変更に強くなる

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

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

□ いきなりDAOクラスから書き始めない。MySQLでSQL文をテストしてからJavaプログラムに組み込む。

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

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

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

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

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

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

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

「JDBCでデータベースのデータを活用する」 最後までお読みいただきありがとうございます。