• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Handling Null Values In Jdbc

 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a form where there are 4 text fields..Out of them 1 is mandatory ,others are optional..Now if a user gives 2 fields then the query should be

The problem with this approach is that I have to check each field and check if its null and then run the respective query(All possible combinations!!).because 'NULL AND Something' gives NULL (Which I dont want).So,what approach should i take to get rid of this problem?
 
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Check your database documentation for null value function(s). Oracle has NVL and a quick lookup on mysql documentation says that COALESCE or IFNULL can be used. I haven't used MySQL functions so not much I can add to that.

Your query would look something like SELECT ... where field1 = NVL(:1,field1) and field2 = NVL (:2, field2) ... (If your DB is Oracle)
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Sudheer Bhat wrote:Your query would look something like SELECT ... where field1 = NVL(:1,field1) and field2 = NVL (:2, field2) ... (If your DB is Oracle)


In JDBC, the :1, :2 should be replaced by question mark.

However, if I'm not seriously mistaken, such query will never use an index on any of these columns (certainly not on Oracle). I'd suggest to build the query dynamically (still using prepared statement) and only include the columns in the where clause for which a value was supplied by the user.
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:

Sudheer Bhat wrote:Your query would look something like SELECT ... where field1 = NVL(:1,field1) and field2 = NVL (:2, field2) ... (If your DB is Oracle)


In JDBC, the :1, :2 should be replaced by question mark.

However, if I'm not seriously mistaken, such query will never use an index on any of these columns (certainly not on Oracle).



I just ran a simple test case against my Oracle DB and it seems to pick up the index.


And this as expected doesn't pick up the index.

 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You didn't test the original query, which would be select * from test_index_usage where object_name = nvl(:x,object_name);.

However, it turns out that Oracle 11 is much smarter than I thought:

So yes, your approach was right, at least in Oracle 11. I know Tom Kyte suggested a different approach in one quite old discussion, so I'd say previous versions of Oracle were not that smart.

Edit: if the goal was database agnostic code, I'd still use the build-the-correct-statement approach.
Edit2: thanks for actually doing the test - I should have it done myself.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yet another update: this approach has the limitation that it does not ever select a row with a null value in one of the fields, even when the field was not specified by the user (true in all databases). This might not be a problem, though in Oracle an empty string is actually a NULL (and wouldn't therefore be ever matched).
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic