I have a simple Java 8 project to update a SQL Server DB table starting by a list of DTO I created.

The DTO obviously reflect the column of the table I want to update, suppose it's similar to the follows:

public class TableToUpdate {
   private int id;
   private String name;

   // getter and setter  
}

Now I have this simple code:

.....
List<TableToUpdate> list = loadList(); // suppose the list contain tot element
Connection conn = DriverManager...  // retrieve connection correctly
String sql = "update table set name = ? where id = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);

for (TableToUpdate t : list) {
  preparedStatement.setString(1, t.getName());
  preparedStatement.setInt(2, t.getId());
  preparedStatement.addBatch();
}

Now with the code above I want to update the column name of table when column id is equals to the id passed by DTO in the batch.

I execute the batch by following command:

int[] affectedRecords = preparedStatement.executeBatch();

Now, suppose the batch doesn't find any match to update, I would expect an array of 0 elements. Instead affectedRecords length is equals to the number of queries in the batch and the element are all equals to 0.

For example:

If the size of the TableToUpdate list is 500 and no DTO id match table id, the affectedRecords will contain an array of 500 elements, all equals to 0.

It is the correct behavior? Did I make some mistake?

CodePudding user response：

That this returns an array with the number of items you added to the batch is absolutely the expected behaviour. executeBatch() returns an array with a value for each set of parameter values you added to the batch. Each item is the update count of that particular set of parameter values. If an item did not perform an update, you will get a value of 0, or for some types of statements, the value of the constant Statement.SUCCESS_NO_INFO (-2).

If this were not the case, it would not be possible for code to correlate the update count with a particular set of parameter values. For example, if you execute a batch of 5 sets of parameter values, and only one had a non-zero update count, your idea would return the array { 1 }, which makes it impossible to know what set of parameters had any effect. While with the JDBC defined way, it returns - for example - { 0, 0, 0, 1, 0 }, and code can derive it was the fourth set of parameter values.

As documented in Statement.executeBatch():

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch. The elements in the array returned by the method executeBatch may be one of the following:

1. A number greater than or equal to zero -- indicates that the command was processed successfully and is an update count giving the number of rows in the database that were affected by the command's execution

2. A value of SUCCESS_NO_INFO -- indicates that the command was processed successfully but that the number of rows affected is unknown

   If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following:

3. A value of EXECUTE_FAILED -- indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails

The possible implementations and return values have been modified in the Java 2 SDK, Standard Edition, version 1.3 to accommodate the option of continuing to process commands in a batch update after a BatchUpdateException object has been thrown.

Returns:
an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.

(emphasis mine)