mybatis用注解的方式批量插入数据

前言：我尝试过用for循环直接将10W数据插入mysql数据库，用时四分半左右。用批量插入的方式用两分钟左右，用时减少一半。插入一万条数据在12秒左右

 

CommodityController代码

package security.controller;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import cn.hutool.core.util.RandomUtil;
import security.service.CommodityService;

@Controller
@RequestMapping("commodity")
public class CommodityController {
	private static Logger log =LoggerFactory.getLogger(CommodityController.class);
	@Autowired
	private CommodityService commodityService;
	
	@RequestMapping("add")
	public void add() {
		long systime = new Date().getTime();//当前系统时间
       
		String sex = "a";
		String money = "100";
		int frequency = 12;
		String chepai = "M12345";
		String phone = "123456789";
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		for(int i=0;i<10;i++) {
			for(int j=0;j<10000;j++) {
				String id = RandomUtil.randomUUID();
				String username = "a"+j;
				Map<String, Object> usermap = new HashMap<String, Object>();
				usermap.put("sex", sex);
				usermap.put("id", id);
				usermap.put("username", username);
				usermap.put("money", money);
				usermap.put("chepai", chepai);
				usermap.put("phone", phone);
				usermap.put("frequency", frequency);
				usermap.put("beizhu", "备注");
				list.add(usermap);
			}
			log.debug("执行第： "+i+" 组数据,组内数据条数为："+list.size()+" 条");
			
			commodityService.addservice(list);
			list.clear();
			log.debug("集合中数据后的数据条数为："+list.size()+" 条");
		}
		 long oldtime = new Date().getTime();//相比较的时间
	     Long time = (oldtime-systime);//相差毫秒数
	     log.debug("用时："+time);
		
	}
}

CommodityService代码

注释掉的部分为用for循环传入参数插入数据库的传值

package security.service;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import security.mapper.CommodityMapper;

@Service
public class CommodityService {
	
	@Autowired
	private CommodityMapper commodityMapper;
	
	/*
	 * public int addservice(String id,String name,String phone, @Param("chepai")
	 * String chepai, @Param("money") String money ,@Param("sex") String sex
	 * ,@Param("beizhu") String beizhu) { return commodityMapper.add(id, name,
	 * phone, chepai, money, sex, beizhu); }
	 */
	public int addservice(List<Map<String, Object>> list) {
		return commodityMapper.add(list);
	}
}

CommodityMapper代码

注释掉的部分为用for循环传入参数插入数据库的传值及sql语句

 

package security.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

public interface CommodityMapper {

	/*
	 * @Insert("INSERT INTO user(id,username,sex,frequency,money,chepai,phone,beizhu) VALUES(#{id},#{name},#{sex},0,#{money},#{chepai},#{phone},#{beizhu})"
	 * ) int add(@Param("id") String id,@Param("name") String name,@Param("phone")
	 * String phone, @Param("chepai") String chepai, @Param("money") String money
	 * ,@Param("sex") String sex ,@Param("beizhu") String beizhu);
	 */
	 
	 @Insert("<script>"  +
		 "INSERT INTO user(id,username,sex,frequency,money,chepai,phone,beizhu) "
	 		+ "VALUES <foreach collection=\"list\" item=\"usermap\" index=\"index\" separator=\",\">" + 
	 		"(#{usermap.id},#{usermap.username},#{usermap.sex},#{usermap.frequency},#{usermap.money},#{usermap.chepai},#{usermap.phone},#{usermap.beizhu})" + 
	 		" </foreach>"
	 		+ "</script>")
	 int add(@Param("list") List<Map<String, Object>> list);
	 
}

@Insert（）括号里面有的会加上{}（如下图查询语句所示），可是我加上后会导致拼接sql语句错误，具体原因没有分析出来。