CODEWITHSUNDEEP
javasqloracle-databaseprepared-statementibatis
NullPointerException
NullPointerException

Reputation: 3814

Update SQL when where clause condition can be null

I have an update SQL statement to update a row 

   UPDATE COM_TRANSACTION_LOGS    
        SET END_TIME  = ?, 
        RESPONSE   = ?  
   WHERE   
   TRANSACTION_ID  = ?   
   AND    
   MESSAGE_ID  = ?

The problem here is that the MESSAGE_ID can be null in some cases so the update SQL comes as 

  [DEBUG] {pstm-100101} Parameters:[2013-05-14 10:38:01.485, XML, 123XYZAAA1236511, null]

This fails to update because the where clause becomes like 

WHERE   
   TRANSACTION_ID  = '123XYZAAA1236511'
   AND    
   MESSAGE_ID  = null

How can I compare against null value through prepared statement.

I know this where clause for null comparison has to be like this

   WHERE   
     TRANSACTION_ID  = '123XYZAAA1236511'
   AND    
      MESSAGE_ID  is null

How can I tell my prepared statement to set the where clause as is null without using two queries and using them conditionally in case of null or ='somevalue

Upvotes: 3

Views: 1121

Answers (3)

Madhu Sharan
Madhu Sharan

Reputation: 68

I would prefer using NVL/NVL2 for your purpose. It gets the work done.

Upvotes: 0

Alexander Tsepkov
Alexander Tsepkov

Reputation: 4186

In Oracle there is an NVL statement for accomplishing this (nvl(?, value_to_replace_null)). I'm not sure what variant of SQL you're using, but there is probably something similar. You might also use IFNULL, ISNULL, and COALESCE for SQL Server and MySQL.

Upvotes: 4

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

You can replace this line:

MESSAGE_ID  = ?

By this:

COALESCE(MESSAGE_ID,-1) = COALESCE(?,-1)

Upvotes: 4

Related Questions