mybatis oracle两种方式批量插入数据(带序号)

需求

批量从excle导入数据

jsp

<input id="file" name="file" type="file"/>
<input id="import" type="button" value="导入">

js

$("#import").click(function () {
        	var file = $("#file");
            if (file.value == "") {
                JEND.page.alert("请选择文件");
                return false;
            }
            if (file.val().indexOf("xlsx") == -1) {
                JEND.page.alert("请选择正确文件");
                return false;
            }
            var formData = new FormData();
            formData.append("file", file[0].files[0]);
            $("#import").attr("disabled",true); 
            $("#import").val("请稍后...");
            $.ajax({
                url: "<%=basePath %>/import/importFile",
                type: "POST",
                data: formData,
                contentType: false,
                processData: false,
                success: function (data) {
                    console.log(data);
                    alert(data)
                }
            });

        });

java

ImportExcelUtils.java

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSON;

public class ImportExcelUtils {

    public static final String OFFICE_EXCEL_XLS = "xls";
    public static final String OFFICE_EXCEL_XLSX = "xlsx";

    /**
     * 读取指定Sheet也的内容
     * @param filepath filepath 文件全路径
     * @param sheetNo sheet序号,从0开始,如果读取全文sheetNo设置null
     * @param rowNo rows序号, 从0开始
     */
    public static String readExcel(InputStream inputStream, Integer sheetNo, Integer rowNo)
            throws EncryptedDocumentException, InvalidFormatException, IOException {
        StringBuilder sb = new StringBuilder();
        Workbook workbook = null;
        try {
        	workbook = WorkbookFactory.create(inputStream);
            if (workbook != null) {
            	if (sheetNo == null) {
            		int numberOfSheets = workbook.getNumberOfSheets();
            		for (int i = 0; i < numberOfSheets; i++) {
            			Sheet sheet = workbook.getSheetAt(i);
            			if (sheet == null) {
            				continue;
            			}
            			sb.append(readExcelSheet(sheet, rowNo));
            		}
            	} else {
            		Sheet sheet = workbook.getSheetAt(sheetNo);
            		if (sheet != null) {
            			sb.append(readExcelSheet(sheet, rowNo));
            		}
            	}
            }
        } finally {
            if (workbook != null) {
            	workbook.close();
            }
        }
        return sb.toString();
    }

    private static String readExcelSheet(Sheet sheet, int rowNo) {
    	List<Map<String, String>> list = null;
        if(sheet != null){
            int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数
            list = new ArrayList<Map<String, String>>(rowNos);
            for (int i = rowNo; i <= rowNos; i++) {// 遍历行
                Row row = sheet.getRow(i);
                if(row != null){
                    int columNos = row.getLastCellNum();// 表头总共的列数
                    Map<String, String> map = new HashMap<String, String>(columNos);
                    for (int j = 0; j < columNos; j++) {
                        Cell cell = row.getCell(j);
                        if(cell != null){
                        	cell.setCellType(Cell.CELL_TYPE_STRING);
                            map.put(j+"", cell.getStringCellValue());
                        }
                    }
                    list.add(map);
                }
            }
        }
        
        return JSON.toJSONString(list);
    }

}

Testing

获取excel数据

String data = ImportExcelUtils.readExcel(file.getInputStream(), 0, 1);

List<Map<String, String>> list = JSON.parseObject(data, new TypeReference<List<Map<String, String>>>() {
		});
for (Map<String, String> map : list) {
}

批量执行

/**
     * 批量新增
     * @param dtoList
     * @return 
     */
    @Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=3600,rollbackFor=Exception.class)
    public int batchInsertService(List<> dtoList){
    	int result = 0;
    	//限制条数
        int dataLimit = 800;
        
    	if(null != dtoList && dtoList.size() > 0) {
			//判断是否有必要分批
			Integer size = dtoList.size();
    		if(dataLimit < size){
    			//分批数
    			int part = size/dataLimit;
    			logger.info("batchInsert 共有 ： "+size+"条，！"+" 分为 ："+part+"批");
    			
    			// 整数倍
    			for (int i = 0; i < part; i++) {
    				List<> listPage = dtoList.subList(0, dataLimit);
    	    		logger.info("batchInsert part = "+(i+1)+",result="+result);
    	    		result = Mapper.batchInsert(listPage);
    				//剔除
    	    		dtoList.subList(0, dataLimit).clear();
    			}
    			
    			// 余数
    			if(!dtoList.isEmpty()){
    				result = Mapper.batchInsert(dtoList);
    	    		logger.info("batchInsert other result="+result);
    			}
    		} else {// 无需分批
    			if(!dtoList.isEmpty()){
    				logger.info("batchInsert insertList="+JSON.toJSONString(dtoList));
	    			result = Mapper.batchInsert(dtoList);
		    		logger.info("batchInsert result="+result);
    			}
    		}
    	}
    	return result;
    }

mybatis.xml

方式一：有返回值

<insert id="batchInsert" parameterType="java.util.List">  
	insert into CUSTOMER_RELATION  
		(ID, MERCHANT_ID, CUSTOM_ID, CUSTOM_NAME, PROVINCE_CODE, PROVINCE_NAME, CITY_CODE, CITY_NAME, AREA_CODE, AREA_NAME, TOWN_CODE, TOWN_NAME, COMPANY_NAME, MERCHANT_NAME, ROW_CREATE_TIME, ROW_LASTUPDATE_TIME, STATUS)
	select SEQ_CUST_REL.NEXTVAL, jcr.*, sysdate, sysdate, 1 from(
		<foreach collection="list" item="item" index="index" separator="union all" >  
	        select #{item.merchantId,jdbcType=VARCHAR}, #{item.customId,jdbcType=VARCHAR}, #{item.customName,jdbcType=VARCHAR}, #{item.provinceCode,jdbcType=VARCHAR}, #{item.provinceName,jdbcType=VARCHAR}, #{item.cityCode,jdbcType=VARCHAR}, #{item.cityName,jdbcType=VARCHAR}, #{item.areaCode,jdbcType=VARCHAR}, #{item.areaName,jdbcType=VARCHAR}, #{item.townCode,jdbcType=VARCHAR}, #{item.townName,jdbcType=VARCHAR}, #{item.companyName,jdbcType=VARCHAR}, #{item.merchantName,jdbcType=VARCHAR}  
	        from dual  
	    </foreach>  
    )jcr
</insert>

方式二：无返回值

<insert id="batchInsert" parameterType="java.util.List">  
	    BEGIN  
	    <foreach collection="list" item="item" index="index" separator="">  
	        insert into CUSTOMER_RELATION  
	        (ID, MERCHANT_ID, CUSTOM_ID, CUSTOM_NAME, ROW_CREATE_TIME, ROW_LASTUPDATE_TIME, STATUS, PROVINCE_CODE, PROVINCE_NAME, CITY_CODE, CITY_NAME, AREA_CODE, AREA_NAME, TOWN_CODE, TOWN_NAME, COMPANY_NAME, MERCHANT_NAME)
	        VALUES  
	        (  
	        SEQ_CUST_REL.NEXTVAL,#{item.merchantId,jdbcType=VARCHAR}, 
	    		#{item.customId,jdbcType=VARCHAR}, 
	    		#{item.customName,jdbcType=VARCHAR},
	      		sysdate,
	      		sysdate,
	      		1,
	      		#{item.provinceCode,jdbcType=VARCHAR},
	    		#{item.provinceName,jdbcType=VARCHAR},
	    		#{item.cityCode,jdbcType=VARCHAR},
	    		#{item.cityName,jdbcType=VARCHAR},
	    		#{item.areaCode,jdbcType=VARCHAR},
	    		#{item.areaName,jdbcType=VARCHAR},
	    		#{item.townCode,jdbcType=VARCHAR},
	    		#{item.townName,jdbcType=VARCHAR},
	    		#{item.companyName,jdbcType=VARCHAR},
	    		#{item.merchantName,jdbcType=VARCHAR});  
	    </foreach>  
	    COMMIT;  
	    END;  
	</insert>  

bug

此处不予许序列

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-02287: 此处不允许序号

### The error may involve com.adou.adminservice.item.dao.UserMapper.batchInsertUser-Inline
### The error occurred while setting parameters
### SQL: insert into CUSTOMER_RELATION     (ID, MERCHANT_ID, MERCHANT_NAME, STATUS, AREA_ID, ROW_CREATE_TIME, ROW_LASTUPDATE_TIME)               select SEQ_CUSTOMER_RELATION.NEXTVAL,?, ?, ?, ?            from dual        union all            select SEQ_CUSTOMER_RELATION.NEXTVAL,?, ?, ?, ?            from dual
### Cause: java.sql.SQLSyntaxErrorException: ORA-02287: 此处不允许序号

解决： union all不予许使用序列

select 'sup30', '供应商名称14', 1, 1,sysdate,sysdate
    from dual
  union all
  select 'sup30', '供应商名称14', 1, 1,sysdate,sysdate from dual