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!

score 10 · Accepted Answer · edited May 23 '17 at 11:47

10

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

edited May 23 '17 at 11:47

Community

1
1

answered Nov 09 '13 at 12:31

Sergey Kalinichenko

714,442
84
1,110
1,523

Thanks @dasblinkenlight. I'll use this approach but instead of 0 and 1 I can use true or false. – Volodymyr Levytskyi Nov 09 '13 at 12:55
Your query will output the same for any input because all conditions are always true. Because I cannot set 0 but 1 only because I don't know which params are correct and which not. Another approach is the only way here, probably. – Volodymyr Levytskyi Nov 09 '13 at 18:17
@VolodymyrLevytskyi I am sure that it is workable, because I have been using an approach similar to the second one in my production code for many years. However, my "front end" keeps the score of what the user has set and what he left unset. If your front end does not have it, you could still use `userLabelText != null` to set the corresponding "is set" parameter. – Sergey Kalinichenko Nov 09 '13 at 18:35

score 3 · Answer 2 · edited Nov 09 '13 at 13:07

3

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.

edited Nov 09 '13 at 13:07

Sergey Kalinichenko

714,442
84
1,110
1,523

answered Nov 09 '13 at 12:36

Deepak Vn

112
1
11

Yes,Indeed this is what I need! – Volodymyr Levytskyi Nov 09 '13 at 12:37
Agree ! This is just to showcase the logic that could be implemented. The inputs should definitely be sanitized to ensure no illegal characters.Completely agree with you on that and thanks for sharing ! – Deepak Vn Nov 09 '13 at 12:54
I get that.Taken it account.Would follow the same next time on.Thanks! – Deepak Vn Nov 09 '13 at 13:06

Jacek Cz · Answer 3 · 2015-09-11T09:27:06.180

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

score 0 · Answer 4 · answered May 29 '18 at 18:08

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

How to write sql query whose conditions are optional?

4 Answers4

Linked