如何编写条件可选的sql查询？ [英] How to write sql query whose conditions are optional?

问题描述

我必须编写一个查询，其中条件参数未知，因为它们是在jdbc中动态设置的。这些条件应该是可选的。
我使用h2数据库。
查询是：

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?

谢谢！

推荐答案

如果您可以切换到命名参数，您可以将条件更改为检查 null的参数，如下所示：

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

如果你无法切换到命名参数，您仍然可以使用相同的技巧，但您需要为每个可选参数传递两个参数：第一个参数如果设置了第二个，则该对的ameter将是 1 ，如果省略第二个，则 0 ：

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

这篇关于如何编写条件可选的sql查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！