MyBatis批量插入数据到MySql数据库

由于项目需要生成数万条数据持久化到数据库当中，然后就在程序中封装了一个List集合对象，需要把该集合中的实体插入到数据库中，应该要比循环对象一个一个插入的效果更好吧，这个肯定不是最优的方法，但应该可以能减少和数据库连接吧，由于才疏学浅，有什么更好的办法进行大量数据插入到数据库中，一定要赐教啊！

具体看代码吧：

Controller层代码：

 /**
     * 新增和修改的数据进行保存
     * @param festivalbeforeJson
     * @return
     */
    @RequestMapping(value="/save", produces = MediaType.APPLICATION_JSON_VALUE)
    public Map<String, Object> save() {
        Map<String, Object> modelMap = new HashMap<>();

        List<StorageFestivalbeforeCheck> data = new ArrayList<StorageFestivalbeforeCheck>();
        //向数据库插入120000条记录
        for (int i=1; i<=120000; i++) {
           data.add(getFestivalbefore(i));
        }
        System.out.println("查看List集合存放了多少条记录。。。。。。。。"+data.size());

        long beginTime = System.currentTimeMillis();
        System.out.println("。。。。。。。。。。。开始插入。。。。。。。。。。。。。");
        int a = 3000;//每次提3000条
        int loop = (int) Math.ceil(data.size() / (double) a);

        List<StorageFestivalbeforeCheck> tempList = new ArrayList<StorageFestivalbeforeCheck>(a);
        int start, stop;
        for (int i = 0; i < loop; i++) {
           tempList.clear();
            start = i * a;
            stop = Math.min(i * a + a - 1, data.size() - 1);
            System.out.println("range:" + start + " - " + stop);
            for (int j = start; j <= stop; j++) {
               tempList.add(data.get(j));
            }

            festivalbeforeCheckService.saveList(tempList);
            System.out.println("已经插入" + (stop + 1) + " 条");
        }
        modelMap.put("status", "success");


        long endTime = System.currentTimeMillis();
        System.out.println("。。。。。。。。。。插入完成，耗时 " + (endTime - beginTime) + " 毫秒！。。。。。。。。。");



        return modelMap;
    }

 或者使用下面的更加简便的方法：

 @RequestMapping(value="/save", produces = MediaType.APPLICATION_JSON_VALUE)
    public Map<String, Object> save() {
        Map<String, Object> modelMap = new HashMap<>();
        int pageCount = 0;
List<StorageFestivalbeforeCheck> data = new ArrayList<StorageFestivalbeforeCheck>();
        //向数据库插入120000条记录
        for (int i=1; i<=120000; i++) {
           data.add(getFestivalbefore(i));
        }
        List<List<StorageFestivalbeforeCheck>> allOrdersList = Lists.partition(data , 3000);
        for (List<StorageFestivalbeforeCheck> tempOrderList : allOrdersList) {
            if(CollectionUtils.isNotEmpty(tempOrderList)){
                festivalbeforeCheckService.saveList(tempOrderList);
                pageCount++;
                if(tempOrderList.size() == 3000){
                    logger.info("已经插入条数:{}", pageCount * 3000);
                }else{
                    logger.info("已经插入条数:{}", ((pageCount-1) * 3000 + tempOrderList.size()) );
                }
            }
        }
         return modelMap;
    }

 //设置保存的记录
    public StorageFestivalbeforeCheck getFestivalbefore(int i) {
         StorageFestivalbeforeCheck festivalbeforeCheck = new StorageFestivalbeforeCheck();
         festivalbeforeCheck.setHouseId(i);
         festivalbeforeCheck.setOrgId(11);
         festivalbeforeCheck.setCheckDate(new Date());
         festivalbeforeCheck.setCheckPerson("孙悟空");
         festivalbeforeCheck.setCheckRecord(1);
         festivalbeforeCheck.setImplementCase(0);
         festivalbeforeCheck.setDutyArrangement(1);
         festivalbeforeCheck.setOperationArrangement(1);
         festivalbeforeCheck.setLeadSystem(0);
         festivalbeforeCheck.setSafetyEducation(1);
         festivalbeforeCheck.setCleaningDrain(1);
         festivalbeforeCheck.setEmergencyFacilities(0);
         festivalbeforeCheck.setEmergencyPlan(1);
         festivalbeforeCheck.setProblem("发现有漏水情况"+i);
         festivalbeforeCheck.setOpinion("必须赶紧完善"+i);
         return festivalbeforeCheck;
    }

xxxMapper.xml： 

  <insert id="saveList" parameterType="java.util.List">
     insert into storage_festivalbefore_check (
        id, house_id, org_id,
        check_date, check_person, check_record,
        implement_case, duty_arrangement, operation_arrangement,
        lead_system, safety_education, cleaning_drain,
        emergency_facilities, emergency_plan, problem,
        opinion
     )
     VALUES
       <foreach collection="list" item="item" index= "index" separator =",">
         (
           #{item.id,jdbcType=INTEGER}, #{item.houseId,jdbcType=INTEGER}, #{item.orgId,jdbcType=INTEGER},
           #{item.checkDate,jdbcType=TIMESTAMP}, #{item.checkPerson,jdbcType=VARCHAR}, #{item.checkRecord,jdbcType=INTEGER},
           #{item.implementCase,jdbcType=INTEGER}, #{item.dutyArrangement,jdbcType=INTEGER}, #{item.operationArrangement,jdbcType=INTEGER},
           #{item.leadSystem,jdbcType=INTEGER}, #{item.safetyEducation,jdbcType=INTEGER}, #{item.cleaningDrain,jdbcType=INTEGER},
           #{item.emergencyFacilities,jdbcType=INTEGER}, #{item.emergencyPlan,jdbcType=INTEGER}, #{item.problem,jdbcType=VARCHAR},
           #{item.opinion,jdbcType=VARCHAR}
         )
       </foreach>
  </insert>

xxxMapper.java(部分)

void saveList(List<StorageFestivalbeforeCheck> festivalbeforeList);

下面是Mybatis中关于foreach的介绍(从网上查看并根据自己的理解整理的)：

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。

foreach元素的属性主要有 item，index，collection，open，separator，close。

item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔 符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：

1.如果传入的是单参数且参数类型是一个List的时候并且List中保存时实体类对象，collection属性值为list，如上面的代码案例；如果List中存放的是Integer这种的封装类型，则collection属性值就是你定义的属性参数名称，比如：

List<StorageFestivalbeforeCheck> selectIds(@Param("Ids") List<Integer> Ids);

 <select id="selectIds" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from storage_festivalbefore_check
        <where>
            id in
            <foreach collection="Ids" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </where>
        ORDER BY create_time DESC
    </select>

2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map

使用批量插入执行的SQL语句应该等价于：

insert into storage_festivalbefore_check
 ( id, house_id, org_id, check_date, check_person, check_record, implement_case, duty_arrangement, operation_arrangement, lead_system, safety_education, cleaning_drain, emergency_facilities, emergency_plan, problem, opinion )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
,( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
,( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )