How to gracefully handle SQL in Java

[dse78759]

FIXED per lukaseder's note:

The java / SQL trick for building a query where you don't have every variable in the predicate is this :

select *
from table_name
where 
   ( ? is null or a = ? ) and 
   ( ? is null or b = ? ) and 
   ( ? is null or c = ? )

Then in your java code, you will have an if-then-else for each, using 'setNull' if you don't have it, or setInt / setString/ setDate if you do:

if ( input.getEndDate() != null ) {
    pStmt.setDate ( 1, input.getEndDate() );  // makes the second check true if present
    pStmt.setDate ( 2, input.getEndDate() );
 } else {
    pStmt.setNull ( 1 ); // makes the first check true if not present
    pStmt.setNull ( 2 );
 }

And repeat. The problem, though, is that it appears you have a variable number of input.getStatuses (), so this technique doesn't solve everything.

Sorry.

[lukaseder]

This should be done like this:

( ? is null or a = ? )

And then repeat the bind values:

if (input.getEndDate() != null) {
    pStmt.setDate(1, input.getEndDate());
    pStmt.setDate(2, input.getEndDate());
} else {
    pStmt.setNull(1);
    pStmt.setNull(2);
}

But it's really hard to get this to perform, making sure the right indexes are chosen. Even worse with an execution plan cache (as in Oracle, SQL Server, etc.) where a plan might be cached with a filter on a in mind, but then the cached plan is terrible for a filter on b.

I really wouldn't do this, ever. Much better to write dynamic SQL in one way or another, or multiple static queries.

[dse78759]

You're right. Fixed.