Clojure解析map向量按键类型过滤拼接原生SQL查询字符串

原有实现的核心问题是硬编码了字段名和比较符的判断逻辑，每新增一个支持的字段或者比较符都要重复写参数过滤、取值的样板代码，冗余度高且难维护。优化核心思路是把比较符和SQL生成逻辑做映射配置，遍历参数列表逐行生成片段后拼接，无需硬编码任何字段名。

具体实现 #

1. 定义比较符规则映射 #

把所有支持的比较符对应的SQL生成逻辑存在映射表中，后续新增比较符只需要在这个映射里加配置即可，不用改动核心转换逻辑：

;; key: 前端传入的comparator值
;; value: 接收单个参数字段map，返回对应SQL条件片段的函数
(def comparator-rules
  {"equal"     (fn [{:keys [fieldName]}] 
                 (str " AND " fieldName " = ?"))
   "not-equal" (fn [{:keys [fieldName]}] 
                 (str " AND " fieldName " != ?"))
   "greater"   (fn [{:keys [fieldName]}] 
                 (str " AND " fieldName " > ?"))
   "less"      (fn [{:keys [fieldName]}] 
                 (str " AND " fieldName " < ?"))
   "include"   (fn [{:keys [fieldName inputValue]}] 
                 (str " AND " fieldName " LIKE '" inputValue "%'"))
   "between"   (fn [{:keys [fieldName inputValue maxInputValue]}] 
                 (str " AND " fieldName " BETWEEN '" inputValue "' AND '" maxInputValue "'"))})

2. 编写通用转换函数 #

函数逻辑非常直接：遍历传入的参数向量，对每个参数项根据它的比较符找到对应的生成规则，生成SQL片段后直接拼接成完整字符串即可，完全不感知具体字段名，新增任意字段都不需要修改函数：

(defn convert [params]
  (->> params
       (map (fn [param]
              (let [rule-fn (get comparator-rules (:comparator param))]
                ;; 遇到未定义的比较符默认返回空串，也可以按需改成抛出异常
                (if rule-fn (rule-fn param) ""))))
       (apply str)))

3. 效果验证 #

传入示例参数执行：

(def params [{:fieldName "salary" :comparator "greater" :inputValue "10000" :inputType "number"}
             {:fieldName "name" :comparator "include" :inputValue "Ros" :inputType "text"}
             {:fieldName "start_date" :comparator "between" :inputValue "2022-01-01" :maxInputValue "2022-06-01" :inputType "date"}])

(convert params)

得到输出：

" AND salary > ? AND name LIKE 'Ros%' AND start_date BETWEEN '2022-01-01' AND '2022-06-01'"

和预期格式完全一致，需要换行的话只需要在每个片段前加换行符即可。

安全扩展建议 #

直接拼接参数值存在SQL注入风险，生产环境建议改成占位符模式，同时收集对应的参数列表传给PreparedStatement执行，调整后的实现如下：

(def comparator-rules-safe
  {"equal"     (fn [{:keys [fieldName inputValue]}]
                 {:sql (str " AND " fieldName " = ?") :params [inputValue]})
   "not-equal" (fn [{:keys [fieldName inputValue]}]
                 {:sql (str " AND " fieldName " != ?") :params [inputValue]})
   "greater"   (fn [{:keys [fieldName inputValue]}]
                 {:sql (str " AND " fieldName " > ?") :params [inputValue]})
   "include"   (fn [{:keys [fieldName inputValue]}]
                 {:sql (str " AND " fieldName " LIKE ?") :params [(str inputValue "%")]})
   "between"   (fn [{:keys [fieldName inputValue maxInputValue]}]
                 {:sql (str " AND " fieldName " BETWEEN ? AND ?") :params [inputValue maxInputValue]})})

(defn convert-safe [params]
  (let [segments (map (fn [p] ((get comparator-rules-safe (:comparator p)) p)) params)]
    {:sql (apply str (map :sql segments))
     :params (vec (mapcat :params segments))}))

内容的提问来源于stack exchange，提问作者Razz