In this JDBC tutorial, you will learn how to efficiently execute multiple or many SQL update statements using Java. For example, a program needs to read thousands of rows from a CSV file and insert them into database, or it needs to efficiently update thousands of rows in the database at once. It is called batch update or bulk update. Table of content:

1. Basic JDBC Batch Update Example
2. JDBC Batch Update with Transaction
3. JDBC Batch Update using PreparedStatement
4. Specify Batch Size
5. Performance Test for JDBC Batch Update

Why do we need to use batch update?

The reason is simple: to get best performance. Basically, a single update statement requires a round trip to the database server, hence executing a large number of update statements individually is not efficient. Batch update allows sending a bulk of statements to the database server to execute all at once, which greatly improves the performance. The program will run much faster if batch update is used.

Now, let’s walk through some Java code examples to understand how to execute bath update using JDBC.

 

1. Basic JDBC Batch Update Example

The following code snippet illustrates how to send a batch of 10 SQL INSERT statements to the database server to be executed at once:

String jdbcURL = "jdbc:mysql://localhost:3306/bookshop";
String username = "root";
String password = "password";

Connection connection = null;

try {
	connection = DriverManager.getConnection(jdbcURL, username, password);
	
	Statement statement = connection.createStatement();			
	
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email1', 'pass1', 'Name 1')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email2', 'pass2', 'Name 2')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email3', 'pass3', 'Name 3')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email4', 'pass4', 'Name 4')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email5', 'pass5', 'Name 5')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email6', 'pass6', 'Name 6')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email7', 'pass7', 'Name 7')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email8', 'pass8', 'Name 8')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email9', 'pass9', 'Name 9')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email0', 'pass0', 'Name10')");

	int[] updateCounts = statement.executeBatch();

	for (int count : updateCounts) {
		System.out.println(count);
	}
	
	connection.close();
	
} catch (SQLException ex) {
	ex.printStackTrace();
}

As you can see, you can add many SQL statements to a Statement object via its addBatch() method, and call executeBatch() to send the batch to the server for processing. The executeBatch() method returns an array of integer numbers indicating the number of rows affected by each query, in the order of the queries added.

Note that with batch update, you can specify only SQL statements that return values like that (Insert, Update, schema update…). That means using SQL Select statements for batch processing makes none sense.

2. JDBC Batch Update with Transaction

In the above code example, there can be situation where some statements failed while others succeeded – which may causes data inconsistency for the database. If you want to make sure either all statements are successfully executed or none are processed, wrap the code inside a transaction as follows:

String jdbcURL = "jdbc:mysql://localhost:3306/bookshop";
String username = "root";
String password = "password";

Connection connection = null;

try {
	connection = DriverManager.getConnection(jdbcURL, username, password);
	connection.setAutoCommit(false);
	
	Statement statement = connection.createStatement();			
	
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email1', 'pass1', 'Name 1')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email2', 'pass2', 'Name 2')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email3', 'pass3', 'Name 3')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email4', 'pass4', 'Name 4')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email5', 'pass5', 'Name 5')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email6', 'pass6', 'Name 6')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email7', 'pass7', 'Name 7')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email8', 'pass8', 'Name 8')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email9', 'pass9', 'Name 9')");
	statement.addBatch("INSERT INTO users (email, pass, name) VALUES ('email0', 'pass0', 'Name 10')");

	int[] updateCounts = statement.executeBatch();

	for (int count : updateCounts) {
		System.out.println(count);
	}

	connection.commit();	
	connection.close();
	
} catch (SQLException ex) {
	ex.printStackTrace();
	
	try {
		connection.rollback();
	} catch (SQLException e) {
		e.printStackTrace();
	}	
}

Read this tutorial to learn more about using transaction with JDBC.

3. JDBC Batch Update using PreparedStatement

In case you need to use update statement with parameters, use the PreparedStatement class as follows:

String sql = "INSERT INTO users (email, password, full_name) VALUES (?, ?, ?)";

PreparedStatement statement = connection.prepareStatement(sql);	
statement.addBatch();

And the following code example illustrates how to execute bath update from a List of custom object type (User):

public void insertBatch(List<User> listUsers) {
	String jdbcURL = "jdbc:mysql://localhost:3306/bookshop";
	String username = "root";
	String password = "password";
	
	Connection connection = null;
	
	try {
		connection = DriverManager.getConnection(jdbcURL, username, password);
		connection.setAutoCommit(false);
		
		String sql = "INSERT INTO users (email, password, full_name) VALUES (?, ?, ?)";
		
		PreparedStatement statement = connection.prepareStatement(sql);
		
		for (User user : listUsers) {
			statement.setString(1, user.getEmail());
			statement.setString(2, user.getPassword());
			statement.setString(3, user.getFullName());
		
			statement.addBatch();
		}

		statement.executeBatch();
		
		connection.commit();
		connection.close();
		
	} catch (SQLException ex) {
		ex.printStackTrace();
		
		try {
			connection.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

Note that the for loop is used to iterate over elements in the collection to set values for the PreparedStatement.

4. Specify Batch Size

As you can see in the previous code examples, we add all statements as a single batch – which is good if the number of statements is small. In case the number of statements is large (hundreds to thousands), it’s better to split the statements into small batches and execute each batch sequentially.

For example, the following code example shows you how to small batch updates in a large number of statements:

public void insertBatch(List<User> listUsers) {
	String jdbcURL = "jdbc:mysql://localhost:3306/bookshop";
	String username = "root";
	String password = "password";
	
	int batchSize = 50;
	
	Connection connection = null;
	
	try {
		connection = DriverManager.getConnection(jdbcURL, username, password);
		connection.setAutoCommit(false);
		
		String sql = "INSERT INTO users (email, password, full_name) VALUES (?, ?, ?)";
		
		PreparedStatement statement = connection.prepareStatement(sql);
		
		int count = 0;
		
		for (User user : listUsers) {
			statement.setString(1, user.getEmail());
			statement.setString(2, user.getPassword());
			statement.setString(3, user.getFullName());
		
			statement.addBatch();
			count++;
			
			if (count % batchSize == 0) {
				statement.executeBatch();
			}
		}

		// execute the remaining queries
		statement.executeBatch();			
		
		connection.commit();
		connection.close();
		
	} catch (SQLException ex) {			
		ex.printStackTrace();
		
		try {
			connection.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

As you can see, the number of statements in each batch is 50. You can tweak this number to get best performance depending on your application.

5. Performance Test for JDBC Batch Update

For your reference, I conducted a simple test to measure the time difference between regular JDBC update and batch update (with batch size = 100). I use SQL Insert statements with MySQL database on the same computer. Here’s the result:

Number of statements	Regular update	Batch update
100	118 ms	71 ms
1000	723 ms	403 ms
10,000	4,465 ms	2,475 ms
100,000	34,890 ms	18,951 ms

 

Other JDBC Tutorials:

• JDBC Driver Downloads
• JDBC Database Connection URLs
• How to connect to a database with JDBC
• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC
• How to insert binary data into database with JDBC
• How to read binary data from database with JDBC
• How to use Scrollable ResultSet
• How to use Updatable ResultSet
• How to use CachedRowSet