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

Заявление

SELECT * FROM tableA WHERE x = ?

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

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

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

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

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

Спасибо!


java null prepared-statement
person Zeemee    schedule 18.11.2010    source источник
comment
Я не использую заранее подготовленные заявления. Я использую строки SQL, а затем заменяю = NULL на Is Null в строке SQL. Работает как шарм.   -  person Erick Robertson    schedule 18.11.2010
comment
Это небезопасно и, возможно, уязвимо для инъекционных атак.   -  person aioobe    schedule 18.11.2010

Ответы (5)


arrow_upward
38
arrow_downward

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

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);
person Paul Tomblin    schedule 18.11.2010
comment
Спасибо, Пол за твою заметку. Итак, вы голосуете за «это лучшая практика»? - person Zeemee; 18.11.2010
comment
Я никогда не находил лучшего способа. - person Paul Tomblin; 18.11.2010
comment
Тем не менее, наличие двух отдельных SQL может быть предпочтительнее, если важна производительность, потому что путь доступа будет отличаться в зависимости от того, хотите ли вы IS NULL или нет. - person Thilo; 12.06.2012
comment
Еще один пример, когда простота ближе всего к элегантности - person dammkewl; 10.02.2017
comment
Конечно, это не большая проблема, особенно когда у вас есть x = ? AND y = ? AND z = ? и вам нужно все это переписать из-за чудесной идеи SQL, которая обрабатывает NULL, как ничто другое. - person doublep; 22.09.2020

arrow_upward
10
arrow_downward

Существует совершенно неизвестный оператор 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')
person Zeemee    schedule 28.03.2012
comment
Пробовал с MySQL + PHP PDO, и он также вернул false при попытке подготовить оператор :( - person Pere; 06.03.2015
comment
ОТЛИЧИТЕЛЬНО работает с PostgreSQL - person Alexander Farber; 27.09.2016
comment
В старых версиях Oracle для того же можно использовать NVL, но COALESCE лучше, если он доступен (см. stackoverflow.com/questions/950084/). - person Istvan Devai; 24.06.2017
comment
Похоже, это отличается от почти всего, что работает только на postgresql. На mysql можно использовать оператор x ‹=›? для не равных и не x ‹=›? для равных. - person Sarel Botha; 23.05.2018

arrow_upward
6
arrow_downward

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

Положение 1:

SELECT * FROM tableA WHERE x is NULL

Положение 2:

SELECT * FROM tableA WHERE x = ?

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

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

person dcp    schedule 18.11.2010
comment
Спасибо, dcp, мне это очень нравится. Но представьте, если бы у моего оператора не 1 параметра, а 5. Мне нужно было бы 5 ^ 2 операторов = 25! - person Zeemee; 18.11.2010
comment
Так что это позволит избежать проблемы, заключающейся в том, что он по-прежнему говорит `=` вместо `IS`? - person aioobe; 18.11.2010
comment
@dcp, похоже, вы изменили свой ответ с одного на совсем другое. Почему? - person Paul Tomblin; 18.11.2010
comment
setNull не делает ничего, чего еще не делает setString (col ++, null). setNull более полезен для типов, которые не имеют естественного нуля, например int или double. - person Paul Tomblin; 18.11.2010
comment
@ Пол Томблин - Да, извините за это. Я думал, что он может использовать setNull, но в его случае это не сработает. Это полезно только тогда, когда вы хотите установить нулевое значение для чего-то, что вы вставляете / обновляете. - person dcp; 18.11.2010
comment
@Mulmoth - Как упоминалось в моем редактировании, вы можете подумать об использовании хранимой процедуры, тогда вы сможете обрабатывать логику в SP. - person dcp; 18.11.2010
comment
@dcp, мне не нравятся хранимые процедуры, потому что они усложняют развертывание, обслуживание и отладку. Однако ваше предложение - это другой аспект. - person Zeemee; 18.11.2010
comment
@Mulmoth - Интересно. Я обнаружил, что хранимые процедуры значительно упрощают обслуживание, поскольку код SQL компилируется на сервере. Кроме того, я могу повторно использовать код SP в других приложениях, вместо того, чтобы каждый раз заново изобретать колесо. - person dcp; 18.11.2010

arrow_upward
0
arrow_downward

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

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

Тогда ты мог бы:

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

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

person Knubo    schedule 18.11.2010
comment
Спасибо, Knubo, но я не могу убедиться, что пустые строки не используются в качестве значения. - person Zeemee; 18.11.2010
comment
Если вам действительно нужна производительность, вы можете выбрать какое-то значение, которое, как вы уверены, никогда не будет присутствовать для тестирования. Это было бы взломом производительности, поэтому я бы предпочел его избегать, если бы вы могли. (Например, может случиться так, что ваши данные никогда не содержат ни одного €, поэтому вы можете проверить это вместо ''. - person Knubo; 18.11.2010

arrow_upward
0
arrow_downward

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

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

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

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

person Brian    schedule 05.01.2017