SQLiteDatabase の query メソッドによる検索で、個々の条件をオプションにする書き方

Android 標準で使える SQLite にアクセスするには SQLiteDatabase クラスを使います。

そして、SQLiteDatabase クラスの query メソッドなどで SELECT 文を発行する場合、WHERE 句に ? パラメータを使うことができます。

query メソッドの定義を以下に示します。

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

selection 引数が WHERE 句です。そして、selectionArgs 引数に ? パラメータに埋め込む値を配列で渡します。

例えば 10 歳から 20 歳までの人の名前を検索する getNames メソッドというのを書いてみましょう。

query メソッドの呼び出しに注目してください。

public class HumanDao {
	public List<String> getNames(SQLiteDatabase database, int minAge, int maxAge) throws Exception {
		Cursor cursor = database.query(
			"human",
			new String[]{"name"},
			"age <= ? AND age < ?",
			new String[]{Integer.toString(minAge), Integer.toString(maxAge)},
			null, null, null, null);

		List<String> result = new ArrayList<>();
		while (cursor.moveNext()) {
			result.Add(cursor.getString("name"));
		}

		return result;
	}
}

さて、以上の処理を進化させて、年齢の上限と下限の条件を必須ではなくオプションにしたいと思います。

ここでいうオプションとは、指定があれば条件に含めるし、無ければ条件から外すということを表しています。

getNames メソッドでは上限・下限に int 型を使っていまいたが、条件なしを null で表せるように Integer 型に変更します。

その上でオプションをどうやって表すか、一つの方法は値の有無に応じて selection / selectionArgs 引数の値を組み立てることです。

public class HumanDao {
	public List<String> getNames2(SQLiteDatabase database, Integer minAge, Integer maxAge) throws Exception {
		Cursor cursor = database.query(
			"human",
			new String[]{"name"},
			"age <= ? AND age < ?",
			getSelectionForGetNames(minAge, maxAge),
			getSelectionArgsForGetNames(minAge, maxAge),
			new String[]{Integer.toString(minAge), Integer.toString(maxAge)},
			null, null, null, null);

		List<String> result = new ArrayList<>();
		while (cursor.moveNext()) {
			result.Add(cursor.getString("name"));
		}

		return result;
	}

	private String[] getSelectionForGetNames(Integer minAge, Integer maxAge) {
		if (minAge == null && maxAge == null) {
			null;
		}
		else if (minAge != null && maxAge == null) {
			return "age < ?";
		}
		else if (minAge == null && maxAge != null) {
			return "? <= age";
		}
		else if (minAge != null && maxAge != null) {
			return "? <= age AND age < ?;
		}
	}

	private String[] getSelectionArgsForGetNames(Integer minAge, Integer maxAge) {
		if (minAge == null && maxAge == null) {
			return null;
		}
		else if (minAge != null && maxAge == null) {
			return new String[]{Integer.toString(maxAge)};
		}
		else if (minAge == null && maxAge != null) {
			return new String[]{Integer.toString(minAge)};
		}
		else if (minAge != null && maxAge != null) {
			return new String[]{Integer.toString(minAge), Integer.toString(maxAge)};
		}
	}
}

とても長いですね。条件をオプションにするというのは良くあることなのでもっとスマートに書きたいと思います。

以下がそれです。

public class HumanDao {
	public List<String> getNames3(SQLiteDatabase database, int minAge, int maxAge) throws Exception {
		Cursor cursor = database.query(
			"human",
			new String[]{"name"},
			"(? = '1' OR age <= ?) AND (? = '1' OR age < ?)",
			new String[]{isNull(minAge), format(minAge), isNull(maxAge), format(maxAge)},
			null, null, null, null);

		List<String> result = new ArrayList<>();
		while (cursor.moveNext()) {
			result.Add(cursor.getString("name"));
		}

		return result;
	}

	public String isNull(Object value) {
		return value == null ? "1" : "0";
	}

	public String format(Integer value) {
		return value != null ? Integer.toString(value) : "dummy";
	}
}

isNull と format の2つのメソッドはヘルパーメソッドです。

ポイントは、

  • selection 引数に (? = '1' OR ? を使ったオプション条件) という形で WHERE 句を書くこと。
  • selectionArgs 引数に isNull(param), format(param) という形でパラメータを書くこと。
  • null 判定は IS NULL ではなく null だったら '1' を渡すようにして判定すること。
  • format では値がなかった場合でも null ではなくダミーの文字列を返すこと。

です。

null だったらオプション条件を無条件で満たすようにするというアイデアです。

また、SQLiteDatabase クラスの特殊な事情として各種メソッドの selectionArgs 引数には null を指定できないという特殊事情も考慮してあります。

SQLiteDatabase クラスの selectionArgs 引数に null の値を1つでも入れると以下のようなエラーメッセージで怒られます。

Bind Value At Index 1 is Null

いろいろ調べたのですが、これは避けようのない問題らしいです。

当初は selection 引数の WHERE 句を (? IS NULL OR ? を使ったオプション条件) で組み立てようとしたのですが null を渡すことができないのであきらめました。

変わりに isNull ヘルパーメソッドを定義し '1' で null を表現することにしました。

また、? を使ったオプション条件の部分にも当然 null を渡せないため、本当はあまりやりたくないですが "dummy" の文字列を渡すことにしました。

null であれば当然 ? = '1' が満たされるため "dummy" でもなんでも影響はないという割り切りをした結果です。

ちなみに null が渡せないのは SQLiteDatabase クラスの実装がそうなっているからであって SQLite が null を扱えないわけではありません。

どっかの英語サイトでこれは重大な欠陥だという書き込みを見ましたが、例えそうであったとしても現状がそうなってしまっているし、直ったとしても旧い Android をサポートするために考慮がいらなくなることはないと思います。