aspose file tools*
The moose likes JDBC and the fly likes Handling Null Values In Jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Handling Null Values In Jdbc" Watch "Handling Null Values In Jdbc" New topic
Author

Handling Null Values In Jdbc

Ayan Biswas
Ranch Hand

Joined: Jul 10, 2010
Posts: 99
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?


AyanBiswas
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
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)
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

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

Joined: Feb 22, 2011
Posts: 75
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

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).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Handling Null Values In Jdbc
 
Similar Threads
B&S: Writing of records
How to do optional wildcard select in JPA named queries?
Hibernate Query Language Question (simple)
Null and Blank in Database
Jdbc Prepared Statement execute()