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 WHERE field IS NULL

 
Michael Zellmann
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am having trouble using a PreparedStatement to search for a record.
As an example, I have a table with fields name and namespace.
Usually the namespace is NULL, but lately we have some records with a specific namespace.
In the past I have used a PS with SELECT id FROM tagnames WHERE name = ?
That works fine.
Now I need to include the namespace - SELECT id FROM tagnames WHERE name = ? AND namespace = ?
If I have a name of Credit and namespace of abc, it works fine
However, there might also be a record with name of Credit, but namespace NULL
If I try ps.setNull(2, java.sql.Types.VARCHAR) the query doesn't get the result
I find I need to use a different query - SELECT id FROM tagnames WHERE name = ? AND namespace IS NULL
So - I need to test if the namespace is NULL and then use the alternate ps
What I would like is to have one ps that would handle both a NULL namespace and a specific namespace.

Is there any way to do this?
 
Apurv Adarsh
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you try using selective OR in this querry?
the querry can be rewrriten like : SELECT id FROM tagnames WHERE name = ? AND (1 = ? OR namespace = ?)
and then you can have

if(null == namespace){
ps.setIntl(2,1);
ps.setNull(3, java.sql.Types.VARCHAR);
}else{
ps.setIntl(2,2);
ps.setStringl(3,"abc");
}
I havent tested this code but think it should run fine.
 
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
Michael Zellmann wrote:What I would like is to have one ps that would handle both a NULL namespace and a specific namespace.

Why do you want a single prepared statement?

I would use different SQL in the two prepared statements. They are different queries and could potentially be optimized differently. Adding complexity to the query and making life difficult for maintainers/optimizers doesn't seem worth writing a tiny bit less code.r
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic