【Java】フォームの検索項目の入力有無によってSQL文を生成してデータベースから情報取得したい

Question

前提・実現したいこと

jspのフォーム画面の検索項目３つ(氏名、誕生月、職種)に入力された値をサーブレットで取得し、SQL文のwhere句以降に連結してmysqlデータベースのテーブル(usertable)から検索結果を取得したいです。

条件：
① JDBCのPreparedStatementを利用する
② AND検索をする
③ 検索項目が空欄の場合は、その項目は絞り込み対象外とする。
④ 全て空欄の場合、全レコードを取得する

上記条件③のせいで、

「SQL文の生成方法」
「SQL文の"?"に割り当てるパラメータインデックス値の決め方」

について分からなくて悩んでいます。

該当のソースコードの一部

　　　　　Connection con = null;
        PreparedStatement st = null;
        try{
            con = 省略;
            
            String sql = "SELECT * FROM usertable";
            boolean flag = false;
　　　　//名前が入力されていればwhere句で連結、されなければスキップ
            if (!user.getName().equals("")) {
                sql += " WHERE name like ?";
                flag = true;
            }
　　　　//誕生月が入力されていれば処理を実施
            if (user.getBirthday()!=null) {
　　　　　//名前が入力されていなかった場合はWHERE、されていたらAND
                if(!flag){
                    sql += " WHERE birthday like ?"; //名前が空欄の時はこの?が1番目
                    flag = true;
                }else{
                    sql += " AND birthday like ?"; //名前が入力されればこの?は2番目
                }
            }
　　　　//職業が入力されていれば処理を実施
            if (user.getType()!=0) {
　　　　　//名前も誕生月も空欄だった場合はwhere、どちらかもしくは両方入力されていたらAND
                if(!flag){
                    sql += " WHERE type like ?";
                }else{
                    sql += " AND type like ?";
                }
            }
            st =  con.prepareStatement(sql);
            st.setString(★1, "%"+user.getName()+"%");
            st.setString(★2, "%"+ new SimpleDateFormat("yyyy").format(user.getBirthday())+"%");
            st.setInt(★3, user.getType());

分からないこと

①　入力項目の有無によってSQL文を連結していく方法はこれで良いのでしょうか？ ifがネストになっている点がとても拙く感じるのですが・・・。

②　★で示す、?に対応するパラメータインデックス値はどのように決めればよいでしょうか。
具体的に悩む点は、名前だけ空欄の場合、名前も誕生月も空欄の場合、誕生月だけ空欄の場合などどこが空欄のまま検索されるかで?の個数も変わるので、例えば名前だけ空欄にするとインデックス範囲は2までだと怒られます。

java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).

?の個数が選択次第で変わる状態でどうパラメータインデックス値を当て込めばいいかがわかりません。

宜しくお願い致します。

Comments

[m.ts10806]

birthdayの検索条件をフォーマットでyyyy固定にされてますけど、年しか入力されないのですか？

[m.ts10806]

あとdate型はどうだったか失念しましたが、int型はlikeで検索できなかったような・・。

[RogerFederer]

本質問を確認いただきありがとうございます。「年」しか入力しません。　調べて確認してみたらint型はlikeで検索できないのですね・・・。そこの前でエラーになっていたため気づけませんでした。

[m.ts10806]

いずれにしても%使わないならlikeではなく=で良いと思います。

Answer 1

動的SQL(ダイナミックSQL)を作成したいんですよね。ORマッパーを使用した方がよい気がしますが。

1, boolean flag = false;を削除して以下のような形にすると、全部andで結合できるかと。

StringBuilder sql = new StringBuilder("SELECT * FROM usertable WHERE 1=1 ");
if (!user.getName().equals("")) {
    sql.append(" AND name like ?");
}

2, bindの部分はSet parameters dynamically to prepared Statement in JDBCを参考にしてくださいな。

---

以下は質問文のコードをみて気づいた点です。

