Database
Due to various factors like scalability, transaction handling capability, performance etc. using database in an enterprise application has become de-facto standard for any modern day enterprise application. Spring Batch provide out of the box implementations to handle reading input data from a database including JDBC, Hibernate, and JPA. We will now see each of them in the following sections.
A common use case in any enterprise application is to process large amount of data read from a database. Typically if you execute a query which fetches millions of record, it won’t be a nice idea to load all these records in memory and then handle it according to business requirement. Spring Batch provides two different methods for loading records one at a time as they are processed, pictorially represented as shown in Figure 23-13 below:
- Cursor – After the ResultSet is opened, every time the next() method is called a record from the database is returned. This makes streaming of one record at a time from the database to the batch processor. The various cursor based item readers provided by Spring Batch are:
- JdbcCursorItemReader
- HibernateCursorItemReader
- StoredProcedureItemReader
- Paging – In the concept of paging, you retrieve a set of records from the database called pages. This makes streaming of a set of records of page from the database to the batch processor. The various paging based item readers provided by Spring Batch are:
- JdbcPagingItemReader
- JpaPagingItemReader
- HibernatePagingItemReader
- IbatisPagingItemReader
Figure 23-13. Working of a Paging and Cursor
JDBC
Spring Batch provides two JDBC item reader classes namely:
- org.springframework.batch.item.database.JdbcCursorItemReader – It’s a simple item reader implementation that opens a JDBC cursor and continually retrieves the next row in the ResultSet. By default the cursor will be opened using a separate connection which means that it will not participate in any transactions created as part of the step processing.
- org.springframework.batch.item.database.JdbcPagingItemReader – It’s an
for reading database records using JDBC in a paging fashion. The performance of the paging depends on the database specific features available to limit the number of returned rows. Setting a fairly large page size and using a commit interval that matches the page size should provide better performance.1ItemReader
To configure the JdbcCursorItemReader, you provide a minimum of three dependencies: a datasource, the query you want to run, and your RowMapper implementation as shown in Listing 23-11 below.
Listing 23-11. Configuring a sample JdbcCursorItemReader
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
… <bean id="sampleItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader"> <property name="dataSource" ref="dataSource"/> <property name="sql" value="select * from sampleTable"/> <property name="rowMapper" ref="sampleRowMapper"/> </bean> … |
To configure the JdbcPagingItemReader, you need to have four dependencies: a datasource, the PagingQueryProvider implementation, your RowMapper implementation, and the size of your page as shown in Listing 23-12 below.
Listing 23-12. Configuring a sample JdbcPagingItemReader
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
… <bean id="sampleItemReader" class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step"> <property name="dataSource" ref="dataSource"/> <property name="queryProvider"> <bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean"> … </bean> </property> <property name="parameterValues"> … </property> <property name="pageSize" value="10"/> <property name="rowMapper" ref="sampleRowMapper"/> </bean> <bean id="sampleRowMapper" class="com.mybook.sample.SampleRowMapper"/> … |
The class org.springframework.batch.item.database.JdbcBatchItemWriter is an ItemWriter that uses the batching features from SimpleJdbcTemplate to execute a batch of statements for all items provided. The user must provide an SQL query and a special callback. SQL query can use either named parameters or the traditional ‘?’ placeholders. Listing 23-13 below shows configuring JdbcBatchItemWriter in the Spring configuration file.
Listing 23-13. Configuring a sample JdbcBatchItemWriter
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
… <bean id="jdbcBatchWriter" class="org.springframework.batch.item.database.JdbcBatchItemWriter"> <property name="dataSource" ref="dataSource"/> <property name="sql" value="…"/> <property name="itemPreparedStatementSetter" ref="… "/> </bean> … |
Page Visitors: 25402
Tomcy John
Latest posts by Tomcy John (see all)
- A Guide to Continuous Improvement for Architects - February 2, 2023
- Cloud-first Architecture Strategy - January 26, 2023
- Architecture Strategy and how to create One - January 24, 2023
Thanks for sharing the information but what’s the difference between CursorItemReader’s setFetchSize() and PagingItemReader’s setPageSize()? isn’t it the same?
Please provide an example for StoredProcedureItem Reader – which returns cursor and how to process cursor in processor – I am new to Spring Batch as part of my work I need to invoke Oracle Stored proc which takes one param as input and returns result set which I need to process in Spring Batch Processor.
Thanks
Laxmi