<?xml version="1.0" encoding="UTF-8"?>
<!--

    Copyright ${license.git.copyrightYears} the original author or authors.

    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.

-->
<document xmlns="http://maven.apache.org/XDOC/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/XDOC/2.0 http://maven.apache.org/xsd/xdoc-2.0.xsd">

  <properties>
    <title>MyBatis-Spring | Spring Batch</title>
    <author email="eduardo.macarron@gmail.com">Eduardo Macarron</author>
    <author email="brice.dutheil@gmail.com">Brice Dutheil</author>
  </properties>

  <body>
    <section name="Spring Batch">
      <p>
        As of version 1.1.0 MyBatis-Spring provides three beans for building Spring Batch applications: the <code>MyBatisPagingItemReader</code>,
        the <code>MyBatisCursorItemReader</code> and the <code>MyBatisBatchItemWriter</code>.
        Also, As of version 2.0.0 provides three builder classes for supporting the Java Configuration: the <code>MyBatisPagingItemReaderBuilder</code>,
        the <code>MyBatisCursorItemReaderBuilder</code> and the <code>MyBatisBatchItemWriterBuilder</code>.
      </p>
      
      <p>
        <span class="label important">NOTE</span> This is about <a href="http://static.springsource.org/spring-batch/">Spring Batch</a> and not
        about MyBatis batch SqlSessions. For information about batch sessions go to section <a href="sqlsession.html">Using an SqlSession</a>.
      </p>
      
      <subsection name="MyBatisPagingItemReader">
        <p>
          This bean is an <code>ItemReader</code> that reads records from a database in a paging fashion.
        </p>
        
        <p>
          It executes the query specified as the <code>setQueryId</code> property to retrieve requested data.
          The query is executed using paged requests of a size specified in <code>setPageSize</code> property.
          Additional pages are requested when needed as <code>read()</code> method is called, returning an object corresponding
          to current position. 
          
          Some standard query parameters are provided by the reader and the SQL in the named query must use some or all of these parameters
          (depending on the SQL variant) to construct a result set of the required size. The parameters are:
        </p>

        <ul>
          <li><code>_page</code>: the page number to be read (starting at 0)</li>
          <li><code>_pagesize</code>: the size of the pages, i.e. the number of rows to return</li>
          <li><code>_skiprows</code>: the product of <code>_page</code> and
          <code>_pagesize</code></li>
        </ul>

        <p>And they could be mapped as the follow in a select statement:</p>
        <source><![CDATA[<select id="getEmployee" resultMap="employeeBatchResult">
  SELECT id, name, job FROM employees ORDER BY id ASC LIMIT #{_skiprows}, #{_pagesize}
</select>]]></source>

        <p>Follows below a sample configuration snippet:</p>
        
        <source><![CDATA[<bean id="reader" class="org.mybatis.spring.batch.MyBatisPagingItemReader">
  <property name="sqlSessionFactory" ref="sqlSessionFactory" />
  <property name="queryId" value="com.my.name.space.batch.EmployeeMapper.getEmployee" />
</bean>]]></source>

        <source><![CDATA[
@Bean
public MyBatisPagingItemReader<Employee> reader() {
  return new MyBatisPagingItemReaderBuilder<Employee>()
      .sqlSessionFactory(sqlSessionFactory())
      .queryId("com.my.name.space.batch.EmployeeMapper.getEmployee")
      .build();
}]]></source>

        <p><strong>Explaining a more complex example:</strong></p>

        <source><![CDATA[
<bean id="dateBasedCriteriaReader" class="org.mybatis.spring.batch.MyBatisPagingItemReader"
  p:sqlSessionFactory-ref="batchReadingSessionFactory"
  p:parameterValues-ref="datesParameters"
  p:queryId="com.my.name.space.batch.ExampleMapper.queryUserInteractionsOnSpecificTimeSlot"
  p:pageSize="200"
  scope="step"/>

<util:map id="datesParameters" scope="step">
  <entry key="yesterday" value="#{jobExecutionContext['EXTRACTION_START_DATE']}"/>
  <entry key="today" value="#{jobExecutionContext['TODAY_DATE']}"/>
  <entry key="first_day_of_the_month" value="#{jobExecutionContext['FIRST_DAY_OF_THE_MONTH_DATE']}"/>
  <entry key="first_day_of_the_previous_month" value="#{jobExecutionContext['FIRST_DAY_OF_THE_PREVIOUS_MONTH_DATE']}"/>
</util:map>]]></source>

        <source><![CDATA[
@StepScope
@Bean
public MyBatisPagingItemReader<User> dateBasedCriteriaReader(
    @Value("#{@datesParameters}") Map<String, Object> datesParameters) throws Exception {
  return new MyBatisPagingItemReaderBuilder<User>()
      .sqlSessionFactory(batchReadingSessionFactory())
      .queryId("com.my.name.space.batch.ExampleMapper.queryUserInteractionsOnSpecificTimeSlot")
      .parameterValues(datesParameters)
      .pageSize(200)
      .build();
}

@StepScope
@Bean
public Map<String, Object> datesParameters(
    @Value("#{jobExecutionContext['EXTRACTION_START_DATE']}") LocalDate yesterday,
    @Value("#{jobExecutionContext['TODAY_DATE']}") LocalDate today,
    @Value("#{jobExecutionContext['FIRST_DAY_OF_THE_MONTH_DATE']}") LocalDate firstDayOfTheMonth,
    @Value("#{jobExecutionContext['FIRST_DAY_OF_THE_PREVIOUS_MONTH_DATE']}") LocalDate firstDayOfThePreviousMonth) {
  Map<String, Object> map = new HashMap<>();
  map.put("yesterday", yesterday);
  map.put("today", today);
  map.put("first_day_of_the_month", firstDayOfTheMonth);
  map.put("first_day_of_the_previous_month", firstDayOfThePreviousMonth);
  return map;
}]]></source>

        <p>
          The previous example makes use of a few different things:
        </p>
        
        <ul>
          <li><code>sqlSessionFactory</code>: You can specify your own sessionFactory to the reader, it might be
            useful if you want to read from several databases.</li>
          <li><code>queryId</code>: If the base code have several tables or databases to read from, and that you have different
            queries, it might be interesting to use different mapper files with different namespaces.
            so when referring to the query, don't forget about the namespace of the mapper file.</li>
          <li><code>parameterValues</code>: You can pass additional parameters via this map, the example above uses
            a map that is build by spring using a SpEL expression taking values from the <code>jobExecutionContext</code>.
            The keys of the map will be used by MyBatis in the mapper file (ex:
            <em>yesterday</em> could be used as <code>#{yesterday,jdbcType=TIMESTAMP}</code>).
            Note that the map and the reader are both built in the <code>step</code> scope in order to be able to use
            the Spring EL expression with the <code>jobExecutionContext</code>. Also if MyBatis type handlers
            are correctly configured you can pass custom instances like the parameters of this map that are JodaTime
            dates.</li>
          <li><code>pageSize</code>: If the batch flow is configured with chunk size, it is relevant to pass this
            information to the reader as well, which is done via this property.</li>
        </ul>

      </subsection>

      <subsection name="MyBatisCursorItemReader">
        <p>
          This bean is an <code>ItemReader</code> that reads records from a database using a cursor.
        </p>

        <p>
          <span class="label important">NOTE</span> To use this bean you need at least MyBatis 3.4.0 or a newer version.
        </p>
        
        <p>
          It executes the query specified as the <code>setQueryId</code> property to retrieve requested data
          by using the method <code>selectCursor()</code>.
          Each time a <code>read()</code> method is called it will return the next element of the cursor until no more
          elements are left.
        </p>
        
        <p>
          The reader will use a separate connection so the select statement does no participate in any transactions created 
          as part of the step processing.
        </p>

        <p>When using the cursor you can just execute a regular query:</p>
        <source><![CDATA[<select id="getEmployee" resultMap="employeeBatchResult">
  SELECT id, name, job FROM employees ORDER BY id ASC
</select>]]></source>

        <p>Follows below a sample configuration snippet:</p>
        
        <source><![CDATA[<bean id="reader" class="org.mybatis.spring.batch.MyBatisCursorItemReader">
  <property name="sqlSessionFactory" ref="sqlSessionFactory" />
  <property name="queryId" value="com.my.name.space.batch.EmployeeMapper.getEmployee" />
</bean>]]></source>

        <source><![CDATA[
@Bean
public MyBatisCursorItemReader<Employee> reader() {
  return new MyBatisCursorItemReaderBuilder<Employee>()
      .sqlSessionFactory(sqlSessionFactory())
      .queryId("com.my.name.space.batch.EmployeeMapper.getEmployee")
      .build();
}]]></source>

      </subsection>

      <subsection name="MyBatisBatchItemWriter">
      
        <p>
          It is an <code>ItemWriter</code> that uses the batching features from <code>SqlSessionTemplate</code>
          to execute a batch of statements for all items provided.
          The <code>SqlSessionFactory</code> needs to be configured with a <code>BATCH</code> executor.
        </p>

        <p>
          When <code>write()</code> is called it executes the mapped statement indicated in the property <code>statementId</code>. 
          It is expected that <code>write()</code> is called inside a transaction.<br/>
        </p>
 
        <p>Follows below a sample configuration snippet:</p>
        
        <source><![CDATA[<bean id="writer" class="org.mybatis.spring.batch.MyBatisBatchItemWriter">
  <property name="sqlSessionFactory" ref="sqlSessionFactory" />
  <property name="statementId" value="com.my.name.space.batch.EmployeeMapper.updateEmployee" />
</bean>]]></source>

        <source><![CDATA[
@Bean
public MyBatisBatchItemWriter<User> writer() {
  return new MyBatisBatchItemWriterBuilder<User>()
      .sqlSessionFactory(sqlSessionFactory())
      .statementId("com.my.name.space.batch.EmployeeMapper.updateEmployee")
      .build();
}]]></source>

        <p><strong>Converting a item that read using ItemReader to an any parameter object:</strong></p>

        <p>
          By default behavior, the <code>MyBatisBatchItemWriter</code> passes a item that read using <code>ItemReader</code>
          (or convert by <code>ItemProcessor</code>) to the MyBatis(<code>SqlSession#update()</code>) as the parameter object.
          If you want to customize a parameter object that passes to the MyBatis, you can realize to use the <code>itemToParameterConverter</code> option.
          For example using <code>itemToParameterConverter</code> option, you can passes any objects other than the item object to the MyBatis.
          Follows below a sample:
        </p>

        <p>
          At first, you create a custom converter class (or factory method). The following sample uses a factory method.
        </p>

        <source><![CDATA[
public static <T> Converter<T, Map<String, Object>> createItemToParameterMapConverter(String operationBy, LocalDateTime operationAt) {
  return item -> {
    Map<String, Object> parameter = new HashMap<>();
    parameter.put("item", item);
    parameter.put("operationBy", operationBy);
    parameter.put("operationAt", operationAt);
    return parameter;
  };
}]]></source>

        <p>
          At next, you write a sql mapping.
        </p>

        <source><![CDATA[
<select id="createPerson" resultType="org.mybatis.spring.sample.domain.Person">
    insert into persons (first_name, last_name, operation_by, operation_at)
           values(#{item.firstName}, #{item.lastName}, #{operationBy}, #{operationAt})
</select>]]></source>

        <p>
          At last, you configure the <code>MyBatisBatchItemWriter</code>.
        </p>

        <source><![CDATA[
// Sample for Java Config
@Bean
public MyBatisBatchItemWriter<Person> writer() throws Exception {
  return new MyBatisBatchItemWriterBuilder<Person>()
      .sqlSessionFactory(sqlSessionFactory())
      .statementId("org.mybatis.spring.sample.mapper.PersonMapper.createPerson")
      .itemToParameterConverter(createItemToParameterMapConverter("batch_java_config_user", LocalDateTime.now()))
      .build();
}]]></source>

        <source><![CDATA[
<!-- Sample for XML Config -->
<bean id="writer" class="org.mybatis.spring.batch.MyBatisBatchItemWriter">
  <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
  <property name="statementId" value="org.mybatis.spring.sample.mapper.PersonMapper.createPerson"/>
  <property name="itemToParameterConverter">
    <bean class="org.mybatis.spring.sample.config.SampleJobConfig" factory-method="createItemToParameterMapConverter">
      <constructor-arg type="java.lang.String" value="batch_xml_config_user"/>
      <constructor-arg type="java.time.LocalDateTime" value="#{T(java.time.LocalDateTime).now()}"/>
    </bean>
  </property>
</bean>
]]></source>

        <p><strong>Writing to different tables using composite writers (with some caveats):</strong></p>

        <p>This technique can only be used with MyBatis 3.2+, as there was an
          <a href="http://code.google.com/p/mybatis/issues/detail?id=741">issue</a> in previous
          versions that made the writer misbehave.
        </p>

        <p>If the batch needs to write complex data, like records with associations, or even to different databases, then
          it is possible to work around the fact that insert statements only insert in one table. In order to make it
          happen the batch have to prepare the <em>Item</em> to be written by the writer. However depending on the
          constraints, opportunities or insight on the processed data it might be interesting to use the following technique.
          The following trick can work on items with simple associations or just with unrelated tables.
        </p>

        <p>In a processor craft the Spring Batch Item in such way it will <em>hold</em> all the different records.
          Suppose for each Item there is an <em>Interaction</em> that have one association
          <em>InteractionMetadata</em>, and two non associated rows <em>VisitorInteraction</em> and
          <em>CustomerInteraction</em>, the holder object will look like:
        </p>

        <source><![CDATA[public class InteractionRecordToWriteInMultipleTables {
  private final VisitorInteraction visitorInteraction;
  private final CustomerInteraction customerInteraction;
  private final Interaction interaction;
  // ...
}

public class Interaction {
  private final InteractionMetadata interactionMetadata;
}]]></source>

        <p>Then in the spring configuration there will be a <code>CompositeItemWriter</code> that will use delegate
          writers specifically configured for each kind of records. Note that as the <em>InteractionMetadata</em> is
          an association in the example it will need to be written first so that Interaction can have the updated key.
        </p>

        <source><![CDATA[<bean id="interactionsItemWriter" class="org.springframework.batch.item.support.CompositeItemWriter">
  <property name="delegates">
    <list>
      <ref bean="visitorInteractionsWriter"/>
      <ref bean="customerInteractionsWriter"/>

      <!-- Order is important -->
      <ref bean="interactionMetadataWriter"/>
      <ref bean="interactionWriter"/>
    </list>
  </property>
</bean>]]></source>

        <source><![CDATA[
@Bean
public CompositeItemWriter<?> interactionsItemWriter() {
  CompositeItemWriter compositeItemWriter = new CompositeItemWriter();
  List<ItemWriter<?>> writers = new ArrayList<>(4);
  writers.add(visitorInteractionsWriter());
  writers.add(customerInteractionsWriter());
  writers.add(interactionMetadataWriter());
  writers.add(interactionWriter());
  compositeItemWriter.setDelegates(writers);
  return compositeItemWriter;
}]]></source>

        <p>Then each delegate writer will be configured as needed; for example for <em>Interaction</em> and
          <em>InteractionMetadata</em>:
        </p>

        <source><![CDATA[<bean id="interactionMetadataWriter"
  class="org.mybatis.spring.batch.MyBatisBatchItemWriter"
  p:sqlSessionTemplate-ref="batchSessionTemplate"
  p:statementId="com.my.name.space.batch.InteractionRecordToWriteInMultipleTablesMapper.insertInteractionMetadata"/>
<bean id="interactionWriter"
  class="org.mybatis.spring.batch.MyBatisBatchItemWriter"
  p:sqlSessionTemplate-ref="batchSessionTemplate"
  p:statementId="com.my.name.space.batch.InteractionRecordToWriteInMultipleTablesMapper.insertInteraction"/>]]></source>

        <p>Same as the reader the <code>statementId</code> can refer to the statement with the prefixed namespace.</p>

        <p>Now in the mapper file the statement have to be crafted for each kind of records in the following way:</p>

        <source><![CDATA[<insert id="insertInteractionMetadata"
  parameterType="com.my.batch.interactions.item.InteractionRecordToWriteInMultipleTables"
  useGeneratedKeys="true"
  keyProperty="interaction.interactionMetadata.id"
  keyColumn="id">
  <!-- the insert statement using #{interaction.interactionMetadata.property,jdbcType=...} -->
</insert>
<insert id="insertInteraction"
  parameterType="com.my.batch.interactions.item.InteractionRecordToWriteInMultipleTables"
  useGeneratedKeys="true"
  keyProperty="interaction.id"
  keyColumn="id">
  <!--
   the insert statement using #{interaction.property,jdbcType=...} for regular properties
   and #{interaction.interactionMetadata.property,jdbcType=...} for the InteractionMetadata property
  -->
</insert>]]></source>

        <p>What's happening is that first the <code>insertInteractionMetadata</code> will be called, and the update
          statement is configured to return the ids created by the jdbc driver (<code>keyProperty</code> and <code>keyColumn</code>).
          As the <code>InteractionMetadata</code> object were updated by this query the next query can be used to write the parent
          object <code>Interaction</code> via <code>insertInteraction</code>.
        </p>

        <p><strong><em>However note that JDBC drivers don't behave the same in this regard. At the time of this writing
          the H2 driver 1.3.168 will only return the latest index even in BATCH mode (see org.h2.jdbc.JdbcStatement#getGeneratedKeys),
          while the MySQL JDBC driver will behave as expected and return all the IDs.</em></strong>
        </p>
      </subsection>
    </section>
  </body>
</document>