Mybatis第七篇：动态SQL

　　1、if元素，if后面的判断成立就会加上拼接上相应的SQL。

<if test判断条件>
    执行的sql语句
</if>

　　sql案例

SELECT
          id,
          user_id,
          create_time,
          up_time
      FROM
          t_order
      WHERE
        1 = 1
        <if test="id != null">
          AND id = #{id}
        </if>
        <if test="userId != null">
          AND user_id = #{userId}
        </if>
        <if test="createTime != null">
          AND create_time = #{createTime}
        </if>
        <if test="upTime != null">
          AND up_time = #{upTime}
        </if>

　　2、choose、when、otherwise：choose的内部条件只要一个满足，就会退出后续的sql拼接。

　　sql案例：

<select id="selectOrderListByChoose" parameterType="entity.TOrder" resultMap="BaseResultMap">
    SELECT
    id,
    user_id,
    create_time,
    up_time
    FROM
    t_order
    WHERE
    1 = 1
    <choose>
      <when test="id != null">
        AND id = #{id}
      </when>
      <when test="userId != null">
        AND user_id = #{userId}
      </when>
      <when test="createTime != null">
        AND create_time = #{createTime}
      </when>
      <when test="upTime != null">
        AND up_time = #{upTime}
      </when>
    </choose>
  </select>

 　  test方法：可以看到test方法中set了两个参数进去，而运行的sql第一个参数id取到值之后就退出了sql的拼接。

 @Test
    public void test18(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        TOrderDao orderDao = sqlSession.getMapper(TOrderDao.class);
        TOrder tOrder = new TOrder();
        tOrder.setId(1);
        tOrder.setUserId(2);
        List<TOrder> tOrders = orderDao.selectOrderListByChoose(tOrder);
        if(CollectionUtils.isNotEmpty(tOrders)){
            log.info("{}", tOrders);
        }
        sqlSession.close();
    }

　　test方法的运行日志截图：

 　　3、where元素标签

　　使用<where>元素会将标签内部 的sql最前面的AND/OR省略掉，并拼接上where，是上面的2个sql中 where 1 = 1 AND的另一种写法。

　　SQL案例：

SELECT
          id,
          user_id,
          create_time,
          up_time
      FROM
          t_order
      <where>
          <if test="id != null">
            AND id = #{id}
          </if>
          <if test="userId != null">
            AND user_id = #{userId}
          </if>
          <if test="createTime != null">
            AND create_time = #{createTime}
          </if>
          <if test="upTime != null">
            AND up_time = #{upTime}
          </if>
      </where>

　　4、set元素

　　当在 update 语句中使用if标签时，如果前面的if没有执行，则或导致逗号多余错误。使用set标签可以将动态的配置 SET关键字，并剔除追加到条件末尾的任何不相关的逗号。使用 if+set 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。

　　SQL案例：

update t_order
    <set>
      <if test="userId != null">
        user_id = #{userId,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=BIGINT},
      </if>
      <if test="upTime != null">
        up_time = #{upTime,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}

　　5、foreach元素

<foreach collection="需要遍历的集合" item="集合中当前元素" index="" open="" separator="每次遍历的分隔符" close="">
动态sql部分
</foreach>

　　list集合查询，SQL案例：

<select id="selectOrderListByIds" parameterType="map" resultMap="BaseResultMap">
    SELECT
      id,
      user_id,
      create_time,
      up_time
      FROM
      t_order
    <where>
        <if test="id != null">
          AND id = #{id}
        </if>
        <if test="userId != null">
          AND user_id = #{userId}
        </if>
        <if test="createTime != null">
          AND create_time = #{createTime}
        </if>
        <if test="upTime != null">
          AND up_time = #{upTime}
        </if>
        <if test="idList != null and idList.size() > 0">
          <foreach collection="idList" separator="," open="AND id in (" close=")" item="id">
              #{id}
          </foreach>
        </if>
    </where>
  </select>

　　test方法：

@Test
    public void test19(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        TOrderDao orderDao = sqlSession.getMapper(TOrderDao.class);
        Map<String, Object> map = new HashMap<>();
        List<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(2);
        map.put("idList", idList);
        List<TOrder> tOrders = orderDao.selectOrderListByIds(map);
        if(CollectionUtils.isNotEmpty(tOrders)){
            log.info("{}", tOrders);
        }
        sqlSession.close();
    }

 　　list集合批量插入，SQL方案：第一个sql，利用SELECT LAST_INSERT_ID()无法获取批量插入之后的主键id（自增长的主键id），使用useGeneratedKeys和keyProperty的设置能够获取到自增长的主键id。

<insert id="batchInsertOrder" parameterType="list" >
    <selectKey resultType="integer" keyProperty="id" order="AFTER">
      SELECT LAST_INSERT_ID()
    </selectKey>
    INSERT
    into t_order (user_id, create_time, up_time) VALUES
    <foreach collection="list" item="tOrder" separator=",">
      (#{tOrder.userId}, #{tOrder.createTime}, #{tOrder.upTime})
    </foreach>
  </insert>

  <insert id="batchInsertOrder2" parameterType="list" useGeneratedKeys="true" keyProperty="id">
    INSERT
    into t_order (user_id, create_time, up_time) VALUES
    <foreach collection="list" item="tOrder" separator=",">
      (#{tOrder.userId}, #{tOrder.createTime}, #{tOrder.upTime})
    </foreach>
  </insert>

　　test方法运行结果：

 　　6、sql、include元素

　　两个元素一起使用，可以实现s<sql id="selectMap">

    <where>
      <if test="id != null">
        AND id = #{id}
      </if>
      <if test="userId != null">
        AND user_id = #{userId}
      </if>
      <if test="createTime != null">
        AND create_time = #{createTime}
      </if>
      <if test="upTime != null">
        AND up_time = #{upTime}
      </if>
      <if test="idList != null and idList.size() > 0">
        <foreach collection="idList" separator="," open="AND id in (" close=")" item="id">
          #{id}
        </foreach>
      </if>
    </where>
  </sql>

  <select id="selectOrderListByIds" parameterType="map" resultMap="BaseResultMap">
    SELECT
      id,
      user_id,
      create_time,
      up_time
      FROM
      t_order
      <include refid="selectMap"/>
  </select>