mybatis批量处理

 

1.list数据批量插入

int insertPaperDetailBatch(List<Map<String, Object>> paperList);

　　<insert id="insertPaperDetailBatch">
        insert into T_PAPERDETAIL
        (SBJ_CODE,INDI_CODE,PAPER_ID,SCORE)
        <foreach close=")" collection="list" item="item" index="index"
                 open="(" separator="union">
            select
            #{item.sbjCode},
            #{item.indiCode},
            #{item.paperId},
            #{item.score}

            from dual
        </foreach>
    </insert>

2.根据现有表数据判断是插入数据还是更新数据

<insert id="insertExamEEBatchMergeNotSelf">
        merge into T_EXAMINEE e
        using
        (select
        distinct(EXA_CODE) as EXA_CODE,
        PLAN_CODE,
        EXA_DEPT_CODE,
        EXA_DUTY_CODE,
        GROUP_ID,
        SCORE_STATE
        from T_EXAMREL_WK) t
        on (e.PLAN_CODE = t.PLAN_CODE and e.EXA_CODE =t.EXA_CODE and e.isself is null)
        when not matched then
        insert
        (
        EXA_ID,
        PLAN_CODE,
        EXA_CODE,
        EXA_DEPT_CODE,
        EXA_DUTY_CODE,
        GROUP_ID,
        SCORE_STATE
        )
        values
        (
        'EA'||SEQ_EXA_ID.NEXTVAL,
        t.PLAN_CODE,
        t.EXA_CODE,
        t.EXA_DEPT_CODE,
        t.EXA_DUTY_CODE,
        t.GROUP_ID,
        t.SCORE_STATE

        )
        when matched then
        update
        set
        EXA_DEPT_CODE =t.EXA_DEPT_CODE,
        EXA_DUTY_CODE = t.EXA_DUTY_CODE,
        GROUP_ID = t.GROUP_ID,
        SCORE_STATE = t.SCORE_STATE

    </insert>

3.根据list数据判断进行merge操作

int insertPlugrelBatch(List<Map<String,Object>> plugrelList);

<insert id="insertPlugrelBatch">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            merge into T_PLUG t
            using
            (select #{item.key1} as planCode,
            #{item.key2} as empeCode,
            #{item.key6} as officeCode,
            #{item.key4} as deptCode
            from dual) u
            on (t.PLAN_CODE = u.planCode and
            t.EMPE_CODE = u.empeCode and
            t.DEPT_CODE = u.deptCode
            )
            when not matched then
            insert
            (PLAN_CODE,
            EMPE_CODE,
            EMPE_NAME,
            DEPT_CODE,
            DEPT_NAME,
            OFFICE_CODE,
            OFFICE_NAME)
            values
            (#{item.key1},
            #{item.key2},
            #{item.key3},
            #{item.key4},
            #{item.key5},
            #{item.key6},
            #{item.key7}
            )
            when matched then
            update
            set
            EMPE_NAME = #{item.key3},
            DEPT_NAME = #{item.key5},
            OFFICE_NAME = #{item.key7}

        </foreach>
    </insert>

4.查询数据存放map中，避免创建对象，实际返回对象也是将对应结果放入对象变量中

List<Map<String, Object>> selectEx();
 <select id="selectEx" resultType="java.util.HashMap">
        SELECT
        PLAN_CODE,
        EVA_CODE,
        EXA_CODE
        FROM T_EX
    </select>