MyBatis--DynamicSQL(动态SQL)

DynamicSQL（动态SQL）

1.内容简介

  (1) if:判断
  (2) where:封装查询条件
  (3) set:封装修改条件
  (4) trim:字符串截取
  (5) choose(when, otherwise):分支选择
  (6) foreach:遍历集合
  (7) bind:可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值
  (8) sql:抽取可重用的sql片段

2.if(根据条件动态拼装SQL)

 2.1 格式：<if test="条件"> 语句 </if>

 2.2 属性

	test:   (1)判断表达式（OGNL）
			(2)从参数中取值进行判断
			(3)特殊符号写转义字符

 2.3 示例

<select id="getCustomerIF" resultType="bean.Customer">
    SELECT * FROM customer WHERE
    <if test="id!=null">
        id=#{id}
    </if>
    <if test="customerName!=null &amp;&amp; customerName!=&quot;&quot;">
        AND customer_name=#{customerName}
    </if>
    <if test="age!=0 &amp;&amp; age!=null">
        AND age=#{age}
    </if>

3.where（将所有的查询条件包括在内，并可以自动去掉第一个多余的AND）

  注：如果此时id为空，customerName不为空则 AND会被自动去掉

<select id="getCustomerIF" resultType="bean.Customer">
    SELECT * FROM customer
    <where>
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="customerName!=null &amp;&amp; customerName!=&quot;&quot;">
            AND customer_name=#{customerName}
        </if>
        <if test="age!=0 &amp;&amp; age!=null">
            AND age=#{age}
        </if>
    </where>
</select>

4.set（可以自动去掉多余的，）

  注：如果此时age为空,则#{customerName}后面的，会被去掉

<mapper namespace="dao.CustomerMapper">

    <update id="UpdateCustomerSET" >
        UPDATE customer
        <set>
            <if test="customerName!=null"> customer_name=#{customerName}, </if>
            <if test="age!=null"> age=#{age} </if>
        </set>
        WHERE id=#{id}

    </update>

</mapper>

5.trim

 5.1属性

 	prefix=""：前缀，prefix给拼串后的整个字符串加一个前缀 

 	prefixOverrides=""：前缀覆盖，去掉整个字符串前面多余的字符

 	suffix=""：后缀，suffix给拼串后的整个字符串加一个后缀 

 	suffixOverrides=""：后缀覆盖，去掉整个字符串后面多余的字符

 5.2 示例

<select id="getCustomerTRIM" resultType="bean.Customer">
     SELECT * FROM customer

     <trim prefix="WHERE" suffixOverrides="AND">
         <if test="id!=null">
             id=#{id} AND
         </if>
         <if test="customerName!=null &amp;&amp; customerName!=&quot;&quot;">
              customer_name=#{customerName} AND
         </if>
         <if test="age!=0 &amp;&amp; age!=null">
             age=#{age}
         </if>
     </trim>
 </select>	

6.choose（分支选择:类似if-esle）

<select id="getCustomerCHOOSE" resultType="bean.Customer">
    SELECT * FROM customer
    <where>
        <choose>
            <when test="id!=null"> id=#{id} </when>
            <when test="customerName!=null"> customer_name=#{customerName}</when>
            <otherwise>age=#{age}</otherwise>
        </choose>
    </where>
</select>

7.foreach

 7.1 属性

	(1) collection：指定要遍历的集合(list类型的参数会特殊处理封装在map中，map的key就叫list)
	 		
	(2) item：将当前遍历出的元素赋值给指定的变量
	(3) separator:每个元素之间的分隔符
	(4) open：遍历出所有结果拼接一个开始的字符
	(5) close:遍历出所有结果拼接一个结束的字符
	(6) index:索引，遍历list的时候是index就是索引，item就是当前值（遍历map的时候index表示的就是map的key，item就是map的值）
	(7) #{变量名}：就能取出变量的值也就是当前遍历出的元素

 7.2 foreach遍历集合（传入的List需要参数命名@Param("…")）

   7.2.1 示例一

<select id="getCustomerForeach" resultType="bean.Customer">
    SELECT * FROM customer

    <foreach collection="ids" item="i_id" open="WHERE id IN (" close=")" separator=","> #{i_id} </foreach>
    
</select>

   7.2.2 示例二

<insert id="insertCustomerForeach">
    INSERT INTO customer(customer_name,age) VALUES
    <foreach collection="customers" separator="," item="i_customer">
        (#{i_customer.customerName},#{i_customer.age})
    </foreach>
</insert>

8.bind（可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值）

<bind name="_lastName" value="'%'+lastName+'%'"/>

9.sql与include (抽取可重用的sql片段)

	(1) sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
	(2) include来引用已经抽取的sql
	(3) include还可以自定义一些property，sql标签内部就能使用自定义的属性
	(4) include-property：取值的方式${prop}

<insert id="insertCustomer">
     INSERT INTO customer(
     <include refid="insertCustomer1"></include>
     ) VALUES(#{customerName},#{age})
 </insert>


 <sql id="insertCustomer1">
     customer_name,age
 </sql>

附录：

   1.bean

package bean;

/**
 * @author LFuser
 * @create 2019-06-26-10:18
 */
public class Customer {
    private Integer id;
    private String customerName;
    private Integer age;

    public Customer(){
        super();
    }

    public Customer(Integer id, String customerName, Integer age) {
        this.id = id;
        this.customerName = customerName;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", customerName='" + customerName + '\'' +
                ", age=" + age +
                '}';
    }
}

   2.CustomerMapper.java

package dao;

import bean.Customer;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author LFuser
 * @create 2019-06-26-10:22
 */
public interface CustomerMapper {
    public List<Customer> getCustomerWHERE(Customer customer);

    public List<Customer> getCustomerTRIM(Customer customer);

    public List<Customer> getCustomerCHOOSE(Customer customer);

    public void UpdateCustomerSET(Customer customer);

    public List<Customer> getCustomerForeach(@Param("ids") List<Integer> ids);

    public void insertCustomerForeach(@Param("customers") List<Customer> customers);
}