Introduction

In Previous article, we implemented the test class of Controller layer with SpringBoot + JUnit5. This time, I would like to summarize the implementation of the service layer and test class that perform DB operations.

However, since there is a little volume in preparing the application to be tested, I decided to divide the article into the first part and the second part. I will summarize it with the following configuration.

-[Part 1] Preparation of database MySQL -[Part 1] Creating Repository class using MyBatis [^ mybatis] -[Part 2] Preparation of test database H2 [^ h2] -[Part 2] Creating a test class using DBUnit [^ dbunit]

** ・・・ The first part does not go to the creation of the essential test class (sweat **

[^ mybatis]: So-called O / R mapper. The mapping between Java and SQL is simple and easy to understand, and dynamic SQL can be written. It is very convenient to be able to gently create Mapper, Model, etc. from TBL with MyBatisGenerator. (To be introduced in another article)

[^ h2]: Java database software. Lightweight, JDBC available, and compatible with major DBs, it has long been popular for lightweight apps, demos, and testing. I will use it for testing purposes this time as well.

[^ dbunit]: A unit test tool for classes that perform operations on DB such as DAO and Repository. It has all the functions necessary for testing involving DB, such as inputting pre-test data, post-verification, and post-test rollback.

Development environment

OS : macOS Catalina IDE : IntelliJ Ultimate Java : 11 Gradle : 6.6.1 SpringBoot : 2.3.4 MySQL : 8.0.17

1. Database preparation

First, prepare the DB to be operated by the application. We'll use MySQL as the main, and use H2 for JUnit testing.

By separating the DB, you don't have to worry about accidentally erasing or destroying data during the test, and the embedded DB H2 is convenient because you don't have to worry about external connections and it starts up quickly. (Especially perfect for testing in a CI / CD disguise environment)

In the first part, we will prepare a local environment using MySQL.

1-1. Preparation of MySQL

Using MySQL with Docker is easy and easy. (Round throw)

-Use mysql with docker

After connecting to MySQL as the root user above, create database and user for this application.

--Database demo_Create db
CREATE DATABASE demo_db;
--Username: demo, password:demo, permissions:demo_All permissions on db
CREATE USER 'demo'@'%' IDENTIFIED BY 'demo';
GRANT ALL PRIVILEGES ON demo_db.* TO 'demo'@'%';
FLUSH PRIVILEGES;

MySQL is now ready.

1-2. Creating a Spring Boot project

Spring Boot project is based on last time (https://qiita.com/kilvis/items/d75461b3596bfb0f6759#1-%E3%83%86%E3%82%B9%E3%83%88%E5%AF%BE % E8% B1% A1% E3% 81% AE% E3% 82% A2% E3% 83% 97% E3% 83% AA% E3% 82% B1% E3% 83% BC% E3% 82% B7% E3 Add MyBatis and JDBC (MySQL) libraries to% 83% A7% E3% 83% B3% E3% 81% AE% E6% BA% 96% E5% 82% 99).

`build.gradle`


dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.3' //add to
    compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.22' //add to
    compileOnly 'org.projectlombok:lombok'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
    testCompileOnly 'org.projectlombok:lombok'
    testAnnotationProcessor 'org.projectlombok:lombok'
}

1-3. Data source settings for the app

Describe the connection settings to MySQL in main / resources / application.yml for the local environment of the application. The connection settings for the user: demo and Database: demo_db created earlier are as follows.

`src/main/resources/application.yml`


spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/demo_db
    username: demo
    password: demo
mybatis:
  configuration:
    map-underscore-to-camel-case: true

map-underscore-to-camel-case: true will map the name of the snake case to camel case when mapping DB and Java objects.

This completes the connection settings for MySQL.

1-4. Creating a table

Create a simple sample table.

customer table specification

Column name	Type	Constraints etc.
id	int	Automatic numbering primary key
name	varchar(100)	not null constraint
age	int	not null constraint
address	varchar(200)

DDL

`schema.sql`


create table customer
(
    id int auto_increment,
    name varchar(100) not null,
    age int not null,
    address varchar(200) null,
    constraint customer_pk primary key (id)
);

DML In addition, I will also make the initial input data.

`data.sql`


insert into
    customer (name, age, address)
VALUES
    ('Luke Skywalker', 19, 'Tatooine'),
    ('Leia Organa', 19, 'Alderaan'),
    ('Han solo', 32, 'Corellia'),
    ('Darth Vader', 41, 'Tatooine');

Execute & commit the above DDL and DML with MySQL to set up the table and initial data.

Also, save the created DDL as schema.sql and DML as data.sql in test / resources. These files will be used in the second part of the JUnit test.

├── build.gradle
└── src
    ├── main
    └── test
        ├── java
        └── resources
            ├── application.yml
            ├── schema.sql   // DDL
            └── data.sql     // DML

2. Creating Repository using MyBatis

Create Repository class to perform DB operation. This time, MyBatis will be used for the O / R mapper, so follow the rules

--Creating a Model (Entity) class that maps to the result of the Select clause --Creating a Mapper that maps Model and CRUD SQL --Creation of Repository class using Mapper

I will make it in the order of.

2-1. Creating Model (Entity) class

Create a Model class that holds the data for one row of the customer table created earlier.

`Customer.java`


package com.example.dbunitdemo.domain.model;

import lombok.Builder;
import lombok.Data;

@Builder
@Data
public class Customer {
    private Long id;
    private String name;
    private Integer age;
    private String address;
}

It's simple because the accessor and toString () are created automatically thanks to @ lombok.Data.

2-2. Creating Mapper

Create a Mapper that connects the Customer class created earlier with the SQL of CRUD. Mapper is created as a set of two, Java interface and XML file that describes SQL.

CustomerMapper.java

`java:com.example.dbunitdemo.domain.mapper.CustomerMapper.java`


package com.example.dbunitdemo.domain.mapper;

import com.example.dbunitdemo.domain.model.Customer;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface CustomerMapper {
    List<Customer> findAll();
    Customer get(@Param("id") Long id);
    int insert(@Param("customer") Customer customer);
    int update(@Param("customer") Customer customer);
    int delete(@Param("id") Long id);
}

@Param specifies the name to refer to the argument in the XML SQL statement described below.

CustomerMappler.xml

`main/resources/com/example/dbunitdemo/domain/mapper/CustomerMapper.xml`


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.dbunitdemo.domain.mapper.CustomerMapper">
    <select id="findAll" resultType="com.example.dbunitdemo.domain.model.Customer">
        SELECT id, name, age, address FROM customer
    </select>
    <select id="get" resultType="com.example.dbunitdemo.domain.model.Customer">
        SELECT id, name, age, address FROM customer WHERE id = #{id}
    </select>
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO customer (name, age, address) VALUES (#{customer.name}, #{customer.age}, #{customer.address})
    </insert>
    <update id="update">
        UPDATE customer SET name = #{customer.name}, age = #{customer.age}, address = #{customer.address} WHERE id = #{customer.id}
    </update>
    <delete id="delete">
        DELETE FROM customer WHERE id = #{id}
    </delete>
</mapper>

Supplement: Notes on creating XML for Mapper

--Create the same directory hierarchy as the Java package hierarchy under main / resources and create a (Mapper name) .xml file. --Specify the FQCN of the Mapper interface in <mapper namespace =" ... ">. --Specify the method name of the Mapper interface in the id attribute of<insert>,<select>,<update>,<delete>, and write SQL in the content part ** Mapper Associate SQL with the method of. ** ** --For <select resultType =" ... ">, specify the FQCN of the Model class that maps the search results. --When registering with <insert> If you want to set the automatically numbered value to the property of the original Model, specify a combination of useGeneratedKeys =" true " and keyProperty =" id ".

AppConfig.java

Finally, specify the package to which the Mapper interface belongs with the annotation of the config class.

`AppConfig.java`


package com.example.dbunitdemo.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.example.dbunitdemo.domain.mapper") //Specify the package to which Mapper belongs
public class AppConfig {
   // ...abridgement
}

This completes the creation of CustomerMapper.

The directory structure after creating Mapper is as follows.

├── main
│   ├── java
│   │   └── com
│   │       └── example
│   │           └── dbunitdemo
│   │               ├── DbunitDemoApplication.java
│   │               ├── config
│   │               │   └── AppConfig.java
│   │               └── domain
│   │                   ├── mapper
│   │                   │   └── CustomerMapper.java
│   │                   └── model
│   │                       └── Customer.java
│   └── resources
│       ├── application.yml
│       ├── com
│       │   └── example
│       │       └── dbunitdemo
│       │           └── domain
│       │               └── mapper
│       │                   └── CustomerMapper.xml

2-3. Creating Repository class using Mapper

Use the simple DI Mapper from Repository.

`CustomerRepository.java`


package com.example.dbunitdemo.domain.repository;

import com.example.dbunitdemo.domain.mapper.CustomerMapper;
import com.example.dbunitdemo.domain.model.Customer;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@RequiredArgsConstructor
public class CustomerRepository {

  private final CustomerMapper customerMapper;

  public List<Customer> findAll() {
    return customerMapper.findAll();
  }

  public Customer get(Long id) {
    return customerMapper.get(id);
  }

  public int create(Customer customer) {
    return customerMapper.insert(customer);
  }

  public int update(Customer customer) {
    return customerMapper.update(customer);
  }

  public int delete(Long id) {
    return customerMapper.delete(id);
  }
}

It may seem like a meaningless class just to wrap Mapper, This is necessary to make Service ** independent of infrastructure and middleware **.

For example, if you use Mapper directly from Service without going through Repository, MyBatis-specific processing (for example, query construction using Example) in the logic of Service, or if you are not good at DB, use MySQL. There is a possibility that some processing that is conscious of something will be implemented.

Service should focus on transaction management of Repository and should not implement infrastructure or middleware dependent processing. Create a Repository to hide such parts.

3. Check the operation

After making it so far, I want to check the operation, so I will connect it to Repository-> Service-> Controller quickly. For the time being, I will implement it only in the findAll method to check the display of the initial data of the DB.

`CustomerService.java`


@Service
@RequiredArgsConstructor
public class CustomerService {
  private final CustomerRepository customerRepository;

  @Transactional(readOnly = true)
  public List<Customer> findAll() {
    return customerRepository.findAll();
  }
}

`CustomerController.java`



@RequiredArgsConstructor
@RestController
public class CustomerController {
    private final CustomerService customerService;

    @GetMapping("customers")
    public List<Customer> findAll() {
        return customerService.findAll();
    }
}

Once created so far, start the application with bootRun and try accessing http: // localhost: 8080 / customers from your browser.

The data that was input safely is displayed!

Continued to the second part ...

In the first part, I introduced the setting of MySQL, the implementation of Mapper of MyBatis and Repository that uses it. In the second part, we will introduce the implementation of unit tests using DBUnit (and JUnit5).

I wrote a CRUD test with SpringBoot + MyBatis + DBUnit (Part 1)