ZB-038-03MyBatis动态sql

动态sql——MyBatis的灵魂

  • <if>
  • <choose>
  • <foreach>
  • <script>

Dynamic SQL

selectUserByName需求

  • if
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 如果你这样 ，name 没有时会报错
<select id="selectUserByName" resultType="User">
    select * from user
    where
    <if test="name != null">
        name like #{name}
    </if>
</select>

# 它会拼成这样
# select * from user where

# 你可以这样
<select id="selectUserByName" resultType="User">
    select * from user
    <if test="name != null">
        where name like #{name}
    </if>
</select>
  • choose
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<select id="chooseUser" resultType="User">
    select * from user
    <choose>
        <when test="name =='wangwu'">
            where name = 'wangwu'
        </when>
        <otherwise>
            where name = 'zhangsan'
        </otherwise>
    </choose>
</select>

// 这里的坑在这
<when test="name =='wangwu'"> 

不要写成这样 
不要写成这样 
不要写成这样 
<when test="name ==wangwu">
这样它会在运行时把 wangwu 当作查询User的字段而不是字符串
  • where / set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<select id="findActiveBlogLike" resultType="Blog">
    select * from Blog
    where
    <if test="state != null">
        name like #{state}
    </if>
</select>

如果条件都state = null,不满足就会导致语法错误
SELECT * FROM Blog
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>

如果条件不满足它就没有where

set语句也能动态

1
2
3
4
5
6
7
8
9
10
<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>
  • 批量操作 foreach
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// UserMapper.xml
// item循环的对象
// index索引
// collection 结果集
<select id="selectIds" resultType="User">
    select *
    from user where id in
    <foreach item="item" index="index" collection="ids"
             open="(" separator="," close=")">
      #{item}
    </foreach>
</select>

// Sql.java
Map<String,Object> param = new HashMap<>();
param.put("ids", Arrays.asList(1,2));
List<User> resUser = session.selectList("com.sql.xml.UserMapper.selectIds",param);
System.out.println(resUser);
  • script 不常用

把xml里的内容放在注解里

1
2
3
4
5
6
7
8
9
10
11
@Update({"<script>",
  "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}",
  "</script>"})
void updateAuthorValues(Author author);

MyBatis实战

涵盖内容

  • CRUD
  • 联表操作
  • association 关系映射