MyBatis的动态SQL

目录

1.什么是动态sql

2.if标签

3.where标签

4.set标签

5.trim标签

5.1trim标签可以代替where标签、set标签

5.2trim主要用于动态向表中插入数据

6.foreach标签

7.sql标签

8.include

9.choose(when,otherwise) 语句

10.?bind 标签?

---

1.什么是动态sql

MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。

Mybatis 动态 sql 可以让我们在 Xml 映射文件内，以标签的形式编写动态 sql，完成逻辑判断和动态拼接 sql 的功能。

2.if标签

我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询，如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。

if 语句使用方法简单，常常与 test 属性联合使用。语法如下。

<if test="判断条件">    SQL语句</if>

mapper接口：

public interface UserDao {

    //复杂条件查询
    public List<User> findByUser(User user);
}

xml映射文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.by.dao.UserDao">
    <select id="findByUser" resultType="user">
        select * from user where 1=1
        <if test="username!=null and username != ''">
            and username=#{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex != ''">
            and sex=#{sex}
        </if>
        <if test="address!=null and address != ''">
            and address=#{address}
        </if>
    </select>
</mapper>

where的条件为1=1

测试类：

    @Test
    public void testFindAll(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setSex("男");
        user.setAddress("香港");
        List<User> userList = userDao.findByUser(user);
        for(User u : userList){
            System.out.println(u);
        }
    }

3.where标签

为了简化上面where 1=1的条件拼装，我们可以使用where标签将if标签代码块包起来，将1=1条件去掉。

若查询条件的开头为 “AND” 或 “OR”，where会将他们去掉

mapper映射文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.by.dao.UserDao">
    <select id="findByUser" resultType="User">
        select * from user where and username=#{username}
        <!--where标签将if标签代码块包起来,并去掉开头 “AND” 或 “OR”-->
        <where>
            <if test="username!=null and username != ''">
                and username=#{username}
            </if>
            <if test="birthday!=null">
                and birthday=#{birthday}
            </if>
            <if test="sex!=null and sex != ''">
                and sex=#{sex}
            </if>
            <if test="address!=null and address != ''">
                and address=#{address}
            </if>
        </where>
    </select>
</mapper>

4.set标签

set标签用于动态包含需要更新的列，并会删掉额外的逗号

mapper映射文件：

  <update id="updateByUser" parameterType="user">
        update user
        <set>
            <if test="username!=null and username != '' ">
                username=#{username},
            </if>
            <if test="birthday!=null">
                birthday=#{birthday},
            </if>
            <if test="sex!=null and sex != '' ">
                sex=#{sex},
            </if>
            <if test="address!=null and address != '' ">
                address=#{address},
            </if>
        </set>
        where id=#{id}
    </update>

5.trim标签

5.1trim标签可以代替where标签、set标签

但个人觉得直接用where或者set，无需用trim代替，trim主要用于动态插入出局比较合适

mapper映射文件：

<update id="updateByUser2" parameterType="User">
        update user
        <!-- 增加SET前缀，忽略，后缀 -->
        <trim prefix="SET" suffixOverrides=",">
            <if test="birthday!=null">
                birthday=#{birthday},
            </if>
            <if test="sex!=null and username != '' ">
                sex=#{sex},
            </if>
            <if test="address!=null and username != '' ">
                address=#{address},
            </if>
        </trim>
        where id=#{id}
    </update>

5.2trim主要用于动态向表中插入数据

mapper映射文件：

  <insert id="addUser" parameterType="com.by.pojo.User">
        INSERT INTO user
        <!--
            set标签一定要包括if标签，作用：
                prefix:加上前缀，“（”
                suffix:加上后缀，“）”
                prefixOverrides:去除多余的前缀内容
                suffixOverrides:去除多余的后缀内容，“，”
        -->
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username,
            </if>
            <if test="birthday!=null">
                birthday,
            </if>
            <if test="sex!=null and username != '' ">
                sex,
            </if>
            <if test="address!=null and username != '' ">
                address,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="username!=null and username!=''">
                #{username},
            </if>
            <if test="birthday!=null">
                #{birthday},
            </if>
            <if test="sex!=null and username != '' ">
                #{sex},
            </if>
            <if test="address!=null and username != '' ">
                #{address},
            </if>
        </trim>
    </insert>

set标签一定要包括if标签，作用：

