Java Database Connectivity (JDBC)

Hi Forum:

How should I handle optional parameter in a PreparedStatement?

I have a sql to query a table against two columns. The user sumbit a form which may or may not contain one of them.
The prepared statement looks like:
//
PreparedStatement ps=con.prepareStatement("select * from address_table where house_number = ? and street_name = ?");
// if the user submit form has houseNumber=105 and streetName="main" then:
ps.setInt(1, inputNumber);
ps.setString(2, streetName);

Everything works fine.

The problems is that if the user did not know a house number so the form submitted contains no house number, the inputNumber will be null. What I really want to do is to query out all the address on that street and let the user choose the correct house number. So the query should be "select * from address_table where street_name=?"; i.e drop out the house number condition.

In my code, I can not prepare "house_number = ? and street_name = ?" then do
ps.setNull(1, Types.INTEGER);
or
ps.setInt(1, 0);

The result will not be correct. I want all records, not the records with house_number as NULL or 0.

I can not find solutions when we code a prepared statement, then in the run time we ignore one or two "?" if no valid input.

Do I have to exam all the input, construct a sql string based on the input, (take out some "?"s), then call preparedStatement(revisedSQL)? In this case, I can not code the sql in advance. is there anyway I can still code the preparedstatement, but somehow setting the prameters differently so the optional parameters is ignored by the database.

I know that using storedprocedure may define some optional parameters and setting null may work, but we need use databases that do not support storedprocedure.


Any idea, suggestions?

Thanks in advance!