Spring Batch Tutorial: Reading Information From a Relational Database

The previous parts of my Spring Batch tutorial described how you can read information from CSV and XML files. These are definitely useful skills, but if you want to write real-life batch jobs, you have to know how you can read the input data of a Spring Batch job from a relational database.

After you have read this blog post, you:

Can read the input data of your batch job by using a database cursor.
Understand how you can read the input data of your batch job by using pagination.

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

This blog post assumes that:

You are familiar with Spring Batch
You can get the required dependencies with Maven or Gradle

Introduction to the Example Application

During this blog post you will read the input data of your batch job from a relational database which contains the student information of an online course. The student information is found from the students table that has the following columns:

The email_address column contains the email address of the student.
The name column contains the name of the student.
The purchased_package column contains the name of the purchased package.

The ItemReader which reads the student list from a relational database must return StudentDTO objects which are sorted in ascending order by using the email address of the student as a sort criteria. The StudentDTO class contains the information of a single student, and its source code looks as follows:

 1public class StudentDTO {
 2
 3    private String emailAddress;
 4    private String name;
 5    private String purchasedPackage;
 6
 7    public StudentDTO() {}
 8
 9    public String getEmailAddress() {
10        return emailAddress;
11    }
12
13    public String getName() {
14        return name;
15    }
16
17    public String getPurchasedPackage() {
18        return purchasedPackage;
19    }
20
21    public void setEmailAddress(String emailAddress) {
22        this.emailAddress = emailAddress;
23    }
24
25    public void setName(String name) {
26        this.name = name;
27    }
28
29    public void setPurchasedPackage(String purchasedPackage) {
30        this.purchasedPackage = purchasedPackage;
31    }
32}

Next, you will find out how you can read the input data of your batch job by using a database cursor.

Reading the Input Data of Your Batch Job by Using a Database Cursor

You can provide the input data for your batch job by configuring an ItemReader bean. Because you must read the student information from a relational database by using a database cursor, you have to configure this bean by following these steps:

First, you have to create the configuration class that contains the beans which describe the flow of your batch job. The source code of your configuration class looks as follows:

1import org.springframework.context.annotation.Configuration;
2
3@Configuration
4public class DatabaseCursorExampleJobConfig {
5}

Second, you have to create a method that configures your ItemReader bean. This method must take a DataSource object as a method parameter and return an ItemReader<StudentDTO> object. After you have created this method, the source code of your configuration class looks as follows:

 1import org.springframework.batch.item.ItemReader;
 2import org.springframework.context.annotation.Bean;
 3import org.springframework.context.annotation.Configuration;
 4
 5import javax.sql.DataSource;
 6
 7@Configuration
 8public class DatabaseCursorExampleJobConfig {
 9
10    @Bean
11    public ItemReader<StudentDTO> itemReader(DataSource dataSource) {
12
13    }
14}

Third, you have to configure your ItemReader bean by following these steps:

Create a new JdbcCursorItemReaderBuilder<StudentDTO> object. This object provides a fluent API which allows you to build a new JdbcCursorItemReader<StudentDTO> object that reads the input data by opening a JDBC cursor and continuously retrieves the next row from the ResultSet.
Configure the name of the created JdbcCursorItemReader<StudentDTO> object.
Configure the data source that provides database connections to the created JdbcCursorItemReader<StudentDTO> object.
Configure the SQL query that’s used to query the input data from the used database.
Configure the RowMapper<T> object that transforms the information of a single database row into a new T object. Because the field names of the StudentDTO class matches with the column names of the students table, you can configure the used RowMapper by creating a new BeanPropertyRowMapper<StudentDTO> object.
Return the created JdbcCursorItemReader<StudentDTO> object.

