(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=[e-mail]]User [id=2, name=bb, phone=13422222222, email=[e-mail]]User [id=3, name=cc, phone=13433333333, email=[e-mail]]User [id=4, name=dd, phone=13444444444, email=[e-mail]]

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

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

测试代码：

@Testpublic 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=[e-mail]]

---

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> 

测试代码：

@Testpublic 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=[e-mail]]User [id=4, name=dd, phone=13444444444, email=[e-mail]]

在以上两个示例当中，都是选取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>

测试代码：

@Testpublic 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=[e-mail]]]

发现查询出来的集合当中有一个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>

测试代码：

@Testpublic 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=[e-mail]]]

---

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>

测试代码：

@Testpublic void update1(){    SqlSession session = MybatisUtils.getSession(true);//修改操作需要使用事务，提交事务    User user = new User();    user.setId(4);    user.setName("update_4");    user.setEmail("[e-mail]");    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=[e-mail]]修改前：User [id=4, name=dd, phone=13444444444, email=[e-mail]]修改后：User [id=4, name=update_4, phone=13444444444, email=[e-mail]]

---

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>

测试代码：

@Testpublic 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=[e-mail]]]

可见与使用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>

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

@Testpublic 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=[e-mail]], User [id=2, name=bb, phone=13422222222, email=[e-mail]], User [id=3, name=cc, phone=13433333333, email=[e-mail]], User [id=4, name=update_4, phone=13444444444, email=[e-mail]]]删除3条记录成功！删除后：[User [id=2, name=bb, phone=13422222222, email=[e-mail]]]

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() {}

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

@Testpublic void addBatch(){    List<User> userList = new ArrayList<>();    User user1 = new User("关羽", "13333333333", "[e-mail]");    User user2 = new User("张飞", "13344444444", "[e-mail]");    User user3 = new User("赵云", "13355555555", "[e-mail]");    User user4 = new User("黄忠", "13366666666", "[e-mail]");    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=[e-mail]]User [id=5, name=关羽, phone=13333333333, email=[e-mail]]User [id=6, name=张飞, phone=13344444444, email=[e-mail]]User [id=7, name=赵云, phone=13355555555, email=[e-mail]]User [id=8, name=黄忠, phone=13366666666, email=[e-mail]]

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