🌶️

SpringBoot MyBatisでMySQLにCRUDするRestAPIを作成してみた

2022/01/03に公開

フォルダ構成

フォルダ構成 抜粋
demo $ tree
.
├── gradlew
├── build.gradle
├── docker-compose.yml
├── db_data
├── my.cnf
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── example
    │   │           └── demo
    │   │               ├── DemoApplication.java
    │   │               └── component
    │   │                   └── user
    │   │                       ├── User.java
    │   │                       ├── UserController.java
    │   │                       ├── UserDao.java
    │   │                       └── UserService.java
    │   └── resources
    │       └── application.yml

db_dataのフォルダを作成し、mysqlのデータを永続化する

DB準備

docker-composeでmysql準備

mysql準備

docker-compose.yml
version: '3'

services:
  mysql:
    image: mysql:5.7
    command: mysqld
    ports:
    - 3306:3306
    environment:
      MYSQL_DATABASE: testdb
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_USER: user
      MYSQL_PASSWORD: userpass
      TZ: Asia/Tokyo
    volumes:
    - ./db_data:/var/lib/mysql
    - ./my.cnf:/etc/mysql/my.cnf
my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
lower_case_table_names=1
[client]
default-character-set=utf8mb4

details lower_case_table_names=1を設定する理由

mysql> create table User ( id int , name char(10) );
ERROR 1146 (42S02): Table 'testdb.user' doesn't exist

といったエラーが出た。
my.cnfに下記を設定することでこの状況を回避できた

lower_case_table_names=1

テーブル作成

docker-compose up -d
docker-compose ps

mysql -u user -puserpass testdb -h 127.0.0.1

create table  users(
  id int(11),
  name char(10)
);

create unique index user_idx on users(id);

desc users;

build.gradleにdependencies追加

build.gradle
plugins {
	id 'org.springframework.boot' version '2.6.2'
	id 'io.spring.dependency-management' version '1.0.11.RELEASE'
	id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '17'

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.0'
	implementation 'mysql:mysql-connector-java:8.0.27'
	compileOnly 'org.projectlombok:lombok'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

test {
	useJUnitPlatform()
}

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-versions.html
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing-maven.html
https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.27

mysql接続設定

application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/testdb
    username: user
    password: userpass
    driver-class-name: com.mysql.cj.jdbc.Driver
    sql-script-encoding: utf-8

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-driver-name.html

MyBatisでの接続

User.java
package com.example.demo.component.user;

import lombok.Data;

@Data
public class User {
  private Integer id;
  private String  name;
}

https://projectlombok.org/features/Data

UserDao.java
package com.example.demo.component.user;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface UserDao {

  @Select("select * from users where id=#{id}")
  User findById(User user);

  @Select("select * from users")
  List<User> findAll();

  @Insert("insert into users (id , name) values (#{id},#{name})")
  void insert(User user);

  @Update("update users set name = #{name} where id=#{id}")
  boolean update(User user);

  @Delete("delete from users where id=#{id}")
  boolean delete(User user);

}

@Mapperは@Autowiredと対になっている
@Mapperで定義しておくことで、@Autowiredでインジェクションできるようになる
xmlでSQLを設定するやり方もありが、そのやり方だと、パッケージのリファクタリングをした際に、vscodeで自動修正できず、手作業での修正が発生してしまう。

UserService.java
package com.example.demo.component.user;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

  private final UserDao userDao;

  @Autowired
  public UserService(UserDao userDao){
    this.userDao = userDao;
  }

  public User findById(User user){
    return this.userDao.findById(user);
  }

  public List<User> getUserList(){
    return this.userDao.findAll();
  }

  public User insert(User user){
    this.userDao.insert(user);
    return user;
  }

  public User update(User user){
    if(this.userDao.update(user)){
      return user;
    }else{
      return null;
    }
  }

  public User delete (User user){
    User delUser = findById(user);
    if(this.userDao.delete(user)){
      return delUser;
    }else{
      return null;
    }

  }
}

@Serviceは@Autowiredと対になっている
@Serviceで定義しておくことで、@Autowiredでインジェクションできるようになる

UserDaoをコンストラクトインジェクションにすることで、userDaoをfinalで定義している。
このため、不変性が担保できる

UserController
package com.example.demo.component.user;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

  private final UserService userService;

  @Autowired
  public UserController(UserService userService){
    this.userService = userService;
  }

  private static final Logger log =
  LoggerFactory.getLogger(UserController.class);

  @GetMapping("/user/{id}")
  public User getUser(@PathVariable Integer id ){
    log.info("getUser");
    User user = new User();
    user.setId(id);
    return userService.findById(user);
  }

  @GetMapping("/users")
  public List<User> getUserList(){
    log.info("getUserList");
    return userService.getUserList();
  }

  @PostMapping("/insert_user/{id}")
  public User insertUser(@PathVariable Integer id ,
                         @RequestParam String name){
    log.info("insertUser");
    User user = new User();
    user.setId(id);
    user.setName(name);
    return userService.insert(user);
  }

  @PutMapping("/update_user/{id}")
  public User updateUser(@PathVariable Integer id ,
                         @RequestParam String name){
    log.info("updateUser");
    User user = new User();
    user.setId(id);
    user.setName(name);
    return userService.update(user);
  }

  @DeleteMapping("/delete_user/{id}")
  public User deleteUser(@PathVariable Integer id){
    log.info("deleteUser");
    User user = new User();
    user.setId(id);
    return userService.delete(user);
  }

}

https://www.baeldung.com/spring-request-param

DemoApplication.java
package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}

}

run

demo $ ./gradlew bootRun

call

Chrome ARC
#GET
http://localhost:8080/users

#GET
http://localhost:8080/user/1

#POST
http://localhost:8080/insert_user/1
#application/x-www-form-urlencoded
name=insertname

#PUT
http://localhost:8080/update_user/1
#application/x-www-form-urlencoded
name=updatename

#DELETE
http://localhost:8080/delete_user/1

Discussion