Overview

As the title suggests, I will try to connect to MySQL from a Java application created with Spring Boot. Use MyBatis for the O / R mapper. * What is MyBatis

Since it corresponds to the continuation of I tried to build the environment Please refer to that as well for the prerequisite environment.

If you cannot connect, please feel free to comment.

Main subject

Let's get into the main subject from here. This time, we will simply create an API ** that will get all the data in the DB in a list when you send a ** GET request. Click here for table names and test data (https://qiita.com/supreme0110/items/896cfa0349be5eacfed5#4-1-db%E3%81%AB%E3%83%86%E3%83%BC%E3% It is assumed that 83% 96% E3% 83% AB% E3% 82% 92% E4% BD% 9C% E3% 82% 8B) is included.

1. Get ready to create an API

1-1. Add a dependency

Since the dependency is insufficient only by the previous environment construction, let's add the dependency. We will use the convenient Spring Initializer. ** Add Web and Lombok to MySQL, MyBatis ** and click ** Generate Project **. * What is Lombok イニシャライザ.png

Copy the contents of build.gradle from the downloaded zip to build.gradle of the project. Just in case, ** Refresh **. 依存追加.png

I was able to capture the addiction safely.

1-2. Set up the IntelliJ plugin

Next, play with the plugin on the IntelliJ side. Open Settings with ** Ctrl + Alt + S ** and click ** Browse repositories ... ** at the bottom of ** Plugins **. プラグイン設定.png

If you search for lombok, you will find ** Lombok Plugin **, so click ** Install ** to install it. プラグイン設定2.png

If you can install it, the word ** Install ** will replace ** Restart IntelliJ IDEA **, so click it to restart. It's OK when it is restarted!

You're ready to go! Let's get into programming!

2. Create an API

This time, let's make it insanely simple, with a ** controller and mapper only ** configuration. (Although there are data classes etc.) Please note that we will not explain the source in depth.

2-1. Make around the Controller

`java:com.example.sampleapi.controller.SampleController.java`


@RestController
@RequiredArgsConstructor
public class SampleController {
    private final UsersMapper usersMapper;  // (1)

    @GetMapping("/get/sample")
    public List<SampleResponse> getSample() {
        List<UsersEntity> usersEntityList = usersMapper.findUserList();  // (2)
        return usersEntityList.stream().map(SampleResponse::create).collect(Collectors.toList());  // (3)
    }
}

`java:com.example.sampleapi.controller.resource.SampleResponse.java`


@Value  // (4)
public class SampleResponse {
    private Integer userId;
    private String userName;

    public static SampleResponse create(UsersEntity entity) {  // (5)
        return new SampleResponse(
                entity.getUserId(),
                entity.getUserName()
        );
    }
}

A brief explanation. (1). @RequiredArgsConstructor automatically generates a constructor that assigns the initial value to the field with final. In other words, it is ** constructor injection **. (2). Call ʻUsersMapperand receive the data obtained from DB as a list of entities. (3). The process of refilling the list received in (2) into the list of theSampleResponseclass is being performed. (4). Lombok's@Value` automatically generates a constructor and getter that initializes all fields. (5). In order to simplify the process description of (3), the process of refilling from entity to response is cut out.

Next, let's create a class and interface related to DB connection.

2-2. Make around Mapper

`java:com.example.sampleapi.mapper.UsersMapper.java`


@Mapper  // (1)
@Component
public interface UsersMapper {
    List<UsersEntity> findUserList(); // (2)
}

`java:com.example.sampleapi.mapper.entity.UsersEntity.java`


@Data  // (3)
public class UsersEntity {
    private Integer userId;
    private String userName;
}

Another brief explanation. (1). Add @Mapper which is an annotation of MyBatis. This recognizes that this class is a mapper associated with XML. (2). Declare the method you want to implement. ** The method name is mapped to the XML id attribute described below and the corresponding SQL is executed **. (3). This is the @Data annotation provided by Lombok. As a result, setters and getters are automatically generated for all fields.

Try it so far ** "Is the column name of the table a snake case, but the field of the entity can be mapped in camel case?" ** I thought you. There are a few more settings, so please proceed as it is. Finally, create an XML file, put simple settings in application.yml, and you're done.

2-3. XML creation and setting by application.yml

`xml:resources.mapper.Users.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.sampleapi.mapper.UsersMapper">  <!-- (1) -->
    <select id="findUserList" resultType="com.example.sampleapi.mapper.entity.UsersEntity">  <!-- (2) -->
        SELECT
            USER_ID,
            USER_NAME
        FROM
            USERS
    </select>
</mapper>

`yml:resources.application.yml`


spring:  #This is not relevant this time as it only defines the DB connection information
  datasource:
    url: jdbc:mysql://localhost/sample_db
    username: root
    password: dummy

mybatis:
  mapperLocations: classpath*:/mapper/*.xml  # (3)
  configuration:
    mapUnderscoreToCamelCase: true  # (4)

The last brief explanation. (1). Specify the path of the class to which @Mapper is given in the namespace attribute. This makes you aware of which Java interface this XML file is mapped to. (2). In order to map with ʻUsersMapper.findUserList ()mentioned above, describefindUserList in the ʻid attribute. Also, since it is necessary to specify the return value, write the path of the ʻUsersEntity class in the resultTypeattribute. (3). If you want to configure the Mapper interface and XML file to exist in different packages like this time, you can specifymapperLocationsto read XML correctly. (4). This is a convenient function that automatically maps the column name of the snake case mentioned above and the field of the entity written in the camel case **. The default isfalse, so let's set it to true`.

There are many other useful settings. Please refer to MyBatis Official.

You have created it! !! After that, let's actually send a request to the API and see if we can get the value from the DB! !!

3. Make an actual request

Use the Postman REST Client (https://chrome.google.com/webstore/detail/tabbed-postman-rest-clien/coohjcphdfgbiolnekdpbcijmhambjff/related) to submit your request. Of course, you can use curl or other tools.

Now let's start the API. Select the Gradle task ** bootRun ** from IntelliJ to launch it.

Specify the URL (http: // localhost: 8080 / get / sample), confirm that it is a GET method, and click ** Send **.

You can confirm that the value was obtained safely!

** Now you can operate the DB at any time! !! ** **

Finally

** I get the impression that "If you have an environment, you can easily make it!" **.

** MyBatis personally likes the fact that SQL can be written and the mapping with java is easy to understand **. It seems that you dislike the place where you have to write XML and the place where there is no automatic generation function like JPA. ..

As with anything, I think it's easy once you get used to it!

02. I made an API to connect to MySQL (MyBatis) from Spring Boot