Oracle Java - Add optional date parameter to Prepared statement

I want to add optional "todate" parameter to Prepared statement

One way is to add a placeholder for optional parameter(s) and replace it before execute

  where id=? OPTIONAL_SECTION 

For example OPTIONAL_SECTION will be set to TO_DATE <=sysdate -1

A better and correct way is to bind optional parameter with handling null

where id=? and TO_DATE <= nvl(?, TO_DATE)

Is there a better way to handle optional date parameter?
Specially because condition can be without equal ( TO_DATE < ?)

• This is a minimal example, Id isn't a primary key (actually it's a foreign key), SQL return multiple records 

> A better and correct way is to bind optional parameter with handling null

That is not necessarily better or more correct. It is a different way, but it's very debatable if it is "better and (more) correct".

SQL Query optimizers generally run before any ? argument values are known, so a TO_DATE <= nvl(?, TO_DATE) cannot be optimized and will require a full table scan, barring any other conditions in the where clause.

However with a TO_DATE <= ?, the optimizer may be able to use an index on TO_DATE to do a ranged scan of the index, so I'd argue that the first option is potentially better, depending on available indexes.

> condition can be without equal ( TO_DATE < ?)

The correct way to write the second version is:

where id = ?
  and (? is null or TO_DATE < ?)

You will then of course have to specify the value twice for the PreparedStatement.

The first decision for a query with an optional parameter is as follows:

1. is it accepatable to use one statement for both / all options

2. it is prefered to use a separate statament for each option

In your case you have two parameter options:

id

id and date_parameter

From the naming, I'd argue, that ID is a primary key of the table, so it returns one row only and will be the driver in the execution plan - simple index access.

The date_parameter could only cause that the query return optionally no row

In this case you may safely use the decision 1) - one query for both option will be fine

But in other interpretation, the ID is a foreign key with tons of rows and the date_parameter is used to return only a small number of recent rows.

In this case the decision 1) aka OR query will fail badly. The execution plan is optimized on the case returning mass data, so you will wait ages to get few rows.

So in this case only decision 2) provides a performant solution. The short answer is to dynamically generate two queries based on the parameter passed

where id=? 

where id=? and data_parameter <= ?

The technical problem with this approach is, that the queries have a different number of bind variables which makes the setXXX complicated.

To avoid this problem you can use the 1=1 or trick, that a) makes the number of the bind variable equal in all queries* and b) eliminates the not necessary onces.

Query for ID only is generated as

where id=? and 1=1 or data_parameter <= ?

Query with IDand DATE_PARAM remains the same

where id=? and data_parameter <= ? 

Some more examples and credit for popularizing this approach can be foud here and there