MyBatis笔记12—动态 SQL

阅读完需：约 9 分钟

动态 SQL

动态 SQL 是 MyBatis 中非常强大的一个功能。例如一些常见的查询场景：

• 查询条件不确定
• 批量插入
• ….

这些类似需求，我们都可以通过 MyBatis 提供的动态 SQL 来解决。

1.if

if 是一个判断节点，如果满足某个条件，节点中的 SQL 就会生效。例如分页查询，要传递两个参数，页码和查询的记录数，如果这两个参数都为 null，那我就查询所有。

MyBatis 中提供的动态 SQL 节点非常多。

我们首先来定义接口方法：

List<User> getUserByPage(@Param("start") Integer start, @Param("count") Integer count);

接口定义成功后，接下来在 XML 中定义 SQL：

<select id="getUserByPage" resultType="org.javaboy.mybatis.model.User">
    select * from user
    <if test="start !=null and count!=null">
        limit #{start},#{count}
    </if>
</select>

if 节点中，test 表示判断条件，如果判断结果为 true，则 if 节点的中的 SQL 会生效，否则不会生效。也就是说，在方法调用时，如果分页的两个参数都为 null，则表示查询所有数据：

public class Main2 {
    public static void main(String[] args) {
        SqlSessionFactory instance = SqlSessionFactoryUtils.getInstance();
        SqlSession sqlSession = instance.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = mapper.getUserByPage(null, null);
        System.out.println(list);
        list = mapper.getUserByPage(2, 2);
        System.out.println(list);
        sqlSession.commit();
    }
}

2.where

where 用来处理查询参数。例如我存在下面一个查询函数：

List<User> getUserByUsernameAndId(@Param("id") Integer id, @Param("name") String name);

这个查询的复杂之处在于：每个参数都是可选的，如果 id 为 null，则表示根据 name 查询，name 为 null，则表示根据 id 查询，两个都为 null，表示查询所有。

<select id="getUserByUsernameAndId" resultType="org.javaboy.mybatis.model.User">
    select * from user
    <where>
        <if test="id!=null">
            and id>#{id}
        </if>
        <if test="name!=null">
            and username like concat('%',#{name},'%')
        </if>
    </where>
</select>

用 where 节点将所有的查询条件包起来，如果有满足的条件，where 节点会自动加上，如果没有，where 节点也将不存在，在有满足条件的情况下，where 还会自动处理 and 关键字。

public class Main2 {
    public static void main(String[] args) {
        SqlSessionFactory instance = SqlSessionFactoryUtils.getInstance();
        SqlSession sqlSession = instance.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = mapper.getUserByUsernameAndId(2, "java");
        System.out.println(list);
        list = mapper.getUserByUsernameAndId(null, "javaboy");
        System.out.println(list);
        list = mapper.getUserByUsernameAndId(5, null);
        System.out.println(list);
        list = mapper.getUserByUsernameAndId(null, null);
        System.out.println(list);
    }
}

3.foreach

foreach 用来处理数组/集合参数。

例如，我们有一个批量查询的需求：

List<User> getUserByIds(@Param("ids")Integer[] ids);

对应的 XML 如下：

<select id="getUserByIds" resultType="org.javaboy.mybatis.model.User">
    select * from user where id in
    <foreach collection="ids" open="(" close=")" item="id" separator=",">
        #{id}
    </foreach>
</select>

在 mapper 中，通过 foreach 节点来遍历数组，collection 表示数组变量，open 表示循环结束后，左边的符号，close 表示循环结束后，右边的符号，item 表示循环时候的单个变量，separator 表示循环的元素之间的分隔符。

注意，默认情况下，无论你的数组/集合参数名字是什么，在 XML 中访问的时候，都是 array，开发者可以通过 @Param 注解给参数重新指定名字。

例如我还有一个批量插入的需求：

Integer batchInsertUser(@Param("users") List<User> users);

然后，定义该方法对应的 mapper：

<insert id="batchInsertUser">
    insert into user (username,address) values 
    <foreach collection="users" separator="," item="user">
        (#{user.username},#{user.address})
    </foreach>
</insert>

然后，在 Main 方法中进行测试：

public class Main2 {
    public static void main(String[] args) {
        SqlSessionFactory instance = SqlSessionFactoryUtils.getInstance();
        SqlSession sqlSession = instance.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = new ArrayList<>();
        User u1 = new User();
        u1.setUsername("zhangsan");
        u1.setAddress("shenzhen");
        users.add(u1);
        User u2 = new User();
        u2.setUsername("lisi");
        u2.setAddress("广州");
        users.add(u2);
        mapper.batchInsertUser(users);
        sqlSession.commit();
    }
}

例2：

将数据库中前三个数据的id修改为1,2,3；

需求：我们需要查询 blog 表中 id 分别为1,2,3的博客信息

1、编写接口

List<Blog> queryBlogForeach(Map map);

2、编写SQL语句

<select id="queryBlogForeach" parameterType="map" resultType="blog">
  select * from blog
   <where>
       <!--
       collection:指定输入对象中的集合属性
       item:每次遍历生成的对象
       open:开始遍历时的拼接字符串
       close:结束时拼接的字符串
       separator:遍历对象之间需要拼接的字符串
       select * from blog where 1=1 and (id=1 or id=2 or id=3)
     -->
       <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
          id=#{id}
       </foreach>
   </where>
</select>

3、测试

@Test
public void testQueryBlogForeach(){
   SqlSession session = MybatisUtils.getSession();
   BlogMapper mapper = session.getMapper(BlogMapper.class);

   HashMap map = new HashMap();
   List<Integer> ids = new ArrayList<Integer>();
   ids.add(1);
   ids.add(2);
   ids.add(3);
   map.put("ids",ids);

   List<Blog> blogs = mapper.queryBlogForeach(map);

   System.out.println(blogs);

   session.close();
}

4.sql 片段

大家知道，在 SQL 查询中，一般不建议写 *，因为 select * 会降低查询效率。但是，每次查询都要把字段名列出来，太麻烦。这种使用，我们可以利用 SQL 片段来解决这个问题。

例如，我们先在 mapper 中定义一个 SQL 片段：

<sql id="Base_Column">
    id,usename,address
</sql>

然后，在其他 SQL 中，就可以引用这个变量：

<select id="getUserByIds" resultType="org.javaboy.mybatis.model.User">
    select <include refid="Base_Column" /> from user where id in
    <foreach collection="ids" open="(" close=")" item="id" separator=",">
        #{id}
    </foreach>
</select>

5.set

set 关键字一般用在更新中。因为大部分情况下，更新的字段可能不确定，如果对象中存在该字段的值，就更新该字段，不存在，就不更新。例如如下方法：

Integer updateUser(User user);

现在，这个方法的需求是，根据用户 id 来跟新用户的其他属性，所以，user 对象中一定存在 id，其他属性则不确定，其他属性要是有值，就更新，没值（也就是为 null 的时候），则不处理该字段。

我们结合 set 节点，写出来的 sql 如下：

<update id="updateUser" parameterType="org.javaboy.mybatis.model.User">
    update user
    <set>
        <if test="username!=null">
            username = #{username},
        </if>
        <if test="address!=null">
            address=#{address},
        </if>
        <if test="favorites!=null">
            favorites=#{favorites},
        </if>
    </set>
    where id=#{id};
</update>

6.trim标签

事实上trim标签有点类似于replace效果。

trim 属性

prefix：表示在trim标签内sql语句加上前缀xxx

suffix：表示在trim标签内sql语句加上后缀xxx

suffixOverrides：表示去除最后一个后缀xxx

prefixOverrides：去掉指定的前缀

<insert id="insertUserInfoByTrim" parameterType="com.cckj.bean.UserInfo">
    INSERT into userinfo
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="username !=null and username != ''">
            username,
        </if>
        <if test="sex !=null and sex != ''">
            sex,
        </if>
        <if test="age !=null">
            age,
        </if>
        <if test="phone !=null and phone != ''">
            phone,
        </if>
        <if test="address !=null and address != ''">
            address,
        </if>
    </trim>

    <trim prefix="values(" suffix=")" suffixOverrides=",">
        <if test="username !=null and username != ''">
         #{username,jdbcType=VARCHAR},
        </if>
        <if test="sex !=null and sex != ''">
            #{sex,jdbcType=CHAR},
        </if>
        <if test="age !=null">
            #{age,jdbcType=INTEGER},
        </if>
        <if test="phone !=null and phone != ''">
            #{phone,jdbcType=VARCHAR},
        </if>
        <if test="address !=null and address != ''">
            #{address,jdbcType=VARCHAR},
        </if>
    </trim>
</insert>

7. choose

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句

1、编写接口方法

List<Blog> queryBlogChoose(Map map);

2、sql配置文件

<select id="queryBlogChoose" parameterType="map" resultType="blog">
  select * from blog
   <where>
       <choose>
           <when test="title != null">
                title = #{title}
           </when>
           <when test="author != null">
              and author = #{author}
           </when>
           <otherwise>
              and views = #{views}
           </otherwise>
       </choose>
   </where>
</select>

3、测试类

@Test
public void testQueryBlogChoose(){
   SqlSession session = MybatisUtils.getSession();
   BlogMapper mapper = session.getMapper(BlogMapper.class);

   HashMap<String, Object> map = new HashMap<String, Object>();
   map.put("title","Java如此简单");
   map.put("author","狂神说");
   map.put("views",9999);
   List<Blog> blogs = mapper.queryBlogChoose(map);

   System.out.println(blogs);

   session.close();
}