Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning 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
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

PreparedStatement WHERE field IS NULL

 
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?
 
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.
 
author & internet detective
Posts: 39957
804
Eclipse IDE VI Editor Java
  • 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
 
I once met a man from Nantucket. He had a tiny ad
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic