`

spring+mybatis实现csv批量上传

阅读更多

简单的一个小例子而已,测试了下9W多条数据导入如果不做批量插入很费时,后改为批量插入在6秒左右:

上传jsp页面:

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <script type="text/javascript" src="../js/jquery-1.11.0.min.js"></script>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Insert title here</title>
    <script type="text/javascript">
        i = 1;
        j = 1;
        $(document).ready(function(){
            $("#btn_add1").click(function(){
                document.getElementById("newUpload1").innerHTML+='<div id="div_'+i+'"><input  name="file" type="file"  /><input type="button" value="删除"  onclick="del_1('+i+')"/></div>';
                i = i + 1;
            });

            $("#btn_add2").click(function(){
                document.getElementById("newUpload2").innerHTML+='<div id="div_'+j+'"><input  name="file_'+j+'" type="file"  /><input type="button" value="删除"  onclick="del_2('+j+')"/></div>';
                j = j + 1;
            });
        });

        function del_1(o){
            document.getElementById("newUpload1").removeChild(document.getElementById("div_"+o));
        }

        function del_2(o){
            document.getElementById("newUpload2").removeChild(document.getElementById("div_"+o));
        }

    </script>
</head>
<body>

<h1>springMVC字节流输入上传文件</h1>
<form name="userForm1" action="/file/upload" enctype="multipart/form-data" method="post">
    <div id="newUpload1">
        <input type="file" name="file">
    </div>

    <input type="button" id="btn_add1" value="增加一行" >
    <input type="submit" value="上传" >
</form>
<br>
<br>
<hr align="left" width="60%" color="#FF0000" size="3">
<br>
<br>
<h1>springMVC包装类上传文件</h1>
<form name="userForm2" action="/file/upload2" enctype="multipart/form-data" method="post"">
<div id="newUpload2">
    <input type="file" name="file">
</div>
<input type="button" id="btn_add2" value="增加一行" >
<input type="submit" value="上传" >


</form>
</body>
</html>

 action部分,每1000条执行一次

 /**
     * 更新csv数据
     * @return
     */
    @RequestMapping("/updatePayFeeData")
    @ResponseBody
    public Map<String, String> updatePayFeeData(HttpServletRequest request,HttpServletResponse response){
    	Map<String,String> map = new HashMap<String, String>();
		 //创建一个通用的多部分解析器  
        CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());  
        //判断 request 是否有文件上传,即多部分请求  
        int pre = (int) System.currentTimeMillis();  
        if(multipartResolver.isMultipart(request)){  
            //转换成多部分request    
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest)request;  
            //取得request中的所有文件名  
            Iterator<String> iter = multiRequest.getFileNames();  
            while(iter.hasNext()){  
                try {
	                	MultipartFile file = multiRequest.getFile(iter.next());
	                	InputStream is=file.getInputStream();
	                	CsvUtil test = new CsvUtil(is);
	                	List<List<String>> csvList = test.readCSVFile();
	                	if (!csvList.isEmpty()) {
//	                		int resNum=payFeeService.deleteData();
	                		payFeeService.deleteData();
//	                		int j=csvList.size();
	                		List<PayFeeEntity> feeEntitiesList=new ArrayList<PayFeeEntity>();
	                		for (List<String> list : csvList) {
	                			PayFeeEntity payFeeEntity =new PayFeeEntity();
	                			payFeeEntity.setYearMon(list.get(0));
	                			payFeeEntity.setIdno(list.get(1));
	                			payFeeEntity.setMonthPay(Integer.valueOf(list.get(2)));
	                			payFeeEntity.setTotlePayfee(Long.valueOf(list.get(3)));
	                			feeEntitiesList.add(payFeeEntity);
	                			if (feeEntitiesList.size()==1000) {
//	                				int resultNum=payFeeService.insertPayFee(payFeeEntity);
	                				int resultNum=payFeeService.addStudentPath(feeEntitiesList);
	                				feeEntitiesList.clear();
								}
	                		}
	                		if (feeEntitiesList.size()>0) {
//                				int resultNum=payFeeService.insertPayFee(payFeeEntity);
                				int resultNum=payFeeService.addStudentPath(feeEntitiesList);
                				feeEntitiesList.clear();
							}
	                		 int finaltime = (int) System.currentTimeMillis();  
	                         System.out.println(finaltime - pre);  
	                		map.put("code",Constant.getCode(Constant.SUCCESS));
	                		map.put("result","success");
	                		return map;
						}else {
							map.put("code",Constant.getCode(Constant.EMP_FILE));
							map.put("result","false");
							return map;
						}
        		} catch (IOException e) {
        			// TODO Auto-generated catch block
        			e.printStackTrace();
        			map.put("code",Constant.getCode(Constant.FILE_FAIL));
        			map.put("msg",e.getMessage());
        			map.put("result","fail");
        			return map;
        		} catch (Exception e) {
        			// TODO Auto-generated catch block
        			e.printStackTrace();
        			map.put("code",Constant.getCode(Constant.FILE_FAIL));
        			map.put("msg",e.getMessage());
        			map.put("result","fail");
        			return map;
        		}
            }
        }
        map.put("code",Constant.getCode(Constant.FILE_FAIL));
		map.put("result","fail");
        return map;
		
	}

 mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.thfund.dao.PayFeeEntityMapper">
	<resultMap id="BaseResultMap" type="com.thfund.model.PayFeeEntity">
		<id column="id" property="id" jdbcType="INTEGER" />
		<result column="year_mon" property="yearMon" jdbcType="VARCHAR" />
		<result column="idno" property="idno" jdbcType="VARCHAR" />
		<result column="month_pay" property="monthPay" jdbcType="INTEGER" />
		<result column="totle_payfee" property="totlePayfee" jdbcType="BIGINT" />
	</resultMap>
	<sql id="Base_Column_List">
		id, year_mon, idno, month_pay, totle_payfee
	</sql>
	
	<insert id="addStudentPath" parameterType="java.util.List">
		insert into pay_fee_info(year_mon, idno,
		month_pay, totle_payfee)
		values
		<foreach collection="list" item="item" index="index" separator="," >  
       		 (#{item.yearMon,jdbcType=VARCHAR},#{item.idno,jdbcType=VARCHAR},#{item.monthPay,jdbcType=INTEGER},#{item.totlePayfee,jdbcType=BIGINT})  
    	</foreach>  
	</insert>	
</mapper>

 mapper.java

int addStudentPath(List<PayFeeEntity> payFeeList);

 

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics