Как бороться с (возможно) нулевыми значениями в PreparedStatement?

Утверждение

SELECT * FROM tableA WHERE x = ?

и параметр вставляется через java.sql.PreparedStatement 'stmt'

stmt.setString(1, y); // y may be null

Если y равно null, оператор не возвращает строк в каждом случае, потому что x = null всегда false (должно быть x IS NULL). Одним из решений было бы

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL)

Но тогда мне нужно установить тот же параметр дважды. Есть ли лучшее решение?

Спасибо!

Ответы

Ответ 1

Я всегда делал это так, как вы показываете в своем вопросе. Установка одного и того же параметра в два раза не является таким огромным лишением, не так ли?

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);

Ответ 2

Существует довольно неизвестный оператор ANSI-SQL IS DISTINCT FROM, который обрабатывает значения NULL. Его можно использовать следующим образом:

SELECT * FROM tableA WHERE x NOT IS DISTINCT FROM ?

Таким образом, должен быть установлен только один параметр. К сожалению, это не поддерживается MS SQL Server (2008).

Другое решение может быть, если есть значение, которое есть и никогда не будет использоваться ('XXX'):

SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX')

Ответ 3

будет просто использовать два разных оператора:

Заявление 1:

SELECT * FROM tableA WHERE x is NULL

Заявление 2:

SELECT * FROM tableA WHERE x = ?

Вы можете проверить свою переменную и построить соответствующий оператор в зависимости от условия. Я думаю, что это делает код намного понятнее и понятнее.

ИЗМЕНИТЬ Кстати, почему бы не использовать хранимые процедуры? Затем вы можете обрабатывать всю эту логику NULL в SP, и вы можете упростить работу на внешнем вызове.

Ответ 4

Если вы используете, например, mysql, вы можете сделать что-то вроде:

select * from mytable where ifnull(mycolumn,'') = ?;

Тогда yo мог бы сделать:

stmt.setString(1, foo == null ? "" : foo);

Вам нужно будет проверить свой план объяснений, чтобы повысить эффективность вашей работы. Однако это означало бы, что пустая строка равна нулю, поэтому она не предоставляется, она будет соответствовать вашим потребностям.

Ответ 5

В Oracle 11g я делаю это так, потому что x = null технически оценивает UNKNOWN:

WHERE (x IS NULL AND ? IS NULL)
    OR NOT LNNVL(x = ?)

Выражение перед OR позаботится о приравнивании NULL к NULL, тогда выражение after позаботится обо всех других возможностях. LNNVL изменяет UNKNOWN на TRUE, TRUE на FALSE и FALSE на TRUE, что прямо противоположное тому, что мы хотим, следовательно, NOT.

В некоторых случаях принятое решение для меня не работало в Oracle, когда оно было частью большего выражения, включая NOT.