I released five new sample lessons from my Test With Spring course: Introduction to Spock Framework

Spring Batch Tutorial: Writing Information to a Database With JDBC

My Spring Batch tutorial has taught us to read the input data of our batch job from different data sources. However, our batch jobs aren’t very useful because we don’t know how we can save the data that is read from these data sources.

It’s time to take the next step and learn how we can save the data that has been processed by our Spring Batch job. This blog post helps us to write the processed data to a database by using JDBC.

Let’s start by taking a quick look at our example application.

If you are not familiar with Spring Batch, you should read the following blog posts before you continue reading this blog post:

Introduction to Our Example Application

During this tutorial we will implement several Spring Batch jobs that processes the student information of an online course. The information of a single student consists of the following information:

  • The name of the student.
  • The email address of the student.
  • The name of the purchased package.

Before we can write this information to a database, we have provide the input data for the component that writes it to a database. In this case, this information is provided by using StudentDTO objects.

The StudentDTO class contains the information of a single student, and its source code looks as follows:

public class StudentDTO {

    private String emailAddress;
    private String name;
    private String purchasedPackage;

    public StudentDTO() {}

    public String getEmailAddress() {
        return emailAddress;
    }

    public String getName() {
        return name;
    }

    public String getPurchasedPackage() {
        return purchasedPackage;
    }

    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setPurchasedPackage(String purchasedPackage) {
        this.purchasedPackage = purchasedPackage;
    }
}

Let’s move on and find out how we can configure the application context of our application.

Configuring the Application Context of Our Application

Before we can configure the ItemWriter<StudentDTO> object that writes student information to the database, we have to configure the NamedParameterJdbcTemplate bean. We can do this by choosing between these two options:

First, if we are using Spring Framework, we have to follow these steps:

  1. Add a jdbcTemplate() method into the application context configuration class.
  2. Ensure that the jdbcTemplate() method returns a NamedParameterJdbcTemplate object and takes a DataSource object as a method parameter.
  3. Annotate the method with the @Bean annotation.
  4. Implement the method by returning a new NamedParameterJdbcTemplate object.

The relevant part of our application context configuration class looks as follows:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
public class PersistenceContext {

    @Bean(destroyMethod = "close")
    DataSource dataSource(Environment env) {
        HikariConfig dataSourceConfig = new HikariConfig();

        dataSourceConfig.setDriverClassName(env.getRequiredProperty("db.driver"));
        dataSourceConfig.setJdbcUrl(env.getRequiredProperty("db.password"));
        dataSourceConfig.setUsername(env.getRequiredProperty("db.url"));
        dataSourceConfig.setPassword(env.getRequiredProperty("db.username"));

        return new HikariDataSource(dataSourceConfig);
    }
	
    @Bean
    NamedParameterJdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
}

Second, if we are using Spring Boot and we haven’t disabled its auto configuration feature, we don’t have to make any changes to our configuration because Spring Boot configures both the JdbcTemplate and NamedParameterJdbcTemplate beans.

Let’s move on and configure the ItemWriter<StudentDTO> bean which writes the processed student information to the database.

Writing Information to a Database With JDBC

We can configure the ItemWriter<StudentDTO> bean by following these steps:

First, we have to create the configuration class that contains the beans which describe the workflow of our Spring Batch job. We can do this by creating a CsvFileToDatabaseJobConfig class and annotating it with the @Configuration annotation.

The source code of the CsvFileToDatabaseJobConfig class looks as follows:

import org.springframework.context.annotation.Configuration;

@Configuration
public class CsvFileToDatabaseJobConfig {

}

Second, we have to create the method that configures our ItemWriter bean. We can do this by following these steps:

  1. Add a new method into our application context configuration class and ensure that the created method returns an ItemWriter<StudentDTO> object.
  2. Ensure that the created method takes the DataSource and NamedParameterJdbcTemplate objects as method parameters.
  3. Implement the method by returning null.

After we have added a new method into the CsvFileToDatabaseJobConfig class, its source code looks as follows:

import org.springframework.batch.item.ItemWriter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class CsvFileToDatabaseJobConfig {
    
    @Bean
    ItemWriter<StudentDTO> csvFileDatabaseItemWriter(DataSource dataSource, 
													 NamedParameterJdbcTemplate jdbcTemplate) {
        return null;
    }
}