1, Stringで文字列連結(+=)するのは避けて、StringBuilderを使ってくださいな。
参考:[Java] Stringの結合について
2,SELECT文にORDER BY句が存在しないため、SELECTの結果順序は保証されていません。
ORDER BY句を付けるようにしてくださいな。
3, Connectionオブジェクトはtry～with～Resources文を使ってclose忘れを防いでくださいな。

Answer 2

かなり冗長に感じます。項目が増えたらその分、コードもかなり追記しなければなりません。
WHEREをつけるのは「1つ以上検索条件が入力されたとき」ですよね？
検索条件を配列に追記していって、その配列の要素個数によってWHEREつけたり、prepareStatementの個数を決めては？
確かjoinもあったはずなので条件の結合も容易かなと。

さっと書けるのがPHPなので、流れの参考程度にしかならないと思いますが。

$where = []; //検索条件の配列
$stValues = []; //プリペアドステートメントにセットするValue
if(!empty($username)){
  $where[] = " name like ? ";
  $valueinfo["value"] = $username;
  $valueinfo["type"] = "string";
  $stValues[] = $valueinfo;
}
if(!empty($birthday)){
  $where[] = " birthday like ? ";
  $valueinfo["value"] = $birthday;
  $valueinfo["type"] = "yeardate";
  $stValues[] = $valueinfo;
}

//// 省略

$sql = "SELECT * FROM usertable";
if(count($where) > 0){ //検索条件があれば配列をandで結合
  $sql .= " where ".implode(" and ",$where);
}


$stmt = $dbh->prepare($sql);
for($i=0;$i<count($stValues);$i++){ //パラメータをそれぞれの型で判定してバインド
  $paramset = $stValues[$i];
  $setType = PDO::PARAM_STR; //カラムの型
  switch($paramset["type"]){
     case "string":
      $setvalue = "%".$paramset["value"]."%";
      break;
     case "yeardate":
      $setvalue = $paramset["value"]."%";
      break;
     case "integer":
      $setvalue = $paramset["value"];
      $setType = PDO::PARAM_INT;
      break;
  }
  $stmt->bindParam(($i+1), $setvalue,$setType);
}

$stmt->execute();

Javaとかはセットする型とかそのあたりで呼び出すメソッドがかわるのでそこは読み替えていただくとしておおよそこのような流れかなと。
型やイレギュラーな検索が増えれば追記していく形にはなりますが。

userがBeanっぽいので、そこにカラムの型まで持っているのでしたらそちらを引っ張ってくればそこでSetTypeが判定できますね。

追記。

コードコピペして思ったんですが、コメント対象じゃないところに全角空白がたくさんありますね。
Connectionの前とか//の前とか。
これではそもそも問題とは別の箇所で動かないコードを提示していることになります。

Answer 3

PreparedStatementと項目が動的に変化するSQLを記述するときに、かつて以下のようなやり方をするのがよく使われていました。（今どきはO/Rマッピングフレームワークがあるので、こういった記述は廃れましたが…。）

String sql = "SELECT * FROM usertable WHERE 1=1 ";

List<String> parameters = new ArrayList<>();

if (!user.getName().equals("")) {
  sql += "AND name like ? ";
  parameters.add("name");
}

if (user.getBirthday()!=null) {
  sql += "AND birthday like ? ";
  parameters.add("birthday");
}

...(中略)...

PreparedStatement statement = connection.preparedStatement(sql);
int columnIndex = 0;
if (parameters.contains("name")) {
  statement.setString(++columnIndex, "%"+user.getName()+"%");
}
if (parameters.contains("birthday")) {
  statement.setString(++columnIndex, "%"+ new SimpleDateFormat("yyyy").format(user.getBirthday())+"%");
}

...(中略)...

ResultSet resultSet = statement.executeQuery();

parametersに格納するときは定数にするか、列挙型使っておくのが安全かもしれませんが、元コードに準拠しました。
Java8以降なら、SQLの組み立て部分でもう少し簡潔にかけるかと思います。