ojdbc14.jar包解決65536批量插入不能的問題

使用oracle驅動ojdbc14包進行批量插入時，當遇到65536條數時會遇到插入不能的問題，導致後續插入操作無法完成，解決該問題，需要在代碼中進行資料的

分組操作，讓每次執行的條數不超過65536條，如下為批量插入ORACLE大量資料的例子代碼。

1。 批量處理公用方法

package com.ffcs.icity.common.util;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.apache.commons.collections.CollectionUtils;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.ffcs.icity.commons2.jdbc.BatchUpdateTemplate;/** * 提供的批量處理公用方法 * @author linwei * */public class BatchAddUtil {private final static Logger log = LoggerFactory.getLogger(BatchAddUtil.class);//每次操作的最大數量private static final int PER_MAX_NUM = 65000;/** * 大量新增方法 * @param dbType   具體數值為"oracle","mysql"等 * @param sql * @param batchArgs * @param dataSource * @return */public static Map<String,Integer> batchAdd(String dbType,String sql,List<Object[]> batchArgs,DataSource dataSource) {if(CollectionUtils.isEmpty(batchArgs)) {log.error("in BatchAddUtil,batchArgs is null.");return null;}BatchUpdateTemplate template = new BatchUpdateTemplate(dataSource);Map<String,Integer> map = new HashMap<String,Integer>();List<Object[]> temp = null;int[] res = null;Map<String,Integer> tempMap = null;//如果是oracle資料庫的話，因為有65536的行限制，所以需要進行分批進行批處理操作。if("oracle".equals(dbType)) {List<Object> tempList =  parseParamList(batchArgs);for(int i=0;i<tempList.size();i++) {temp = (List<Object[]>)tempList.get(i);long a = System.currentTimeMillis();res = template.batchUpdate(sql,temp);long b = System.currentTimeMillis();log.info("in batchAdd,i=" + i + ",and time is " + (b-a)/1000);tempMap = parseBatchResult(res);if(tempMap != null && tempMap.size() > 0) {map.putAll(tempMap);}}} else {//如果是其他資料庫類型的話，直接批量插入即可。res = template.batchUpdate(sql,batchArgs);tempMap = parseBatchResult(res);map = tempMap;}return map;}/** * 採用分組來實現分批資料的插入操作。 * @param list * @return */private static List<Object> parseParamList(List<Object[]> list) {List<Object[]> temp = null;List<Object> resList = new ArrayList<Object>();//把總的資料分組，以便後面使用int size = list.size();int sumSize = size/PER_MAX_NUM + 1;int startPos = 0;int endPos = 0;for (int i = 0 ;i < sumSize;i++){startPos = i * PER_MAX_NUM;endPos = (i + 1) * PER_MAX_NUM;//如果最後的分組數大於總的列表數量,取最後剩餘的資料為一組if(endPos > size) {temp = list.subList(startPos,size);resList.add(temp);break;}temp = list.subList(startPos,endPos);resList.add(temp);}return resList;}/** * 直接進行返回資料的解析工作 * @param result * @return */private static Map<String,Integer> parseBatchResult(int[] result) {Map<String,Integer> map = new HashMap<String,Integer>();if(result != null && result.length > 0) {String temp = null;int intTemp = 0;for(int i=0;i<result.length;i++) {temp = String.valueOf(result[i]);if(map.containsKey(temp)) {intTemp = map.get(temp);map.put(temp,intTemp + 1);} else {map.put(temp, 0);}}}return map;}}

2。 批量處理公用模板

package com.ffcs.icity.commons2.jdbc;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.text.SimpleDateFormat;import java.util.Arrays;import java.util.Date;import java.util.List;import javax.sql.DataSource;import org.slf4j.Logger;import org.slf4j.LoggerFactory;/** * 此類提供進行JDBC批量處理的便捷方法. *  * @Copyright: Copyright (c) 2013 FFCS All Rights Reserved * @Company: 北京福富軟體有限公司 * @author 陳作朋 Oct 16, 2013 * @version 1.00.00 * @history: *  */public class BatchUpdateTemplate {private final static Logger logger = LoggerFactory.getLogger(BatchUpdateTemplate.class);private final static Logger batchUpdateMessagelogger = LoggerFactory.getLogger("BatchUpdateTemplate.batchupdate");private DataSource dataSource;public BatchUpdateTemplate(DataSource dataSource){this.dataSource = dataSource;}public int[] batchUpdate(String sql, List<Object[]> batchArgs) {Connection connection = null;PreparedStatement pstmt = null;int[] result = null;try {connection = dataSource.getConnection();connection.setAutoCommit(false);pstmt = connection.prepareStatement(sql);for (int i = 0; i < batchArgs.size(); i++) {Object[] args = batchArgs.get(i);for (int j = 0; j < args.length; j++) {pstmt.setObject(j + 1, args[j]);}pstmt.addBatch();}result = pstmt.executeBatch();connection.commit();} catch (Exception e) {logger.error("failure to batchUpdate - SQL: " + sql, e);if (e instanceof BatchUpdateException) {handleBatchUpdateException(connection,pstmt,sql, batchArgs, (BatchUpdateException) e);}} finally {if (null != pstmt) {try {pstmt.close();} catch (SQLException e) {logger.error("failure to close PreparedStatement", e);}}if (null != connection) {try {connection.close();} catch (Exception e) {logger.error("failure to close Connection", e);}}}return result;}private void handleBatchUpdateException(Connection connection,PreparedStatement pstmt,String sql, List<Object[]> batchArgs, BatchUpdateException batchUpdateException) {int batchSize = batchArgs.size();int[] updateCounts = batchUpdateException.getUpdateCounts();int handledCount = updateCounts.length;String bid = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());batchUpdateMessagelogger.error("------ BatchUpdate[{}] --- batchSize: {} --- handledCount: {} --- Exception caused by {} ------", bid, batchSize,handledCount, batchUpdateException.getLocalizedMessage());batchUpdateMessagelogger.error("BatchUpdate[{}] - updateCounts: {}", bid,Arrays.toString(updateCounts));boolean isRoolback=false;if (handledCount < batchSize) {try {connection.commit();batchUpdateMessagelogger.error("BatchUpdate[{}] - failed update[SQL: {},Args: {}],caused by {}", bid, sql, Arrays.toString(batchArgs.get(handledCount)), batchUpdateException.getLocalizedMessage());for (int j = updateCounts.length + 1; j < batchArgs.size(); j++) {batchUpdateMessagelogger.error("BatchUpdate[{}] - interrupted -  SQL: {},Args: {}", bid, sql, Arrays.toString(batchArgs.get(j)));}} catch (Exception e) {logger.error("BatchUpdate["+bid+"] failure to commit - SQL: " + sql, e);isRoolback=true;}} else {isRoolback=true;}if(isRoolback){try {connection.rollback();} catch (SQLException e1) {logger.error("BatchUpdate["+bid+"] failure to rollback - SQL: " + sql, e1);} finally {try {pstmt.clearBatch();} catch (SQLException e2) {logger.error("BatchUpdate["+bid+"] failure to clearBatch - SQL: " + sql, e2);}}batchUpdateMessagelogger.error("BatchUpdate[{}] - rollback BatchUpdate,caused by {}", bid, batchUpdateException.getLocalizedMessage());for (int i = 0; i < updateCounts.length; i++) {batchUpdateMessagelogger.error("BatchUpdate[{}] - rollback - SQL: {},Args: {}", bid, sql, Arrays.toString(batchArgs.get(i)));}}batchUpdateMessagelogger.error("------ BatchUpdate[{}] ------", bid);}}

3。 調用代碼

String sql = "insert into " + tableUsername + "TEMP_MSGPUSH_RELA(MOBILE,IMSI) values (?, ?)";Map<String,Integer> map = BatchAddUtil.batchAdd("oracle", sql, objList, dataSource);