java - mybatis根据传入的null变量条件执行查询WHERE子句

Question

在我的 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') 

如果 ${passedInVar} 为空，那么我想使用以下 WHERE 条件!

WHERE SYSDATE BETWEEN support_version_valid_from_dt AND support_version_valid_to_dt

我怎样才能像这样构建它？

Answer 1

使用dynamic 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>