MyBatis - 4.动态SQL

• 动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作。
• 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
• MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作。
  
  

1.if

新建接口

public interface IemployeeMapperDynamicSQL {

    public List<employee> getEmpsByConditionIf(employee emp);
}

新建Mapper IemployeeMapperDynamicSQL.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.tangge.Mapper.IemployeeMapperDynamicSQL">

  <!--
    if
    choose (when, otherwise)
    trim (where, set)
    foreach
  -->

  <!--public List<employee> getEmpsByConditionIf(employee emp);-->
  <select id="getEmpsByConditionIf" resultType="com.tangge.model.employee">
    select * from tbl_employee WHERE 1=1
    <if test="id != 0">
      and id=#{id}
    </if>
    <!--QGNL中的（和）：and 和 && ，XML不支持&&，所以可以写成	&amp;&amp;-->
    <if test="lastName != null 	&amp;&amp; lastName != ''">
    and last_name like #{lastName}
    </if>
    <!--QGNL中的（或）：or 和 || -->
    <if test="  gender ==0 || gender == 1">
      and gender = #{gender}
    </if>
    <if test="email != null">
      and email=#{email}
    </if>
  </select>
</mapper>

---->【测试】：

    public static void getEmpsByConditionIf() {

        SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
        SqlSession session = sqlSessionFactory.openSession();
        try {
            IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
            employee  employee  = new employee(0,"%i%",null,null);

            List<employee> employees = mapper.getEmpsByConditionIf(employee);
            System.out.println(employees);
            /**
			 * 结果：
             * [employee{id=1, lastName='null', email='[e-mail]', gender=0, dept=null},
             * employee{id=5, lastName='null', email='[e-mail]', gender=1, dept=null}]
             */
        } finally {
            session.close();
        }
    }

2.choose

类似带了 breack 的 swicth-case

  <select id="getEmpsByConditionChoose" resultType="com.tangge.model.employee">
    select * from tbl_employee
    <where>
      <choose>
        <when test="id != 0">
          id=#{id}
        </when>
        <when test="lastName != null 	&amp;&amp; lastName != ''">
          last_name like #{lastName}
        </when>
        <when test="email != null">
          email=#{email}
        </when>
        <otherwise>
          1 = 1
        </otherwise>
      </choose>
    </where>
  </select>

3.Trim

如果条件后面加and，最后生成的SQL条件也有一条and，我们需要删除掉。

使用 <trim>标签

<select id="getEmpsByConditionTrim" resultType="com.tangge.model.employee">
    select * from tbl_employee
    <!--
    trim:
      - prefix:前缀
      - prefixOverrides:前缀覆盖
      - suffix:后缀
      - prefixOverrides:后缀覆盖
    -->
    <trim prefix="where" suffixOverrides="and">
      <if test="id != 0">
        id=#{id} and
      </if>
      <!--QGNL中的（和）：and 和 && ，XML不支持&&，所以可以写成	&amp;&amp;-->
      <if test="lastName != null 	&amp;&amp; lastName != ''">
        last_name like #{lastName} and
      </if>
      <!--QGNL中的（或）：or 和 || -->
      <if test="  gender ==0 || gender == 1">
        gender = #{gender} and
      </if>
      <if test="email != null">
        email=#{email} and
      </if>
    </trim>
  </select>

3.1 where

where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

如果 where 元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

3.2.set（封装修改条件）

 <!--
    set标签：
      set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。
      （因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留）
  -->
  <update id="updateEmp" >
    UPDATE tbl_employee
    <set>
      <if test="lastName != null 	&amp;&amp; lastName != ''">
        last_name = #{lastName},
      </if>
      <!--QGNL中的（或）：or 和 || -->
      <if test="gender ==0 || gender == 1">
        gender = #{gender},
      </if>
      <if test="email != null">
        email=#{email},
      </if>
    </set>
    <where>
      id = #{id}
    </where>
  </update>

若你对 set 元素等价的自定义 trim 元素的代码感兴趣，那这就是它的真面目：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

4.foreach

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

定义个接口

public List<employee> getEmpsByConditionForeach(List<Integer> list1);

Mapper

 <select id="getEmpsByConditionForeach" resultType="com.tangge.model.employee">
    select * from tbl_employee WHERE id in
    <!--
    foreach:
      - collection:指定要遍历的集合：list类型的参数会封装在map中，map的key就叫list
      - item:将遍历出的元素赋值给指定变量
        #{变量名} 当前遍历的元素
      - separator:每个元素之间的分隔符
      - open:开始的字符前缀
      - close:结束字符后缀
      - index:
        - list: index是list的索引，item是当前值
        - map: index是map的key，item是map的value
    -->
    <foreach collection="list" item="item_id" separator="," open="(" close=")">
      #{item_id}
    </foreach>
  </select>

---->【测试】：

    public static void getEmpsByConditionForeach() {

        SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
        SqlSession session = sqlSessionFactory.openSession();
        try {
            IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
            List<Integer> list1 = new ArrayList<>();
            list1.add(5);
            list1.add(6);
            List<employee> rows = mapper.getEmpsByConditionForeach(list1);
			//执行的SQL：select * from tbl_employee WHERE id in  (  5 ,  6  )
            System.out.println(rows);
        } finally {
            session.close();
        }
    }

