This week's book giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Building Blockchain Apps and have Michael Yuan on-line!
See this thread for details.
Win a copy of Building Blockchain Apps this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

IS NULL Test in PrepareStatement

 
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?
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • 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: 1087
Oracle Spring Java
  • 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
 
author & internet detective
Posts: 39890
798
Eclipse IDE VI Editor Java
  • 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: 1087
Oracle Spring Java
  • 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
Posts: 39890
798
Eclipse IDE VI Editor Java
  • 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: 1087
Oracle Spring Java
  • 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
 
Weeds: because mother nature refuses to be your personal bitch. But this tiny ad is willing:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!