Mybatis 框架 7 - 动态更新、sql片段

1.批量更新

<!--nvl(comm,0)如果为 null取0-->
<update id = "updateEmps">
    update emp set comm = (nvl(comm,0) + #{addComm}) where empno in
    <foreach collection = "empons" item = "empno" open="(" separator="," close=")">#{empnos}</foreach>
</update>

//接口 
public int updateEmps (Map<String,Object> params);

//用map传值
Map<String,Object> params = new HashMap<String,Object>();
params.put("addcomm","10");//增加量
params.put("empnos",new Integer[]{7839,7788,7360});//传入要修改的id

2.动态更新

<update id = "updateEmpDynamic" parameterType="com.nue.model.Emp">
update emp
    <set>
        <if test-"ename !=null and ename !='' ">
            ename = #{ename},
        </if>
        <if test-"job != null and jab !='' ">
            job = #{job},
        </if>
    </set>
    where empno = #{empno}
</update>

3.sql片段

通过Sql代码片段，实现sql代码重用
查询用户信息和分页总记录数的代码重用

//mapper.xml文件内
<sql id="query_user">
    <if test-"ename !=null and ename !='' ">
            ename = #{ename},
        </if>
        <if test-"job != null and jab !='' ">
            job = #{job},
        </if>
</sql>

<update id = "updateEmpDynamic" parameterType="com.nue.model.Emp">
update emp
    <set>
        <include refid="query_user"/>
    </set>
    where empno = #{empno}
</update>

<select id = "findUserList" parameterType="user" resultType="java.util.Map">
select id,username,birthday,address from t_user
    <where>
        <include refid="query_user"/>
    </where>
</select>