PreparedStatement“为空".在Where子句中，如果没有条件(动态查询)或乱码

假设我有以下查询:

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?