mybatis针对Oracle数据库进行（单个或多个条件）批量操作（新增、修改、删除）的sql写法--mysql

1、批量新增:
 

方式一：
<insert id="addMonthDutyIntoDB" parameterType="java.util.List">
     insert into TB_DUTY select SEQ_TB_DUTY.nextval,A.* from(
    <foreach collection="list" item="item" index="index" separator="union">  
      SELECT  #{item.dscd}, #{item.unitId},#{item.year},#{item.month},#{item.day},#{item.weekDay}, 
      #{item.morningPeopleIds}, #{item.morningPeopleNames},#{item.afternoonPeopleIds},#{item.afternoonPeopleNames},#{item.eveningPeopleIds}, 
      #{item.eveningPeopleNames},#{item.leaderIds},#{item.leaderNames},#{item.flag},#{item.remark},#{item.day0} FROM DUAL
    </foreach> 
    ) A 
  </insert>
方式二：
<!-- 批量新增-->
<insert id="batchAdd" parameterType="java.util.List">  
	 insert all 
	 <foreach collection="list" item="item" index="index" separator="" >    
		into t_student ( a, b ) values( #{item.a}, #{item.b} )
	 </foreach>   
	 select 1 from dual   
</insert>

2、批量修改：
单个条件、单个修改字段：

 <update id="auditMultiByIds" parameterType="java.util.List">  
   update tb_code_name_result_new set state=#{state,jdbcType=INTEGER} where id in
    <foreach collection="list" item="item" index="index" open="(" separator="," close=")" >  
        #{item}  
    </foreach>  
</update>

多个条件、多个修改字段：

<update id="updateByMultiConditions" parameterType="java.util.List">   
    <foreach collection="list" item="item" index="index" open="begin" close="; end;" separator=";"> 
     update TB_DUTY 
     <set>
      <if test="item.morningPeopleIds != null and item.morningPeopleIds != '' " >
     MORNING_PEOPLE_IDS=#{item.morningPeopleIds,jdbcType=VARCHAR},
      </if>
      <if test="item.morningPeopleNames != null and item.morningPeopleNames != '' " >
     MORNING_PEOPLE_NAMES=#{item.morningPeopleNames,jdbcType=VARCHAR}, 
      </if> 
      <if test="item.afternoonPeopleIds != null and item.afternoonPeopleIds != '' " >
     AFTERNOON_PEOPLE_IDS=#{item.afternoonPeopleIds,jdbcType=VARCHAR},  
      </if>
      <if test="item.afternoonPeopleNames != null and item.afternoonPeopleNames != '' " >
     AFTERNOON_PEOPLE_NAMES=#{item.afternoonPeopleNames,jdbcType=VARCHAR}, 
      </if> 
      <if test="item.eveningPeopleIds != null and item.eveningPeopleIds != '' " >
     EVENING_PEOPLE_IDS=#{item.eveningPeopleIds,jdbcType=VARCHAR},  
      </if>
      <if test="item.eveningPeopleNames != null and item.eveningPeopleNames != '' " >
     EVENING_PEOPLE_NAMES=#{item.eveningPeopleNames,jdbcType=VARCHAR},
      </if>
      <if test="item.leaderIds != null and item.leaderIds != '' " >  
     LEADER_IDS=#{item.leaderIds,jdbcType=VARCHAR},  
      </if>
     <if test="item.leaderNames != null and item.leaderNames != '' " >
     LEADER_NAMES=#{item.leaderNames,jdbcType=VARCHAR},
     </if>
     </set> 
     where DUTY_ID=#{item.dutyId,jdbcType=INTEGER}
    </foreach>
</update>

3、批量删除：
单个条件：

<delete id="delMultiByIds" parameterType="java.util.List">  
     delete from TB_CODE_NAME_RESULT_NEW
    where ID in
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">   
        #{item}   
    </foreach>  
</delete>

多个条件：

<delete id="delMultiByIds2" parameterType="java.util.List"> 
  delete from tb_duty A
  where exists 
  ( 
   select 1 from(
    <foreach collection="list" item="item" index="index" separator="union all">  
     select  B.* from tb_duty B where 1=1 and  B.dscd=${item.dscd} and B.unit_id=${item.unitId} and 
      B.year=${item.year} and B.month=${item.month} and B.flag=${item.flag} 
    </foreach>
    )S where  A.duty_id=S.duty_id
  )
</delete>

多个条件第2种形式：

<!-- 成功删除返回的是-1而不是删除的记录数 -->
	 <delete id="delMultiByIds" > 
	    <foreach collection="list" item="item" index="index"  open="begin" close="; end;" separator=";">  
		delete from tb_duty_statistics a 
		where a.person_id 
		in(
		select b.person_id from tb_duty_person_info b where b.dscd=#{dscd,jdbcType=CHAR} and b.unit_id=#{unitId,jdbcType=INTEGER}
		) 
		and substr(a.duty_id,1,7)=#{item.dutyId,jdbcType=CHAR}
		</foreach>
	</delete>

4、批量查询：（未验证）

<select id="selectBySomePoiIds" resultType="list" parameterType="java.util.Map">      
	SELECT <include refid="Base_Column_List" /> FROM 表名      
	WHERE poi_id in
	
	<foreach collection="poiIds" item="poiId" index="index" open="(" close=")" separator=",">            
	#{poiId}        
	</foreach>
	
	AND pass_uid = #{passUid}          
	<if test="status != null">            
	AND status = #{status,jdbcType=BIGINT}           
	</if>  
</select>

PS:带in的查询：
4.1mybatis sql in 查询总结

http://blog.csdn.net/u013628152/article/details/51184641

String[] array={CommonEnum.INITIATIVE_PAYMENT_AWARD_FUN.getCode(),CommonEnum.INITIATIVE_PAYMENT_AWARD_NUM.getCode()};
            	List<ModuleConfigEntity> list=moduleConfigRepository.selectMultiRecords(array);
            	if(CollectionUtils.isNotEmpty(list)){
            		for (ModuleConfigEntity moduleConfigEntity : list) {
						if(CommonEnum.INITIATIVE_PAYMENT_AWARD_FUN.getCode().equals(moduleConfigEntity.getConfigName())){
							unionPaySltEntities.get(0).setInitiativePaymentAwardFun(moduleConfigEntity.getConfigValue());//开关
						}else if(CommonEnum.INITIATIVE_PAYMENT_AWARD_NUM.getCode().equals(moduleConfigEntity.getConfigName())){
							unionPaySltEntities.get(0).setInitiativePaymentAwardNum(moduleConfigEntity.getConfigValue());//数量
						}
					}
            	}
//
<!--根据config_name查询多个开关配置-->
	<select id="selectMultiRecords" resultType="ModuleConfigEntity">
		SELECT  
		CONFIG_NAME           configName,
		CONFIG_VALUE          configValue
		FROM t_ddq_module_config 
		WHERE DELETE_FLAG=0
		and CONFIG_NAME in
		<foreach item="item" index="index" collection="array" open="(" separator="," close=")">  
		     #{item}  
		</foreach>  
	</select>

5、mapper常用写法

<resultMap id="BaseResultMap" type="com.xyxc.CustomerInfoEntity">
	<id column="id" property="id" />
	<result column="aa" property="aa" />
	<result column="bb" property="bb" />
</resultMap>

<sql id="table_column_no_id">
	aa,
	bb ,
	cc,
	dd,
	ee
</sql>

<sql id="columns" >
	id,<include refid="table_column_no_id"/>
</sql>

<sql id="table_name" >
	TableName
</sql>

<insert id="insert" parameterType="com.xyxc.CustomerInfoEntity" useGeneratedKeys="true" keyProperty="installmentId">
	<![CDATA[
		INSERT INTO T_DDQ_INSTALLMENT_INFO (
		aa,
		bb ,
		cc,
		dd,
		ee
		)
		VALUES (
		#{sltAccountId},
		#{loanPeriodNo},
		#{scheduleAmount},
		now(),
		now()
		)
	]]>

	<selectKey resultType="Long" keyProperty="installmentId" order="AFTER">
		<![CDATA[ SELECT LAST_INSERT_ID()  AS installmentId  ]]>
	</selectKey>
</insert>