Third, we have to create a new JdbcBatchItemWriter<StudentDTO> object, set the used datasource and JDBC template, and ensure that our method returns the created object.

After we have done this, the source code of the CsvFileToDatabaseJobConfig class looks as follows:

import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class CsvFileToDatabaseJobConfig {

    @Bean
    ItemWriter<StudentDTO> csvFileDatabaseItemWriter(DataSource dataSource, 
													 NamedParameterJdbcTemplate jdbcTemplate) {
        JdbcBatchItemWriter<StudentDTO> databaseItemWriter = new JdbcBatchItemWriter<>();
        databaseItemWriter.setDataSource(dataSource);
        databaseItemWriter.setJdbcTemplate(jdbcTemplate);
        
        return databaseItemWriter;
    }
}

Our next step is to configure the INSERT statement that inserts student information into our database. We can use two different strategies for specifying the parameters of our INSERT statement, and both of these strategies require a bit different configuration.

Let’s start by creating an INSERT statement that uses indexed parameters.

Using Indexed Parameters

If we want to use indexed parameters, we can configure the used INSERT statement by following these steps:

First, we have to create an INSERT statement that inserts data into the students table and uses indexed parameters.

After we have created a constant field that contains our new INSERT statement and ensured that the JdbcBatchItemWriter object uses our new INSERT statement, the source code of the CsvFileToDatabaseJobConfig class looks as follows:

import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class CsvFileToDatabaseJobConfig {

    private static final String QUERY_INSERT_STUDENT = "INSERT " +
            "INTO students(email_address, name, purchased_package) " +
            "VALUES (?, ?, ?)";

    @Bean
    ItemWriter<StudentDTO> csvFileDatabaseItemWriter(DataSource dataSource,
                                                     NamedParameterJdbcTemplate jdbcTemplate) {
        JdbcBatchItemWriter<StudentDTO> databaseItemWriter = new JdbcBatchItemWriter<>();
        databaseItemWriter.setDataSource(dataSource);
        databaseItemWriter.setJdbcTemplate(jdbcTemplate);

        databaseItemWriter.setSql(QUERY_INSERT_STUDENT);

        return databaseItemWriter;
    }
}

Second, we have to create a class that implements the ItemPreparedStatementSetter<T> interface. This class is responsible of setting the actual parameter values to the PreparedStatement object given as a method parameter when the setValues() method of the ItemPreparedStatementSetter<T> interface is invoked.

We can create this class by following these steps:

  1. Create a class that implements the ItemPreparedStatementSetter<T> interface. When we implement this interface, we have to specify one type parameter that specifies the type of the item (StudentDTO) which contains the actual parameter values.
  2. Implement the setValues() method and configure the parameter values in this order: emailAddress, name, and purchasedPackage.

The source code of the created class looks as follows:

import org.springframework.batch.item.database.ItemPreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;

final class StudentPreparedStatementSetter implements ItemPreparedStatementSetter<StudentDTO> {

    @Override
    public void setValues(StudentDTO student, 
						  PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setString(1, student.getEmailAddress());
        preparedStatement.setString(2, student.getName());
        preparedStatement.setString(3, student.getPurchasedPackage());
    }
}

Third, we have to ensure that the JdbcBatchItemWriter uses the StudentPreparedStatementSetter class when it determines the actual parameter values of the invoked INSERT statement.

After we have configured the used ItemPreparedStatementSetter object, the source code of the CsvFileToDatabaseJobConfig class looks as follows:

import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.ItemPreparedStatementSetter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class CsvFileToDatabaseJobConfig {

    private static final String QUERY_INSERT_STUDENT = "INSERT " +
            "INTO students(email_address, name, purchased_package) " +
            "VALUES (?, ?, ?)";

    @Bean
    ItemWriter<StudentDTO> csvFileDatabaseItemWriter(DataSource dataSource,
                                                     NamedParameterJdbcTemplate jdbcTemplate) {
        JdbcBatchItemWriter<StudentDTO> databaseItemWriter = new JdbcBatchItemWriter<>();
        databaseItemWriter.setDataSource(dataSource);
        databaseItemWriter.setJdbcTemplate(jdbcTemplate);

        databaseItemWriter.setSql(QUERY_INSERT_STUDENT);

        ItemPreparedStatementSetter<StudentDTO> valueSetter = 
                new StudentPreparedStatementSetter();
        databaseItemWriter.setItemPreparedStatementSetter(valueSetter);

        return databaseItemWriter;
    }
}

