前面几篇博客我们通过实例讲解了用 mybatis 对一张表进行的 CRUD 操作, 但是我们发现写的 SQL 语句都比较简单, 如果有比较复杂的业务, 我们需要写复杂的 SQL 语句, 往往需要拼接, 而拼接 SQL , 稍微不注意, 由于引号, 空格等缺失可能都会导致错误.
那么怎么去解决这个问题呢? 这就是本篇所讲的使用 mybatis 动态 SQL, 通过 if, choose, when, otherwise, trim, where, set, foreach 等标签, 可组合成非常灵活的 SQL 语句, 从而在提高 SQL 语句的准确性的同时, 也大大提高了开发人员的效率.
我们以 User 表为例来说明:
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
1, 动态 SQL:if 语句
根据 username 和 sex 来查询数据. 如果 username 为空, 那么将只根据 sex 来查询; 反之只根据 username 来查询
首先不使用 动态 SQL 来书写
- "selectUserByUsernameAndSex"
- resultType="user" parameterType="com.ys.po.User">
写啥都可以, 但是不要空着, 如果有多个参数则必须写 pojo 类里面的属性 -->
select * from user where username=#{username} and sex=#{sex}
上面的查询语句, 我们可以发现, 如果 #{username} 为空, 那么查询结果也是空, 如何解决这个问题呢? 使用 if 来判断
- "selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
- select * from user where
- username=#{username}
- and sex=#{sex}
这样写我们可以看到, 如果 sex 等于 null, 那么查询语句为 select * from user where username=#{username}, 但是如果 usename 为空呢? 那么查询语句为 select * from user where and sex=#{sex}, 这是错误的 SQL 语句, 如何解决呢? 请看下面的 where 语句
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
2, 动态 SQL:if+where 语句
- "selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
- select * from user
- username=#{username}
- and sex=#{sex}
这个 "where" 标签会知道如果它包含的标签中有返回值的话, 它就插入一个'where'. 此外, 如果标签返回的内容是以 AND 或 OR 开头的, 则它会剔除掉.
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
3, 动态 SQL:if+set 语句
同理, 上面的对于查询 SQL 语句包含 where 关键字, 如果在进行更新操作的时候, 含有 set 关键词, 我们怎么处理呢?
- "updateUserById" parameterType="com.ys.po.User">
- update user u
- u.username = #{username},
- u.sex = #{sex}
- where id=#{id}
这样写, 如果第一个条件 username 为空, 那么 sql 语句为: update user u set u.sex=? where id=?
如果第一个条件不为空, 那么 sql 语句为: update user u set u.username = ? ,u.sex = ? where id=?
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
4, 动态 SQL:choose(when,otherwise) 语句
有时候, 我们不想用到所有的查询条件, 只想选择其中的一个, 查询条件有一个满足即可, 使用 choose 标签可以解决此类问题, 类似于 Java 的 switch 语句
- "selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
- select * from user
- "id !='' and id != null">
- id=#{id}
- "username !='' and username != null">
- and username=#{username}
- and sex=#{sex}
也就是说, 这里我们有三个条件, id,username,sex, 只能选择一个作为查询条件
如果 id 不为空, 那么查询语句为: select * from user where id=?
如果 id 为空, 那么看 username 是否为空, 如果不为空, 那么语句为 select * from user where username=?;
如果 username 为空, 那么查询语句为 select * from user where sex=?
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
5, 动态 SQL:trim 语句
trim 标记是一个格式化的标记, 可以完成 set 或者是 where 标记的功能
, 用 trim 改写上面第二点的 if+where 语句
- "selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
- select * from user
- username=#{username}
- and sex=#{sex}
- -->
- "where" prefixOverrides="and | or">
- and username=#{username}
- and sex=#{sex}
prefix: 前缀
prefixoverride: 去掉第一个 and 或者是 or
, 用 trim 改写上面第三点的 if+set 语句
- "updateUserById" parameterType="com.ys.po.User">
- update user u
- u.username = #{username},
- u.sex = #{sex}
- -->
- "set" suffixOverrides=",">
- u.username = #{username},
- u.sex = #{sex},
- where id=#{id}
suffix: 后缀
suffixoverride: 去掉最后一个逗号 (也可以是其他的标记, 就像是上面前缀中的 and 一样)
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
6, 动态 SQL: SQL 片段
有时候可能某个 sql 语句我们用的特别多, 为了增加代码的重用性, 简化代码, 我们需要将这些代码抽取出来, 然后使用时直接调用.
比如: 假如我们需要经常根据用户名和性别来进行联合查询, 那么我们就把这个代码抽取出来, 如下:
- "selectUserByUserNameAndSexSQL">
- AND username = #{username}
- AND sex = #{sex}
引用 sql 片段
- "selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
- select * from user
- "where" prefixOverrides="and | or">
- "selectUserByUserNameAndSexSQL">
注意:, 最好基于 单表来定义 sql 片段, 提高片段的可重用性
, 在 sql 片段中不要包括 where
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
7, 动态 SQL: foreach 语句
需求: 我们需要查询 user 表中 id 分别为 1,2,3 的用户
sql 语句: select * from user where id=1 or id=2 or id=3
select * from user where id in (1,2,3)
, 建立一个 UserVo 类, 里面封装一个 List<Integer> ids 的属性
- package com.ys.vo;
- import java.util.List;
- public class UserVo {
- // 封装多个用户的 id
- private List ids;
- public List getIds() {
- return ids;
- }
- public void setIds(List ids) {
- this.ids = ids;
- }
- }
, 我们用 foreach 来改写 select * from user where id=1 or id=2 or id=3
- "selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
- select * from user
collection: 指定输入对象中的集合属性
item: 每次遍历生成的对象
open: 开始遍历时的拼接字符串
close: 结束时拼接的字符串
separator: 遍历对象之间需要拼接的字符串
- select * from user where 1=1 and (id=1 or id=2 or id=3)
- -->
- "ids" item="id" open="and (" close=")" separator="or">
- id=#{id}
测试:
- // 根据 id 集合查询 user 表数据
- @Test
- public void testSelectUserByListId(){
- String statement = "com.ys.po.userMapper.selectUserByListId";
- UserVo uv = new UserVo();
- List ids = new ArrayList<>();
- ids.add(1);
- ids.add(2);
- ids.add(3);
- uv.setIds(ids);
- List listUser = session.selectList(statement, uv);
- for(User u : listUser){
- System.out.println(u);
- }
- session.close();
- }
, 我们用 foreach 来改写 select * from user where id in (1,2,3)
- "selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
- select * from user
collection: 指定输入对象中的集合属性
item: 每次遍历生成的对象
open: 开始遍历时的拼接字符串
close: 结束时拼接的字符串
separator: 遍历对象之间需要拼接的字符串
- select * from user where 1=1 and id in (1,2,3)
- -->
- "ids" item="id" open="and id in (" close=")" separator=",">
- #{id}
回到顶部 http://www.cnblogs.com/ysocean/p/7289529.html#_labelTop
8, 总结
其实动态 sql 语句的编写往往就是一个拼接的问题, 为了保证拼接准确, 我们最好首先要写原生的 sql 语句出来, 然后在通过 mybatis 动态 sql 对照着改, 防止出错.
来源: http://www.bubuko.com/infodetail-2680422.html