Menu
The statement is
SELECT * FROM tableA WHERE x = ? and the parameter is inserted via java.sql.PreparedStatement 'stmt'
stmt.setString(1, y); // y may be null If y is null, the statement returns no rows in every case because x = null is always false (should be x IS NULL). One solution would be
SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL) But then i have to set the same parameter twice. Is there a better solution?
Thanks!
361
Use an "if" statement to create a condition for the null. You can use the boolean value as a condition for what the statement does next. For example, if the value is null, then print text "object is null". If "==" does not find the variable to be null, then it will skip the condition or can take a different path.
Yes, you have to close the prepared statements ( PreparedStatement Object) and result sets as they may cause memory leakage.
setString(1, usernameObject); setString(2, privilegeObject); The purpose of PreparedStatement is to reduce the difficulty and readability of the database connection code.
Closing PreparedStatement ObjectIf you close the Connection object first, it will close the PreparedStatement object as well. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.
I've always done it the way you show in your question. Setting the same parameter twice is not such a huge hardship, is it?
SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL); There is a quite unknown ANSI-SQL operator IS DISTINCT FROM that handles NULL values. It can be used like that:
SELECT * FROM tableA WHERE x NOT IS DISTINCT FROM ? So only one parameter has to be set. Unfortunately, this is not supported by MS SQL Server (2008).
Another solution could be, if there is a value that is and will be never used ('XXX'):
SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With