java.sql.BatchUpdateException – How to resolve Batch Update Exception

Aldo ZiflajOctober 13th, 2014Last Updated: October 8th, 2014

2 3,312 4 minutes read

In this example I will discuss about BatchUpdateException and how to resolve it. This exception is thrown when an error occurs during a batch update operation. It provides (among others) the update counts for all commands that were executed successfully during the batch update.

The BatchUpdateException class is a subclass of SQLException, which represents an exception that provides information on a database access error or other errors.

The BatchUpdateException exists since JDK1.2.

The structure of BatchUpdateException

Constructor:

BatchUpdateException()
Constructs a BatchUpdateException instance.
BatchUpdateException(int[] updateCounts)
Constructs a BatchUpdateException object initialized with a given updateCounts.
BatchUpdateException(int[] updateCounts, Throwable cause)
Constructs a BatchUpdateException object initialized with a given cause and updateCounts.
BatchUpdateException(String reason, int[] updateCounts)
Constructs a BatchUpdateException object initialized with a given reason and updateCounts.
BatchUpdateException(String reason, int[] updateCounts, Throwable cause)
Constructs a BatchUpdateException object initialized with a given reason, cause and updateCounts.
BatchUpdateException(String reason, String SQLState, int[] updateCounts)
Constructs a BatchUpdateException object initialized with a given reason, SQLState and updateCounts.
BatchUpdateException(String reason, String SQLState, int[] updateCounts, Throwable cause)
Constructs a BatchUpdateException object initialized with a given reason, SQLState,cause, and updateCounts.
BatchUpdateException(String reason, String SQLState, int vendorCode, int[] updateCounts)
Constructs a BatchUpdateException object initialized with a given reason, SQLState, vendorCode and updateCounts.
BatchUpdateException(String reason, String SQLState, int vendorCode, int[] updateCounts, Throwable cause)
Constructs a BatchUpdateException object initialized with a given reason, SQLState, vendorCode cause and updateCounts.
BatchUpdateException(Throwable cause)
Constructs a BatchUpdateException object initialized with a given cause.

The BatchUpdateException in Java

Before witnessing when the exception is thrown, create a database, a table and put some data in it with this query:

CREATE DATABASE db_test;
CREATE TABLE IF NOT EXISTS db_test.tbl (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	surname VARCHAR(20) NOT NULL,
	age INT NOT NULL
);

INSERT INTO db_test.tbl VALUES (1, 'Bruce', 'Scott', 65);
INSERT INTO db_test.tbl VALUES (2, 'John', 'Doe', 40);
INSERT INTO db_test.tbl VALUES (3, 'Jane', 'Doe', 35);

Now we can go on simulating the exception. To do this, create a class called ExceptionThrownExample with this source code:

ExceptionThrownExample.java

package com.javacodegeeks.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ExceptionThrownExample {
	
	private static final String HOST = "jdbc:mysql://localhost/";
	private static final String DB = "db_test";
	private static final String USER = "root";
	private static final String PASSWORD = "bitnami";
	
	public static void main(String[] args) {
		String sqlQuery = "INSERT INTO `tbl` (`id`, `name`, `surname`, `age`) VALUES (?,?,?,?)";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(HOST+DB,USER,PASSWORD);
			PreparedStatement ps = conn.prepareStatement(sqlQuery);
			
			ps.setInt(1,1);
			ps.setString(2, "Name1");
			ps.setString(3, "Surname1");
			ps.setInt(4, 1);
			ps.addBatch();
			
			int[] affectedRecords = ps.executeBatch();
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

First thing you would want to do is replace my public static final credentials with your own.

I created a prepared statement that inserts data into the database, then I added a record with ID of 1. This will throw the exception, since there is a duplication of ID (Bruce Scott has the ID 1).

When this is executed, it will throw the exception:

java.sql.BatchUpdateException: Duplicate entry '1' for key 'PRIMARY'
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1981)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1388)
	at com.javacodegeeks.examples.Main.main(Main.java:31)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
	at com.mysql.jdbc.Util.getInstance(Util.java:383)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2826)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933)
	... 2 more

Catching a BatchUpdateException

On the above example, the BatchUpdateException was caught by the last catch clause, since it is a subclass of SQLException. You can manually catch it before it reaches the SQLException. The following code acts on the same database (with 3 records) and shows how to catch the BatchUpdateException.

CatchExceptionExample.java

package com.javacodegeeks.examples;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class CatchExceptionExample {
	
	private static final String HOST = "jdbc:mysql://localhost/";
	private static final String DB = "db_test";
	private static final String USER = "root";
	private static final String PASSWORD = "bitnami";
	
	public static void main(String[] args) {
		String sqlQuery = "INSERT INTO `tbl` (`id`, `name`, `surname`, `age`) VALUES (?,?,?,?)";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(HOST+DB,USER,PASSWORD);
			PreparedStatement ps = conn.prepareStatement(sqlQuery);
			
			ps.setInt(1,6);
			ps.setString(2, "Name1");
			ps.setString(3, "Surname1");
			ps.setInt(4, 1);
			ps.addBatch();
			
			ps.setInt(1,1);
			ps.setString(2, "Name1");
			ps.setString(3, "Surname1");
			ps.setInt(4, 1);
			ps.addBatch();
			
			int[] affectedRecords = ps.executeBatch();
			
		} catch (BatchUpdateException ex) {
			int[] updateCount = ex.getUpdateCounts();
			
			int count = 1;
			for (int i : updateCount) {
				if  (i == Statement.EXECUTE_FAILED) {
					System.out.println("Error on request " + count +": Execute failed");
				} else {
					System.out.println("Request " + count +": OK");
				}
				count++;
				
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

The above example is a modified version of the first one, which tries to add the same record with id 1 and 6. In the catch clause I caught the BatchUpdateException and got the data that it holds using the getUpdateCounts() method. Then, all I did was outputing what the method gave.

The output is:

Request 1: OK
Error on request 2: Execute failed

Predictable, since the first request, that of adding a 6th record, is legitimate and no errors are generated, while the second request, that of adding a 1st record, is wrong, since there is a record with ID 1 and IDs are unique per each record.

More about BatchUpdateException

The BatchUpdateException is thrown when an error occurs during a batch update operation. In addition to the information provided by SQLException, a BatchUpdateException provides the update counts for all commands that were executed successfully during the batch update, that is, all commands that were executed before the error occurred. The order of elements in an array of update counts corresponds to the order in which commands were added to the batch.

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts() will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver continues processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.