ERP项目——Ibatis2.3Excel导入及数据库优化

2016/12.22

easypoi

excel导入+excel字段校验（字段长度，非空，正则，Decimal等校验）

ExcelImportResult<MiOdSoExcelImportOfMode> result=null;
        try {
            ImportParams params = new ImportParams();
            params.setNeedVerfiy(true);
            long start =System.currentTimeMillis();
            result = ExcelImportUtil.importExcelVerify(
                file, MiOdSoExcelImportOfMode.class, params);
            List<MiOdSoExcelImportOfMode> list = result.getList();
            
        } catch (Exception e) {
            
                }

代码说明

ImportParam：Excel操作设置，比如设置Excel导入校验功能：

params.setNeedVerfiy(true);

返回结果，Result：

result = ExcelImportUtil.importExcelVerify(
                file, MiOdSoExcelImportOfMode.class, params);

参数：

file:Excel文件。

MIOdSoExcelImportOfMode：Excel字段对应Model。

param：ImportParam。

2016/12.23

批量操作Excel1000条(20个字段)<1s

mybatis3.2  mysql5.6测试批量插入

<insert id="insertBatch" parameterType="ArrayList">  
	    insert people(name,email,lastname,firstname,address,sex,hair,
		hands,feet,mouse,value,value1,value2,value3,
		value4,value5,value6,value7,value8,value9)
	    values  
	    <foreach collection="list" item="item" index="index" separator="," >  
	       (#{item.name},#{item.email},#{item.lastname},#{item.firstname},#{item.address},
		#{item.sex},#{item.hair},#{item.hands},#{item.feet},#{item.mouse},#{item.value},
		#{item.value1},#{item.value2},#{item.value3},#{item.value4},#{item.value5},
		#{item.value6},#{item.value7},#{item.value8},#{item.value9})
	    </foreach>  
    </insert> 

【i3四核处理器】0.6s

2016/12.26

excel校验字段1000条（20字段）<1.6s

10000条<8s

2016/12.27

数据库Mysql5.6

1000条：数据库插入+导入excel+校验字段=2.6s

10000：数据库插入（8s）+导入excel+校验字段=16.6s

2016/12.28

ibatis优化数据库批量插入失败，1000条，22个字段，iterate批处理Oracle数据库，插入耗时30s。

Oracle不支持批量多行 insert into values

 Oracle大数据量无法使用insert all

insert all支持出错（错误提示不能大于1000列。）

<insert id ="saveBatchForMiOdSoGateway"  parameterClass ="java.util.List" >   
	<![CDATA[ 
	INSERT ALL
	]]>
	<iterate conjunction =""  >
		<![CDATA[
		INTO MI_OD_SO_GATEWAY (so_id ,total_qty ,sales_unit ,total_amt ,total_tax ,total_carriage ,total_premiums ,pay_type ,pay_company_no ,pay_no ,terms_time ,sender_country ,sender_name ,sender_city ,send_type ,wb_no ,zipcode ,send_no ,send_status ,send_message ,send_systemaccount ,so_no ,remark ,logis_company_code ,charged_weight ,other_carriage ,sender_province ,sender_city_code ,sender_company ,sender_address ,sender_phone ,sender_zipcode  )    
		VALUES (#item[].so_id#,#item[].total_qty#,#item[].sales_unit#,#item[].total_amt#,#item[].total_tax#,#item[].total_carriage#,#item[].total_premiums#,#item[].pay_type#,#item[].pay_company_no#,#item[].pay_no#,#item[].terms_time#,#item[].sender_country#,#item[].sender_name#,#item[].sender_city#,#item[].send_type#,#item[].wb_no#,#item[].zipcode#,#item[].send_no#,#item[].send_status#,#item[].send_message#,#item[].send_systemaccount#,#item[].so_no#,#item[].remark#,#item[].logis_company_code#,#item[].charged_weight#,#item[].other_carriage#,#item[].sender_province#,#item[].sender_city_code#,#item[].sender_company#,#item[].sender_address#,#item[].sender_phone#,#item[].sender_zipcode#) 
		]]>
	</iterate>
	<![CDATA[ 
	select * from dual
	]]>
</insert > 

insert into values 不支持。

<!-- 批量把保存miOdSoItems-->
<insert id ="saveListMiOdSoItems"  parameterClass ="java.util.List" >   
    <![CDATA[  
        INSERT INTO MI_OD_SO_ITEMS (is_abnormal,so_row_id ,so_id ,so_row_no ,goods_name ,cust_goods_no ,wms_goods_no ,org_id ,warehouse_id ,batch_no ,item_qty ,rmb_price ,rmb_price_tex ,tex_rate ,tex_amt ,rmb_amt ,rmb_amt_tex ,orgin_price ,orgin_cur_amt ,carriage_fee ,insurance_fee ,import_date ,product_country ,gross_weight ,net_weight ,hs_code ,spec_code ,ref_contract_row ,remark ,item_unit ,numerator ,exchange_unit ,denominator ,ref_order_row ,created_by ,created_time ,is_inactive ,updated_by ,updated_time ,version ,id ,isolation_code ,is_del ,goods_model ,goods_spec ,legal_unit_a ,legal_unit_b ,legal_volumne_a ,legal_volumne_b ,basic_unit ,goods_id ,goods_code ,base_unit_qty ,goods_owner ,goods_brand_name ,goods_name_en ,so_no ,ext_code ) 
    ]]>    
    <iterate  conjunction ="," >   
        <![CDATA[  
        ( #list[].is_abnormal#  ,#list[].so_row_id#  ,#list[].so_id#  ,#list[].so_row_no#  ,#list[].goods_name#  ,#list[].cust_goods_no#  ,#list[].wms_goods_no#  ,#list[].org_id#  ,#list[].warehouse_id#  ,#list[].batch_no#  ,#list[].item_qty#  ,#list[].rmb_price#  ,#list[].rmb_price_tex#  ,#list[].tex_rate#  ,#list[].tex_amt#  ,#list[].rmb_amt#  ,#list[].rmb_amt_tex#  ,#list[].orgin_price#  ,#list[].orgin_cur_amt#  ,#list[].carriage_fee#  ,#list[].insurance_fee#  ,#list[].import_date#  ,#list[].product_country#  ,#list[].gross_weight#  ,#list[].net_weight#  ,#list[].hs_code#  ,#list[].spec_code#  ,#list[].ref_contract_row#  ,#list[].remark#  ,#list[].item_unit#  ,#list[].numerator#  ,#list[].exchange_unit#  ,#list[].denominator#  ,#list[].ref_order_row#  ,
          #list[].created_by#  ,#list[].created_time#  ,#list[].is_inactive#  ,#list[].updated_by#  ,#list[].updated_time#  ,#list[].version#  ,#list[].id#  ,#list[].isolation_code#  ,#list[].is_del#  ,#list[].goods_model#  ,#list[].goods_spec#  ,#list[].legal_unit_a#  ,#list[].legal_unit_b#  ,#list[].legal_volumne_a#  ,#list[].legal_volumne_b#  ,#list[].basic_unit#  ,#list[].goods_id#  ,#list[].goods_code#  ,#list[].base_unit_qty#  ,#list[].goods_owner#  ,#list[].goods_brand_name#  ,#list[].goods_name_en#  ,#list[].so_no# ,#list[].ext_code#  )
        ]]>   
    </iterate >   
</insert > 

2016/12.29

easypoi导入项目，测试。

Oracle批量查询优化。

批量查询

<select id="getBatchMiOdSoByso_noAndcust_id" parameterClass="java.util.List" resultClass="miOdSo">
	select 
		so_id,so_no,so_status,pay_sender_status,cust_id
	from 
		MI_OD_SO
  	WHERE 
  		(so_no,cust_id,isolation_code,is_del) 
  	IN
	<iterate conjunction="," close=")" open="(" >  
	(#item[].so_no#,#item[].cust_id#,#item[].isolation_code#,'0') 
	</iterate>  
</select>

思路：jdbc优化Oracle数据库批量插入。

2017/1、3

批量查询完成。

总结：1、批量查询效率远高于单次查询（遍历查询）

2、批量查询业务逻辑处理多与单次查询，以空间换时间。

2017/1/4

项目优化完成。

2017/1/5

部署测试，测试服务器1000条Excel，用时70s。

处理5000条数据报错，报错原因：Oracle批量插入SQL语句长度限制，当前项目多与1500条报错。

Caused by: java.lang.Throwable:   
--- The error occurred while applying a parameter map.  
--- Check the MiOdSoServiceImpl.saveListMiOdSo-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: java.sql.SQLException: ORA-01745: invalid host/bind variable name

2017/1/6

测试服务器测试，分组导入数据库，本地测试成功，服务器报错

ORA-00903：invalid table name。

错误原因：测试服务器数据库连接池的数据库连接数过少，导致无法连接数据库失败。

总结：本地部署成功，服务器部署失败，一定是服务器配置出错。

项目总结：

easypoi效率提高将近百倍，批量处理优化。

数据校验，批量处理远远高于单次处理（遍历处理），处理数据量越多批量处理的效率越明显，但是，批量处理带来就是业务逻辑的代码量，而这一点就体现出的编程中的性能优化——空间换时间的思想。对于程序开发来说，时间复杂度和空间复杂度是可以相互转化的。说通俗一点，就是：对于执行的慢的程序，可以通过消耗内存（即构造新的数据结构）来进行优化。而消耗内存的程序，也可以多消耗时间来降低内存的消耗。 

1、在程序开发过程中，我们对于数据的处理，会有一些校验。校验分为两种：简单校验和复杂校验。

  对于一些简单的校验，如用户是否存在，密码是否正确等等。这种校验，可以说几乎不耗时的。所以也没必要在这里做优化。 
  对于复杂的校验，需要进行联合查询，通过查询很多次之后，才可以得出数据的正确性与否。当然这种校验执行会很慢。

2、复杂校验的优化：

①、批量处理。

②、重塑数据结构。

链接： 

java性能优化——数据结构