多参数批量增删查改 mybatis

动态多条件查询

多个条件

第二个if中需要添加 and 语句用于多条件查询，并且当运行时需要 and 的条件前面没有条件时 mybatis 会自动去除

<!-- 动态条件查询（多个条件），结合<where>标签和<if>标签来实现 -->
<select id="listStudents" parameterType="map" resultMap="stuMap">
    select stu_id, stu_name, stu_age from stu_info
    <where>
        <if test="uname != null and uname != ''">
            stu_name = #{uname}
        </if>
        <if test="age != null">
            and stu_age = #{age}
        </if>
    </where>
    order by stu_id desc
</select>

多选一

顾名思义，多个条件满足其一就通过

<!-- 动态条件选择(多选一)，使用<choose>标签 -->
<select id="listStudents2" parameterType="map" resultMap="stuMap">
    select stu_id, stu_name, stu_age from stu_info
    <choose>
        <when test="uname != null and uname != ''">
            where stu_name = #{uname}
        </when>
        <when test="age != null">
            where stu_age = #{age}
        </when>
        <otherwise>
            order by stu_id desc
        </otherwise>
    </choose>
</select>

循环条件

使用 foreach 遍历进行批量操作
<foreach collection="list或者String[]等等" item="遍历集合返回的值" open="循环开始出现一次" separator="返回的值之间的值" close="循环结束出现一次">

<!-- 使用<foreach>标签循环参数，适用于or或者in子句查询操作,
     当参数是List或者数组时，parameterType的值为collection，
     ognl表达式中使用list-->
<select id="listStudents3" parameterType="collection" resultMap="stuMap">
    select stu_id, stu_name, stu_age from stu_info
    <where>
        stu_age in
        <if test="list != null">
            <foreach collection="list" item="age" open="(" separator="," close=")">
                #{age}
            </foreach>
        </if>
    </where>
</select>

改

使用 set 加 if 实现动态修改

<!-- 动态更新，使用<set>标签实现动态更新字段 -->
<update id="updateStu" parameterType="edu.nf.ch05.entity.Student">
    update stu_info
    <set>
        <if test="stuName != null and stuName != ''">
            stu_name = #{stuName},
        </if>
        <if test="stuAge != null">
            stu_age = #{stuAge}
        </if>
    </set>
    where stu_id = #{stuId}
</update>

增删

<!-- 使用<foreach>标签实现批量添加 -->
<insert id="batchAdd" parameterType="collection">
    insert into stu_info(stu_name, stu_age) values
    <foreach collection="list" item="stu" separator=",">
        (#{stu.stuName}, #{stu.stuAge})
    </foreach>
</insert>

<!-- 批量删除, 与in范围查询类似 -->
<delete id="batchDel" parameterType="collection">
    delete from stu_info where stu_id in
    <if test="list != null">
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </if>
</delete>