(3).Mybatis动态sql的使用

Mybatis可以帮助我们方便的在SQL语句中实现某些逻辑。Mybatis动态sql语法常用的标签有：

1. 使用_parameter
2. if判断
3. switch判断（使用choose…when…otherwise语法标签 ）
4. where子句
5. set子句
6. trim语法
7. foreach迭代变量

_parameter的使用

_parameter 表示当前传入的参数，如果查询的时候传入的参数只有一个，则使用 _parameter
数据库mybatis1中的表users中现在有记录如下：

User [id=1, name=aa, phone=13411111111, email=aa@163.com]
User [id=2, name=bb, phone=13422222222, email=bb@163.com]
User [id=3, name=cc, phone=13433333333, email=cc@126.com]
User [id=4, name=dd, phone=13444444444, email=dd@126.com]

现要查询id值为4的user信息，返回的结果集为User对象，userMapper.xml文件中写法如下：

<select id="getUser1" parameterType="int" resultType="User">
    select id, name, phone, email from users where id = #{_parameter}
</select>

测试代码：

@Test
public void getUser1(){
    SqlSession session = MybatisUtils.getSession(false);
    User user = session.selectOne("com.qcc.mapping.userMapper.getUser1", 4);
    System.out.println(user);
}

运行结果：User [id=4, name=dd, phone=13444444444, email=dd@126.com]

---

if判断

if其实就是简单的条件判断，条件成立，就拼接sql语句，不成立就不拼接。

<!--
    获取所有id值大于2的User对象
 -->
<select id="getUsersByIf" parameterType="int" resultType="User">
    select id, name, phone, email from users where 1=1
    <if test="_parameter != null">
        and id > #{_parameter}
    </if>
</select> 

测试代码：

@Test
public void get1(){
    SqlSession session = MybatisUtils.getSession(false);
    List<User> userList = session.selectList("com.qcc.mapping.userMapper.getUsersByIf", 2);
    for (User user : userList) {
        System.out.println(user);
    }
}

运行结果：

User [id=3, name=cc, phone=13433333333, email=cc@126.com]
User [id=4, name=dd, phone=13444444444, email=dd@126.com]

在以上两个示例当中，都是选取id, name, phone, email这四个字段，以下还会多次出现，实际开发当中选择的字段更多，更复杂，浪费时间且容易写错，于是使用一个叫sql的标签定义这些字段，只需要在使用的时候引入即可。

<sql id="columns">
    id, name, phone, email
</sql>

引入的写法是：<include refid="columns"/>，它就相当于sqls标签中id, name, phone, email

---

switch判断

类似Java中的switch语法，在这里使用的是choose…when…otherwise的语法
使用示例如下：

<!-- 
    与java中的switch(value)...case;break;...default格式类似，
    当when元素中的条件满足的时候就拼接其中的内容到sql语句，同时就会跳出choose语句，
    即所有的when和otherwise条件中，只有一个会输出，
    当所有条件都不满足的时候就拼接otherwise中的内容。
    也就是说when和otherwise中的多个语句只会拼接上一个。所以这个语法用的不多。
 -->
<select id="getUser2" parameterType="User" resultType="User">
    select <include refid="columns"/> from users where 1=1
    <choose>
        <when test="id!=0">
            and id = #{id}
        </when>
        <when test="name!=null">
            and name = #{name}
        </when>
        <otherwise>
            and phone = #{phone}
        </otherwise>
    </choose>
</select>

测试代码：

@Test
public void getUser2(){
    SqlSession session = MybatisUtils.getSession(false);
    User user = new User();
    user.setId(2);
    user.setName("aa");
    List<User> list = session.selectList("com.qcc.mapping.userMapper.getUser2", user);
    System.out.println(list);
}

运行结果：

[User [id=2, name=bb, phone=13422222222, email=bb@163.com]]

发现查询出来的集合当中有一个User对象，对象的name属性值是bb，而不是aa，这就验证了有多个when条件成立的时候，它会自上往下执行拼接，一旦拼接上一个when中的语句后就不再继续执行了，跳出choose语句。

---

where子句

where语句的作用主要是简化SQL语句中where中的条件判断的
上边的if和switch的两个示例中select标签中写的sql语句中，在where关键字后面都有个1=1，这个是必需的，
如果没有，拼接上对应的条件后sql语法就不正确了，多了个and，
没有1=1时：select * from users where and id > #{_parameter}语法错误，多个and。
有了1=1后就是select * from users where 1=1 and id > #{_parameter}语法正确。
示例：

