IDEA Mybatis动态SQL语句使用

1.公用映射类

UserMapper

	/**
     * 根据参数条件查询User
     * @param user
     * @return
     */
    User getUserByParameter(User user);

	/**
     * 修改一条User记录
     * @param user
     */
    void update(User user);

	/**
     * 根据ID集合获取相对应User记录
     * @param idList
     * @return
     */
    List<User> getUserByCollection(List<Integer> idList);

2.if运用

	<select id="getUserByParameter" resultType="User">
        select * from t_user where 1 = 1
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="password != null">
                and password = #{password}
            </if>
    </select>

3.where运用

	<!--where动态条件会当条件满足时会自动去除第一个and/or-->
	<select id="getUserByParameter" resultType="User">
        select * from t_user
        <where>
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="password != null">
                and password = #{password}
            </if>
        </where>
    </select>

4.set运用

	<!--SET动态条件会自动去除最后一个参数的","-->
	<update id="update">
        update t_user
          <set>
              <if test="username != null">username = #{username},</if>
              <if test="password != null">password = #{password},</if>
          </set>
        where id = #{id}
    </update>

5.trim运用

前缀Where运用

	<!--
        trim
        prefix自定义前缀为WHERE,
        prefixOverrides前缀覆盖,即去除第一个前缀与where功能相同
    -->
    <select id="getUserByParameter" resultType="User">
        select * from t_user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="password != null">
                and password = #{password}
            </if>
        </trim>
    </select>

前缀Set运用

	<!--
        trim
        prefix自定义前缀为SET,
        suffixOverrides后缀覆盖,即去除最后一个",",与SET功能相同
    -->
	<update id="update">
        update t_user
          <trim prefix="set" suffixOverrides=",">
              <if test="username != null">username = #{username},</if>
              <if test="password != null">password = #{password},</if>
          </trim>
        where id = #{id}
    </update>

6.foreach运用

①UserMapper映射类添加

	/**
     * 根据ID集合获取相对应User记录
     * @param idList
     * @return
     */
    List<User> getUserByCollection(@Param("idList") List<Integer> idList);

②UserMapper.xml配置文件添加,该条件主要用于IN条件语句中

	<!--
        collection采集数据,来自于集合或数组
        index当前键
        item当前获取值
        separator分隔符
    -->
	<select id="getUserByCollection" resultType="User">
        select * from t_user where id in
        <foreach collection="idList" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

③测试

	@Test
    public void test06() {
        List<Integer> integerList = new ArrayList<Integer>();
        integerList.add(2);
        integerList.add(5);
        integerList.add(11);
        List<User> list = userMapper.getUserByCollection(integerList);
        for (User user : list) {
            System.out.println(user);
        }
    }

⑤结果