本节讲 mybatis 应对动态SQL的场景。
动态SQL是指 “条件" 不固定的SQL,对比于一般的SQL,动态SQL会有一个或者多个条件/参数。
比如:场景:查询的时候,如果用户输入了姓名,就按姓名模糊查询;同时又输入了邮箱,就再增加一个条件按姓名+邮箱两个条件查询。
传统的拼接SQL很费力,拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。
借助功能强大的基于 OGNL 的表达式,MyBatis 3 大大精简了元素种类,有下列这些:
场景:查询的时候,如果用户输入了姓名,就按姓名模糊查询;同时又输入了邮箱,就再增加一个条件按姓名+邮箱两个条件查询。
<select id="selectDynamic" resultType="cn.zyfvir.SysUser">
select * from sys_user
<where>
<if test="userName != null">
user_name like #{userName}"%"
</if>
<if test="userEmail != null">
and user_email like #{userEmail}"%"
</if>
</where>
</select>
对应的java代码示例:
private static void testSelect(SqlSessionFactory sqlSessionFactory) {
try (SqlSession session = sqlSessionFactory.openSession(true)) {
SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class);
// 仅一个参数
SysUser para = new SysUser();
para.userName = "jack";
List<SysUser> list1 = sysUserMapper.selectDynamic(para);
printf("list=%s", list1.size());
//两个参数都有
SysUser para2 = new SysUser();
para2.userName = "jack";
para2.userEmail = "jack";
List<SysUser> list2 = sysUserMapper.selectDynamic(para2);
printf("list=%s", list2.size());
// 只有 email
SysUser para3 = new SysUser();
para3.userEmail = "jack";
List<SysUser> list3 = sysUserMapper.selectDynamic(para3);
printf("list=%s", list3.size());
}
}
场景:当 sys_user 的 用户名有值(不是 Null )时,就把用户名改了。如果 email 有值,就同时修改 email。示例:
<update id="updateUserDynamic">
UPDATE sys_user
<set>
<if test="userName != null">
user_name= #{userName},
</if>
<if test="userPassword != null">
user_password= #{userPassword},
</if>
<if test="userEmail != null">
user_email=#{userEmail},
</if>
<if test="userInfo != null">
user_info= #{userInfo}
</if>
</set>
WHERE id=#{id}
</update>
场景:如果 sys_user 对象的 名称有值,就插入,如果 邮箱地址有值,也插入。示例是这样的:
<!-- 插入后获得自增的主键 -->
<insert id="insertDynamic" useGeneratedKeys="true" keyProperty="id">
INSERT INTO sys_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null">
user_name,
</if>
<if test="userPassword != null">
user_password,
</if>
<if test="userEmail != null">
user_email,
</if>
<if test="userInfo != null">
user_info,
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userName != null">
#{userName},
</if>
<if test="userPassword != null">
#{userPassword},
</if>
<if test="userEmail != null">
#{userEmail},
</if>
<if test="userInfo != null">
#{userInfo}),
</if>
</trim>
</insert>
场景:假设有一个 list 集合,里面有很多元素,选择一个快速的方式插入到数据库。 我们可以使用 foreach 标签来生成一个 动态插入的SQL,示例:
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
INSERT INTO sys_user (user_name,user_password) values
<foreach collection="list" item="item" separator=",">
( #{item.userName}, #{item.userPassword} )
</foreach>
</insert>
场景:一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
和 where 配合使用的示例:
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
示例展示了:传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形。若两者都没有传入,就返回标记为 featured=1 的 BLOG。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>
我的代码示例:https://github.com/vir56k/java_demo/tree/master/mybatisdemo5_dynamic_sql