Mybatis的动态sql传入int类型的0时不会当成参数查询

动态SQL的代码如下

@Select("<script>SELECT * FROM User WHERE DELETED = 0 " +
        "<if test= \"id != null and id != ''\"> and id = #{id} </if>" +
        "<if test= \"userId != null and userId != ''\"> and user_id = #{userId} </if>" +
        "<if test= \"userName != null and userName != ''\"> and user_name like CONCAT('%', #{userName},'%')  </if>"+
        "<if test= \"level != null and level != ''\"> and level = #{level} </if>" +
        "</script>")
List<User> selectAllUser(Map<String,Object> map);
           

其中注意这一行

"<if test= \"level != null and level != ''\"> and level = #{level} </if>"
           

 当前端传入值 level = 0 的时候，打印出来的SQL 如下：

问题原因就是当 level 的值为 0 的时候，因为其数据类型为 Integer，所以这个判断是为 false 的，就是说 int 类型的判断，Mybatis是认定 0 为 '' 的值为 false。

修改的方法为：

// 将 and level != '' 去掉
"<if test= \"level != null\"> and level = #{level} </if>" +
           

 重新执行

另外

将 level 的判断修改为 level == 0 判断也是成立的

代码如下：

@Select("<script>SELECT * FROM User WHERE DELETED = 0 " +
        "<if test= \"id != null and id != ''\"> and id = #{id} </if>" +
        "<if test= \"userId != null and userId != ''\"> and user_id = #{userId} </if>" +
        "<if test= \"userName != null and userName != ''\"> and user_name like CONCAT('%', #{userName},'%')  </if>"+
        "<if test= \"level == 0 \"> and level = #{level} </if>" +
        "</script>")
List<User> selectAllUser(Map<String,Object> map);