Periodically update DB with Spring Batch and MyBatis

I wrote the code using Spring Batch, which is one of the popular Spring Framework functions in Java, at work, so I will implement it at home for both introduction and review. The final code can be found on GitHub.

environment

• Java10
• CentOS7
• MySQL5.7

What to make

I can't put the code I wrote in business, so I'll make a substitute. I decided to make a batch that corrects the age data of a certain personal table to the correct age by looking at the date of birth.

data

First, prepare the data. Start CentOS 7 with Vagrant and install MySQL there. Create a DB called batch and prepare the following table. This time, it would be nice if I had my age and date of birth, but I'm lonely with that, so I prepared a name. By the way, the batch is supposed to be executed daily.

mysql> use batch;
Database changed
mysql> desc person;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30) | NO   |     | NULL    |                |
| age      | int(3)      | NO   |     | NULL    |                |
| birthday | date        | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

Enter data for about 4 people. Today is August 29th, so I will enter birthday data that differs only in the Christian era. I will prepare one on another birthday so that I can see if it has been updated.

mysql> select * from person;
+----+-------+-----+------------+
| id | name  | age | birthday   |
+----+-------+-----+------------+
|  1 | Alice |  23 | 1995-08-29 |
|  2 | Bob   |  38 | 1980-08-29 |
|  3 | Carol |  29 | 1989-08-29 |
|  4 | Dave  |  23 | 1995-08-30 |
+----+-------+-----+------------+

Java component

When using Spring Framework, it is quick to download the template from Spring Initializr, so use this. Since Gradle is used for build, select Gradle Project and enter it appropriately. Dependencies

• Spring Batch
• Lombok
• MyBatis Framework
• MySQL Driver

Select the area appropriately, download it with "Generate the project", and unzip it. We will add the necessary files to this. The architecture of Spring Batch is described in detail in here, so I think you should read it. ..

In SpringBatch, the batch execution unit is defined as JOB and the processing unit is defined as STEP. In addition, two concepts, ** tasklet model ** and ** chunk model **, are provided as templates for the STEP processing flow. Originally, I think that tasklet is enough if you just refer to and update a single table, but this time I will dare to adopt and implement a chunk model.

The chunk model needs to be implemented by dividing it into three flows of "read → process → write", and interfaces are prepared for each.

Reading and writing data

To read and write data, connect to the DB. Since the implementation class of each interface is provided by the MyBatis library, we will use that. Read (ItemReader) extracts personal records whose birthday is the same as the batch execution date. So I will issue the following SQL. The date of the day (today) will be passed from the app.

python

SELECT
    id,
    name,
    age,
    birthday
FROM
    person
WHERE
    Date_format(birthday, '%m%d') = Date_format(#{today}, '%m%d')

Writing (ItemWriter) only updates the age.

python

update
    person
set
    age = #{age}
where
    id = #{id}

Data processing

In data processing (ItemProcessor), it is the responsibility to correct the age of the extracted personal data object correctly. That said, all you have to do is calculate the difference between this year and the year of birth and create an update object.

CorrectAgeProcessor.java

//import omitted
@Component
@Slf4j
public class CorrectAgeProcessor implements ItemProcessor<Person, Person> {
    @Override
    public Person process(Person person) throws Exception {
        log.info("Correct {}.", person.getName());
        return new Person(
                person.getId(),
                person.getName(),
                LocalDate.now().getYear() - person.getBirthday().getYear(),
                person.getBirthday());
    }
}

Job configuration definition

Define the job configuration. This time, one step is enough, so prepare one bean called step. Spring Batch seems to execute transaction processing by default, and commits at the number interval set by chunk (n). The purpose is to reduce the overhead at the time of commit by committing to some extent together. This time, I will commit one by one for the time being.

BatchConfiguration.java

//import omitted
@Configuration
@EnableBatchProcessing
@RequiredArgsConstructor
public class BatchConfiguration {

    public final JobBuilderFactory jobBuilderFactory;

    public final StepBuilderFactory stepBuilderFactory;

    private final SqlSessionFactory sqlSessionFactory;

    private final CorrectAgeProcessor correctAgeProcessor;

    @Bean
    public MyBatisCursorItemReader<Person> reader() {
        Map<String, Object> params = new HashMap<>();
        params.put("today", LocalDate.now());
        return new MyBatisCursorItemReaderBuilder<Person>()
                .sqlSessionFactory(sqlSessionFactory)
                .queryId("com.github.hysrabbit.agecorrector.mybatis.mapper.PersonMapper.findByBirthday")
                .parameterValues(params)
                .build();
    }

    @Bean
    public MyBatisBatchItemWriter<Person> writer() {
        return new MyBatisBatchItemWriterBuilder<Person>()
                .sqlSessionFactory(sqlSessionFactory)
                .statementId("com.github.hysrabbit.agecorrector.mybatis.mapper.PersonMapper.save")
                .build();
    }

    @Bean
    public Job correctAge(JobListener jobListener, Step step) {
        return jobBuilderFactory.get("correctAge")
                .incrementer(new RunIdIncrementer())
                .listener(jobListener)
                .flow(step)
                .end()
                .build();
    }

    @Bean
    public Step step(ItemReader<Person> reader, ItemWriter<Person> writer) {
        return stepBuilderFactory.get("step")
                .<Person, Person> chunk(1)
                .reader(reader)
                .processor(correctAgeProcessor)
                .writer(writer)
                .build();
    }

}

I've implemented everything else and pushed the final code to here. I haven't implemented the test code, so I'll try to implement it soon.

Execution is performed on the VM on which MySQL is installed. Before execution, define the DB setting information in the environment variable.

$ export SPRING_DATASOURCE_URL=jdbc:mysql://<hostname>:<port>/<database>;
$ export SPRING_DATASOURCE_USERNAME=<username>;
$ export SPRING_DATASOURCE_PASSWORD=<password>;

Then build with Gradle and run the resulting Jar file. The name of the person who updated it is output in the inserted logger.

$ ./gradlew clean build
.
.
.
$ java -jar build/libs/agecorrector.jar

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.7.RELEASE)
.
.
.
2019-08-29 01:50:29.334  INFO 2781 --- [           main] c.g.h.agecorrector.batch.JobListener     : Start job.
2019-08-29 01:50:29.391  INFO 2781 --- [           main] o.s.batch.core.job.SimpleStepHandler     : Executing step: [step]
2019-08-29 01:50:29.565  INFO 2781 --- [           main] c.g.h.a.batch.CorrectAgeProcessor        : Correct Alice.
2019-08-29 01:50:29.609  INFO 2781 --- [           main] c.g.h.a.batch.CorrectAgeProcessor        : Correct Bob.
2019-08-29 01:50:29.624  INFO 2781 --- [           main] c.g.h.a.batch.CorrectAgeProcessor        : Correct Carol.
2019-08-29 01:50:29.651  INFO 2781 --- [           main] c.g.h.agecorrector.batch.JobListener     : Completed job.
.
.
.

Let's check the data in MySQL as well. The day I ran it was 8/29, but you can update the age of the person born on that day.

mysql> select * from person;
+----+-------+-----+------------+
| id | name  | age | birthday   |
+----+-------+-----+------------+
|  1 | Alice |  24 | 1995-08-29 |
|  2 | Bob   |  39 | 1980-08-29 |
|  3 | Carol |  30 | 1989-08-29 |
|  4 | Dave  |  23 | 1995-08-30 |
+----+-------+-----+------------+

Summary

After that, if you register with Cron etc., you can update your age regularly. I think the combination of Spring Batch and MyBatis is a framework that is easy for Java programmers to understand and handle. Also, there are many useful functions that are not implemented this time in Spring Batch, so please use them by all means.

reference

• https://spring.io/guides/gs/batch-processing/
• https://terasoluna-batch.github.io/guideline/current/ja/
• https://blog.mybatis.org/