mybatis中批量增、删、改的SQL语句

疯狂的攻城狮-Alun

已于 2023-08-25 11:03:13 修改

阅读量1.9k

点赞数 2

文章标签： mybatis sql spring java javaweb

于 2017-06-13 17:45:32 首次发布

本文链接：https://blog.csdn.net/Alun_kong/article/details/73189009

版权

1.批量增

<insert id="insertBatch" parameterType="java.util.List">
	insert into SYS_DEPT ( 
		DEPT_ID, ORG_ID, DEPT_NAME, LEADER, IS_USED, REMARK, 
		ENTRY_PROGRAM,ENTRY_USER, MODIFY_DATE, MODIFY_PROGRAM, MODIFY_USER
	) 
	<foreach collection="list" item="dept" index="index" open="(" separator="union" close=")">
	select 
		#{dept.deptId,jdbcType=VARCHAR}, #{dept.orgId,jdbcType=VARCHAR}, #{dept.deptName,jdbcType=VARCHAR}, 
		#{dept.leader,jdbcType=VARCHAR}, #{dept.isUsed,jdbcType=DECIMAL}, #{dept.remark,jdbcType=VARCHAR}, 
		#{dept.entryProgram,jdbcType=VARCHAR}, #{dept.entryUser,jdbcType=VARCHAR}, 
		#{dept.modifyDate,jdbcType=DATE}, #{dept.modifyProgram,jdbcType=VARCHAR}, #{dept.modifyUser,jdbcType=VARCHAR}
	from dual
	</foreach>
</insert>

2.批量删

  <delete id="deleteBatch" parameterType="java.util.List">
    delete from SYS_DEPT
    where DEPT_ID IN
    <foreach collection="list" item="sd" index="index" open="(" separator="," close=")">
    	#{sd.deptId,jdbcType=VARCHAR}
    </foreach>
  </delete>

3.批量改

  <update id="updateBatch" parameterType ="java.util.List">
    <foreach collection="list" item="user" index="index" open="begin" separator=";" close=";end;">
      update SYS_USER 
      set IS_USED = #{user.isUsed}
      where USER_ID = #{user.userId}
    </foreach>
  </update>