1.批量新增
主键自增
<insert id="timeTaskBatch" parameterType="java.util.List">insert into t_policy_schedule(SCHEDULE_ID,policy_id, start_date, end_date,turn_on_time, turn_off_time)SELECT SEQ_T_POLICY_SCHEDULE.Nextval,a.* FROM(<foreach collection="list" item="item" index="index" separator="union all">SELECT#{item.policyId},#{item.startDate}, #{item.endtDate}, #{item.turnOnTime}, #{item.turnOffTime}FROM dual</foreach>)a</insert>
不自增
<insert id="insertCCubicle" parameterType="java.util.List" useGeneratedKeys="false">INSERT ALL<foreach collection="list" item="item" index="index">INTO C_CUBICLE (CUBICLE_ID,CUBICLE_NAME,CUBICLE_TYPE,ORG_NO,NOTE)VALUES(#{item.cubicleId},#{item.cubicleName},#{item.cubicleType},#{item.orgNo},#{item.note})</foreach>SELECT 1 FROM DUAL</insert>
2.批量修改
<update id="updateTemp" parameterType="java.util.List">UPDATE T_LAMP_BRIGHT_TEMP<foreach collection="list" item="item" index="index">SET BRIGHT = #{item.bright},END_TIME = TO_DATE (#{item.time}, 'YYYY-MM-DD HH24:MI')WHERELAMP_ID = #{item.lightId}</foreach></update>
<update id="updateGLineById" parameterType="java.util.List">update g_line set a_phase = seq_a_phase.nextval , b_phase = seq_b_phase.nextval ,c_phase = seq_c_phase.nextval where line_id in<foreach collection="list" item="item" index="index" separator="," open="(" close=")">#{item.lineId}</foreach></update>
3.批量删除
<delete id="deleteTemplate1" parameterType="java.lang.String">delete from t_task_template where template_id in<foreach collection="array" item="item" index="index" separator="," open="(" close=")">#{item}</foreach></delete>
4.批量查询
<select id="checkChildNode1" resultType="java.util.Map">select 1 from C_CUBICLE where ORG_NO in<foreach collection="array" index="index" item="item" open="(" separator="," close=")">#{item}</foreach></select>
<select id="selectCLamp5" resultType="java.util.Map">SELECTTO_CHAR (C.HOLIDAY_BEGIN,'YYYY-MM-DD') HOLIDAY_BEGIN,TO_CHAR (C.HOLIDAY_END, 'YYYY-MM-DD') HOLIDAY_END,B. GROUP_ID,A .LAMP_IDFROMC_LAMP A,T_POLICY_GROUP_INFO B,T_POLICY_GROUP CWHEREA .LAMP_ID = B.DEVICE_IDAND B. GROUP_ID = C. GROUP_IDAND A.LAMP_ID = #{lampId}<foreach collection="list" separator="UNION ALL" index="index" item="item">SELECTTO_CHAR (C.HOLIDAY_BEGIN,'YYYY-MM-DD') HOLIDAY_BEGIN,TO_CHAR (C.HOLIDAY_END, 'YYYY-MM-DD') HOLIDAY_END,B. GROUP_ID,A .LAMP_IDFROMC_LAMP A,T_POLICY_GROUP_INFO B,T_POLICY_GROUP CWHEREA .LAMP_ID = B.DEVICE_IDAND B. GROUP_ID = C. GROUP_IDAND A.LAMP_ID = #{item}</foreach></select>