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

Question

I have an application where I am writing batches of 1000 rows to a SQL Server database using a prepared statement. Now if any of these inserts fails, I want to be able to write the offending line to a log file, so that the data is not completely lost. I would prefer if possible to only write the one line that failed to the log, rather than all 1000 lines, so my question is this:

If one line fails while inserting into the table, say a Primary Key violation occurs, is the whole batch prevented from inserting, or only the one row that failed? If the batch fails, is it possible for me to do some sort of loop on the prepared statement, executing each query one by one, and in that way I can figure out the line that failed, and log only that line? Or is the only way for me to achieve this to keep a separate array of each insert statement, and loop through that if the batch happens to fail?

Some more details:

Database Type: SQL Server 2008
Connection Library: java.sql
SQL Statement:

Insert into Table(Column1,Column2) Values (Value1, Value2)  

Java Code:

PreparedStatement prpStmt = dbConnection.prepareStatement(insertQuery.toString());  
for (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){
            for (PreparedStatement ps : prpStmt.getBatches()){
            if (!logToDBIndividually())
                logToFile();
            }
        }
    }  
}

Answer 1

If one line fails while inserting into the table, say a Primary Key violation occurs, is the whole batch prevented from inserting, or only the one row that failed?

Statements that were executed prior to the one where an error occurred were still executed. Statements later in the batch may or may not have been executed, at the driver's discretion. Supposing that you have turned off autocommit for the statement's connection, as you should do when using executeBatch() and as it appears you have done, whether to commit or roll back the changes that were successfully made is at your discretion.

Whether or not the driver continues past a failed statement, if one does fail then you can determine which of the batched statements were executed successfully by examining the array returned by the getUpdateCounts() method of the resulting BatchUpdateException. Refer to that's method's documentation or to the documentation of Statement.executeBatch() for details.

Do note also that the example code you present is seriously flawed. In the event that one of your updates fails, such that an exception is thrown, it is important to either commit or roll back the transaction, and to clear any batched statements that you do not want to re-execute in the next iteration of your loop. Also, it is rarely appropriate to catch plain Exception, and it is especially inappropriate here, given that you want to handle BatchUpdateException differently than other exceptions. Something like this might be better:

PreparedStatement prpStmt = dbConnection.prepareStatement(insertQuery.toString());  
for (List lst : listOfValues){
    prpStmt.setString(1,lst[0]);  
    prpStmt.setString(2,lst[1]);  
    prpStmt.addBatch();  
    dbCount++;  
    if (dbCount >= DB_COUNT_LIMIT) {  // should not be >, but 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) {
                /*
                 * The first updateCounts.length statements (only) were
                 * executed successfully.  The next one failed, and no more
                 * were attempted.
                 */
            } else {
                /*
                 * The failed statements can be identified by having
                 * updateCounts[i] == Statement.EXECUTE_FAILED
                 */
            }

            // Presumably you want to:
            dbConnection.commit();

            // Maybe you want to:
            dbCount = 0;  
            prpStmt.clearBatch();
            // Otherwise you need to do some other kind of cleanup / retry
        }

        /*
         * no need to catch any other exception, including SQLException, in
         * this scope, as it's unlikely that the overall bulk insertion can be
         * continued after such an exception.
         */
    }  
}

If the batch fails, is it possible for me to do some sort of loop on the prepared statement, executing each query one by one, and in that way I can figure out the line that failed, and log only that line?

You can determine the statement or statements that failed as shown above. That will allow you to log the failures. You cannot remove interrogate a Statement object's current batch, however, or remove lines other than via clearBatch(), so if the driver happens to be the kind that stops processing a batch after the first error, then recovering from such a failure might not be a straightforward as you would like. The needed information is there, though; it might be easier to use you used an indexed for loop to iterate over your list instead of an enhanced for loop.

Or is the only way for me to achieve this to keep a separate array of each insert statement, and loop through that if the batch happens to fail?

No, that's not the only way, but I can imagine variations on that that could be implemented cleanly. With an indexed for loop, however, you can recover very cleanly indeed.