prefix	加上前缀，“（”
suffix	加上后缀，“）”
prefixOverrides:	去除多余的前缀内容
suffixOverrides:	去除多余的后缀内容，“，”

6.foreach标签

foreach标签的常见使用场景是集合进行遍历

foreach 标签可以对数组， Map 或实现 Iterable 接口。

foreach 中有以下几个属性

• collection: 必填， 集合/数组/Map的名称.
• item: 变量名。即从迭代的对象中取出的每一个值
• index: 索引的属性名。当迭代的对象为 Map 时， 该值为 Map 中的 Key.
• open: 循环开头的字符串
• close: 循环结束的字符串
• separator: 每次循环的分隔符

?mapper接口：

    void deleteUserByIds(@Param("idArr") Integer[] idArr);

mapper映射文件：

    <delete id="deleteUserByIds" parameterType="list">
        DELETE FROM user WHERE id in
        <!--
            collection：取值list、array、@Param("keyName")
            item="id"：循环每次取出的具体对象
            open="(" ：加上前缀
            close=")" ：加上后缀
            separator=","：分隔符
        -->
        <foreach collection="idArr" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

?测试代码：

 @Test
    public void testForeach2(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = new ArrayList<>();
        long beginTime = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("刘德华");
            user.setBirthday(new Date());
            user.setSex("男");
            user.setAddress("香港");

            //userMapper.addUser(user); //io我们的mysql一万次
            userList.add(user);
        }
        userMapper.addUser2(userList);
        long endTime = System.currentTimeMillis();
        System.out.println("插入一万条记录需要：" + (endTime-beginTime) + "ms");
    }

如果直接在for里每加入一个都要访问一次mysql，添加一万次，又耗资源，又浪费时间，foreach就很好的解决了这个问题。

7.sql标签

在实际开发中会遇到许多相同的SQL，比如根据某个条件筛选，这个筛选很多地方都能用到，我们可以将其抽取出来成为一个公用的部分，这样修改也方便，一旦出现了错误，只需要改这一处便能处处生效了，此时就用到了<sql>这个标签了。

当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用。为求 <select> 结构清晰也可将 sql 语句分解。

mapper：

 //复杂条件查询
    public List<User> findByUser3(User user);

 <!-- 定义SQL片段 -->
    <sql id="query_user_where">
        <if test="username!=null and username != ''">
            and username=#{username}
        </if>
        <if test="birthday!=null">
            and birthday=#{birthday}
        </if>
        <if test="sex!=null and sex != ''">
            and sex=#{sex}
        </if>
        <if test="address!=null and address != ''">
            and address=#{address}
        </if>
    </sql>
    <select id="findByUser3" resultType="User">
        select * from user
        <where>
            <include refid="query_user_where"></include>
        </where>
    </select>

8.include

这个标签和<sql>是绝配，是共生的，include用于引用sql标签定义的常量。比如引用上面sql标签定义的常量。

<select id="findbyid"  resultType="student">
    <include refid="selectvp"/>
    WHERE 1=1
    <if test="sid != null">
        AND sid like #{sid}
    </if>
</select>

9.choose(when,otherwise) 语句

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

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
      select * from user
      <where>
          <choose>
              <when test="id !='' and id != null">
                  id=#{id}
              </when>
              <when test="username !='' and username != null">
                  and username=#{username}
              </when>
              <otherwise>
                  and sex=#{sex}
              </otherwise>
          </choose>
      </where>
  </select>

也就是说，这里我们有三个条件，id,username,sex，只能选择一个作为查询条件

　　　　如果 id 不为空，那么查询语句为：select * from user where ?id=?

　　　　如果 id 为空，那么看username 是否为空，如果不为空，那么语句为 select * from user where username=?;

　　　　如果 username 为空，那么查询语句为 select * from user where sex=?
?

10.?bind 标签?

bind 标签是通过 OGNL 表达式去定义一个上下文的变量， 这样方便我们使用。

如在?selectByStudentSelective?方法中， 有如下：

<if test="name != null and name !=''">
      and name like concat('%', #{name}, '%')
    </if>

在 MySQL 中， 该函数支持多参数， 但在 Oracle 中只支持两个参数。那么我们可以使用 bind 来让该 SQL 达到支持两个数据库的作用

<if test="name != null and name !=''">
     <bind name="nameLike" value="'%'+name+'%'"/>
     and name like #{nameLike}
</if>