Introduction

Here I am going to tell you how to insert and update a list of objects in batch using JDBC PreparedStatement in Java programming language.

Generally when you have a list of objects or lots of data then probably it is not a good idea to insert or update individual record or object into database, because it makes lots of database calls. So to avoid such too many database calls we insert or update the records into batch and commit the transaction at the end of the batch execution.

The PreparedStatement object is created from connection.prepareStatement() method and SQL query is passed to this method for pre-compilation on database if JDBC driver supports it. If it does not happens during this time then pre-compilation happens when the prepared SQL statement gets executed.

Related Posts:

• How to select single or multiple records using JDBC executeQuery() and executeQueryForList()

PreparedStatememt or parameterized statement is a feature used to execute the same SQL statement multiple times with high efficiency much quicker than normal queries generated by Statement. An SQL statement is a precompiled and stored in PreparedStatement object.

PreparedStatement is a class in java.sql package and allows you to execute SQL statement by using the JDBC package.

The following advantages we get from the PreparedStatement:

• Allows you to write dynamic and parameterized queries
• PreparedStatements are faster than Statement
• PreparedStatement prevents SQL injection

Prerequisites

Java, JDBC API, Database Server

Table in Database – let’s say it user. This user table has columns – id, name, email, address.

Connection object that will establish connection with the underlying database – private Connection connection;

A User class that maps Java attributes to table columns.

public class User {
	private int id;
	private String name;
	private String email;
	private String address;
	
	//getters and setters
}

List of User objects which need to be inserted or updated into database.

List<User> userListInsert = new ArrayList<>();

User user1 = new User();
user1.setName("Soumitra");
user1.setEmail("soumitra@email.com");
user1.setAddress("Earth");
...

userListInsert.add(user1);
userListInsert.add(user2);
userListInsert.add(user3);
userListInsert.add(user4);

Next we will see how to insert or update this list of objects into database using JDBC PreparedStatement.

Batch Insert Example

First thing is we create an SQL statement for insertion users into database. The query is parameterized and ? is replaced by the given value.

private static final String INSERT_USER = 'INSERT INTO USER(name, email, address) VALUES(?, ?, ?)';

Next we create PreparedStatement using Connection object and pass the above SQL statement to this PreparedStatement.

Then we iterate through the list of user objects and add to the PreparedStatement batch.

Finally we execute the batch and return the number of records inserted from the batch size.

public int batchInsert(List<User> userList) {	
	try {
		PreparedStatement ps = connection.prepareStatement(INSERT_USER);
		for(int i = 0; i < userList.size(); i++) {
			User u = userList.get(i);
			
			ps.setString(1, u.getName());
			ps.setString(2, u.getEmail());
			ps.setString(3, u.getAddress());
			
			ps.addBatch();
		}
		
		int[] executeBatch = ps.executeBatch();
		
		return executeBatch.length;
	} catch(Exception ex) {
		//log error
	}
	
	return 0;
}

Batch Update Example

Let’s say we have the following query for update batch list of users:

private static final String UPDATE_USER = 'UPDATE USER SET name = ?, email = ?, address = ? WHERE id = ?';

So here we need to set also the user id to distinguish which user we are going to update.

Next we create prepared statement to pass the User objects for update. Make sure when you are updating the user, you also need to set id to the User object.

public int batchUpdate(List<User> userList) {	
	try {
		PreparedStatement ps = connection.prepareStatement(INSERT_USER);
		for(int i = 0; i < userList.size(); i++) {
			User u = userList.get(i);
			
			ps.setString(1, u.getName());
			ps.setString(2, u.getEmail());
			ps.setString(3, u.getAddress());
			ps.setInt(4, u.getId());
			
			ps.addBatch();
		}
		
		int[] executeBatch = ps.executeBatch();
		
		return executeBatch.length;
	} catch(Exception ex) {
		//log error
	}
	
	return 0;
}

In the above batch update, we are also returning the number of records updated into database table otherwise 0.

That’s all. Hope you got an idea how to perform batch insert or update using JDBC PreparedStatement.

Thanks for reading.