How to write sql query whose conditions are optional?

Question

I have to write a query where conditional parameters are not known because they are set dynamically in jdbc. And those conditions should be optional. I use h2 database. The query is :

select e.event_id,a.attempt_id,a.preferred,a.duration,a.location 
from event e,attempt a 
where e.user_label=? and e.start_time=? 
and e.end_time=? and e.duration_min=? 
and e.duration_max=? 
and e.event_id=a.event_id

But how to make these conditions optional except using OR because params are not known?

Thanks!

Answer 1

If you can switch to named parameters, you could change the condition to to check parameters for null, like this:

select e.event_id,a.attempt_id,a.preferred,a.duration,a.location 
from event e,attempt a 
where
     (:ul is null OR e.user_label=:ul)
 and (:st is null OR e.start_time=:st)
 and (:et is null OR e.end_time=:et)
 and (:dmin is null OR e.duration_min=:dmin)
 and (:dmax is null OR e.duration_max=:dmax)
 and e.event_id=a.event_id

If you cannot switch to named parameters, you could still use the same trick, but you would need to pass two parameters for each optional one: the first parameter of the pair would be 1 if the second one is set, and 0 if the second one is omitted:

select e.event_id,a.attempt_id,a.preferred,a.duration,a.location 
from event e,attempt a 
where
     (? = 1 OR e.user_label=?)
 and (? = 1 OR e.start_time=?)
 and (? = 1 OR e.end_time=?)
 and (? = 1 OR e.duration_min=?)
 and (? = 1 OR e.duration_max=?)
 and e.event_id=a.event_id

Answer 2

What you are probably looking at is a dynamic SQL. Parts of the query that can change can be appended when the required values are not null :

String sqlQuery ="select e.event_id,a.attempt_id,a.preferred,a.duration,a.location from     event e,attempt a where 1=1"  


if (vUserLabel!=null){ //vUserLabel : The variable expected to contain the required value
sqlQuery = sqlQuery+"e.user_label=?";
}

Later on you can perform :

int pos = 1;
...
if (vUserLabel!=null) {
    stmt.setString(pos++, vUserLabel);
}

stmt.executeQuery(sqlQuery);

This way the conditions get appended to the query dynamically and without duplicated effort , you work is done.