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.
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