mysql 批量插入和批量更新的

来源：互联网发布：淘宝魔盒有什么用编辑：程序博客网时间：2024/04/29 12:31

利用mysql的insert into 表名 values (.....),(.....),(.....),(.....) 特性一次性大批量的插入数据，提高效率

备注：我用的是ibatis

业务层：

private void batchInsertAct(List<ProgramDetail> acts) throws DataBaseException {        Map<Integer, List<ProgramDetail>> map = this.groupListBySize(acts, GROUP_SIZE);        if ((map != null) && !map.isEmpty()) {            for (Integer key : map.keySet()) {                Map<String, Object> paramaters = new HashMap<String, Object>();                paramaters.put("acts", map.get(key));                this.programDetailDao.batchInsertAct(paramaters);            }        }    }

数据库层:

<insert id="batchInsertAct" parameterClass="map">     <![CDATA[     INSERT INTO T_PROGRAMDETAIL(PROGRAMDETAILID,PROGRAMLISTID,PROGRAMTIME,PROGRAMNAME,VISIBLE,COLLATESTATE,VIDEOSTARTTIME,VIDEOENDTIME) VALUES     ]]>     <iterate property="acts" conjunction=",">          <![CDATA[             (null,#acts[].programListId#, #acts[].programTime#, #acts[].programName#, 0, #acts[].collateState#, #acts[].videoStartTime#, #acts[].videoEndTime#)           ]]>     </iterate>   </insert>

利用mysql的replace into 表名 values (.....),(.....),(.....),(.....) 特性一次性大批量的修改数据，提高效率

业务层：

private void batchUpdateAct(List<ProgramDetail> acts) throws DataBaseException {        Map<Integer, List<ProgramDetail>> map = this.groupListBySize(acts, GROUP_SIZE);        if ((map != null) && !map.isEmpty()) {            for (Integer key : map.keySet()) {                Map<String, Object> paramaters = new HashMap<String, Object>();                paramaters.put("acts", map.get(key));                this.programDetailDao.batchUpdateAct(paramaters);            }        }    }

数据库层：

<insert id="batchUpdateAct" parameterClass="map">     <![CDATA[     REPLACE INTO T_PROGRAMDETAIL(PROGRAMDETAILID,PROGRAMLISTID,PROGRAMTIME,PROGRAMNAME,VISIBLE,COLLATESTATE,VIDEOSTARTTIME,VIDEOENDTIME) VALUES     ]]>     <iterate property="acts" conjunction=",">          <![CDATA[             (#acts[].programDetailId#,#acts[].programListId#, #acts[].programTime#, #acts[].programName#, 0, #acts[].collateState#, #acts[].videoStartTime#, #acts[].videoEndTime#)           ]]>     </iterate>   </insert>

0 0