Overview
MyBatis3 (hereafter, called [MyBatis]) is used for database access in TERASOLUNA Batch 5.x. Please refer below TERASOLUNA Server 5.x Development Guideline for basic usage of database access using MyBatis.
This chapter mainly explain how to use database access as TERASOLUNA Batch 5.x specifically.
Since this function works differently for chunk model and tasket model, respective explanations are given.
How to use
Explain how to use database access as TERASOLUNA Batch 5.x.
There are following 2 ways to use database access in TERASOLUNA Batch 5.x.
Please choose them based on the components accessing the database.
-
Use ItemReader and ItemWriter for MyBatis.
-
For Input/Output by using database access as chunk model.
-
org.mybatis.spring.batch.MyBatisCursorItemReader
-
org.mybatis.spring.batch.MyBatisBatchItemWriter
-
-
-
Use Mapper interface
-
For bussiness logic processing as chunk model.
-
With ItemProcessor implementation.
-
-
For whole database access as tasklet model.
-
With Tasklet implementation.
-
-
Common Settings
Explain common settings required for database access.
DataSource Setting
It assumes two data sources in TERASOLUNA Batch 5.x.
Show 2 default data sources in launch-context.xml
.
Data source name | Description |
---|---|
|
Data source used by Spring Batch and TERASOLUNA Batch 5.x |
|
Data source used by job |
Show the property of connection information and launch-context.xml below.
Set these settings according to the user’s environment.
<!-- (1) -->
<bean id="adminDataSource" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close"
p:driverClassName="${admin.jdbc.driver}"
p:url="${admin.jdbc.url}"
p:username="${admin.jdbc.username}"
p:password="${admin.jdbc.password}"
p:maxTotal="10"
p:minIdle="1"
p:maxWaitMillis="5000"
p:defaultAutoCommit="false"/>
<!-- (2) -->
<bean id="jobDataSource" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close"
p:driverClassName="${jdbc.driver}"
p:url="${jdbc.url}"
p:username="${jdbc.username}"
p:password="${jdbc.password}"
p:maxTotal="10"
p:minIdle="1"
p:maxWaitMillis="5000"
p:defaultAutoCommit="false" />
# (3)
# Admin DataSource settings.
admin.jdbc.driver=org.h2.Driver
admin.jdbc.url=jdbc:h2:mem:batch;DB_CLOSE_DELAY=-1
admin.jdbc.username=sa
admin.jdbc.password=
# (4)
# Job DataSource settings.
jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/postgres
jdbc.username=postgres
jdbc.password=postgres
Sr. No. | Description |
---|---|
(1) |
|
(2) |
|
(3) |
Connection information to database used by |
(4) |
Connection information to database used by |
MyBatis Setting
Important points for setting MyBatis on TERASOLUNA Batch 5.x.
One of the important points in implementing batch processing is "to efficiently process large amounts of data with certain resources"
Explain the setting.
-
fetchSize
-
In general batch processing, it is mandatory to specify the appropriate
fetchSize
for the JDBC driver to reduce the communication cost of processing large amounts of data.fetchSize
is a parameter that sets the number of data to be acquired by one communication between the JDBC driver and the database. It is desirable to set this value as large as possible. However, if it is too large, it presses memory. So please be careful. user has to tune the parameter. -
In MyBatis, user can set
defaultFetchSize
as a common setting for all queries, and can override it withfetchSize
setting for each query.
-
-
executorType
-
In general batch processing, the same SQL is executed within the same transaction for the number of
total data count/fetchSize
. At this time, it is possible to process efficiently by reusing a statement instead of creating it each time. -
In the MyBatis setting, it can reuse statements by setting
REUSE
indefaultExecutorType
and contributes to improved processing throughput. -
When updating a large amount of data at once, performance improvement can be expected by using batch update of JDBC.
Therefore,SqlSessionTemplate
used inMyBatisBatchItemWriter
is set toBATCH
(notREUSE
) inexecutorType
.
-
In TERASOLUNA Batch 5.x, two different ExecutorType
exists at the same time.
It is assumed that it is often implemented by one ExecutorType
, but special attention is required when using them together.
The detail will be explained in Database Access other than ItemReader・ItemWriter.
Other parameter of MyBatis
For other parameters, refer to the following links and make settings that match the application characteristics. |
Show the default setting below.
<bean id="jobSqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean"
p:dataSource-ref="jobDataSource">
<!-- (1) -->
<property name="configuration">
<bean class="org.apache.ibatis.session.Configuration"
p:localCacheScope="STATEMENT"
p:lazyLoadingEnabled="true"
p:aggressiveLazyLoading="false"
p:defaultFetchSize="1000"
p:defaultExecutorType="REUSE"/>
</property>
</bean>
<!-- (2) -->
<bean id="batchModeSqlSessionTemplate"
class="org.mybatis.spring.SqlSessionTemplate"
c:sqlSessionFactory-ref="jobSqlSessionFactory"
c:executorType="BATCH"/>
Sr. No. | Description |
---|---|
(1) |
Various setting of MyBatis |
(2) |
For |
For the definition of SqlSessionFactory using adminDataSource
When performing synchronous execution, META-INF/spring/async-batch-daemon.xml
|
Mapper XML definition
Please refer to Implementation of database access process in TERASOLUNA Server 5.x Development Guideline, because there are no specific description about TERASOLUNA Batch 5.x.
MyBatis-Spring setting
When using ItemReader and ItemWriter provided by MyBatis-Spring, it is necessary to set Mapper XML used in Mapper’s Config.
As the setting method, there are following 2 methods.
-
Register Mapper XML to be used for all jobs as a common setting.
-
All Mapper XML has to be described in
META-INF/spring/launch-context.xml
.
-
-
Register Mapper XML to be used for each job as individual setting.
-
Mapper XML required by each job has to be described in bean definition under
META-INF/jobs/
-
If common settings are made, the following adverse effects arise because not only Mapper XML of jobs executed, but also Mapper XML used by other jobs are also read when executing synchronous execution.
-
It takes time to start the job
-
Consumption of memory resources increases
To avoid it, TERASOLUNA Batch 5.x adopts a setting method that specifies only Mapper XML that the job requires for each job definition as individual setting.
For the basic setting method, please refer to MyBatis-Spring settings in TERASOLUNA Server 5.x Development Guideline.
In TERASOLUNA Batch 5.x, since multiple SqlSessionFactory
and SqlSessionTemplate
are defined,
it is necessary to explicitly specify which one to use.
Basically, specify jobSqlSessionFactory
Show setting example below.
<!-- (1) -->
<mybatis:scan
base-package="org.terasoluna.batch.functionaltest.app.repository.mst"
factory-ref="jobSqlSessionFactory"/>
Sr. No. | Description |
---|---|
(1) |
Set |
Database access with ItemReader
Explain Database access with ItemReader here.
ItemReader of MyBatis
MyBatis-Spring provides the following two ItemReader.
-
org.mybatis.spring.batch.MyBatisCursorItemReader
-
org.mybatis.spring.batch.MyBatisPagingItemReader
MyBatisPagingItemReader
is an ItemReader
that uses the mechanism described
in Pagination search for Entity (SQL refinement method) of TERASOLUNA Server 5.x Development Guideline
Since SQL is issued again after acquiring a certain number of cases, there is a possibility that data consistency may not be maintained.
Therefore, it is dangerous to use it in batch processing, so TERASOLUNA Batch 5.x does not use it in principle.
TERASOLUNA Batch 5.x uses only MyBatisCursorItemReader
.
In TERASOLUNA Batch 5.x, as explained in MyBatis-Spring setting,
It adopts a method of dynamically registering Mapper XML with mybatis:scan
.
Therefore, it is necessary to prepare an interface corresponding to Mapper XML.
For details, please refer to
Implementation of database access process in TERASOLUNA Server 5.x Development Guideline.
Show an example of usage of MyBatisCursorItemReader
below.
<!-- (1) -->
<mybatis:scan
base-package="org.terasoluna.batch.functionaltest.app.repository.mst"
factory-ref="jobSqlSessionFactory"/>
<!-- (2) (3) (4) -->
<bean id="reader"
class="org.mybatis.spring.batch.MyBatisCursorItemReader" scope="step"
p:queryId="org.terasoluna.batch.functionaltest.app.repository.mst.CustomerRepository.findAll"
p:sqlSessionFactory-ref="jobSqlSessionFactory"/>
<!-- (5) -->
<mapper namespace="org.terasoluna.batch.functionaltest.app.repository.mst.CustomerRepository">
<!-- (6) -->
<select id="findAll"
resultType="org.terasoluna.batch.functionaltest.app.model.mst.Customer">
<![CDATA[
SELECT
customer_id AS customerId,
customer_name AS customerName,
customer_address AS customerAddress,
customer_tel AS customerTel,
charge_branch_id AS chargeBranchId,
create_date AS createDate,
update_date AS updateDate
FROM
customer_mst
ORDER by
charge_branch_id ASC, customer_id ASC
]]>
</select>
<!-- omitted -->
</mapper>
public interface CustomerRepository {
// (7)
List<Customer> findAll();
// omitted
}
Sr. No. | Description |
---|---|
(1) |
Register Mapper XML. |
(2) |
Define |
(3) |
Specify the SQL ID defined in (6) with |
(4) |
Specify |
(5) |
Define Mapper XML. Match the value of namespace with the FQCN of the interface. |
(6) |
Define SQL. |
(7) |
Define the method corresponding to the SQL ID defined in (6) for the interface. |
Database Access with ItemWriter
Explain database access with ItemWriter in here.
ItemWriter of MyBatis
MyBatis-Spring provides only one following ItemWriter.
-
org.mybatis.spring.batch.MyBatisBatchItemWriter
The basic setting is the same as ItemReader of MyBatis.
MyBatisBatchItemWriter
needs to specify batchModeSqlSessionTemplate
described in MyBatis Setting.
Show an example definition of MyBatisBatchItemWriter
below.
<!-- (1) -->
<mybatis:scan
base-package="org.terasoluna.batch.functionaltest.app.repository.plan"
factory-ref="jobSqlSessionFactory"/>
<!-- (2) (3) (4) -->
<bean id="detailWriter" class="org.mybatis.spring.batch.MyBatisBatchItemWriter"
p:statementId="org.terasoluna.batch.functionaltest.app.repository.plan.SalesPlanDetailRepository.create"
p:sqlSessionTemplate="batchModeSqlSessionTemplate"/>
<!-- omitted -->
<!-- (5) -->
<mapper namespace="org.terasoluna.batch.functionaltest.app.repository.plan.SalesPlanDetailRepository">
<!-- (6) -->
<insert id="create"
parameterType="org.terasoluna.batch.functionaltest.app.model.plan.SalesPlanDetail">
<![CDATA[
INSERT INTO
sales_plan_detail(branch_id, year, month, customer_id, amount)
VALUES (
#{branchId}, #{year}, #{month}, #{customerId}, #{amount}
)
]]>
</insert>
<!-- omitted -->
</mapper>
public interface SalesPlanDetailRepository {
// (7)
void create(SalesPlanDetail salesPlanDetail);
// omitted
}
Sr. No. | Description |
---|---|
(1) |
Register Mapper XML. |
(2) |
Define |
(3) |
Specify the SQL ID defined in (6) with |
(4) |
Specify |
(5) |
Define Mapper XML. Match the value of namespace with the FQCN of the interface. |
(6) |
Define SQL. |
(7) |
Define the method corresponding to the SQL ID defined in (6) for the interface. |
Database Access other than ItemReader・ItemWriter
Explain database access except for ItemReader・ItemWriter.
To access the database except for ItemReader・ItemWriter, use the Mapper interface. In using the Mapper interface, TERASOLUNA Batch 5.x has the following restrictions.
Process | ItemProcessor | Tasklet | Listner |
---|---|---|---|
Reference |
Available |
Available |
Available |
Update |
Conditionally available |
Available |
Unavailable |
- Restrictions in ItemProcessor
-
There is a restriction that it should not be executed with two or more
ExecutorType
within the same transaction in MyBatis.
If "useMyBatisBatchItemWriter
for ItemWriter" and "use ItemProcessor to update and reference the Mapper interface" are satisfied at the same time, it conflicts with this restriction.
To avoid this restriction, database is accessed by using Mapper interface thatExecutorType
isBATCH
in ItemProcessor.
In addition,MyBatisBatchItemWriter
checks whether it is SQL issued by itself with the status check after executing SQL but naturally it can not manage SQL execution by ItemProcessor and an error will occur.
Therefore, ifMyBatisBatchItemWriter
is used, updating with the Mapper interface will not be possible and only reference.
It can set to invalidate the error check of |
- Restrictions in Tasklet
-
In Tasklet, since it is basic to use the Mapper interface, there is no influence like ItemProcessor.
It is possible to useMyBatisBatchItemWriter
by Inject, but in that case Mapper interface itself can be processed withBATCH
setting. In other words, there is basically no need to useMyBatisBatchItemWriter
by Inject. - Restrictions in Listener
-
Even at the listener, the same restriction as that of ItemProcessor is established. In addition, for listeners, use cases requiring updates are difficult to think. Therefore, update processing is prohibited at the listner.
Replacement of update processing assumed by the listner
|
Database access with ItemProcessor
Show an example of database access with ItemProcessor.
@Component
public class UpdateItemFromDBProcessor implements
ItemProcessor<SalesPerformanceDetail, SalesPlanDetail> {
// (1)
@Inject
CustomerRepository customerRepository;
@Override
public SalesPlanDetail process(SalesPerformanceDetail readItem) throws Exception {
// (2)
Customer customer = customerRepository.findOne(readItem.getCustomerId());
// (3)
SalesPlanDetail writeItem = new SalesPlanDetail();
writeItem.setBranchId(customer.getChargeBranchId());
writeItem.setYear(readItem.getYear());
writeItem.setMonth(readItem.getMonth());
writeItem.setCustomerId(readItem.getCustomerId());
writeItem.setAmount(readItem.getAmount());
return writeItem;
}
}
<!-- (2) -->
<mybatis:scan
base-package="org.terasoluna.batch.functionaltest.app.repository"
template-ref="batchModeSqlSessionTemplate"/>
<bean id="reader" class="org.mybatis.spring.batch.MyBatisCursorItemReader"
p:queryId"org.terasoluna.batch.functionaltest.app.repository.performance.SalesPerformanceDetailRepository.findAll"
p:sqlSessionFactory-ref="jobSqlSessionFactory"/>
<!-- (3) -->
<bean id="writer" class="org.mybatis.spring.batch.MyBatisBatchItemWriter"
p:statementId="org.terasoluna.batch.functionaltest.app.repository.plan.SalesPlanDetailRepository.create"
p:sqlSessionTemplate-ref="batchModeSqlSessionTemplate"/>
<batch:job id="DBAccessByItemProcessor" job-repository="jobRepository">
<batch:step id="DBAccessByItemProcessor.step01">
<batch:tasklet transaction-manager="jobTransactionManager">
<!-- (4) -->
<batch:chunk reader="reader"
processor="updateItemFromDBProcessor"
writer="writer" commit-interval="10"/>
</batch:tasklet>
</batch:step>
</batch:job>
Mapper interface and Mapper XML are omitted.
Sr. No. |
Description |
(1) |
Inject Mapper interface. |
(2) |
Register Mapper XML. |
(3) |
Define |
(4) |
Set ItemProcessor that injected Mapper interface. |
Supplement of MyBatisCursorItemReader setting
Different
|
Database Access with Tasklet
Show an example of database access in Tasklet.
@Component
public class OptimisticLockTasklet implements Tasklet {
// (1)
@Inject
ExclusiveControlRepository repository;
// omitted
@Override
public RepeatStatus execute(StepContribution contribution,
ChunkContext chunkContext) throws Exception {
Branch branch = repository.branchFindOne(branchId); // (2)
ExclusiveBranch exclusiveBranch = new ExclusiveBranch();
exclusiveBranch.setBranchId(branch.getBranchId());
exclusiveBranch.setBranchName(branch.getBranchName() + " - " + identifier);
exclusiveBranch.setBranchAddress(branch.getBranchAddress() + " - " + identifier);
exclusiveBranch.setBranchTel(branch.getBranchTel());
exclusiveBranch.setCreateDate(branch.getUpdateDate());
exclusiveBranch.setUpdateDate(new Timestamp(System.currentTimeMillis()));
exclusiveBranch.setOldBranchName(branch.getBranchName());
int result = repository.branchExclusiveUpdate(exclusiveBranch); // (3)
return RepeatStatus.FINISHED;
}
}
<!-- (4) -->
<mybatis:scan
base-package="org.terasoluna.batch.functionaltest.ch05.exclusivecontrol.repository"
factory-ref="jobSqlSessionFactory"/>
<batch:job id="taskletOptimisticLockCheckJob" job-repository="jobRepository">
<batch:step id="taskletOptimisticLockCheckJob.step01">
<batch:tasklet transaction-manager="jobTransactionManager"
ref="optimisticLockTasklet"> <!-- (5) -->
</batch:tasklet>
</batch:step>
</batch:job>
Mapper interface and Mapper XML are omitted.
Sr. No. |
Description |
(1) |
Inject Mapper interface. |
(2) |
Execute the search process with the Mapper interface. |
(3) |
Execute the update process with the Mapper interface. |
(4) |
Register Mapper XML |
(5) |
Inject Mapper interface and set Tasklet. |
Use batchModeSqlSessionTemplate
If there are many updating processes with the tasklet model, set |
Database Access with Listener
Database access with listener is often linked with other components. Depending on the listener to be used and the implementation method, It is necessary to prepare additional mechanism to hand over to other components.
Show an example in which StepExecutionListener acquires data before step execution and uses the data acquired by ItemProcessor.
public class CacheSetListener extends StepExecutionListenerSupport {
// (1)
@Inject
CustomerRepository customerRepository;
// (2)
@Inject
CustomerCache cache;
@Override
public void beforeStep(StepExecution stepExecution) {
// (3)
customerRepository.findAll().forEach(customer ->
cache.addCustomer(customer.getCustomerId(), customer));
}
}
@Component
public class UpdateItemFromCacheProcessor implements
ItemProcessor<SalesPerformanceDetail, SalesPlanDetail> {
// (4)
@Inject
CustomerCache cache;
@Override
public SalesPlanDetail process(SalesPerformanceDetail readItem) throws Exception {
Customer customer = cache.getCustomer(readItem.getCustomerId()); // (5)
SalesPlanDetail writeItem = new SalesPlanDetail();
// omitted
writerItem.setCustomerName(customer.getCustomerName); // (6)
return writeItem;
}
}
// (7)
@Component
public class CustomerCache {
Map<String, Customer> customerMap = new HashMap<>();
public Customer getCustomer(String customerId) {
return customerMap.get(customerId);
}
public void addCustomer(String id, Customer customer) {
customerMap.put(id, customer);
}
}
<!-- omitted -->
<!-- (8) -->
<mybatis:scan
base-package="org.terasoluna.batch.functionaltest.app.repository"
template-ref="batchModeSqlSessionTemplate"/>
<!-- (9) -->
<bean id="cacheSetListener"
class="org.terasoluna.batch.functionaltest.ch05.dbaccess.CacheSetListener"/>
<!-- omitted -->
<batch:job id="DBAccessByItemListener" job-repository="jobRepository">
<batch:step id="DBAccessByItemListener.step01">
<batch:tasklet transaction-manager="jobTransactionManager">
<batch:chunk reader="reader"
processor="updateItemFromCacheProcessor"
writer="writer" commit-interval="10"/> <!-- (10) -->
<!-- (11) -->
<batch:listeners>
<batch:listener ref="cacheSetListener"/>
</batch:listeners>
</batch:tasklet>
</batch:step>
</batch:job>
Sr. No. |
Description |
(1) |
Inject Mapper interface. |
(2) |
Inject a bean for caching data acquired from the Mapper interface. |
(3) |
Get data from the Mapper interface and cache it at the listener. |
(4) |
Inject the same bean as the cache set in (2). |
(5) |
Get corresponding data from the cache. |
(6) |
Reflect the data from the cache in the update data. |
(7) |
Implement the cache class as a component. |
(8) |
Register Mapper XML |
(9) |
Define the listener that uses the Mapper interface. |
(10) |
Specify ItemProcessor that uses cache. |
(11) |
Register the listener defined in (9). |
Using SqlSessionFactory with the Listener
In the above example, For listeners that run outside the scope of chunks,
since it is processed outside the transaction, setting |