Java 我基于传入的空变量 batis 条件执行查询 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21713758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
my batis conditional execution of query WHERE clause based on null variable passed in
提问by user836087
In my batis, I have a query that will be used to query based on date, else it should just get sysdate between a range. There are 2 potentially executable where clauses, but I would only like to execute one of them.
在我的 batis 中，我有一个用于根据日期进行查询的查询，否则它应该只获取一个范围之间的 sysdate。有 2 个潜在的可执行 where 子句，但我只想执行其中之一。
SELECT distinct support_id AS object_id, 2 as app_context_id,
0 as domain_cdc_id, 'S' as object_type, 'NEW' as domain_cdc_status, 1 as attribute_group_num,
sysdate as created_on FROM fdrdbo.v_facility_support
WHERE support_version_valid_from_dt = to_date('${passedInVar}', 'yyyy/mm/dd')
IF ${passedInVar} is null, then I want the following WHERE condition to be used!
如果 ${passedInVar} 为空，那么我希望使用以下 WHERE 条件！
WHERE SYSDATE BETWEEN support_version_valid_from_dt AND support_version_valid_to_dt
How can I build it like this?
我怎样才能像这样构建它？
回答by Rida BENHAMMANE
You can join the two WHEREs in one like this :
您可以像这样将两个 WHERE 合二为一：
WHERE
('${passedInVar}' IS NULL AND SYSDATE BETWEEN support_version_valid_from_dt AND support_version_valid_to_dt)
OR
('${passedInVar}' IS NOT NULL AND support_version_valid_from_dt = to_date('${passedInVar}', 'yyyy/mm/dd'))
回答by Roman Konoval
Use dynamic SQL
使用动态 SQL
SELECT distinct support_id AS object_id, 2 as app_context_id,
0 as domain_cdc_id, 'S' as object_type, 'NEW' as domain_cdc_status, 1 as attribute_group_num,
sysdate as created_on FROM fdrdbo.v_facility_support
WHERE
<if test="passedInVar != null">
support_version_valid_from_dt = to_date('${passedInVar}', 'yyyy/mm/dd')
</if>
<if test="passedInVar == null">
SYSDATE BETWEEN support_version_valid_from_dt AND support_version_valid_to_dt
</if>
