Handling Null Values In Jdbc

[Ayan Biswas]

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

SELECT * FROM TABLE WHERE FIELD1=? AND FIELD2=?

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?

[Sudheer Bhat]

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 Vashko]

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]

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.
SQL> create table test_index_usage as select * from dba_objects where rownum < 50000; Table created. SQL> create index test_index_usage_n1 on test_index_usage(object_name); Index created. SQL> var x varchar2(30); SQL> exec :x := 'TEST'; PL/SQL procedure successfully completed. SQL> set autotrace traceonly explain SQL> select * from test_index_usage where object_name = nvl(:x,'TEST'); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=73 Card=507 Bytes= 104949) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_INDEX_USAGE' (TABLE ) (Cost=73 Card=507 Bytes=104949) 2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX_USAGE_N1' (INDEX) (Cos t=1 Card=203)

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

SQL> select * from test_index_usage where upper(object_name) = nvl(:x,'TEST'); Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=196 Card=507 Bytes =104949) 1 0 TABLE ACCESS (FULL) OF 'TEST_INDEX_USAGE' (TABLE) (Cost=19 6 Card=507 Bytes=104949) SQL>

[Martin Vashko]

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:
SQL> select * from test_index_usage where object_name = nvl(:x,object_name); Execution Plan ---------------------------------------------------------- Plan hash value: 2405226981 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 44736 | 6902K| 300 (1)| 00:00:04 | | 1 | CONCATENATION | | | | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS FULL | TEST_INDEX_USAGE | 44293 | 6834K| 204 (1)| 00:00:03 | |* 4 | FILTER | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX_USAGE | 443 | 69994 | 96 (0)| 00:00:02 | |* 6 | INDEX RANGE SCAN | TEST_INDEX_USAGE_N1 | 241 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:X IS NULL) 3 - filter("OBJECT_NAME" IS NOT NULL) 4 - filter(:X IS NOT NULL) 6 - access("OBJECT_NAME"=:X) Note ----- - dynamic sampling used for this statement (level=2)
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]

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