After you have configured your ItemReader bean, the source code of your configuration class looks as follows:

 1import org.springframework.batch.item.ItemReader;
 2import org.springframework.batch.item.database.builder.JdbcCursorItemReaderBuilder;
 3import org.springframework.context.annotation.Bean;
 4import org.springframework.context.annotation.Configuration;
 5import org.springframework.jdbc.core.BeanPropertyRowMapper;
 6
 7import javax.sql.DataSource;
 8
 9@Configuration
10public class DatabaseCursorExampleJobConfig {
11
12    private static final String QUERY_FIND_STUDENTS =
13            "SELECT " +
14                    "email_address, " +
15                    "name, " +
16                    "purchased_package " +
17            "FROM STUDENTS " +
18            "ORDER BY email_address ASC";
19
20    @Bean
21    public ItemReader<StudentDTO> itemReader(DataSource dataSource) {
22        return new JdbcCursorItemReaderBuilder<StudentDTO>()
23                .name("cursorItemReader")
24                .dataSource(dataSource)
25                .sql(QUERY_FIND_STUDENTS)
26                .rowMapper(new BeanPropertyRowMapper<>(StudentDTO.class))
27                .build();
28    }
29}

Additional Reading:

Let’s find out how you can read the input data of your batch job by using pagination.

Reading the Input Data of Your Batch Job by Using Pagination

You can provide the input data for your batch job by configuring an ItemReader bean. Because you must read the student information from a relational database by using pagination, you have to configure this bean by following these steps:

First, you have to create the configuration class that contains the beans which describe the flow of your batch job. The source code of your configuration class looks as follows:

1import org.springframework.context.annotation.Configuration;
2
3@Configuration
4public class JDBCPaginationExampleJobConfig {
5}

Second, you have create a method that configures the SqlPagingQueryProviderFactoryBean bean and ensure that this method takes a DataSource object as a method parameter. The SqlPagingQueryProviderFactoryBean class has two responsibilities:

It auto-detects the used database.
It determines the appropriate PagingQueryProvider implementation that constructs the SQL queries which fetch the paginated data from the database.

After you have created this method, you have to implement it by following these steps:

Create a new SqlPagingQueryProviderFactoryBean object.
Configure the data source which is used to determine the type of the used database.
Specify the SELECT and FROM clauses which query all rows from the source table.
Specify the sort keys which are used to sort the query results of the executed database queries.
Return the created SqlPagingQueryProviderFactoryBean object.

After you have configured the SqlPagingQueryProviderFactoryBean bean, the source code of your configuration class looks as follows:

 1import org.springframework.batch.item.database.Order;
 2import org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean;
 3import org.springframework.context.annotation.Bean;
 4import org.springframework.context.annotation.Configuration;
 5
 6import javax.sql.DataSource;
 7import java.util.HashMap;
 8import java.util.Map;
 9
10@Configuration
11public class JDBCPaginationExampleJobConfig {
12
13    @Bean
14    public SqlPagingQueryProviderFactoryBean queryProvider(DataSource dataSource) {
15        SqlPagingQueryProviderFactoryBean provider =
16                new SqlPagingQueryProviderFactoryBean();
17
18        provider.setDataSource(dataSource);
19        provider.setSelectClause("SELECT email_address, name, purchased_package");
20        provider.setFromClause("FROM students");
21        provider.setSortKeys(sortByEmailAddressAsc());
22
23        return provider;
24    }
25
26    private Map<String, Order> sortByEmailAddressAsc() {
27        Map<String, Order> sortConfiguration = new HashMap<>();
28        sortConfiguration.put("email_address", Order.ASCENDING);
29        return sortConfiguration;
30    }
31}

Third, you have to create a method that configures your ItemReader bean. This method must take DataSource and PagingQueryProvider objects as method parameters, and it must return an ItemReader<StudentDTO> object.

After you have created this method, you must implement it by following these steps:

