The Problem

MyBatis Spring support provides utility classes for interacting with Spring Batch (see http://www.mybatis.org/spring/batch.html). These classes are specialized implementations of Spring Batch's ItemReader and ItemWriter interfaces that have support for MyBatis mappers.

The ItemWriter implementations work with SQL generated by MyBatis Dynamic SQL with no modification needed.

The ItemReader implementations need special care. Those classes assume that all query parameters will be placed in a Map (as per usual when using multiple parameters in a query). MyBatis Dynamic SQL, by default, builds a parameter object that should be the only parameter in a query and will not work when placed in a Map of parameters.

The Solution

The solution involves these steps:

1. The SQL must be rendered such that the parameter markers are aware of the enclosing parameter Map in the ItemReader
2. The SelectStatementProvider must be placed in the ItemReader parameter Map with a known key.
3. The @SelectProvider must be configured to be aware of the enclosing parameter Map

MyBatis Dynamic SQL provides utilities for each of these requirements. Each utility uses a shared Map key for consistency.

Spring Batch Item Readers

MyBatis Spring support supplies two implementations of the ItemReader interface:

1. org.mybatis.spring.batch.MyBatisCursorItemReader - for queries that can be efficiently processed through a single select statement and a cursor
2. org.mybatis.spring.batch.MyBatisPagingItemReader - for queries that should be processed as a series of paged selects. Note that MyBatis does not provide any native support for paged queries - it is up to the user to write SQL for paging. The MyBatisPagingItemWriter simply makes properties available that specify which page should be read currently.

MyBatis Dynamic SQL supplies specialized select statements that will render properly for the different implementations of ItemReader:

1. SpringBatchUtility.selectForCursor(...) will create a select statement that is appropriate for the MyBatisCursorItemReader - a single select statement that will be read with a cursor
2. SpringBatchUtility.selectForPaging(...) will create a select statement that is appropriate for the MyBatisPagingItemReader - a select statement that will be called multiple times - one for each page as configured on the batch job.

Very Important: The paging implementation will only work for databases that support limit and offset in select statements. Fortunately, most databases do support this - with the notable exception of Oracle.

  SelectStatementProvider selectStatement =  SpringBatchUtility.selectForCursor(person.allColumns())
      .from(person)
      .where(lastName, isEqualTo("flintstone"))
      .build()
      .render(); // renders for MyBatisCursorItemReader

  SelectStatementProvider selectStatement =  SpringBatchUtility.selectForPaging(person.allColumns())
      .from(person)
      .where(lastName, isEqualTo("flintstone"))
      .build()
      .render(); // renders for MyBatisPagingItemReader

Creating the Parameter Map

The SpringBatchUtility provides a method to create the parameter values Map needed by the MyBatis Spring ItemReader implementations. It can be used as follows:

For cursor based queries…

  MyBatisCursorItemReader<Person> reader = new MyBatisCursorItemReader<>();
  reader.setQueryId(PersonMapper.class.getName() + ".selectMany");
  reader.setSqlSessionFactory(sqlSessionFactory);
  reader.setParameterValues(SpringBatchUtility.toParameterValues(selectStatement)); // create parameter map

For paging based queries…

  MyBatisPagingItemReader<Person> reader = new MyBatisPagingItemReader<>();
  reader.setQueryId(PersonMapper.class.getName() + ".selectMany");
  reader.setSqlSessionFactory(sqlSessionFactory);
  reader.setPageSize(7);
  reader.setParameterValues(SpringBatchUtility.toParameterValues(selectStatement)); // create parameter map

Specialized @SelectProvider Adapter

MyBatis mapper methods should be configured to use the specialized @SelectProvider adapter as follows:

  @SelectProvider(type=SpringBatchProviderAdapter.class, method="select") // use the Spring batch adapter
  @Results({
    @Result(column="id", property="id", id=true),
    @Result(column="first_name", property="firstName"),
    @Result(column="last_name", property="lastName")
  })
  List<Person> selectMany(Map<String, Object> parameterValues);

Complete Example

The unit tests for MyBatis Dynamic SQL include a complete example of using MyBatis Spring Batch support using the MyBatis supplied reader as well as both types of MyBatis supplied writers. You can see the full example here: https://github.com/mybatis/mybatis-dynamic-sql/tree/master/src/test/java/examples/springbatch