Java学習録

【Java】フォーム入力項目によってSQL文を動的生成してDB検索する

Java初心者の学習記録

GEEK JOB(ギークジョブ)でJavaを学習中の忘備録。

JSPとJavaサーブレットでデータベース連携をするところの勉強しているのですが、

  1. JSP画面のフォームで名前・職業の2項目を入力する
  2. 入力された情報をもとにSQL文を生成しデータベースを検索(SELECT * FROM ~)する
  3. PreparedStatementを使うこと
  4. 名前・職業は、どちらかもしくは両方空欄でも検索できること(両方空欄の場合は全件検索)

という要件でコードを書く必要がありました。フォームは例えばこんな感じ↓です。

mysql, Where 1=1

僕が頭を悩ませたのは、「どちらか、もしくは両方空欄でも検索できる」要件です。

入力条件によってSELECT文を動的に生成しないといけないけどどうすれば・・・?もし、そんな状況で悩む方がいるとしたら、解決するためのキーワードは

WHERE 1=1

です。以下、まず私が直面した悩みの説明から入り、解決例のコードを記載してみます。

悩み:WHEREで始まる?ANDは要る?どっちも要らない?

まず、空欄でも検索可とするということは、全部で以下4パターンが考えられます。

  1. 名前だけ入力し、職業は空欄
  2. 名前は空欄で職業は入力
  3. 名前も職業も入力
  4. どちらも空欄

この入力パターンにしたがって、正しいSQL文を動的に生成するコードを書かないといけません。

もし、名前だけ入力があった場合、usersというテーブルから検索するSQLは以下。1項目だけであればWHEREで連結します。

名前が空欄で職業のみの場合、職業をWHEREで連結します。

では、名前も職業も入力された場合は以下。1項目めである名前はWHERE、2項目めである職業はANDで連結します。

そしてそして、名前も職業も空欄ならWHEREもANDも不要です。

つまり

  • 2項目めに入力される職業は、1項目めの名前が空欄ならWHEREで連結され、名前が入力されればANDで連結される
  • どちらも空欄ならWHEREなんてそもそも要らない

という感じで、WHEREの出現場所が定まらないのです。↓ココ↓までしかSQL文の共通部として変数定義できません。

ここまで変数で定義しておいて、あとはif文で「名前が入力されたなら名前をWHEREで連結、両方入力されたらWHEREとANDで連結、両方空欄ならWHEREも不要にして〜」という風に「Whereを入れるか入れないか(でそのあとにANDも来るか)」を判断させる冗長なコードが出来上がってしまいます。

しかし、冒頭で書いた「WHERE 1=1」という魔法のキーワードを使うことで「WHEREは要るのか要らないのか」を全く考えなくて良くなります(if文のネストが1段減る)

解決策:”WHERE 1=1″ ならANDで連結するだけ

SQL文の変数定義を

String sql = “SELECT * FROM users”

から

String sql =”SELECT * FROM users WHERE 1=1

に変更することで、以下10行目が”AND”で決め打ちすることができます。入力1項目めにも2項目めにもなりうる職業(job)は、WHEREで連結するのかANDで連結するのか分からなかったためもう1段if文を作る必要がありましたが、不要になりました。
※以下XXX.getName, XXX.getJobの部分は別途定めているJavaBeansで、名前・職業情報を取得するメソッドです

これは入力項目が2つしかない場合の例ですが、もちろん何項目あっても考え方は同じです。

また「全部の入力項目が空欄のままだったらどうなるの?」と一瞬考えるかもしれません。全部が空欄の場合、実行されるSQL文は

SELECT * FROM users WHERE 1=1;

となりますが、WHERE 1=1は「あってないようなもの」のように処理されるので実質的に

SELECT * FROM users;

と同じに扱われるため無事に全件検索が可能になります。

まとめ

フォームからの情報入力によってデータベースを検索する場合は、WHERE 1=1というマジックワードを変数定義で固定してしまい、if文で「入力があった場合はANDで連結する」という処理を書くだけでOKになります。