mybatis+oracle批量操作

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>