<!-- 
    1.where元素的作用是会在写入where元素的地方输出一个where，
    2.where子句会自动把拼接后的语句中多余的and去掉
    3.另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的，MyBatis会智能的帮你处理，
        如果所有的条件都不满足那么MyBatis就会查出所有的记录，
    4.如果输出后是and开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；
    5.在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上。
 -->
<select id="getUser3" parameterType="User" resultType="User">
    select <include refid="columns"/> from users
    <where>
        <if test="id!=0">
            and id = #{id}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
    </where>
</select>

测试代码：

@Test
public void getUser3(){
    SqlSession session = MybatisUtils.getSession(false);
    User user = new User();
    user.setId(2);
//  user.setName("aa");
    List<User> list = session.selectList("com.qcc.mapping.userMapper.getUser3", user);
    System.out.println(list);
}

测试结果：

[User [id=2, name=bb, phone=13422222222, email=bb@163.com]]

---

set子句

set子句一般更新数据库记录的操作
示例如下：

<!-- 
    set标签的作用主要是在包含的语句前输出一个set，
    然后如果包含的语句是以逗号结束的话将会把该逗号忽略，（即忽略赋值语句中最后一个，）
    如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段。
 -->
<update id="update1" parameterType="User">
    update users
    <set>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="phone != null">
            phone = #{phone},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </set>
    where id = #{id}
</update>

测试代码：

@Test
public void update1(){
    SqlSession session = MybatisUtils.getSession(true);//修改操作需要使用事务，提交事务
    User user = new User();
    user.setId(4);
    user.setName("update_4");
    user.setEmail("newUser@qq.com");
    System.out.println("传入的user对象：" + user);

    User user1 = session.selectOne("com.qcc.mapping.userMapper.getUser", 4);
    System.out.println("修改前：" + user1);

    /*用户的电话号码未重新赋值，则user的phone属性为null,因此保留不做修改*/
    session.update("com.qcc.mapping.userMapper.update1", user);

    //根据传入的id查询对应的实体对象，查询修改后的user对象
    User user2 = session.selectOne("com.qcc.mapping.userMapper.getUser", 4);
    System.out.println("修改后：" + user2);
}

测试结果：

传入的user对象：User [id=4, name=update_4, phone=null, email=newUser@qq.com]
修改前：User [id=4, name=dd, phone=13444444444, email=dd@126.com]
修改后：User [id=4, name=update_4, phone=13444444444, email=newUser@qq.com]

---

trim语法

trim添加需要的内容，去掉多余的内容
trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；
可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides；
正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where子句、set子句的功能，
语法格式如下：

<trim prefix="where" prefixOverrides="and |or ">
  <if ...>
    AND ...
  </if>
  <if ...>
    AND ...
  </if>
  <if ...>
    AND ...
  </if>
</trim>

它的作用是将生成的sql中添加 where 前缀，并将 sql 的 and 或者 or 前缀删除掉
在更新操作时：

<trim prefix="set" suffixOverrides=",">
  <if ...>
    account = #{account},
  </if>
  <if ...>
    password = #{password},
  </if>
  <if ...>
    name = #{name},
  </if>
</trim>

它的作用是将生成的sql中添加 set 前缀，并将 sql 的 最后一个，后缀去掉
查询时使用trim子句示例：

<select id="getUser4" parameterType="User" resultType="User">
    select <include refid="columns"/> from users
    <!-- 添加 where 关键字，去掉最前面的 and 关键字 -->
    <trim prefix="where" prefixOverrides="and">
        <if test="id != 0">
            and id = #{id}
        </if>
        <if test="name != null">
            and name = #{name}
        </if>
    </trim>
</select>

测试代码：

@Test
public void getUser4(){
    SqlSession session = MybatisUtils.getSession(false);
    User user = new User();
//  user.setId(1);
    user.setName("aa");
    List<User> list = session.selectList("com.qcc.mapping.userMapper.getUser4", user);
    System.out.println(list);
}

测试结果：

[User [id=1, name=aa, phone=13411111111, email=aa@163.com]]

可见与使用where 语法的情况是一样的。使用trim更新的操作类似，略。

---

foreach迭代变量

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合，比如批量删除。也可以使用于比如批量插入操作。
foreach元素的属性主要有item，index，collection，open，separator，close。

