title: PreparedStatement "is null" in Where clause without if conditional (dynamic query) or gibberish values
body: <p>Suppose I have the query:</p>
  
  <pre><code>SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = ?
  </code></pre>
  
  <p>With PreparedStatement, I can bind the variable:</p>
  
  <pre><code>pstmt.setString(1, custID);
  </code></pre>
  
  <p>However, I cannot obtain the correct results with the following binding:</p>
  
  <pre><code>pstmt.setString(1, null);
  </code></pre>
  
  <p>As this results in:</p>
  
  <pre><code>SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = NULL
  </code></pre>
  
  <p>which does not give any result. The correct query should be:</p>
  
  <pre><code>SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID IS NULL
  </code></pre>
  
  <p>The usual solutions are:</p>
  
  <h2>Solution 1</h2>
  
  <p>Dynamically generate query:</p>
  
  <pre><code>String sql = "SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID "
                  + (custID==null ? "IS NULL" : "= ?");
  if (custID!=null)
      pstmt.setString(1, custID);
  </code></pre>
  
  <h2>Solution 2</h2>
  
  <p>Use <code>NVL</code> to convert null value to a gibberish value:</p>
  
  <pre><code>SELECT * FROM CUSTOMERS WHERE NVL(CUSTOMER_ID, 'GIBBERISH') = NVL(?, 'GIBBERISH');
  </code></pre>
  
  <p>But you need to be 100% sure that the value 'GIBBERISH' will never be stored.</p>
  
  <h2>Question</h2>
  
  <p>Is there a way to use a static query <strong>and</strong> avoid depending on gibberish value conversions? I am looking for something like:</p>
  
  <pre><code>SELECT * FROM CUSTOMERS
      WHERE /** IF ? IS NULL THEN CUSTOMER_ID IS NULL ELSE CUSTOMER_ID = ? **/
  </code></pre>
  
  <p>I think I may have a working solution:</p>
  
  <pre><code>SELECT * FROM CUSTOMERS
      WHERE ((? IS NULL AND CUSTOMER_ID IS NULL) OR CUSTOMER_ID = ?)
  
  pstmt.setString(1, custID);
  pstmt.setString(2, custID);
  </code></pre>
  
  <p><strong>Will the above work reliably?</strong> 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?</p>