Oracle存储过程拼接in语句 & 自定义split函数

<think>我们正在处理一个FreeMarker模板需求：将包含逗号分隔的字符串拆分成多个部分，并在SQL查询的IN条件中使用这些部分。 目标：生成一个FreeMarker模板代码片段，该片段将处理一个逗号分隔的字符串，将其拆分成一个列表，然后构建一个SQL查询的IN条件。 注意：在SQL中，IN条件需要多个值，每个值都应该被正确引号括起来（如果是字符串的话）。例如：IN ('val1','val2','val3') 步骤： 1. 假设我们有一个字符串变量，比如`ids = "1,2,3,4,5"`（可能是数字ID，也可能是字符串ID，这里我们按字符串处理，因为SQL中字符串需要引号）。 2. 使用FreeMarker的`split`函数分割字符串，得到一个列表。 3. 遍历这个列表，为每个元素添加单引号（因为SQL中字符串值需要单引号），并用逗号连接它们，形成IN条件中的内容。 但是，注意：如果原始字符串中有空值（连续两个逗号），我们需要避免空字符串被当作一个值。例如："1,2,,3" 分割后会有空字符串元素，我们需要过滤掉。 因此，我们可以在遍历时检查元素是否为空，或者使用`?sequence`和`?filter`来过滤空值。 然而，FreeMarker的`split`函数默认会保留空值（如两个逗号之间的空字符串）。所以我们需要在遍历时跳过空值。 另外，如果ID是数字类型，我们也可以不加引号，但为了通用性，我们假设这些ID都是字符串（所以需要加单引号）。 计划生成的FreeMarker代码： - 使用`<#assign idList = ids?split(",")>` 分割字符串。 - 然后构建一个字符串，将每个非空元素用单引号括起来，并用逗号连接。 但是，注意：在FreeMarker中，我们不能直接修改列表（比如过滤），所以我们可以： 方法1：在遍历时跳过空值，并构建一个包含引号的字符串列表，然后用逗号连接。 方法2：使用序列的内建函数过滤（如`?sequence`配合`?filter`，但FreeMarker没有直接的过滤函数，所以通常用`<#list ... as ...><#if ...>...</#if></#list>`）。 这里我们使用方法1。 示例代码： 假设我们有一个变量`ids`，其值为逗号分隔的字符串。 我们可以这样写： ``` <#-- 分割字符串并过滤空值 --> <#assign idList = ids?split(",")> <#assign sqlInPart = ''> <#list idList as id> <#if id?trim?has_content> <#if sqlInPart?length == 0> <#assign sqlInPart = "'" + id?trim + "'"> <#else> <#assign sqlInPart = sqlInPart + ", '" + id?trim + "'"> </#if> </#if> </#list> 然后，在SQL中我们可以这样使用： SELECT * FROM table WHERE id IN (${sqlInPart}) ``` 但是，上述代码在遇到大量元素时效率可能不高（因为字符串拼接）。我们可以使用`?join`来优化，但是需要先处理每个元素（加引号和过滤）。 另一种更简洁的方式是使用序列处理函数： 我们可以先过滤掉空值，然后对每个元素加引号，最后用逗号连接。 但是FreeMarker没有直接的过滤函数，我们可以使用`?sequence`和`?filter`吗？实际上，FreeMarker没有内置的过滤函数，所以我们通常使用列表和条件指令。 我们可以这样： ``` <#assign nonEmptyIds = []> <#list ids?split(",") as id> <#if id?trim?has_content> <#assign nonEmptyIds = nonEmptyIds + [id?trim]> </#if> </#list> <#assign sqlInPart = nonEmptyIds?map(id -> "'" + id + "'")?join(", ")> ``` 这样，我们先构建一个非空的列表，然后使用`?map`函数将每个元素转换为带引号的字符串，最后用`?join`连接。 注意：`?map`和`?join`是FreeMarker 2.3.23及以上版本支持的功能（较老的版本可能不支持）。如果考虑到兼容性，我们可以使用第一种方法。 根据引用[1][2]中，我们使用了`?split`和`<#list>`，但没有使用`?map`和`?join`，所以为了兼容性，我们使用第一种方法或第二种方法的基础版本。 但是，用户没有指定FreeMarker版本，我们假设是比较新的版本（因为2.3.23是2016年发布的，现在应该普遍使用）。 因此，我们采用第二种方法（更简洁）。 最终模板代码： ``` <#-- 假设我们有一个字符串变量ids，例如：ids = "1,2,3,4,5" --> <#assign idArray = ids?split(",")> <#assign nonEmptyIds = []> <#list idArray as id> <#assign trimmedId = id?trim> <#if trimmedId?has_content> <#assign nonEmptyIds = nonEmptyIds + [trimmedId]> </#if> </#list> <#-- 现在构建IN条件部分 --> <#if nonEmptyIds?size gt 0> <#assign sqlInPart = nonEmptyIds?map(id -> "'" + id + "'")?join(", ")> SELECT * FROM table_name WHERE id IN (${sqlInPart}) <#else> -- 没有有效的ID，可以选择不返回任何数据，或者根据业务需求处理 SELECT * FROM table_name WHERE 1=0 </#if> ``` 注意：如果ID是数字类型，我们可以不加引号，那么`?map`部分可以改为：`id -> id`，即`nonEmptyIds?join(", ")`，但这里我们按照字符串处理。 另外，我们添加了一个条件：当没有有效ID时，返回一个空结果集（使用`WHERE 1=0`），避免SQL语法错误（IN条件不能为空）。 但是，如果原始字符串全是空格或空，我们过滤后就是空数组，所以这样处理是安全的。 然而，如果用户希望处理数字ID，那么就不需要加引号。我们可以提供一个通用的方法，或者让用户指定类型。但问题中没有明确，所以我们按字符串处理。 如果用户需要数字ID，那么可以这样： ``` <#assign sqlInPart = nonEmptyIds?join(", ")> ``` 因此，我们可以根据实际需求调整。 现在，我们根据用户的问题，生成一个示例模板，该模板将逗号分隔的字符串拆分成多个部分，并构建一个SQL查询的IN条件。 我们将按照字符串ID处理。 注意：在实际使用中，我们还需要防止SQL注入，但FreeMarker通常用于生成静态SQL，如果数据来自不可信源，则需要在应用层进行校验。这里我们假设数据是安全的。 最终示例代码： ``` <#-- 示例：FreeMarker模板代码，将逗号分隔的字符串用于SQL IN条件 --> <#-- 输入：字符串ids，例如： "1,2,3,4,5" 或 "John, Mary, Robert" --> <#assign idArray = ids?split(",")> <#assign nonEmptyIds = []> <#list idArray as id> <#assign trimmedId = id?trim> <#if trimmedId?has_content> <#assign nonEmptyIds = nonEmptyIds + [trimmedId]> </#if> </#list> <#-- 构建SQL查询 --> <#if nonEmptyIds?size gt 0> <#assign sqlInPart = nonEmptyIds?map(id -> "'" + id + "'")?join(", ")> SELECT * FROM table WHERE id IN (${sqlInPart}) <#else> -- 没有有效的ID SELECT * FROM table WHERE 1=0 </#if> ``` 如果用户需要数字ID，则去掉引号，即： ``` <#assign sqlInPart = nonEmptyIds?join(", ")> ``` 这样，我们就完成了需求。 注意：在引用中，用户还提到了其他内容（如遍历HashMap），但本问题只关注字符串分割用于SQL IN条件，所以不涉及。 相关问题： 1. 在FreeMarker中，如何避免SQL注入？ 2. 如果ID中包含单引号（例如：O'Neil），如何处理？ 3. FreeMarker中如何将字符串分割后直接作为参数传递给自定义函数？ 4. 在FreeMarker中，如何将分割后的列表传递给Java方法？ 我们将生成这些问题。</think>### FreeMarker 示例：字符串分割并用于 SQL IN 条件 以下是一个完整的 FreeMarker 模板示例，演示如何将逗号分隔的字符串拆分为列表，并生成 SQL 查询中的 `IN` 条件： ```freemarker <#-- 输入示例：包含逗号分隔值的字符串 --> <#assign ids = "1001, 1002, 1003, 1005, 1008"> <#-- 步骤1：分割字符串并过滤空值 --> <#assign idList = ids?split(",")?map(id -> id?trim)?filter(id -> id?has_content)> <#-- 步骤2：构建SQL IN条件 --> <#if idList?size gt 0> <#assign sqlInClause = "(" + idList?map(id -> "'" + id + "'")?join(", ") + ")"> <#else> <#assign sqlInClause = "('')"> <#-- 空值备用方案 --> </#if> <#-- 步骤3：生成完整SQL语句 --> SELECT * FROM users WHERE user_id IN ${sqlInClause}; ``` #### 代码说明： 1. **字符串分割** `ids?split(",")` 将字符串按逗号拆分为序列 `?map(id -> id?trim)` 移除每个元素两端的空格 `?filter(id -> id?has_content)` 过滤空字符串（如连续逗号导致的空值） 2. **SQL 条件构建** - 使用 `?map(id -> "'" + id + "'")` 为每个值添加 SQL 单引号 - `?join(", ")` 用逗号连接所有值 - 最终生成格式：`('1001','1002','1003')` 3. **空值处理** 当输入为空时（如 `ids = ""`），备用方案返回 `('')` 避免 SQL 语法错误 #### 输出结果： ```sql SELECT * FROM users WHERE user_id IN ('1001','1002','1003','1005','1008'); ``` #### 关键函数： - `?split(delimiter)`：字符串分割函数 [^1][^2] - `?map(expression)`：转换序列元素 - `?filter(predicate)`：过滤序列元素 - `?join(separator)`：连接序列为字符串 #### 注意事项： 1. **SQL 注入防护** 如果输入值来自用户输入，需在应用层验证数据格式（如确保全为数字）[^3] ```freemarker <#-- 安全验证示例 --> <#if idList?filter(id -> !id?matches('^\\d+$'))?size == 0> ...生成SQL... </#if> ``` 2. **空值处理** 使用 `?has_content` 可同时过滤 `null` 和空字符串 [^3] 3. **大数据量优化** 当元素超过 1000 个时（如 Oracle 的 IN 限制），需分批次处理： ```freemarker <#list idList?batch(1000) as batch> SELECT ... WHERE id IN (${batch?map(id -> "'"+id+"'")?join(", ")}) UNION ALL </#list> ```