PreparedStatement“为空".在Where子句中，如果没有条件(动态查询)或乱码 [英] PreparedStatement "is null" in Where clause without if conditional (dynamic query) or gibberish values

问题描述

假设我有以下查询:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = ?

使用PreparedStatement，我可以绑定变量:

With PreparedStatement, I can bind the variable:

pstmt.setString(1, custID);

但是，我无法通过以下绑定获得正确的结果:

However, I cannot obtain the correct results with the following binding:

pstmt.setString(1, null);

结果如下:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = NULL

，不会产生任何结果.正确的查询应为:

which does not give any result. The correct query should be:

SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID IS NULL

通常的解决方案是:

动态生成查询:

String sql = "SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID "
                + (custID==null ? "IS NULL" : "= ?");
if (custID!=null)
    pstmt.setString(1, custID);

解决方案2

使用NVL将空值转换为乱码:

Solution 2

Use NVL to convert null value to a gibberish value:

SELECT * FROM CUSTOMERS WHERE NVL(CUSTOMER_ID, 'GIBBERISH') = NVL(?, 'GIBBERISH');

但是您需要100％确保永远不会存储'GIBBERISH'值.

But you need to be 100% sure that the value 'GIBBERISH' will never be stored.

是否可以使用静态查询 来避免依赖乱码转换?我正在寻找类似的东西:

Is there a way to use a static query and avoid depending on gibberish value conversions? I am looking for something like:

SELECT * FROM CUSTOMERS
    WHERE /** IF ? IS NULL THEN CUSTOMER_ID IS NULL ELSE CUSTOMER_ID = ? **/

我想我可能有一个可行的解决方案:

I think I may have a working solution:

SELECT * FROM CUSTOMERS
    WHERE ((? IS NULL AND CUSTOMER_ID IS NULL) OR CUSTOMER_ID = ?)

pstmt.setString(1, custID);
pstmt.setString(2, custID);

上面的方法可靠吗?是否有更好的方法(可能是只需要设置一次参数的方法)?还是根本没有办法可靠地做到这一点?

Will the above work reliably? Is there a better way (possibly one that requires setting the parameter only once)? Or is there no way to do this reliably at all?

推荐答案

您的工作解决方案很好(并且与 ).如果只想绑定一次，则可以使用CTE或内联视图为实际查询提供值:

Your working solution is fine (and similar to what I've used before). If you only want to bind once you can use a CTE or inline view to provide the value to the real query:

WITH CTE AS (
    SELECT ? AS REAL_VALUE FROM DUAL
)
SELECT C.*   -- but not * really, list all the columns
FROM CTE
JOIN CUSTOMERS C
ON (CTE.REAL_VALUE IS NULL AND C.CUSTOMER_ID IS NULL)
    OR C.CUSTOMER_ID = CTE.REAL_VALUE

因此，只有一个占位符可以绑定.

So there is only one placeholder to bind.

不过，除非您的实际查询更加复杂并且会导致大量重复，否则我实际上并没有在Java端看到分支的问题.

I don't really see a problem with a branch on the Java side though, unless your actual query is much more complicated and would lead to significant duplication.

这篇关于PreparedStatement“为空".在Where子句中，如果没有条件(动态查询)或乱码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！