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')
WHERE
LAMP_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">
SELECT
TO_CHAR (
C.HOLIDAY_BEGIN,
'YYYY-MM-DD'
) HOLIDAY_BEGIN,
TO_CHAR (C.HOLIDAY_END, 'YYYY-MM-DD') HOLIDAY_END,
B. GROUP_ID,
A .LAMP_ID
FROM
C_LAMP A,
T_POLICY_GROUP_INFO B,
T_POLICY_GROUP C
WHERE
A .LAMP_ID = B.DEVICE_ID
AND B. GROUP_ID = C. GROUP_ID
AND A.LAMP_ID = #{lampId}
<foreach collection="list" separator="UNION ALL" index="index" item="item">
SELECT
TO_CHAR (
C.HOLIDAY_BEGIN,
'YYYY-MM-DD'
) HOLIDAY_BEGIN,
TO_CHAR (C.HOLIDAY_END, 'YYYY-MM-DD') HOLIDAY_END,
B. GROUP_ID,
A .LAMP_ID
FROM
C_LAMP A,
T_POLICY_GROUP_INFO B,
T_POLICY_GROUP C
WHERE
A .LAMP_ID = B.DEVICE_ID
AND B. GROUP_ID = C. GROUP_ID
AND A.LAMP_ID = #{item}
</foreach>
</select>