Create a new JdbcPagingItemReaderBuilder<StudentDTO> object. This object provides a fluent API which allows you to build a new JdbcPagingItemReader<StudentDTO> object that reads the input data of your batch job one page at a time by using JDBC.
Configure the name of the created JdbcPagingItemReader<StudentDTO> object.
Configure the data source that provides database connections to the created JdbcPagingItemReader<StudentDTO> object.
Configure the used page size. You can use one as page size because the students table has only three rows. However, if this would be a real-life application, you should select the best page size by profiling your batch job.
Configure the used PagingQueryProvider object. As you remember, this object constructs the SQL queries which fetch the paginated data from the database.
Configure the RowMapper<T> object that transforms the information of a single database row into a new T object. Because the field names of the StudentDTO class matches with the column names of the students table, you can configure the used RowMapper by creating a new BeanPropertyRowMapper<StudentDTO> object.
Return the created JdbcPagingItemReader<StudentDTO> object.

After you have configured your ItemReader bean, the source code of your configuration class looks as follows:

 1import org.springframework.batch.item.ItemReader;
 2import org.springframework.batch.item.database.Order;
 3import org.springframework.batch.item.database.PagingQueryProvider;
 4import org.springframework.batch.item.database.builder.JdbcPagingItemReaderBuilder;
 5import org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean;
 6import org.springframework.context.annotation.Bean;
 7import org.springframework.context.annotation.Configuration;
 8import org.springframework.jdbc.core.BeanPropertyRowMapper;
 9
10import javax.sql.DataSource;
11import java.util.HashMap;
12import java.util.Map;
13
14@Configuration
15public class JDBCPaginationExampleJobConfig {
16
17    @Bean
18    public ItemReader<StudentDTO> itemReader(DataSource dataSource,
19                                             PagingQueryProvider queryProvider) {
20        return new JdbcPagingItemReaderBuilder<StudentDTO>()
21                .name("pagingItemReader")
22                .dataSource(dataSource)
23                .pageSize(1)
24                .queryProvider(queryProvider)
25                .rowMapper(new BeanPropertyRowMapper<>(StudentDTO.class))
26                .build();
27    }
28
29    @Bean
30    public SqlPagingQueryProviderFactoryBean queryProvider(DataSource dataSource) {
31        SqlPagingQueryProviderFactoryBean provider =
32                new SqlPagingQueryProviderFactoryBean();
33
34        provider.setDataSource(dataSource);
35        provider.setSelectClause("SELECT email_address, name, purchased_package");
36        provider.setFromClause("FROM students");
37        provider.setSortKeys(sortByEmailAddressAsc());
38
39        return provider;
40    }
41
42    private Map<String, Order> sortByEmailAddressAsc() {
43        Map<String, Order> sortConfiguration = new HashMap<>();
44        sortConfiguration.put("email_address", Order.ASCENDING);
45        return sortConfiguration;
46    }
47}
48}

Because the implementations of the PagingQueryProvider interface paginate the query results by using database specific features, the performance of this solution depends from the size of your dataset and the used database.

Additional Reading:

You can now read the input data of your batch job from a relational database. Let’s summarize what you learned from this blog post.

Summary

This blog post has taught you three things:

If you want to read the input data of your batch job from a relational database by using a database cursor, you can build the JdbcCursorItemReader<T> object by using the JdbcCursorItemReaderBuilder<T> class.
If you want to read the input data of your batch job from a relational database by using pagination, you can auto-detect the used database and select the approriate pagination logic by configuring the SqlPagingQueryProviderFactoryBean bean.
If you want to read the input data of your batch job from a relational database by using pagination, you can build the JdbcPagingItemReader<T> object by using the JdbcPagingItemReaderBuilder<T> class.

The next part of this tutorial describes how you can implement a custom ItemReader.

P.S. You can get the example applications of this blog post from Github.

Introduction to the Example Application

Reading the Input Data of Your Batch Job by Using a Database Cursor

Reading the Input Data of Your Batch Job by Using Pagination

Summary

Write Better Code and Save Time