mybatis中set/where/trim/foreach标签以及concat(解决模糊查询)函数的使用

mybatis中set/where/trim/foreach标签以及concat(解决模糊查询)函数的使用

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--
    namespace必须是接口的全类名(包名加类名)
-->
<mapper namespace="com.qf.mapper.IEmpMapper">

    <sql id="base_emp">
        empno,
        ename,
        job,
        sal,
        comm,
        hiredate,
        mgr,
        deptno
    </sql>
    <select id="getEmpList" resultType="emp">
        select
            <include refid="base_emp"/>
        from emp
    </select>

    <select id="getEmpByEmpno" resultType="emp">
        select
            <include refid="base_emp"/>
        from emp where empno=#{empno}
    </select>

    <select id="findEmp" resultType="emp">
        select
            <include refid="base_emp"></include>
        from
            emp
        where ename like concat("%",#{ename},"%")
        and deptno=#{deptno}
        and sal>#{sal}
    </select>

    <update id="update">
        update emp
        <set>
            <if test="ename!=null and ename!=''">
                ename=#{ename},
            </if>
            <if test="sal!=null">
                sal=#{sal},
            </if>
            <if test="job!=null and job!=''">
                job=#{job}
            </if>
        </set>
        <where> empno=#{empno}
        </where>
    </update>

    <update id="update2">
        update emp
        <trim prefix="set" suffixOverrides=",">
            <if test="ename!=null and ename!=''">
                ename=#{ename},
            </if>
            <if test="sal!=null">
                <![CDATA[sal=#{sal},]]]>
            </if>
            <if test="job!=null and job!=''">
                job=#{job}
            </if>
        </trim>
        <where> empno=#{empno}
        </where>
    </update>

    <delete id="batchDel">
        delete from emp where empno in
        <foreach collection="list" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>
</mapper>