We have now configured an ItemWriter bean that inserts information into our database by using an INSERT statement which uses indexed parameters.

Let’s find out how we can achieve the same result by using an INSERT statement that uses named parameters.

Using Named Parameters

If we want to use named parameters, we can configure the used INSERT statement by following these steps:

First, we have to create an INSERT statement that inserts data into the students table and uses named parameters. When we specify the names of our named parameters, we should use parameter names that are equal to the property names of the StudentDTO class.

After we have created a constant field that contains our new INSERT statement and ensured that the JdbcBatchItemWriter object uses our new INSERT statement, the source code of the CsvFileToDatabaseJobConfig class looks as follows:

import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class CsvFileToDatabaseJobConfig {
    
    private static final String QUERY_INSERT_STUDENT = "INSERT " +
            "INTO students(email_address, name, purchased_package) " +
            "VALUES (:emailAddress, :name, :purchasedPackage)";
    
    @Bean
    ItemWriter<StudentDTO> csvFileDatabaseItemWriter(DataSource dataSource, 
                                                     NamedParameterJdbcTemplate jdbcTemplate) {
        JdbcBatchItemWriter<StudentDTO> databaseItemWriter = new JdbcBatchItemWriter<>();
        databaseItemWriter.setDataSource(dataSource);
        databaseItemWriter.setJdbcTemplate(jdbcTemplate);

        databaseItemWriter.setSql(QUERY_INSERT_STUDENT);

        return databaseItemWriter;
    }
}

Second, we have to configure the used ItemSqlParameterSourceProvider<StudentDTO> object. The JdbcBatchItemWriter object uses this object for obtaining a reference to SqlParameterSource object that is used to get the parameter values of our named parameters.

Because the names of our named parameters are equal to the property names of the StudentDTO class, we can use the BeanPropertyItemSqlParameterSourceProvider class for this purpose.

After we have configured the used ItemSqlParameterSourceProvider<StudentDTO> object, the source code of the CsvFileToDatabaseJobConfig class looks as follows:

import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.ItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class CsvFileToDatabaseJobConfig {
    
    private static final String QUERY_INSERT_STUDENT = "INSERT " +
            "INTO students(email_address, name, purchased_package) " +
            "VALUES (:emailAddress, :name, :purchasedPackage)";
    
    @Bean
    ItemWriter<StudentDTO> csvFileDatabaseItemWriter(DataSource dataSource, 
                                                     NamedParameterJdbcTemplate jdbcTemplate) {
        JdbcBatchItemWriter<StudentDTO> databaseItemWriter = new JdbcBatchItemWriter<>();
        databaseItemWriter.setDataSource(dataSource);
        databaseItemWriter.setJdbcTemplate(jdbcTemplate);

        databaseItemWriter.setSql(QUERY_INSERT_STUDENT);

        ItemSqlParameterSourceProvider<StudentDTO> paramProvider = 
                new BeanPropertyItemSqlParameterSourceProvider<>();
        databaseItemWriter.setItemSqlParameterSourceProvider(paramProvider);

        return databaseItemWriter;
    }
}

We have now configured an ItemWriter bean that inserts information into our database by using an INSERT statement which uses named parameters.

Let’s summarize what we learned from this lesson.

Summary

This blog post has taught us four things:

  • We can write information to a database by using the JdbcBatchItemWriter<T> class.
  • If we want to use indexed parameters, we can provide the actual parameter values to the JdbcBatchItemWriter<T> by implementing an the ItemPreparedStatementSetter<T> interface.
  • If we want to use named parameters, we can provide the actual parameter values to the JdbcBatchItemWriter<T> by implementing an the ItemSqlParameterSourceProvider<T> interface.
  • If we are using named parameters and our parameter names are equal to the property names of the class that contains the actual parameter values, we can provide the actual parameter values to the JdbcBatchItemWriter<T> by using the BeanPropertyItemSqlParameterSourceProvider<T> class.

P.S. You can get the example applications of this blog post from Github: Spring example and Spring Boot example.

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

1 comment… add one

    Leave a Comment