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.url"));
        dataSourceConfig.setUsername(env.getRequiredProperty("db.username"));
        dataSourceConfig.setPassword(env.getRequiredProperty("db.password"));

        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.

30 comments… add one
  • rajkumar ramakrishnan Sep 17, 2017 @ 12:18

    I am trying to use Spring Batch and am running into a configuration issue. I would like to use two different data sources, one for the job repository and the other for my reader which will read data from an Oracle DB. Can you please let me know how I can configure these two data sources, am using Spring Boot and Java Configuration.

    • Petri Sep 21, 2017 @ 20:59

      Hi,

      You have to follow these steps:

      1. Configure two data source beans.
      2. Configure the ItemReader bean and provide the "correct" DataSource bean to your item reader.
      3. Configure the ItemWriter bean and provide the "correct" DataSource bean to your item writer.

      Also, you should take a look at this StackOverflow question. Its answer explains how Spring Batch manages transactions if you use multiple data sources.

      If you have any additional questions, don't hesitate to ask them.

  • Danny Bullis Apr 11, 2018 @ 21:10

    Looks like you've got a couple typos in your code in the "Configuring the Application Context of our Application" section. Note that the parameters passed for the URL, username and password are out of order.

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


    should be

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


    Great article, it's very helpful!

    • Petri Apr 11, 2018 @ 21:45

      Hi,

      Good catch! I have no idea how I missed that when I proofread this post. Anyway, it should be fixed now. Thank you for reporting this typo!

  • joannes May 6, 2018 @ 12:28

    Nice one. I am having the content file content which is extracted from xlsx. the content will be jsonarray of jsonobject. each jsonobject will have the information to be inserted in a row. the jsonarray looks like [{"name":"aaa","age":22"}][{"name":"aaa","age":22"}] how do i iterate through this in preparedstatement.

    • Petri May 6, 2018 @ 16:17

      Hi,

      I would write a custom ItemReader which transforms the json array into a list of objects. After I have read this list from the input file, I can write it to the database by following the instructions given in this blog post.

      If you have any additional questions, don't hesitate to ask them.

  • EitherDoItRightOrDontDoIt/Nothing is better than nonsense May 6, 2018 @ 14:59

    Where is the information about the csv file? how are you getting the details from the file.?

  • shilpa Aug 14, 2018 @ 8:15

    Writer gets called for each CSV row. Inserting in Writer class wont hit the performance ?? If we have huge number of records.

    • Petri Aug 14, 2018 @ 11:24

      Hi,

      Spring Batch uses so called chunk oriented processing where items are processed one by one and the transaction is committed when the chunk size is met. In other words, Spring Batch provides you an easy way to manage the size of your transactions and helps you to optimize your batch jobs.

  • Chris Oct 30, 2018 @ 18:28

    What a great and clean tutorial. Thank you very much for saving my day!

    • Petri Nov 14, 2018 @ 21:11

      You are welcome! I am happy to hear that this post was useful to you.

  • Jorge Feb 7, 2019 @ 18:57

    Hi

    I have an issue:
    You have a register, for example a StudentDTO with id 1 and Name Petri (id is PK in DB); you try to update this record first. If there is no return for this action (in Jdbc, when you update a record, an integer is returned), you insert this record.

    I am trying to do this using JdbcItemWriter but I could be able to find a solution for this issue. I have read about ClassifierItemWriter but I don' t know if suits.

    Great article!

    Thanks

    • Petri Feb 7, 2019 @ 22:32

      Hi,

      You can solve your problem by using merge (aka upsert). This allows you to either insert a new row into the database or update the information of an existing row.

  • Jose Nov 15, 2019 @ 15:32
    • Petri Nov 26, 2019 @ 20:59

      Hi,

      Unfortunately it's quite hard (impossible) to say what's wrong without debugging the application. Is this possible?

  • natanya Sep 16, 2020 @ 16:01

    How do you launch your job? what i see here is just defining beans, but how you launch the execution?

  • Jeff Mosea Nov 24, 2020 @ 4:37

    Great article, thanks for taking the time to help others learn the power of Spring Batch.

  • Iman Mar 14, 2022 @ 11:32

    I am using Springbatch for a webapp. My webapp reads the file from an XML file and stores it in a database. Before webapp saves all files in database, I want to check whether this file is already saved or not. If not, then save the file.
    how does this work in spring batch?

    • Petri Mar 19, 2022 @ 15:48

      Hi,

      As you probably found out, it's quite easy to write a job which reads information from an XML file and writes it to the database. The tricky part is to ensure that you don't process the same file twice. There are (at least) two different solutions to this problem:

      • If a source file appears on the source directory only once, you can write a tasklet which moves the processed files to an archive directory. This ensures that every file is processed only once.
      • If a source file can appear on the source directory multiple times, you have to implement two tasklets:
        • The first tasklet moves the processed files to an archive directory and marks the file as processed (basically you have to have a database table which contains the file names of processed files).
        • The second tasklet gets the file names of the files found from the source directory and checks if the found files have been processed (a file is processed if its name is found from the database table which I mentioned earlier). If a file has been processed, the tasklet deletes the file (or archives it).

      I hope that helps you to solve your problem. If you have any additional questions, don't hesitate to ask them.

  • Venkatesh Prasad Aug 10, 2022 @ 6:54

    I want to write test cases for JdbcBatchItemWriter, whether it's writing data into exact column.
    can any one help me to solve this problem.

  • Somesh Mar 20, 2023 @ 12:15

    Hi, how can we get auto incremented value using JdbcBatchItemWriter, using above steps. If we could so, could you please provide code here. Thankyou.

    • Petri Mar 21, 2023 @ 12:42

      Hi,

      I think that the database should take care of this. What database are you using?

      • somesh Mar 23, 2023 @ 10:31

        i am using MYSQL DB.

      • Somesh Mar 23, 2023 @ 10:34

        i need auto incremented id, when we are inserting a new record into db using JdbcBatchItemWriter. If there is a way, could you please prove the code here!

        • Petri Mar 23, 2023 @ 17:57

          I would use the AUTO_INCREMENT keyword.

          • Somesh Mar 24, 2023 @ 9:20

            @Bean
            public JdbcBatchItemWriter productsInsertWriter() {
            String insertSql = "INSERT INTO SET_PRODUCTS_AND_SERVICES(PRODUCTNAME, PRODUCTCODE,etc.....)";

            JdbcBatchItemWriter itemWriter = new JdbcBatchItemWriter();
            itemWriter.setDataSource(batch.datasource);
            itemWriter.setSql(insertSql);
            itemWriter.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider());
            return itemWriter;
            }

            Currently, i'm using this snippet to insert data into db. but here, using this snippet, i'm unable to understand how to get (*fetch) auto incremented Id from db. Could you please let me know how to fetch auto incremented id using this snippet!. (*Try to make required changes & provide the code here.Thankyou.)

          • Petri Mar 25, 2023 @ 10:13

            If you want to get the ids of the inserted rows from the database, you have to implement a custom ItemWriter and insert data into the database by using either the JdbcTemplate or NamedParameterJdbcTemplate class. May I ask why you need to get the ids from the database? I am asking this because I am wondering if your problem could be solved by adding a new step to the invoked Spring Batch job.

Leave a Reply