1、item表示集合中每一个元素进行迭代时的别名，
2、index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，
3、open表示该语句以什么开始，
4、separator表示在每次进行迭代之间以什么符号作为分隔符，
5、close表示以什么结束，
6、collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：
    （1）如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
    （2）如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
    （3）如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，
        实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，
        所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key。

foreach示例1：使用在in条件中，批量删除：

userMapper.xml 文件中映射 sql 的代码如下：
<!-- 传入数组ids进行批量删除操作 -->
<delete id="delBatch">
    delete from users where id in
    <!--如果传入的是单参数且参数类型是一个Array的时候，collection属性值为array-->
    <foreach collection="array" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</delete>

测试代码：（删除前先查询数据库中的所有记录并输出，进行删除操作后，再查询数据库中所有记录并输出）

@Test
public void delBatchDemo(){
    SqlSession session = MybatisUtils.getSession(true);
    /*查询数据库中所有记录*/
    List<User> list = session.selectList("com.qcc.mapping.userMapper.getAll");
    System.out.println("删除前：" + list);

    /*以下是批量删除操作*/
    int[] ids = new int[]{1, 3, 4};
    int result = session.delete("com.qcc.mapping.userMapper.delBatch", ids);
    if(result>0){
        System.out.println("删除" + result + "条记录成功！");
    }else{
        System.out.println("删除失败！");
    }

    /*批量删除操作完成后，再次查询数据库中所有记录*/
    list = session.selectList("com.qcc.mapping.userMapper.getAll");
    System.out.println("删除后：" + list);
}

测试结果：

删除前：[User [id=1, name=aa, phone=13411111111, email=aa@163.com], User [id=2, name=bb, phone=13422222222, email=bb@163.com], User [id=3, name=cc, phone=13433333333, email=cc@126.com], User [id=4, name=update_4, phone=13444444444, email=newUser@qq.com]]
删除3条记录成功！
删除后：[User [id=2, name=bb, phone=13422222222, email=bb@163.com]]

foreach示例2：使用foreach执行批量插入操作：
userMapper.xml 文件中批量插入的映射 sql 的代码如下：

<insert id="addBatchByList">
    insert into users(name, phone, email) values
    <!--如果传入的是单参数且参数类型是一个List的时候，collection属性值为list-->
    <foreach item="user" collection="list" separator=",">
        (#{user.name}, #{user.phone}, #{user.email})
    </foreach>
</insert>

在User实体类中添加无参和带参的构造方法，以便初始化User对象。

public User(String name, String phone, String email) {
    this.name = name;
    this.phone = phone;
    this.email = email;
}

public User() {}

批量插入的测试代码如下：

@Test
public void addBatch(){
    List<User> userList = new ArrayList<>();
    User user1 = new User("关羽", "13333333333", "GuanYu@shu.com");
    User user2 = new User("张飞", "13344444444", "ZhangFei@shu.com");
    User user3 = new User("赵云", "13355555555", "ZhaoYun@shu.com");
    User user4 = new User("黄忠", "13366666666", "HuangZhong@shu.com");
    userList.add(user1);
    userList.add(user2);
    userList.add(user3);
    userList.add(user4);
    SqlSession session = MybatisUtils.getSession(false);//传入false,意味着不会自动提交事务，等批量操作完成后再提交事务。提升操作数据库的效率。
    int result = session.insert("com.qcc.mapping.userMapper.addBatchByList", userList);
    if (result>0) {
        System.out.println("批量插入" + result + "条记录成功！");
    } else {
        System.out.println("批量插入失败！");
    }
    session.commit();//提交事务
    System.out.println("遍历数据库中所有的user信息");
    userList = session.selectList("com.qcc.mapping.userMapper.getAll");
    for (User user : userList) {
        System.out.println(user);
    }
}

批量插入的测试运行结果：

批量插入4条记录成功！
遍历数据库中所有的user信息
User [id=2, name=bb, phone=13422222222, email=bb@163.com]
User [id=5, name=关羽, phone=13333333333, email=GuanYu@shu.com]
User [id=6, name=张飞, phone=13344444444, email=ZhangFei@shu.com]
User [id=7, name=赵云, phone=13355555555, email=ZhaoYun@shu.com]
User [id=8, name=黄忠, phone=13366666666, email=HuangZhong@shu.com]

Mybatis动态sql常用的就以上这些了。