初めに
検索条件が複数あり、いずれの項目も必須でない場合、
項目が入力されているかどうかをチェックし、それに合わせてSQL 文の WHERE 句の内容を
ツギハギすることがよくあると思う。
【例】
String sql = "SELECT * FROM Person";
if (id != null) {
sql = sql + "WHERE id=" + id;
}
でも、これはめんどいし、きれいじゃないし、PreparedStatementの良さが発揮しづらいし。。。
で、こんな時に、PreparedStatementを使う方法をメモしておく。
案１'：? IS NULLを追加する
WHERE (id = ? OR ? IS NULL) AND (name = ? OR ? IS NULL)
→欠点：同じ検索条件を2回入れないといけない。
案２：NULLIFとCOALESCEを使用する
WHERE NULLIF(id, '') = COALESCE(?, NULLIF(id, '')) AND NULLIF(name, '') = COALESCE(?, NULLIF(name, ''))
ってすると、null をセットした項目は、検索条件として使われないかのようになる
（「?」は、それぞれの検索条件）
→欠点：「空文字」を検索できない(詳細は以下の【考察】を参照のこと)
補足
* NULLIF(値1, 値2) :
+ 値1が値2と等しい場合は、NULL を返す
+ 値1が値2と等しくない場合は、値1 を返す
* COALESCE(値1, 値2) : 最初の null でないデータを返す
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/psql_k15.htm
考察
NULLIF(id, '') = COALESCE(?, NULLIF(id, ''))
~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
について考える。
まず、波線部分の「NULLIF(id, '')」は、以下のようになる。
+ idの項目が空文字だったら、NULLを返す。
+ idの項目がNULLだったら、結局、NULLを返す。
+ idの項目が値（例えば「id='X000001'」）があったら、その値（'X000001')を返す。
で、「COALESCE(?, NULLIF(id, ''))」は、以下のようになる。
+ 検索条件（？部分）が空文字の場合、COALESCE(【空文字】, 【NULL】)で「空文字」になる
→最終的に「NULL = 空文字」となり、成立しない（★空文字は検索できない★）
+ 検索条件（？部分）がNULLの場合、、COALESCE(【NULL】, 【NULL】)で「NULL」になる
→最終的に「NULL = NULL」となり、成立（OK）
+ 検索条件（？部分）が何らかの値の場合、、COALESCE(値1, 値2)で「値1」になる
→最終的に「値1 = 値2」となり、検索条件があえば検索され、条件が合わなければ検索に引っかからない（OK）
使用するテーブル
CREATE TABLE person
(
id character(8) NOT NULL,
name character varying(100),
sex character(1),
updatedate timestamp without time zone,
CONSTRAINT person_pkey PRIMARY KEY (id)
)
解決法１のサンプル
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SamplePreparedStatement {
public static void main(String[] args) {
try {
System.out.println("Start");
SamplePreparedStatement.dbAccess("X0000010", null, null);
SamplePreparedStatement.dbAccess(null, null, "f");
} catch (Exception e) {
e.printStackTrace();
} finally {
System.out.println("Finished");
}
}
private static void dbAccess(String id, String name, String sex) throws Exception {
try {
Class.forName("org.postgresql.Driver");
try (Connection connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/Sample", "user", "password");
PreparedStatement statement = connection
.prepareStatement(
"SELECT * FROM Person"
+ " WHERE (id = ? AND name = ? AND sex = ?) IS NOT FALSE");) { // ★差異はここだけ★
statement.setString(1, id);
statement.setString(2, name);
statement.setString(3, sex);
try (ResultSet resultSet = statement.executeQuery();) {
while (resultSet.next()) {
System.out.println("=============");
System.out.println(resultSet.getString("id"));
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("sex"));
System.out.println(resultSet.getString("updateDate"));
System.out.println("=============");
}
} catch (Exception ex) {
throw ex;
}
} catch (Exception ex) {
throw ex;
}
} catch (Exception ex) {
throw ex;
}
}
}
解決法２のサンプル
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SamplePreparedStatement {
public static void main(String[] args) {
try {
System.out.println("Start");
SamplePreparedStatement.dbAccess("X0000010", null, null);
} catch (Exception e) {
e.printStackTrace();
} finally {
System.out.println("Finished");
}
}
private static void dbAccess(String id, String name, String sex) throws Exception {
try {
Class.forName("org.postgresql.Driver");
try (Connection connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/Sample", "user", "password");
PreparedStatement statement = connection
.prepareStatement(
"SELECT * FROM Person"
+ " WHERE NULLIF(id, '') = COALESCE(?, NULLIF(id, ''))" // ★差異はここ★
+ " AND NULLIF(name, '') = COALESCE(?, NULLIF(name, ''))" // ★差異はここ★
+ " AND NULLIF(sex, '') = COALESCE(?, NULLIF(sex, ''))");) { // ★差異はここ★
statement.setString(1, id);
statement.setString(2, name);
statement.setString(3, sex);
try (ResultSet resultSet = statement.executeQuery();) {
while (resultSet.next()) {
System.out.println("=============");
System.out.println(resultSet.getString("id"));
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("sex"));
System.out.println(resultSet.getString("updateDate"));
System.out.println("=============");
}
} catch (Exception ex) {
throw ex;
}
} catch (Exception ex) {
throw ex;
}
} catch (Exception ex) {
throw ex;
}
}
}
