MyBatis动态SQL

 MyBatis动态SQL

    关键字:set,where,trim

     判断元素:if,choose

      循环元素:foreach

    1.动态查询

<!--id对应映射方法中方法名-->    

<select  id="selectAll" resultType="emp">

   select * from emp
  <if test="eno!=null and eno!=''">
   where eno=#{eno}
</if>
  </select>

    2.使用where+if条件查询

<!--    where 标签:
        可以将条件都放到where标签中,如果它包含的标签中有返回值,就会在sql语句中插入一个where
        另外,如果返回值的标签内容是以and或者or开头,则会自动剔除
        如果所有的条件都不满足,就不会插入where
-->

  <select id="findEmpWhere" resultType="emp">
        select  * from emp
        <where>
        <if test="deptno!=null">
            deptno=#{deptno}
        </if>
        <if test="ename!=null">
            and ename=#{ename}
        </if>
        <if test="salary!=null">
            and salary=#{salary}
        </if>
        </where>
    </select>

 3. trim元素

 prefix:需要添加的前缀

suffix:需要添加的后缀

prefixoverrides:需要替换的前缀

suffixOverrides:需要替换的后缀

<select id="findEmpTrim" resultType="emp">
    select * from emp
    <trim prefix="where" prefixOverrides="and|or">
        <if test="deptno!=null">
           and deptno=#{deptno}
        </if>
        <if test="ename!=null">
            and ename=#{ename}
        </if>
        <if test="salary!=null">
            and salary>6000
        </if>
    </trim>
</select>

4.foreach批量添加

   <insert id="insertMultiple">
        insert  into emp (ename,job,manager,hiredate,salary,comm,deptno)
        values
        <foreach collection="list" item="emp" separator=",">
            (
            #{emp.ename},
            #{emp.job},
            #{emp.manager},
            #{emp.hiredate},
            #{emp.salary},
            #{emp.comm},
            #{emp.deptno}
            )
        </foreach>
    </insert>

5.foreach批量更新(修改)

    <update id="updateEmpByMap">
        update  emp
        <set>
            <foreach collection="map" item="var" index="key" separator=",">
            ${key}=#{var}
            </foreach>
        </set>
        where empno=#{empno}
    </update>

6.foreach批量查询

   <select id="findEmpFor" resultType="emp">
        select * from emp
        where empno in
        <foreach collection="list" open="(" separator="," close=")" item="empno">
              #{empno}
       </foreach>
    </select>

7.bind模糊查询

   <select id="findByEname" resultType="emp">
        select  * from emp
        <where>
            <if test="ename!=null and ename!=''">
              <!--  <bind name="e_name" value="'%'+ename+'%'"/>
            ename like #{e_name}-->
                <bind name="e_name" value="'%'+ename+'%'"/>
                ename like #{e_name}
            </if>
        </where>
    </select>