Example Spring 4 JDBC – Batch Operations

This example presents the basic concept of using batch operations in Spring JDBC.

The technologies used are :

You can convert this example to an Eclipse IDE project by going to folder where is the pom.xml is, and use the command :

mvn eclipse:eclipse

Inspired from « Beginning Spring » Mert Caliskan, Kenan Sevindik, Rod Johnson (Foreword by), Jürgen Höller (Foreword by).

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>javaspringexamples.springJDBC</groupId>
	<artifactId>springJdbcBatchOperations</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.3.18.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.3.18.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>1.3.175</version>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.6</version>
		</dependency>
	</dependencies>

</project>

package javaspringexamples.springJDBC.batchOperations;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Configuration
public class Conf {

	@Value("classpath:schema.sql")
	private Resource schemaScript;

	@Value("classpath:data.sql")
	private Resource dataScript;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.h2.Driver");
		dataSource.setUrl("jdbc:h2:tcp://localhost/~/javaspringexamples");
		dataSource.setUsername("sa");
		dataSource.setPassword("");
		return dataSource;
	}

	@Bean
	public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
		final DataSourceInitializer initializer = new DataSourceInitializer();
		initializer.setDataSource(dataSource);
		initializer.setDatabasePopulator(databasePopulator());
		return initializer;
	}

	private DatabasePopulator databasePopulator() {
		final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
		populator.addScript(schemaScript);
		populator.addScript(dataScript);
		return populator;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource());
		return jdbcTemplate;
	}

	@Bean
	public UserDao userDao() {
		UserDaoImpl userDao = new UserDaoImpl();
		userDao.setJdbcTemplate(jdbcTemplate());
		return userDao;
	}
}

package javaspringexamples.springJDBC.batchOperations;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class Main {
	public static void main(String[] args) throws SQLException {
		AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Conf.class);

		List<Long> usersIds = new ArrayList<>();
		usersIds.add(65L);
		usersIds.add(256L);
		usersIds.add(999L);
		
		UserDao userDao = applicationContext.getBean(UserDao.class);
		List<User> users = userDao.find(usersIds);
		for (User user : users) {
			System.out.println(user.toString());
			user.setName("Waaaaaaw");
			user.setUserName("No no noooooooooo");
		}

		User u = new User();
		u.setId(36L);
		u.setName(":-|");
		users.add(u);
		
		userDao.update(users);

		users = userDao.find(usersIds);
		for (User user : users) {
			System.out.println(user.toString());
		}
	}
}

package javaspringexamples.springJDBC.batchOperations;

import org.springframework.dao.DataAccessException;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UpdateFailedException extends DataAccessException {
	public UpdateFailedException(String msg) {
		super(msg);
	}
}

package javaspringexamples.springJDBC.batchOperations;

import java.util.Date;

import lombok.Data;
import lombok.ToString;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
@Data
public class User {
	private long id;
	private String name;
	private String userName;
	@ToString.Exclude
	private Date accessTime;
	private boolean locked;
}

package javaspringexamples.springJDBC.batchOperations;

import java.util.List;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public interface UserDao {
	List<User> find(List<Long> userIds);
	void update(final List<User> users);
}

package javaspringexamples.springJDBC.batchOperations;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserDaoImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private UserRowMapper userRowMapper = new UserRowMapper();

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	}

	public List<User> find(List<Long> userIds) {
		SqlParameterSource sqlParameterSource = new MapSqlParameterSource("userIds", userIds);
		return namedParameterJdbcTemplate.query("select * from user where id in (:userIds)", sqlParameterSource,
				userRowMapper);
	}

	public void update(final List<User> users) {
		int[] counts = jdbcTemplate.batchUpdate("update user set (name, user_name, locked) = (?,?,?) where id = ?",
				new BatchPreparedStatementSetter() {
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						User user = users.get(i);
						ps.setString(1, user.getName());
						ps.setString(2, user.getUserName());
						ps.setBoolean(3, user.isLocked());
						ps.setLong(4, user.getId());
					}

					public int getBatchSize() {
						return users.size();
					}
				});
		int i = 0;
		for (int count : counts) {
			if (count == 0)
				throw new UpdateFailedException("Row not updated :" + i);
			i++;
		}
	}
}

package javaspringexamples.springJDBC.batchOperations;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

/**
 * 
 * @author mounir.sahrani@gmail.com
 *
 */
public class UserRowMapper implements RowMapper<User> {

	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("id"));
		user.setName(rs.getString("name"));
		user.setUserName(rs.getString("user_name"));
		user.setAccessTime(rs.getTimestamp("access_time"));
		user.setLocked(rs.getBoolean("locked"));
		return user;
	}

}

MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (999L, 'Mounir SAHRANI', 'msahrani', '2019-01-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (256L, 'Laurent SOURDEAU', 'lsourdeau', '2019-06-01',false);
MERGE INTO USER (ID, NAME, USER_NAME, ACCESS_TIME, LOCKED) values (65L, 'John SMITH', 'jsmith', '2019-06-01',true);

CREATE TABLE IF NOT EXISTS USER (ID BIGINT IDENTITY PRIMARY KEY, NAME VARCHAR(60), USER_NAME VARCHAR(60), ACCESS_TIME TIMESTAMP, LOCKED BOOLEAN);

Get the sources of the example from the following GitHub url

Or Download a .zip file