mybatis动态sql

where + if

    <!--where+if   使用where标签可以无参时，不会出现where   有参数时,会剔除多余的and-->
    <select id="select" resultMap="selectMap" parameterType="Dept">
            select * from dept
            <where>
                <if test="deptno != null">
                    and deptno = #{deptno}
                </if>
                <if test="dname != null">
                    and dname = #{dname}
                </if>
            </where>
    </select>

set + if

    <!--set + if -->
    <update id="updateDeptByDeptno" parameterType="Dept" >
        update dept
        <set>
            <if test="dname != null">
                dname = #{dname},
            </if>
            <if test="loc != null">
                loc = #{loc},
            </if>
        </set>
        where deptno = #{deptno}
    </update>

choose

    <!--choose when otherwise
        只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签
    -->
    <select id="selectChoose" resultType="Dept">
        select * from dept
        <where>
            <choose>
                <when test="deptno != null and deptno != 10">
                    deptno = #{deptno}
                </when>
                <otherwise>
                    dname = #{dname}
                </otherwise>
            </choose>
        </where>
    </select>

foreach

    <!--foreach-->
    <select id="selectTrim" resultType="Dept" parameterType="java.util.List">
        select * from dept
        <where>
        <!--    deptno in-->
        <!--    <trim prefix="(" suffix=")" prefixOverrides=",">-->
        <!--        -->
        <!--    </trim>-->
            <foreach collection="list" item="id" separator="," open="deptno in (" close=")">
                #{id}
            </foreach>
        </where>

    </select>

trim

  <!--trim-->
  <insert id="insertTrim" parameterType="Dept">
      insert into dept
--         prefixOverrides去掉拼接好的字符串后边的指定字符
      <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="deptno != null ">
              deptno,
          </if>
          <if test="dname !=null">
              dname,
          </if>
          <if test="loc != null">
              loc,
          </if>
      </trim>
      values
--         prefixOverrides去掉拼接好的字符串前边的指定字符
      <trim prefix="(" suffix=")" prefixOverrides=",">
          <if test="deptno != null">
              ,#{deptno}
          </if>
          <if test="dname != null">
              ,#{dname}
          </if>
          <if test="loc != null">
              ,#{loc}
          </if>
      </trim>
  </insert>