mybatis條件判斷及動態sql的簡單擴充

menghl發表於2024-06-11
MyBatisSQL

在MyBatis中，可以透過使用一些特定的標籤(if、choose...)以及其他動態SQL功能來實現條件判斷。
這使得SQL查詢可以根據不同的條件動態生成，從而提高查詢的靈活性和可維護性。
本文以訂單列表簡單查詢為例, 對mybatis條件判斷及動態sql進行簡單擴充。

建表語句

CREATE TABLE order_table (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '訂單序號',
  `order_type` varchar(255) NULL COMMENT '訂單型別',
  `status` varchar(255) NULL COMMENT '訂單狀態',
  `customer_id` bigint(20) NULL COMMENT '所屬客戶id',
  `quantity` double NULL COMMENT '數量',
  `address` varchar(500) NULL COMMENT '收貨地',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '訂單表';

條件判斷及動態sql

以if標籤為例,if標籤類似於 Java 中的 if 語句, 是mybatis中最常用的判斷標籤。

語法
<if test="判斷條件">
  SQL語句
</if>
簡單使用
判斷數值:
<if test="id != null ">
 and id = #{id,jdbcType=BIGINT}
</if>
判斷字元:
// 判斷是否為空
<if test="orderType != null and orderType.trim().length() > 0">
 sql語句
</if>
// 判斷是否包含某個字元
<if test="orderType != null and orderType eq 'customer'">
 sql語句
</if>
判斷集合:
<if test="idSet != null and idSet.size > 0">
  and id in
  <foreach collection="idSet" item="item" separator="," open="(" close=")">
    #{item}
  </foreach>
</if>

簡單擴充

if標籤與常用Java工具類結合

// 判空
<if test="@java.util.Objects@nonNull(customerId)">
    sql語句
</if>
<if test="@org.apache.commons.lang3.StringUtils@isNotBlank(status)">
  sql語句
</if>
// 判斷是否相等
<if test="@java.util.Objects@equals(1,flag)">
    sql語句
</if>
<if test='@org.apache.commons.lang3.StringUtils@equals("customer",status)' >
  sql語句
</if>
// 判斷集合是否為空
<if test="@org.apache.commons.collections.CollectionUtils@isNotEmpty(idSet)">
    and id in
    <foreach collection="idSet" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</if>
同理
// 判斷兩個欄位同時不為空
<if test="@org.apache.commons.lang3.ObjectUtils@allNotNull(flag1,flag2)">
  sql語句
</if>
// 判斷集合中只要包含某一個欄位
<if test="@org.apache.commons.collections.CollectionUtils@containsAny(flagList, "1")">
  sql語句
</if>
等等...

同理, 別的標籤也適用;

對查詢欄位的特殊處理

列表查詢中,有時需要對某些欄位做特殊的處理查詢, 例: 對數值欄位進行特殊處理查詢(>、<、>=、<=、!=), 對某些文字欄位的查詢做特殊處理..., 我們可以巧用佔位符來實現這些處理。
在MyBatis中，# 和 $ 是兩種不同的佔位符，用於在SQL語句中插入引數。
佔位符：用於安全的引數繫結和轉義，防止SQL隱碼攻擊，適用於大多數情況。
$ 佔位符：用於直接文字替換，不進行轉義，適用於動態SQL片段或表名等非使用者輸入的值，但要特別注意SQL隱碼攻擊風險。
透過合理使用這兩種佔位符，可以在確保安全的前提下實現靈活的SQL查詢。

數值欄位特殊處理

新建列舉類(OperationFlagEnum), 事先與前端規定運算子的傳遞;
運算子列舉

/**
 * 操作標識列舉
 * 
 * @author alin
 * @date 2024-06-11
 */
@Getter
@AllArgsConstructor
public enum OperationFlagEnum {

    EQUAL("EQUAL", "="),
    NOT_EQUAL("NOT_EQUAL", "!="),
    LARGER("LARGER", ">"),
    LESS("LESS", "<"),
    LARGER_OR_EQUAL("LARGER_OR_EQUAL", ">="),
    LESS_OR_EQUAL("LESS_OR_EQUAL", "<=");

    private String code;
    private String value;

    private static final Map<String, OperationFlagEnum> VALUE_MAP = new HashMap<>(values().length);

    static {
        Arrays.stream(OperationFlagEnum.values()).forEach(c -> VALUE_MAP.put(c.getCode(), c));
    }

    public static OperationFlagEnum getByCode(String code) {
        return StringUtils.isBlank(code) ? null : VALUE_MAP.get(code);
    }

    public static String getValueByCodeDefault(String code) {
        OperationFlagEnum flagEnum = null;
        if(StringUtils.isNotEmpty(code)){
            flagEnum = getByCode(code);
        }
        return flagEnum == null ? EQUAL.getValue(): flagEnum.value;
    }

}

mapper檔案中的動態sql

<if test="quantity != null">
    and quantity
    ${@com.alin.common.enums.OperationFlagEnum@getValueByCodeDefault(quantityFlag)}
    ${quantity}
</if>

文字欄位特殊處理

若文字欄位中含有某些特殊字元, 則對這個欄位進行特殊處理, 例: 若某些欄位值中間含有空格(或別的字元), 則對此欄位用空格切割後進行範圍查詢;
新建mapper工具類: MapperUtils, 用於特殊處理;

/**
 * @author alin
 * @date 2024-06-11
 */
public class Mapperutils {
    private static final String SPACE = " ";
    public static final String EQUALS = " = #{${propertyField},jdbcType=VARCHAR}";

    public static final String IN = " in ('${propertyField}')";

    /**
     * 獲取通用字串查詢模板
     *
     * @param propertyField model欄位屬性
     * @param value         對應值
     * @return
     */
    public static String getQuery(String propertyField, String value) {
        if (StringUtils.contains(value.trim(), SPACE)) {
            // sql注入過濾
            sqlInject(value);
            return replace(IN, StringUtils.join(StringUtils.split(value, SPACE), "','"));
        } else if(...) {
            .....
        }
        //sql注入過濾
        sqlInject(value);
        //等值查詢
        return replace(EQUALS, propertyField);
    }

    /**
     * 替代
     *
     * @param type
     * @param propertyField
     * @return
     */
    public static String replace(String type, String propertyField) {
        Map<String, Object> params = Maps.newHashMap();
        params.put("propertyField", propertyField);
        return new StrSubstitutor(params).replace(type);
    }

    /**
     * SQL隱碼攻擊過濾
     *
     * @param str 待驗證的字串
     * @throws
     */
    public static void sqlInject(String str) {
        if (StringUtils.isBlank(str)) {
            return;
        }
        //去掉'|"|;|\字元
        str = StringUtils.replace(str, "'", "");
        str = StringUtils.replace(str, "\"", "");
        str = StringUtils.replace(str, ";", "");
        str = StringUtils.replace(str, "\\", "");

        //轉換成小寫
        str = str.toLowerCase();

        //非法字元
        String[] keywords = {"master", "truncate", "insert", "select", "delete", "update", "declare", "alert", "create", "drop"};

        //判斷是否包含非法字元
        for (String keyword : keywords) {
            if (str.contains(keyword)) {
                throw new RuntimeException("查詢輸入條件存在非法字元!");
            }
        }
    }
}

mapper檔案中的動態sql

<if test="@org.apache.commons.lang3.StringUtils@isNotBlank(address)">
  and ord.address ${@com.alin.common.utils.MapperUtils@getQuery("ord.address",address)}
</if>

相關文章