Spring Boot中如何为MSSQL构建带动态条件的原生查询（含参数占位符）

背景与需求 #

使用MSSQL作为数据库，Spring Boot项目仓库层有如下方法：

List<Object[]> SummaryData(@Param("startDate") LocalDateTime startDate,
                           @Param("endDate") LocalDateTime endDate, List<Integer> cNoList);

其中cNoList仅包含0或多个数值，查询逻辑需满足：

• 当cNoList包含45、30、20等非0数值时，执行带AND cId in (?3)的查询：

SELECT 'CUSTOMER' as title_key, COUNT(*) as total_count 
FROM LOGIN_TABLE lg left join USER_TABLE ut on lg.u_id = ut.tid_no 
where lg.user_type = 'CUSTOMER' and lg.is_verified = 1 and 
lg.sys_time BETWEEN ?1 AND ?2 AND cId in (?3)

• 当cNoList仅包含0时，不执行上述查询的最后部分，即执行：

SELECT 'CUSTOMER' as title_key, COUNT(*) as total_count 
FROM LOGIN_TABLE lg left join USER_TABLE ut on lg.u_id = ut.tid_no 
where lg.user_type = 'CUSTOMER' and lg.is_verified = 1 and lg.sys_time BETWEEN ?1 AND ?2

尝试的实现 #

我尝试了如下动态SQL实现：

@Query(value = "DECLARE @part VARCHAR(100) = ''; " +
               "DECLARE @region_no INT = 420; " +
               "DECLARE @start Datetime = '2022-07-01 00:00:00'; " +
               "DECLARE @end Datetime = '2023-01-17 23:59:59'; " +
               "DECLARE @tp VARCHAR(100) = 'CUSTOMER'; " +
               "SET @part = (CASE WHEN @region_no != 0 THEN 'and region_no = ' + CAST(@region_no AS VARCHAR(100)) ELSE '' END); " +
               "EXEC ('SELECT ''' + @tp + ''' as title_key, COUNT(*) as total_count FROM LOGIN_TABLE lg left join USER_TABLE ut on lg.u_id = ut.tid_no where lg.user_type = ''' + @tp + ''' and lg.is_verified = 1 and " +
               "lg.sys_time BETWEEN " +
               "CONVERT(DATETIME, ''' + @start + ''', 120) " +
               "AND " +
               "CONVERT(DATETIME, ''' + @end + ''', 120) ' + @part)", nativeQuery = true)
List<Object[]> SummaryData(@Param("startDate") LocalDateTime startDate,
                           @Param("endDate") LocalDateTime endDate, List<Integer> cNoList);

该查询使用静态值@region_no、@start、@end时可正常执行，但需要动态赋值这些参数，目前遇到以下问题：

问题点 #

• 尝试用占位符?3替换DECLARE @region_no INT = 420中的420但无效，且@region_no需要支持int数组类型而非单个int；
• 如何用占位符替换DECLARE @start Datetime = '2022-07-01 00:00:00';中的静态日期，例如写成DECLARE @start Datetime = ?1;？

内容的提问来源于stack exchange，提问作者Md.Habibur Rahman