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

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 **. postman1.png

You can confirm that the value was obtained safely! postman2.png

** 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!

Recommended Posts

02. I made an API to connect to MySQL (MyBatis) from Spring Boot
03. I sent a request from Spring Boot to the zip code search API
To connect from Spring to MySQL on virtual server (unsolved)
I want to connect to Heroku MySQL from a client
Upgrade spring boot from 1.5 series to 2.0 series
Change Spring Boot REST API request / response from CamelCase to SankeCase
I made a simple search form with Spring Boot + GitHub Search API.
What I did in the migration from Spring Boot 1.4 series to 2.0 series
What I did in the migration from Spring Boot 1.5 series to 2.0 series
Story when moving from Spring Boot 1.5 to 2.1
Connect from Java to MySQL using Eclipse
Changes when migrating from Spring Boot 1.5 to Spring Boot 2.0
Changes when migrating from Spring Boot 2.0 to Spring Boot 2.2
01. I tried to build an environment with SpringBoot + IntelliJ + MySQL (MyBatis) (Windows10)
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
Connect to Aurora (MySQL) from a Java application
I made an API client for Nature Remo
An introduction to Spring Boot + in-memory data grid
I wanted to gradle spring boot with multi-project
◆ Get API created by Spring Boot from React
From creating a Spring Boot project to running an application with VS Code
Settings for connecting to MySQL with Spring Boot + Spring JDBC
From building an AWS cloud environment to deploying a Spring Boot app (for beginners)
Automatically map DTOs to entities with Spring Boot API
[Java] Connect to MySQL
05. I tried to stub the source of Spring Boot
I started MySQL 5.7 with docker-compose and tried to connect
I tried to reduce the capacity of Spring Boot
I created an api domain with Spring Framework. Part 1
Introduce swagger-ui to REST API implemented in Spring Boot
Load an external jar from a Spring Boot fat jar
I made a function to register images with API in Spring Framework. Part 1 (API edition)
I tried to build an API server with Go (Echo) x MySQL x Docker x Clean Architecture
I made a function to register images with API in Spring Framework. Part 2 (Client Edition)
The story of raising Spring Boot from 1.5 series to 2.1 series part2
I tried to make an application in 3 months from inexperienced
I tried to make an introduction to PHP + MySQL with Docker
Steps required to issue an asynchronous event for Spring Boot
What I fixed when updating to Spring Boot 1.5.12 ・ What I was addicted to
I want to push an app made with Rails 6 to GitHub
How to call and use API in Java (Spring Boot)
Connect to database with spring boot + spring jpa and CRUD operation
I tried to get started with Swagger using Spring Boot
I made a simple MVC sample system using Spring Boot
Moved from iBATIS to MyBatis3
Connect from Java to PostgreSQL
Introduction to Spring Boot ① ~ DI ~
Introduction to Spring Boot ② ~ AOP ~
Connect to MySQL 8 with Java
[Spring Boot] Send an email
Introduction to Spring Boot Part 1
[Android] Connect to MySQL (unfinished)
Since I switched from Spring Boot (Java) to Ruby on Rails, I summarized my favorite points of Rails.
I tried to create an API to get data from a spreadsheet in Ruby (with service account)
Implement a simple Web REST API server with Spring Boot + MySQL
I can't log in to MySQL from Django when using docker-compose
Introduction to Spring Boot x OpenAPI ~ OpenAPI made with Generation gap pattern ~
I implemented an OAuth client with Spring Boot / Security (LINE login)
I tried connecting to MySQL using JDBC Template with Spring MVC
I want to INSERT Spring Local Time with MySQL Time (also milliseconds)
I want to control the default error message of Spring Boot