演習: JDBC と検索

JDBC と検索 で作った Spring Initializr のプロジェクトで取り組みます。各問題のコードは com.example パッケージの Main クラスの main メソッドに書き、main には throws SQLException を付けます。

• Directoryjdbc-practice/

  • Directorysrc/main/java/com/example/

    • …

8-A. 書籍の一覧を取得する

bookstore に接続し、books テーブルから id・title・price を価格の高い順に取得して、1 行ずつ出力してください。接続は try-with-resources で閉じます。URL は jdbc:mysql://127.0.0.1:3307/bookstore です。

出力例（先頭 3 行）:

58: SRE サイトリライアビリティエンジニアリング (5280円)
55: データ指向アプリケーションデザイン (5060円)
29: リファクタリング(第2版) (4840円)

ヒント

SQL は SELECT id, title, price FROM books ORDER BY price DESC です。Connection・PreparedStatement・ResultSet を 1 つの try-with-resources にまとめ、while (rs.next()) のループで rs.getInt("id")・rs.getString("title")・rs.getInt("price") を取り出して出力します。

8-B. 著者の一覧を取得する

同じ bookstore の authors テーブルから、id・name・country を id の昇順に取得して出力してください。country は著者の出身国です。

出力例（先頭 3 行）:

1: 夏目漱石 (日本)
2: 村上春樹 (日本)
3: 東野圭吾 (日本)

ヒント

SQL は SELECT id, name, country FROM authors ORDER BY id です。8-A と同じ構造で、テーブルと列名だけが変わります。name と country はどちらも文字列なので rs.getString で取り出します。

8-C. 在庫の合計を集計する

books テーブルの stock（在庫数）をすべて取得し、ResultSet のループの中で合計を計算して、最後に総在庫数を出力してください。

出力例:

総在庫数: 2098

ヒント

SQL は SELECT stock FROM books です。ループに入る前に合計用の変数 int total = 0; を宣言し、while (rs.next()) の中で total += rs.getInt("stock"); と足し込みます。ループを抜けたあとに total を出力します。

8-D. 価格で絞り込む

利用者が指定した金額以上の書籍だけを取得します。int minPrice = 4000; とし、プレースホルダを使って「価格が minPrice 円以上」の書籍のタイトルと価格を、価格の高い順に取得して出力してください。

出力例:

SRE サイトリライアビリティエンジニアリング: 5280円
データ指向アプリケーションデザイン: 5060円
リファクタリング(第2版): 4840円
Googleのソフトウェアエンジニアリング: 4840円
プログラミング言語Go: 4180円
Clean Code: 4104円

ヒント

SQL は SELECT title, price FROM books WHERE price >= ? ORDER BY price DESC です。? に値を設定するには、PreparedStatement の stmt.setInt(1, minPrice); を executeQuery の前に呼びます。price は整数なので setInt を使います。

8-E. タイトルで部分一致検索する

String keyword = "森"; とし、タイトルにこのキーワードを含む書籍を取得して出力してください。部分一致には SQL の LIKE を使います。

出力例:

ノルウェイの森 上
ノルウェイの森 下

ヒント

SQL は SELECT title FROM books WHERE title LIKE ? です。部分一致の LIKE には前後に % を付けた値を渡します。プレースホルダには stmt.setString(1, "%" + keyword + "%"); のように、% で挟んだ文字列を設定します。% を SQL 文の側ではなく設定する値の側に入れるのがポイントです。

8-F. 文字列連結をプレースホルダに直す

次のコードは、利用者が入力したタイトルで書籍を検索しますが、SQL を文字列連結で組み立てています。この書き方は SQL インジェクションの危険があります。プレースホルダを使う形に書き直してください。

Main.java

String input = "こころ";
String sql = "SELECT id, title FROM books WHERE title = '" + input + "'";

try (Connection conn = DriverManager.getConnection(url, "root", "root");
     PreparedStatement stmt = conn.prepareStatement(sql);
     ResultSet rs = stmt.executeQuery()) {
  while (rs.next()) {
    System.out.println(rs.getInt("id") + ": " + rs.getString("title"));
  }
}

書き直したあと、input に ' OR '1'='1 を入れても全件が返らないこと（一致する書籍がなければ 0 件）を確認してください。

ヒント

SQL の中の '" + input + "' を ? に変え、input は stmt.setString(1, input); で設定します。setString は executeQuery の前に呼ぶため、ResultSet は PreparedStatement の try の中で宣言します。本文 プレースホルダ と同じ構造です。

8-G. 別のデータベースに接続する

接続先を world データベースに変えます。URL を jdbc:mysql://127.0.0.1:3307/world にして接続し、city テーブルから Name（都市名）・Population（人口）を人口の多い順に取得して、先頭 10 件を出力してください。

出力例（先頭 3 行）:

Mumbai (Bombay): 10500000
Seoul: 9981619
São Paulo: 9968485

ヒント

URL の末尾の bookstore を world に変えるだけで、接続先のデータベースが切り替わります。SQL は SELECT Name, Population FROM city ORDER BY Population DESC LIMIT 10 です。Population は整数なので rs.getInt("Population") で取り出します。列名は大文字始まりなので、rs.getString("Name") のように正確に指定します。

8-H. 国コードで都市を絞り込む

world データベースで、String countryCode = "JPN"; とし、プレースホルダを使って、その国コードの都市を人口の多い順に取得して出力してください。JPN は日本の国コードです。

出力例（先頭 3 行）:

Tokyo: 7980230
Jokohama [Yokohama]: 3339594
Osaka: 2595674

ヒント

SQL は SELECT Name, Population FROM city WHERE CountryCode = ? ORDER BY Population DESC です。CountryCode は文字列なので、stmt.setString(1, countryCode); で ? に値を設定します。8-D の数値の絞り込みと同じ構造で、設定するメソッドが setString に変わります。