MyBatis动态SQL与批量插入

动态SQL可以通过给定不同得条件,执行不同得SQL语句,实现动态SQL得方式就是通过mybatis提供得标签语法进行配置;

二 动态SQL标签

2.1 if 标签

  1. if 标签表示条件判断
  2. customer_name 不为空时会执行当前if标签的内容;此时的sql 语句 就是 select * from customer where and customer_name = #{customer_name}
  3. gender不为空时会执行if语句;此时执行的sql语句就是 select * from customer where 1 = 1 and gender = #{gender}
  4. customer_name 和 gender 标签都不为空时;此时执行的sql语句就是 select * from customer where and customer_name = #{customer_name} and gender = #{gender}
<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
    select
        *
    from `customer`
    where 1=1 
        <if test="customer_name!=null and customer_name!='' ">
            and `customer_name` = #{customer_name}
        </if>
        <if test="gender!=null and gender!=''">
            and `gender` = #{gender}
        </if>
</select>

2.2 where 标签

2.1 中我们在 where 后面使用 1=1 的操作,表示永远为真,不是一个规范的数据库操作;我们通常在select 语句的条件部分会使用 where 标签 与 if 标签 搭配使用,此时就可以消除 1=1操作带来的负面影响

<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
    select
        *
    from `customer`
    <where>
        <if test="customer_name!=null and customer_name!='' ">
            and `customer_name` = #{customer_name}
        </if>
        <if test="gender!=null and gender!=''">
            and `gender` = #{gender}
        </if>
    </where>
        
</select>

2.3 choose, when, otherwise标签

有时候需要一种场景就是 if else 形式的判断,如下示例中

  1. 当 customer_name 不为空 会执行 when标签体的内容;此时sql 就是 select gender from customer;
  2. 当输入的条件customer_name 为空时会执行otherwise标签体的内容;此时sql就是 select * from customer;
<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
      select
      <choose>
          <when test="customer_name=!null">
              `gender`
          </when>
          <otherwise>
              *
          </otherwise>
      </choose>
      from `customer`
  </select>

2.4 trim标签

先说下trim 属性 代表的涵义

  1. prefix 表示前缀,就是会为 trim 标签体加一个前缀;
  2. prefixOverrides 表示会将匹配到的第一个字符串去除;
  3. suffix 表示后缀,会为 trim 标签体加一个后缀内容;
  4. suffixOverrides表示会将匹配到的最后一个字符串去除;

如下示例中 执行的SQL语句就是 select * from customer where customer_name = #{customer_name}

<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
    select
        *
    from `customer`
    <trim prefix="where" prefixOverrides="and">
        and `customer_name` = #{customer_name}
    </trim> 

</select>

2.5 set标签

set 标签 只有在 更新操作中能用到;如下示例中set标签会默认将最后一个多余的逗号去除;

<update id="updateCustomer" parameterType="com.zszxz.dynamic.entity.Customer">
    update `customer`
    <set>
        <if test="customer_name!=null">
            `customer_name` = #{customer_name},
        </if>
        <if test="gender!=null">
            `gender` = #{gender},
        </if>
        <if test="telephone!=null">
            `telephone` = #{telephone},
        </if>
        <if test="register_time!=null">
            `register_time` = #{register_time},
        </if>
    </set>
    <where>
        id = #{id}
    </where>
</update>

如果所有的参数都不为空 执行的语句如下

update `customer` SET `customer_name` = ?, `gender` = ?, `telephone` = ?, `register_time` = ? WHERE id = ? 

set标签此时会等效于trim标签的如下形式

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

2.6 foreach 标签实现in

foreach 标签属性说明如下

  1. collection 参数
  2. open 前缀
  3. separator 分割符
  4. item 迭代中取得值,当入参数是Map.Entry或者map就是其 中 得值
  5. index 迭代中得键,当入参数是Map.Entry或者map就是其 中 得键;若入参是组数就是索引;
<select id="getCustomerById" resultType="com.zszxz.dynamic.entity.Customer" >
    select
        *
    from `customer`
    <where>
        <if test="ids!=null">
            and id in 
            <foreach collection="ids" open="(" separator="," item="id" index="i"  close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

最终得SQL语句执行形式如下

select * from `customer` WHERE id in ( ? , ? )

2.7 foreach标签实现批量插入

在xml中添加如下语句

<insert id="addCustomer" >
        insert into `customer`(
           `customer_name`,
           `gender`,
           `telephone`,
           `register_time`
        )values 
        <foreach collection="customers"  separator="," item="customer" index="i">
            (
                #{customer.customer_name},
                #{customer.gender},
                #{customer.telephone},
                #{customer.register_time}
            )
        </foreach>
   </insert>

部分测试类如下 在 for循环中创建3 个客户对象放入List,然后执行批量插入方法

@Test
public void testInsert(){
    // 获得mapper的形式
    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
    ArrayList<Customer> list = new ArrayList<>();
    for (int i = 0; i < 3; i++) {
        Customer customer = new Customer();
        customer.setCustomer_name("知识追寻者");
        customer.setGender("男");
        customer.setTelephone("999"+i);
        list.add(customer);
    }
    // 添加客户
    mapper.addCustomer(list);
    sqlSession.commit();
    sqlSession.close();
}

最终执行得SQL语句格式如下

insert into `customer`( `customer_name`, `gender`, `telephone`, `register_time` )values ( ?, ?, ?, ? ) , ( ?, ?, ?, ? ) , ( ?, ?, ?, ? ) 

2.8 script标签

script标签 用于 注解版本得动态SQL,官方示例如下

@Update({"<script>",
      "update Author",
      "  <set>",
      "    <if test='username != null'>username=#{username},</if>",
      "    <if test='password != null'>password=#{password},</if>",
      "    <if test='email != null'>email=#{email},</if>",
      "    <if test='bio != null'>bio=#{bio}</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
    void updateAuthorValues(Author author);

2.9 sql , include标签

sql 标签是个sql片段,如下示例中将查询条件抽出为一个sql片段,然后使用include标签实现引用;我们要进行复用sql片段时就很有用,减少代码量;

<sql id="condition">
        <where>
            <if test="customer_name!=null and customer_name!='' ">
                and `customer_name` = #{customer_name}
            </if>
            <if test="gender!=null and gender!=''">
                and `gender` = #{gender}
            </if>
        </where>
    </sql>
    <select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
        select
            *
        from `customer`
        <include refid="condition">

        </include>

    </select>

2.10 bind标签

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文;使用bind标签好处就是在数据库替换过程中可以除去不同数据库语法得影响;如下示例中就可以实现在mysql,oracle,postgresql 中数据替换;如果使用 mysql 得concat 函数在oracle中不再适用;

<sql id="condition">
       <where>
           <if test="customer_name!=null and customer_name!='' ">
               and customer_name like #{customer_name}
           </if>
           <if test="gender!=null and gender!=''">
               and gender = #{gender}
           </if>
       </where>
   </sql>
   <select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
       <bind name="customer_name" value="'%'+customer_name+'%'"/>
       select
           *
       from customer
       <include refid="condition">

       </include>

   </select>