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);