mybatis学习笔记(day6) 动态sql mybatis核心,对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接、组装。
Choose
< select id= "findUserCount" parameterType= "po.UserQueryVo" resultType= "int"> SELECT count (* ) FROM users.user < where > < if test= "userCustom!=null"> < choose> < ! < when test= "userCustom.sex!=null and userCustom.sex != ''"> < ! AND user.sex= #{userCustom.sex} < / when > < when test= "userCustom.username!=null and userCustom.username != ''"> AND user.username LIKE '%${userCustom.username}%' < / when > < otherwise> < ! < / otherwise> < / choose> < / if> < ! < ! < ! < ! < ! < ! < ! < ! < / where > < / select >
if判断
< ! < select id= "findUserList" parameterType= "com.iot.mybatis.po.UserQueryVo" resultType= "com.iot.mybatis.po.UserCustom"> SELECT * FROM user < ! < where > < if test= "userCustom!=null"> < if test= "userCustom.sex!=null and userCustom.sex != '' "> AND user.sex= #{userCustom.sex} < / if> < if test= "userCustom.username!=null and userCustom.username != '' "> AND user.username LIKE '%${userCustom.username}%' < / if> < / if> < / where > < / select > < ! < select id= "findUserCount" parameterType= "com.iot.mybatis.po.UserQueryVo" resultType= "int"> SELECT count (* ) FROM user < where > < if test= "userCustom!=null"> < if test= "userCustom.sex!=null and userCustom.sex != '' "> AND user.sex= #{userCustom.sex} < / if> < if test= "userCustom.username!=null and userCustom.username != '' "> AND user.username LIKE '%${userCustom.username}%' < / if> < / if> < / where > < / select >
1.注释掉testFindUserList()方法中的userCustom.setUsername("张三");
userQueryVo.setUserCustom(userCustom);
可以看到sql语句变为了SELECT * FROM user
sql片段(重点) 将上边实现的动态sql判断代码块抽取出来,组成一个sql片段。其它的statement中就可以引用sql片段。
<sql id ="query_user_where" > <if test ="userCustom!=null" > <if test ="userCustom.sex!=null and userCustom.sex!=''" > AND user.sex = #{userCustom.sex} </if > <if test ="userCustom.username!=null and userCustom.username!=''" > AND user.username LIKE '%${userCustom.username}%' </if > </if > </sql >
< ! < select id= "findUserList" parameterType= "com.iot.mybatis.po.UserQueryVo" resultType= "com.iot.mybatis.po.UserCustom"> SELECT * FROM user < ! < where > < ! < include refid= "query_user_where"> < / include> < ! < / where > < / select >
相当于只用写一遍sql 然后让别的pojo全部去调用sql包裹住的sql语句
输出是一样的
foreach标签 向sql传递数组或List ,mybatis使用foreach解析
在用户查询列表和查询总数的statement中增加多个id输入查询。两种方法,sql语句如下:
SELECT * FROM USER WHERE id=1 OR id=10 OR id=16
SELECT * FROM USER WHERE id IN(1,10,16)
一个使用OR,一个使用IN
在输入参数类型中添加List<Integer> ids传入多个id
public class UserQueryVo { private List<Integer> ids; }
<if test ="ids!=null" > <foreach collection ="ids" item ="user_id" open ="AND (" close =")" separator ="or" > id=#{user_id} </foreach > </if >