前言:
有小伙伴反馈使用org.apache.ibatis.annotations.Select 进行sql动态拼接时入参为空查询失败。
写了个简单的案例:
1.使用<script></script>标签包围
@Select("<script>"+
"SELECT name,phone,addr FROM `user` WHERE 1 = 1 AND id= #{id}"+
"<if test='time!= null'> AND DATE_FORMAT(time,'%Y%m%d') = DATE_FORMAT(#{time}, '%Y%m%d') </if>"+
"</script>")
List<User> selectUserById(@Param("id") Long id, @Param("time") String time);
//Controller调用
@GetMapping("/user/test")
public Object test(@RequestParam("id") Long id,@RequestParam("time") String time) {
return Class.selectUserById(id, time);
}
当访问/user/test?id=1&time= 返回结果为:NULL
2.解决办法(加入空值判断,不够仔细^_^)
@Select("<script>"+
"SELECT name,phone,addr FROM `user` WHERE 1 = 1 AND id= #{id}"+
"<if test='time != null and time != \"\"'> AND DATE_FORMAT(time,'%Y%m%d') = DATE_FORMAT(#{time}, '%Y%m%d') </if>"+
"</script>")
List<User> selectUserById(@Param("id") Long id, @Param("time") String time);
OR:
@Select({"<script>",
"SELECT name,phone,addr FROM `user` WHERE 1 = 1 AND id= #{id}",
"<if test='time != null and time != \"\"'> AND DATE_FORMAT(time,'%Y%m%d') = DATE_FORMAT(#{time}, '%Y%m%d') </if>",
"</script>"})
List<User> selectUserById(@Param("id") Long id, @Param("time") String time);
当访问/user/test?id=1&time= 返回正常数据