PreparedStatementでの複数検索

質問

PreparedStatementでの複数検索

環境：struts1.3,postgresql8.4,JDK1.6
テキストボックスなどから1つから6つの条件でsqlを検索したいのですが
この場合、1つでもnullがある時は検索されません。

Connection con = null;
con = ProductDAO.getConnection();
//SQLの実行
String selectStatement =
"SELECT * FROM test,test_list WHERE test.id = test_list.id " +
"AND prefecture=? " +
"AND low_price>=? " +
"AND low_age>=? " +
"AND sameday_select =? " +
"AND terms_select =?" +
"AND category=?";

PreparedStatement prepStmt =
con.prepareStatement(selectStatement);

prepStmt.setString(1, aForm.getPrefecture());
prepStmt.setInt(2, aForm.getLow_price());
prepStmt.setInt(3, aForm.getLow_age());
prepStmt.setString(4, aForm.getSameday_select());
prepStmt.setString(5, aForm.getTerms_select());
prepStmt.setString(6, aForm.getCategory());

ResultSet rs = prepStmt.executeQuery();

(3) SQLの実行結果の処理
while (rs.next()) {
　　処理
}

この場合、if文などでaForm.getPrefecture()がnullの場合などで1つ1つ条件を付けないと駄目でしょうか？その場合、if文が膨大な数になってしまうため何か良い方法はありますか。
あるならば、具体的に教えて下さい。

回答No.3

yamada59 · ベストアンサー

回答番号: No.2 の続きです。

aForm.getPrefecture() などの戻り値が null ではなく空文字 ("") の場合には、coalesce 関数の 1 つ目に渡している条件式の結果が NULL になりません。空文字の場合には NULL になるように nullif 関数をかませるとうまくいくと思います。

String selectStatement =
"SELECT * FROM test,test_list WHERE test.id = test_list.id " +
"AND coalesce(prefecture=nullif(?, ''), true) " +
"AND coalesce(low_price>=nullif(?, ''), true) " +
"AND coalesce(low_age>=nullif(?, ''), true) " +
"AND coalesce(sameday_select =nullif(?, ''), true) " +
"AND coalesce(terms_select =nullif(?, ''), true)" +
"AND coalesce(category=nullif(?, ''), true)";

値が指定されていない場合に aForm.getLow_price() などの int 型の戻り値がどうなるかが分からないのですが、0 を返すなら nullif(?, 0) などと書き換えてあげる必要があるかもしれません。

お礼

ありがとうございます。
無事if文を使わずに複数の検索ができました。
int 型もnullif(?, 0)にした場合、無事できました。

回答No.2

yamada59

NULLに対する演算はNULLになるので、以下のSQLを書き換えてみてはいかがでしょうか。

String selectStatement =
"SELECT * FROM test,test_list WHERE test.id = test_list.id " +
"AND coalesce(prefecture=?, true) " +
"AND coalesce(low_price>=?, true) " +
"AND coalesce(low_age>=?, true) " +
"AND coalesce(sameday_select =?, true) " +
"AND coalesce(terms_select =?, true)" +
"AND coalesce(category=?, true)";

coalesce関数は引数の中から最初のNULLでない値を返すので、NULL以外が渡された場合には条件式を評価してtrueかfalseとなり、NULLが渡された場合には2つ目の引数が返されtrueとなって条件式が無視されます。

補足

回答ありがとうございます。

"AND coalesce(prefecture=?, true) " +
"AND coalesce(low_price>=?, true) " +
"AND coalesce(low_age>=?, true) " +
"AND coalesce(sameday_select =?, true) " +
"AND coalesce(terms_select =?, true)" +
"AND coalesce(category=?, true)";

上記の方法で試してみましたが、
すべての値を選択する場合は、検索できましたが、
やはりどれか一つでも値が選択されていない場合は、検索されませんでした。

logを見ると選択されていない物は、NULLではなく、=""　の状態です。

回答No.1

root139

if文でnull判定する以外に簡単な方法は思いつきませんね。
StringBuilder や PreparedStatement#setObject を使えば多少簡潔に出来るとは思いますが。

例 ----------------------------------------------------
StringBuilder sqlBuilder =
new StringBuilder("SELECT * FROM test,test_list WHERE test.id = test_list.id ");
List<Object> sqlParams = new ArrayList<Object>();
if (aForm.getPrefecture() != null) {
sqlBuilder.append("AND prefecture = ? ");
sqlParams.add(aForm.getPrefecture());
}
if (aForm.getLow_price() != null) {
sqlBuilder.append("AND low_price >= ? ");
sqlParams.add(aForm.getLow_price());
}
if (aForm.getLow_price() != null) {
sqlBuilder.append("AND low_age >= ? ");
sqlParams.add(aForm.getLow_age());
}
if (aForm.getSameday_select() != null) {
sqlBuilder.append("AND sameday_select = ? ");
sqlParams.add(aForm.getSameday_select());
}
if (aForm.getSameday_select() != null) {
sqlBuilder.append("AND terms_select = ? ");
sqlParams.add(aForm.getTerms_select());
}
if (aForm.getCategory() != null) {
sqlBuilder.append("AND category = ? ");
sqlParams.add(aForm.getCategory());
}

PreparedStatement prepStmt =
conn.prepareStatement(sqlBuilder.toString());

int paramIndex = 0;
for (Object param : sqlParams) {
prepStmt.setObject(++paramIndex, param);
}

ResultSet rs = prepStmt.executeQuery();
-------------------------------------------------------
(aForm の getLow_price, getLow_age はInteger型を返すとする)

補足

回答ありがとうございます。

例えば、PrefectureとSameday_selectなど複数で検索したい場合は、
さらにif文を記載するという方法ですよね。