This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement and 'null' value in WHERE clause

 
Radoslaw Sztando
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,



Why above code does not update column AGE for all records which NAME is null? In normal query I would rather use "...WHERE NAME IS NULL" for this case but I want to have general solution (handling both NULL and not NULL values in WHERE clause). Guys, do you have any idea how to do it?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Radoslaw Sztando:
Why above code does not update column AGE for all records which NAME is null?

Because you aren't using "is null". Null will never match on =.


but I want to have general solution (handling both NULL and not NULL values in WHERE clause). Guys, do you have any idea how to do it?

You are on the right track with your if statement. You just need to add a bit more logic:

 
Xiangning Liu
Greenhorn
Posts: 3
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jeanne Boyarsky:



In this case, it's not necessary to use PreparedStatement, why not use Statement directly?

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Xiangning Liu:
In this case, it's not necessary to use PreparedStatement, why not use Statement directly?

Because prepared statements protect you from SQL injection. I'd much rather the JDBC driver handle that than my having to write a (possibly incorrect) validator.
 
Srilakshmi Vara
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In your query your are checking

name = null or name = passed value

in oracle name = null is different from name is null.

Change your code accordingly.

HTH
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic