How to write sql query whose conditions are option

问题:

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!

回答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

回答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.

回答3:

My homegrown solution. Lines with [ ] are eliminated if named parameter is not given. So (1=1) sometimes is required to keep syntax OK.

In such sample :label, :startt, :endt are optional, all other required

select e.event_id,a.attempt_id,a.preferred,a.duration,a.location 
from event e,attempt a 
where (1=1) -- sometimes required
[and e.user_label=:label and e.start_time=:startt ]
[and e.end_time=:endt ]
and e.duration_min=:durationmin
and e.duration_max=:durationmax
and e.event_id=a.event_id

NOTE: the my important problem is JDBC have not named parameters. I use Spring JDBC NamedParameterJdbcTemplate but has heavy dependencies. Many ad-hoc named parameter solutions have errors, build wrong query if parameter is used more than 1x (BTW most important argument for me, to use named instead positional), or string substitution are not safe for special characters

回答4:

Ok, it's a while since this question was asked, but i will write my solution to this problem.

I found that when queries are too big or complex it is not easy to append another String at the end or sometimes the resource can be stored in a file or a db, what i did is this.

I had my query in a .sql file so i added a marker tho the query.

my.sql file where the query is stored.

SELECT * FROM my_table
WHERE date_added BETWEEN :param1 and param2
$P{TEX_TO_REPLACE}
GROUP BY id

now in my code

String qry = component.findQuery("my.sql");//READ THE FILE

String additionalQuery = " AND classification = 'GOB'";
if(condition1 == true) {
    additionalQuery = " AND customer_id = 66";
}


qry = qry.replace("$P{TEX_TO_REPLACE}",additionalQuery);

Now my query looks like this.

SELECT * FROM my_table
WHERE date_added BETWEEN :param1 and param2
AND customer_id = 66
GROUP BY id