Mybatis映射文件（3）

àì夳堔傛蜴生んèń 2022-05-24 04:16 231阅读 0赞

### 动态SQL ###

##### 1、if/where #####

<select id="getEmpsByConditionIfAndWhere" resultType="bean.Employee">
        SELECT * FROM t_employee
        <where>
            <if test="id != null">
                 id=#{id}
            </if>
            <if test="lastName != null and lastName != ''">
                AND last_name=#{lastName}
            </if>
            <if test="email != null and email.trim() != ''">
                AND  email=#{email}
            </if>
            <if test="gender==0 or gender==1">
                AND gender=#{gender}
            </if>
        </where>
    </select>

##### 2、trim自定义字符串截取规则 #####

<select id="getEmpsByConditionTrim" resultType="bean.Employee">
        SELECT * FROM t_employee
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
                id=#{id} AND
            </if>
            <if test="lastName != null and lastName != ''">
                last_name=#{lastName} AND
            </if>
            <if test="email != null and email.trim() != ''">
                email=#{email} AND
            </if>
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
        </trim>
    </select>

##### 3、choose (when, otherwise):分支选择    #####

<select id="getEmpsByConditionChoose" resultType="bean.Employee">
        SELECT * FROM t_employee
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null and lastName!=''">
                    last_name LIKE #{lastName}
                </when>
                <when test="email!=null and email!=''">
                    email=#{email}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

##### 4、where-封装查询条件, set-封装修改的条件 #####

<update id="updateEmp">
        UPDATE t_employee
        <set>
            <if test="lastName!=null and lastName!=''">
                last_name=#{lastName},
            </if>
            <if test="email!=null and email!=''">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        <where>
            id=#{id}
        </where>
     
    </update>

##### 5、foreach标签——批量处理 #####

<select id="getEmpsByConditionForeach" resultType="bean.Employee">
        SELECT * FROM t_employee WHERE id IN
            <foreach collection="list" item="item_id" separator="," open="(" close=")">
                #{item_id}
            </foreach>
    </select>

<insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            INSERT INTO t_employee(id,last_name,gender,email) VALUES
            (#{emp.id},#{emp.lastName},#{emp.gender},#{emp.email})
        </foreach>
    </insert>

##### 6、内置参数和bind    #####

<select id="getEmpsTestInnerParameter" resultType="bean.Employee">
        <bind name="_lastName" value="'%'lastName'%'"></bind>
        <if test="_databaseId=='mysql'">
            SELECT * FROM t_employee
            <if test="_parameter!=null">
                WHERE last_name LIKE #{_lastName}
            </if>
        </if>
        <if test="_databaseId=='oracle'">
            SELECT * FROM employee
            <if test="_parameter!=null">
                WHERE last_name=#{lastName}
            </if>
        </if>
    </select>

##### 7、可重用SQL——经常将要查询的列名或插入用的列名抽取出来方便引用 #####

<sql id="insertColumn">
        <if test="_databaseId=='mysql'">
          id,last_name,gender,email
        </if>
        <if test="_databaseId=='oracle'">
            --------
        </if>
    </sql>
    <insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            INSERT INTO t_employee(
            <include refid="insertColumn"></include>
            ) VALUES
            (#{emp.id},#{emp.lastName},#{emp.gender},#{emp.email})
        </foreach>
    </insert>