exception - Is it Possible to Insert Row by Row from a Java PreparedStatement? -

i have application writing batches of 1000 rows sql server database using prepared statement. if of these inserts fails, want able write offending line log file, data not lost. prefer if possible write 1 line failed log, rather 1000 lines, question this:

if 1 line fails while inserting table, primary key violation occurs, whole batch prevented inserting, or 1 row failed? if batch fails, possible me sort of loop on prepared statement, executing each query 1 one, , in way can figure out line failed, , log line? or way me achieve keep separate array of each insert statement, , loop through if batch happens fail?

some more details:

database type: sql server 2008
connection library: java.sql
sql statement:

insert table(column1,column2) values (value1, value2)   

java code:

preparedstatement prpstmt = dbconnection.preparestatement(insertquery.tostring());   (list lst : listofvalues){     prpstmt.setstring(1,lst[0]);       prpstmt.setstring(2,lst[1]);       prpstmt.addbatch();       dbcount++;       if (dbcount == db_count_limit){           try {             prpstmt.executebatch();             dbconnection.commit();               dbcount = 0;               prpstmt.clearbatch();         } catch (exception e){             (preparedstatement ps : prpstmt.getbatches()){             if (!logtodbindividually())                 logtofile();             }         }     }   } 

if 1 line fails while inserting table, primary key violation occurs, whole batch prevented inserting, or 1 row failed?

statements executed prior 1 error occurred still executed. statements later in batch may or may not have been executed, @ driver's discretion. supposing have turned off autocommit statement's connection, should when using executebatch() , appears have done, whether commit or roll changes made @ discretion.

whether or not driver continues past failed statement, if 1 does fail can determine of batched statements executed examining array returned getupdatecounts() method of resulting batchupdateexception. refer that's method's documentation or documentation of statement.executebatch() details.

do note example code present flawed. in event 1 of updates fails, such exception thrown, important either commit or roll transaction, , clear batched statements not want re-execute in next iteration of loop. also, appropriate catch plain exception, , inappropriate here, given want handle batchupdateexception differently other exceptions. might better:

preparedstatement prpstmt = dbconnection.preparestatement(insertquery.tostring());   (list lst : listofvalues){     prpstmt.setstring(1,lst[0]);       prpstmt.setstring(2,lst[1]);       prpstmt.addbatch();       dbcount++;       if (dbcount >= db_count_limit) {  // should not >, no harm in being safe         try {             prpstmt.executebatch();             dbconnection.commit();               dbcount = 0;               prpstmt.clearbatch();         } catch (batchupdateexception bue){             int[] updatecounts = bue.getupdatecounts();              if (updatecounts.length < dbcount) {                 /*                  * first updatecounts.length statements (only)                  * executed successfully.  next 1 failed, , no more                  * attempted.                  */             } else {                 /*                  * failed statements can identified having                  * updatecounts[i] == statement.execute_failed                  */             }              // presumably want to:             dbconnection.commit();              // maybe want to:             dbcount = 0;               prpstmt.clearbatch();             // otherwise need other kind of cleanup / retry         }          /*          * no need catch other exception, including sqlexception, in          * scope, it's unlikely overall bulk insertion can          * continued after such exception.          */     }   } 

if batch fails, possible me sort of loop on prepared statement, executing each query 1 one, , in way can figure out line failed, , log line?

you can determine statement or statements failed shown above. allow log failures. cannot remove interrogate statement object's current batch, however, or remove lines other via clearbatch(), if driver happens kind stops processing batch after first error, recovering such failure might not straightforward like. needed information there, though; might easier use used indexed for loop iterate on list instead of enhanced for loop.

or way me achieve keep separate array of each insert statement, , loop through if batch happens fail?

no, that's not way, can imagine variations on that implemented cleanly. indexed for loop, however, can recover cleanly indeed.