4.1 批量插入使用foreach

接口声明：

public boolean addEmpList(@Param("emps") List<employee> employees);

Mapper

<!--批量插入-->
  <!-- public boolean addEmpList(@Param("emps") List<employee> employees);-->
  <insert id="addEmpList">
    INSERT INTO tbl_employee(last_name,gender,email,dept_id) VALUES
  <foreach collection="emps" item="item" separator="," >
    (#{item.lastName},#{item.gender},#{item.email},#{item.dept.departmentId})
  </foreach>
  </insert>

---->【测试】：

  public static void addEmpList() {

        SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
        SqlSession session = sqlSessionFactory.openSession();
        try {
            IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
            employee  employee  = new employee("wwa","sdas@sasa",null);
            employee  employee1  = new employee("wwc","sdas@sasa",null);
            List<employee> emps = new ArrayList<>();
            emps.add(employee);
            emps.add(employee1);
            boolean rows = mapper.addEmpList(emps);
            System.out.println(rows);
        } finally {
            session.close();
        }
    }

5.内置参数：_parameter 与 _databaseId

public List<employee> getEmpsInnerParameter(employee emp);

配置

 <!--
  两个内置参数：
      - _parameter：代表整个参数
          * 单个参数：_parameter就是整个参数
          * 多个参数：参数封装一个map,_parameter就代表真个map
      - _databaseId：如果config xml配置了databaseIdProvider标签。
          * _databaseId代表当前数据库的别名oracle
  -->
  <!--public List<employee> getEmpsInnerParameter(employee emp);-->
  <select id="getEmpsInnerParameter" resultType="com.tangge.model.employee">
    <if test="_databaseId=='mysql'">
      select * from tbl_employee
      <if test="_parameter!=null">
        WHERE last_name LIKE #{_parameter.lastName}
      </if>
    </if>
    <if test="_databaseId=='oracle'">
      select * from employees
    </if>
  </select>

---->【测试】：

public static void getEmpsInnerParameter() {

        SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
        SqlSession session = sqlSessionFactory.openSession();
        try {
            IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
            employee  employee  = new employee(0,"%w%",null,null);

            List<employee> employees = mapper.getEmpsInnerParameter(employee);
            System.out.println(employees);
        } finally {
            session.close();
        }
    }

结果：

DEBUG 08-28 15:46:43,167 ==>  Preparing: select * from tbl_employee WHERE last_name LIKE ?   (BaseJdbcLogger.java:139)
DEBUG 08-28 15:46:43,192 ==> Parameters: %w%(String)  (BaseJdbcLogger.java:139)
DEBUG 08-28 15:46:43,221 <==      Total: 3  (BaseJdbcLogger.java:139)
[
employee{id=6, lastName='null', email='[e-mail]', gender=1, dept=null},
employee{id=7, lastName='null', email='sdas@sasa', gender=null, dept=null},
employee{id=8, lastName='null', email='sdas@sasa', gender=null, dept=null}
]

6.bind （OGNL 表达式绑定）

上面传参 %w%

 employee  employee  = new employee(0,"%w%",null,null);

Mapper

  <if test="_parameter!=null">
        WHERE last_name LIKE #{_parameter.lastName}
      </if>

然后输出的是

DEBUG 08-28 15:46:43,192 ==> Parameters: %w%(String)

---->【需求】：：现在我们想要传入参数%w%变为w，%在Mapper里添加。

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文

<select id="getEmpsInnerParameter" resultType="com.tangge.model.employee">
	<!--bind使用-->
    <bind name="pattern" value="'%' + _parameter.lastName + '%'" />
    <if test="_databaseId=='mysql'">
      select * from tbl_employee
      <if test="_parameter!=null">
        WHERE last_name LIKE #{pattern}
      </if>
    </if>
    <if test="_databaseId=='oracle'">
      select * from employees
    </if>
  </select>

7.sql 和 include（抽取可重用的SQL片段）

<select id="getEmpsInnerParameter" resultType="com.tangge.model.employee">
    <bind name="pattern" value="'%' + _parameter.lastName + '%'" />
    <if test="_databaseId=='mysql'">
      <!--
	  引用外部的SQL
	  		- property：自定义属性
	  		这里${abc} 值 tbl_employee，可以在<sql>里运用
	  -->
      <include refid="employee">
        <property name="abc" value="tbl_employee"/>
      </include>
      <if test="_parameter!=null">
        WHERE last_name LIKE #{pattern}
      </if>
    </if>
    <if test="_databaseId=='oracle'">
      select * from employees
    </if>
  </select>
  <!--
    抽取可重用的SQL片段，方便引用
      - <sql>抽取：经常查询的列名，或者插入用的列名抽取出来方便引用
      - <include>:引用抽取的SQL
      - <include>还可以定义<property>,sql内部标签可以使用 ${prop}
  -->
  <sql id="employee">
    select * from ${abc}
  </sql>