• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

IS NULL Test in PrepareStatement

 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can I use NULL for =? test in prepare statement? i.e.

pstmt=con.prepareStatement("SELECT * from tmp WHERE pk=?");

Can I use :

pstmt.setNull(1,Types.NUMERIC);

to make the SQL test for NULL instead of supplying another SQL using
WHERE pk IS NULL?
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No you can not !!

A null is the absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero.

To test for nulls, use only the comparison operators IS NULL and IS NOT NULL. Null represents a lack of data, a null cannot be equal or unequal to any value or to another null.

Shailesh
 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is means I need to create an extra PreparedStatement object just for the NULL test?

pstmt1=con.prepareStatement("update tmp set a=1 where key=?");
pstmt1.setInt(1, 10);
...
pstmt2=con.prepareStatement("update tmp set a=1 where key IS NULL" );
pstmt2.execute();

I am just wondering is there a more generic SQL which also includes the NULL case.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Alec Y.L. Lee:

That is means I need to create an extra PreparedStatement object just for the NULL test?

I am just wondering is there a more generic SQL which also includes the NULL case.


It depends on you how you want to do this.
you can do same in single query




Shailesh
 
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
Shailesh,
That wouldn't always work though. If you wanted to only do the update when a field is "7", that query would match on nulls as well.

Alec,
I think it's better to go with the 2 query option. It's clearer, simpler and more efficient.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jeanne Boyarsky:
That wouldn't always work though. If you wanted to only do the update when a field is "7", that query would match on nulls as well.


Jeanne,

I agree that query will update data if either condition is matched,but same is the requirement.


I think it's better to go with the 2 query option. It's clearer, simpler and more efficient


even if we follow this approach how far result would differ from above approach ???

let say I want to only do the update when a field is "7",

first query will do the task afterwards second query will update all rows having null values.

this approach will even have one more round trip to database

thanks
[ April 03, 2005: Message edited by: Shailesh Chandra ]
 
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
Shailesh,
It depends on what the requirement is. I interpretted it that either a given value will be provided or null should be searched. Not both at the same time.

If it is the case that null should be searched no matter what, I agree with your approach.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually both of us were on same track with different version of statement

Shailesh
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic