Ojdbc14.jar Package solves 65536 BULK INSERT problem

When you use the Oracle driver OJDBC14 package for bulk inserts, you encounter an insert problem when you encounter a 65,536 number of bars, causing subsequent inserts to fail, and you need to work with the data in the

Group operation, so that the number of bars per execution does not exceed 65,536, the following is a batch of Oracle data inserted in the sample code.

1. Bulk processing public methods

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;
	GetLogger (Batchaddutil.class);
	
	
	Maximum number of each operation private static final int per_max_num = 65000; /** * Bulk Add method * @param dbType specific values are "Oracle", "MySQL", etc. * @param SQL * @param batchargs * @param dataSource * @r Eturn */public static map<string,integer> Batchadd (String dbtype,string sql,list<object[]> batchArgs,Data
			Source 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;
		In the case of an Oracle database, batch processing is required because of a 65536 row limit.
			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") + (B-A)/1000);
				Tempmap = Parsebatchresult (res);
				if (tempmap!= null && tempmap.size () > 0) {map.putall (TEMPMAP);
			}} else {//If it is a different database type, insert it directly in bulk.
			res = template.batchupdate (Sql,batchargs);
			Tempmap = Parsebatchresult (res);
		Map = Tempmap;
	} return map;
	 /** * uses grouping to realize the insert operation of batch data. * @param list * @return/private static LIST&LT;OBJECT> parseparamlist (list<object[]> List) {list<object[]> temp = null;
		list<object> reslist = new arraylist<object> ();
		The total data is grouped so that the following uses 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 the last grouping number is greater than the total list number, the last remaining data is a set of if (Endpos > size) {temp = List.sublist (startpos,size);
				Reslist.add (temp);
			Break
			temp = list.sublist (Startpos,endpos);
		Reslist.add (temp);
	return reslist; /** * Work directly to parse the return data * @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. Bulk Process Public templates

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;
 /** * This class provides a convenient way to perform JDBC batch processing. * @Copyright: Copyright (c) 2013 Ffcs all Rights Reserved * @Company: Beijing fu Fu Software Co., Ltd. * @author Chen Oct, 2013 * @vers Ion 1.00.00 * @history: * * */public class Batchupdatetemplate {private final static Logger Logger = LOGGERFACTORY.G

	Etlogger (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) {Connectionconnection = 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, BatchUpdateExce
			ption) 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[]& Gt
		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)));
				The 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 (batch
			Args.get (i)));
	} batchupdatemessagelogger.error ("------batchupdate[{}]------", bid);
 }

}

3. Calling code

		String sql = "INSERT INTO" + Tableusername + "Temp_msgpush_rela (Mobile,imsi) VALUES (?,?)";
		map<string,integer> map = Batchaddutil.batchadd ("Oracle", SQL, Objlist, dataSource);