假设我有这样的查询:
SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = ?
使用PreparedStatement,我可以绑定变量:
pstmt.setString(1, custID);
但是,我无法通过以下绑定获得正确的结果:
pstmt.setString(1, null);
结果是:
SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = NULL
这不会给出任何结果。正确的查询应该是:
SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID IS NULL
通常的解决方案是:
解决方案1
动态生成查询:
String sql = "SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID "
+ (custID==null ? "IS NULL" : "= ?");
if (custID!=null)
pstmt.setString(1, custID);
解决方案2
Use NVL
将 null 值转换为乱码值:
SELECT * FROM CUSTOMERS WHERE NVL(CUSTOMER_ID, 'GIBBERISH') = NVL(?, 'GIBBERISH');
但您需要 100% 确定值“GIBBERISH”永远不会被存储。
Question
有没有办法使用静态查询and避免依赖乱码值转换?我正在寻找类似的东西:
SELECT * FROM CUSTOMERS
WHERE /** IF ? IS NULL THEN CUSTOMER_ID IS NULL ELSE CUSTOMER_ID = ? **/
我想我可能有一个可行的解决方案:
SELECT * FROM CUSTOMERS
WHERE ((? IS NULL AND CUSTOMER_ID IS NULL) OR CUSTOMER_ID = ?)
pstmt.setString(1, custID);
pstmt.setString(2, custID);
上述工作可靠吗?有没有更好的方法(可能只需要设置一次参数)?或者根本没有办法可靠地做到这一点?