上传CSV文件，并分批导入至数据库

1.需求场景

外部导入csv文件，将数据解析并插入到mysql数据库

2.项目环境

spring spring mvc mybatis

3.解决方法

(1) 上传并读取csv文件

	/**
	 * @TODO spring mvc 方式文件上传
	 * @param multipartFile
	 * @param request
	 * @return
	 * @throws Exception
	 */
	public static String uploadFileCSV(MultipartFile multipartFile,
			HttpServletRequest request, String realPath) throws Exception {
		// String realPath =
		// request.getSession().getServletContext().getRealPath(_path);
		String newFileName = System.currentTimeMillis() + ".csv";

		File path = new File(realPath);
		if (!path.exists()) {
			path.mkdirs();
		}
		String fileName = multipartFile.getOriginalFilename();
		fileName = realPath + File.separator + newFileName;
		File restore = new File(fileName);
		multipartFile.transferTo(restore);
		return newFileName;
	}

/**
	 * 
	 * @Title: readCSVFile
	 * @Description: TODO 解析CSV文件  使用 javacsv.jar 解析
	 * @param csvFilePath
	 * @return
	 * @throws IOException
	 */
	public static List<String[]> readCSVFile(String csvFilePath) {
		List<String[]> csvList = new ArrayList<String[]>();
		CsvReader reader = null;
		try {
			reader = new CsvReader(csvFilePath, ',', Charset.forName("UTF-8"));
			 reader.readHeaders(); // 跳过表头   如果需要表头的话，不要写这句。  
			while (reader.readRecord()) { // 逐行读入除表头的数据
				csvList.add(reader.getValues());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (reader != null) {
				reader.close();
			}
		}
		
		
		return csvList;
	}

public static boolean isCsv(String fileName) {
		if (fileName.endsWith(".csv")) {
			return true;
		} else {
			return false;
		}
	}

(2)分批执行插入数据库

/**
 *
 * TODO CSV文件上传导入到数据库
 * @param param
 *
 */
@ResponseBody
@RequestMapping("insertbatch")
public ModelAndView insertBatch(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request,String startDate, Model model) {  
	final String username=(String) request.getSession().getAttribute("username");
	String fileName="";
	String filePath="";
	String time = FormatTime.getDefaultTime();
	startDate = EncodeUtil.checkParamEmpty(startDate, time);
	if(FileUtil.isCsv(file.getOriginalFilename())){ //判断上次的文件是否是 .csv结尾
		String path = request.getSession().getServletContext().getRealPath("upload/data");
		try {
			fileName =  FileUtil.uploadFileCSV(file,request,path);
			filePath = path+File.separator+fileName;//上传文件的真实路径
			List<String[]> dataFromCSV = FileUtil.readCSVFile(filePath);
			final List<UploadBean> datalist =new ArrayList<UploadBean>();
			if(dataFromCSV!=null && dataFromCSV.size()>0){
				for (int i = 0; i < dataFromCSV.size(); i++) {
					UploadBean b = new UploadBean();
					    b.setPeriodcode(startDate);
					    b.setId(str[0]);
					    b.setPrice(str[1]);
				      datalist.add(b);
					} 
				}
				
				//执行批量插入操作(耗时)
		ExecutorService cachedThreadPool = Executors.newCachedThreadPool();  
	             cachedThreadPool.execute(new Runnable() {  
	                 public void run() {  
	                	 int len = 1000;
	     				try{
	     					if(datalist!=null && datalist.size()>0){
	     						int totalSize = datalist.size(); //总记录数
     							int pageSize = len; //每页N条
     							int totalPage = totalSize/pageSize; //共N页
     							if (totalSize % pageSize != 0) {  
     								totalPage += 1;  
     								if (totalSize < pageSize) {  
     									pageSize = datalist.size(); 
     								}  
     							}  
     					System.out.println("循环保存的次数："+totalPage);//循环多少次  
     					for (int pageNum = 1; pageNum < totalPage+1; pageNum++) {
     					int starNum = (pageNum-1)*pageSize;
     					int endNum = pageNum*pageSize>totalSize?(totalSize):pageNum*pageSize;
     					System.out.println("起始："+starNum+"-"+endNum);
     					System.out.println("第"+pageNum+"批：");
     				service.insertBatch(datalist.subList(starNum, endNum));//上传文件数据插入 表

     							}
	     					}
	     				}catch(Exception e){
	     					e.printStackTrace();
	     						try {
						EmailUtil.sendEmail("test@demo.com", "标题", "内容");
								} catch (Exception e1) {
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
	     				}
	     				 
	                 }  
	             });  
	             cachedThreadPool.shutdown();  
				
				try{
				 EmailUtil.sendEmail(filePath,"test@demo.com", "标题", "内容");
				 }catch(Exception e){
					 e.printStackTrace();
					 model.addAttribute("message", "邮件发送失败");
				 }
				 model.addAttribute("message", "文件上传成功");
			}
		}catch(Exception e){
		 e.printStackTrace();	
		}
	}
	 return new ModelAndView(new MappingJacksonJsonView());
}

(3)Mybatis 批量入库配置

<select id="insertBath"  resultType="java.lang.Integer">
	 insert into  demo
            (id,price) 
        values 
        <foreach collection="list" item="item" index="index"
        separator=",">  
            (#{item.id},#{item.price})  
        </foreach>  
	</select>