mybatis 传参 0 与 判断条件 ！=‘‘ 问题及处理

记录开发中遇到的问题：背景

Mapper中有一个接口的传入类型为map：

/**
* 会员列表
*/
List<Map<String, Object>> memberList(@Param("params")  Map<String, Object> params);

对应的Mapper.xml：

<select id="memberList" resultType="map">
        SELECT 
        um.member_id memberId, 
        um.nickname,
        um.phone,
        um.gender,
        um.birthday, 
        DATE_FORMAT( um.create_time, '%Y-%m-%d %T' ) createTime
        FROM
        user_member um 
        WHERE
        um.del_flag = 0 
        <if test="params.commanderFlag != null and params.commanderFlag != ''">
            <choose>
                <when test="params.commanderFlag == 0">
                    AND NOT EXISTS (SELECT 1 FROM user_village_commander uvc WHERE uvc.del_flag = 0 and  uvc.member_id= um.member_id )
                </when>
                <when test="params.commanderFlag == 1">
                    AND EXISTS (SELECT 1 FROM user_village_commander uvc WHERE uvc.del_flag = 0 and  uvc.member_id= um.member_id )
                </when>
            </choose>
        </if>
        ORDER BY um.member_id DESC
    </select>

问题一：当params参数（commanderFlag）为int类型且值为0 时，

无法进入判断语句，然后log打印出的sql中并没有执行对应的sql。

原因：当mapper接口传入的参数类型为Integer值为0时，会默认将这个值转换为空字符串！

解决办法：

去掉【参数 != ‘‘“】的非空判断，因为Integer类型的值除了0也不可能为空字符串

<if test="params.commanderFlag != null ">

不用integer类型，直接换成string类型

现在对应的Mapper.xml：

<select id="memberList" resultType="map">
        SELECT 
        um.member_id memberId, 
        um.nickname,
        um.phone,
        um.gender,
        um.birthday, 
        DATE_FORMAT( um.create_time, '%Y-%m-%d %T' ) createTime
        FROM
        user_member um 
        WHERE
        um.del_flag = 0 
        <if test="params.commanderFlag != null ">
            <choose>
                <when test="params.commanderFlag == 0">
                    AND NOT EXISTS (SELECT 1 FROM user_village_commander uvc WHERE uvc.del_flag = 0 and  uvc.member_id= um.member_id )
                </when>
                <when test="params.commanderFlag == 1">
                    AND EXISTS (SELECT 1 FROM user_village_commander uvc WHERE uvc.del_flag = 0 and  uvc.member_id= um.member_id )
                </when>
            </choose>
        </if>
        ORDER BY um.member_id DESC
    </select>

问题二：当params参数（commanderFlag）为 " " 时，

打印的sql显示，进入了判断语句里<when test="params.commanderFlag == 0">

原因：当mapper接口传入的参数值为“”（空字串）时，会默认匹配==0 这个条件！

解决办法： 

       1、简单粗暴的使用lambde表达式

params.entrySet().removeIf(entry -> Objects.isNull(entry.getValue()));

        2、在params传入前，遍历map值移除空字符串（兼容多规格，移除key和value为空的数据）

	/**
     * 过滤map集合中key或value为空的值 
     */
    public static void removeNullParameters(Map<String, Object> params) {
        if (isEmpty(params)) {
            return;
        }
        Set set = params.keySet();
        for (Iterator iterator = set.iterator(); iterator.hasNext(); ) {
            //removeNullKey
            Object obj = iterator.next();
            if (isEmpty(obj)) {
                iterator.remove();
                continue;
            }
            //removeNullValue
            Object value = params.get(obj);
            if (isEmpty(value)) {
                iterator.remove();
            }
        }
    }
	/**
     * 校验任意对象是否为空
     * （集合为null或size==0）
     * (字符串为null或字符串为“null”或字符串去除空格为“”)
     */
    public static boolean isEmpty(Object obj) {
        String nullStr = "null";
        String undefineStr = "undefined";
        if (obj == null) {
            return true;
        }
        if (obj instanceof List) {
            return ((List) obj).isEmpty() ? true : ((List) obj).get(0) == null;
        }
        if (obj instanceof Collection) {
            return ((Collection) obj).isEmpty();
        }
        if (obj instanceof Map) {
            return ((Map) obj).isEmpty();
        }
        if (obj.getClass().isArray()) {
            List<Object> arr2List = Arrays.asList(obj);
            return arr2List.isEmpty();
        }
        if (obj instanceof String) {
            return "".equals(obj.toString().trim()) || nullStr.equalsIgnoreCase(obj.toString().trim()) || undefineStr.equalsIgnoreCase(obj.toString().trim());
        }
        return false;
    }