[Spring Boot] REST API 만들기(2) - MySQL + MyBatis 연동

REST API 만들기(2) - MySQL + MyBatis 연동

1. MySQL 설치

오픈 소스의 관계형 데이터베이스 관리 시스템(RDBMS)인 MySQL을 설치하세요.

MySQL을 다운로드하여 설치하는 방법과 Docker로 MySQL을 설치를 방법에 대한 내용이니 참고하세요.

• MySQL 다운로드 및 설치 - https://tychejin.tistory.com/15 
• Docker로 MySQL 설치(docker-compose.yml) - https://tychejin.tistory.com/393

 

2. Database 생성, 계정 생성 및 권한 부여

# Database 생성
# create database [database명];
create database sample;

# 계정 생성
# create user ['user명']@['server명'] identified by ['패스워드'];
create user 'sample'@'%' identified by 'password1!';

# 권한 확인
# show grants for ['user명']@['server명'];
show grants for 'sample'@'%';

# 권한 설정
# grant all privileges on [database명].[table명] to ['user명']@['server명'];
grant all privileges on sample.* to 'sample'@'%';

# 캐시를 지우고 새로운 설정을 적용
flush privileges;

# 계정 삭제
# drop user ['user명']@['server명'];
drop user 'sample'@'%'

 

3. 테이블 생성 및 데이터 추가
3_1. 테이블 생성

CREATE TABLE TODO (
	ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	TITLE VARCHAR(255),
	DESCRIPTION VARCHAR(255),
	COMPLETED BOOLEAN
);

3_2. 데이터 추가

INSERT
  INTO TODO (TITLE, DESCRIPTION, COMPLETED)
VALUES ('Title Test 01', 'Description Test 01', false),
       ('Title Test 02', 'Description Test 02', true),
       ('Title Test 03', 'Description Test 03', false),
       ('Title Test 04', 'Description Test 04', true),
       ('Title Test 05', 'Description Test 05', false);

 

4. build.gradle 의존성 추가
build.gradle에 @ConfigurationProperties, MySQL Connector, MyBatis, Lombok을 사용하기 위해 의존성을 추가하세요.

plugins {
    id 'org.springframework.boot' version '2.7.3'
    id 'io.spring.dependency-management' version '1.0.13.RELEASE'
    id 'java'
}

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

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'

    // https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-configuration-processor
    implementation 'org.springframework.boot:spring-boot-configuration-processor:2.7.3'

    // https://mvnrepository.com/artifact/mysql/mysql-connector-java
    implementation 'mysql:mysql-connector-java:8.0.30'

    // https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.2'

    // https://mvnrepository.com/artifact/org.projectlombok/lombok
    compileOnly 'org.projectlombok:lombok:1.18.24'
    testCompileOnly 'org.projectlombok:lombok:1.18.24'
}

tasks.named('test') {
    useJUnitPlatform()
}

5. application.properties 수정
application.properties를 application.yml로 변경한 후 MySQL JDBC Driver 및 접속 정보를 추가하세요.

# server 설정
server:
  port: 8081

---
# datasource 설정
spring:
  datasource:
    hikari:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/sample?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC
      username: sample
      password: password1!

 

6. Todo.java 추가
com.example.springbootrestapi.domain 패키지를 생성한 후 Todo 클래스를 추가하세요.

package com.example.springbootrestapi.domain;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import org.apache.ibatis.type.Alias;

public class Todo {

  @Alias("TodoRequest")
  @Getter
  @Setter
  @Builder
  @AllArgsConstructor
  @NoArgsConstructor
  @ToString
  public static class Request {

    private Integer id;
    private String title;
    private String description;
    private Boolean completed;
  }

  @Alias("TodoResponse")
  @Getter
  @Setter
  @Builder
  @AllArgsConstructor
  @NoArgsConstructor
  @ToString
  public static class Response {

    private Integer id;
    private String title;
    private String description;
    private Boolean completed;
  }
}

 

7. TodoMapper.java 추가
com.example.springbootrestapi.mapper 패키지를 생성한 후 TodoMapper 인터페이스를 추가하세요. 

package com.example.springbootrestapi.mapper;

import com.example.springbootrestapi.domain.Todo;
import java.util.List;

public interface TodoMapper {

  /** To-Do 조회 */
  List<Todo.Response> getTodos(Todo.Request todoRequest);
}

 

8. TodoMapper.xml 추가
데이터베이스에서 사용하는 SQL을 정의하기 위해 src/main/resource에 mapper/TodoMapper.xml 추가하세요.

• namespace는 TodoMapper 인터페이스의 패키지명 + 인터페이스명으로 작성
• id는 TodoMapper 인터페이스의 메소드명과 일치하도록 작성
• parameterType, resultType는 TodoMapper 인터페이스에 선언된 메소드의 파라미터, 리턴 타입과 동일하게 작성
• MyBatis에서 Inner Class에 접근하려면, 마침표(.) 대신에 달러 표시($)를 사용

<?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.springbootrestapi.mapper.TodoMapper">

  <select id="getTodos"
    parameterType="com.example.springbootrestapi.domain.Todo$Request"
    resultType="com.example.springbootrestapi.domain.Todo$Response">

    SELECT ID
         , TITLE
         , DESCRIPTION
         , COMPLETED
      FROM TODO
    <where>
      <if test="title != null and title != ''">
       AND TITLE LIKE CONCAT('%', #{title}, '%')
     </if>
     <if test="description != null and description != ''">
       AND DESCRIPTION LIKE CONCAT('%', #{description}, '%')
     </if>
     <if test="completed != null">
       AND COMPLETED = #{completed}
     </if>
    </where>

  </select>

</mapper>

 

9. TodoService.java 추가
com.example.springbootrestapi.service 패키지를 생성한 후 TodoService 클래스를 추가하세요.

package com.example.springbootrestapi.service;

import com.example.springbootrestapi.domain.Todo;
import com.example.springbootrestapi.mapper.TodoMapper;
import java.util.List;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

@RequiredArgsConstructor
@Service
public class TodoService {

  private final TodoMapper todoMapper;

  /** To-Do 조회 */
  public List<Todo.Response> getTodos(Todo.Request todoRequest) {
    return todoMapper.getTodos(todoRequest);
  }
}

10. TodoController.java 추가
com.example.springbootrestapi.web.controller 패키지를 생성한 후 TodoController 클래스를 추가하세요.

package com.example.springbootrestapi.web.controller;

import com.example.springbootrestapi.domain.Todo;
import com.example.springbootrestapi.service.TodoService;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RequiredArgsConstructor
@RestController
public class TodoController {

  private final TodoService todoService;

  /**
   * To-Do 조회
   */
  @PostMapping(
      value = "/api/todos",
      consumes = MediaType.APPLICATION_JSON_VALUE,
      produces = MediaType.APPLICATION_JSON_VALUE)
  public List<Todo.Response> getTodos(
      HttpServletRequest request,
      HttpServletResponse response,
      @RequestBody Todo.Request todoRequest) {
    return todoService.getTodos(todoRequest);
  }
}

 

11. DataSourceConfig.java 추가
com.example.springbootrestapi.config 패지키를 생성한 후 DataSourceConfiguration 클래스를 추가하세요.
DataSourceConfiguration 클래스는 application.properties에서 데이터베이스 접속 정보를 읽어 DataSource 설정과 SqlSessionFactory, SqlSessionTemplate 을 설정하는 역할을 합니다. 

@ComponentScan
각각의 Service를 Bean 형태로 선언해서 등록할 수 있지만, @ComponentScan 어노테이션을 이용해서 스캔하도록 선언하세요.

@MapperScane
Mapper를 Bean 형태로 선언해서 각각 등록할 수 있지만, 개수가 많으면 번거로울 수 있기 때문에 @MapperScan 어노테이션을 사용하여 대상 패키지에 있는 Mapper를 자동으로 스캔할 수 있도록 설정값을 Mapper 인터페이스가 위치한 패키지명(com.example.springbootrestapi.mapper)로 선언하세요.

@ConfigurationProperties
prefix를 spring.datasource.hikari로 지정하였으므로, DataSource 생성 시 application.yml 파일에서 spring.datasource.hikari로 시작하는 설정 정보를 사용합니다.

package com.example.springbootrestapi.config;

import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@ComponentScan(basePackages = "com.example.springbootrestapi.service")
@MapperScan(
    basePackages = "com.example.springbootrestapi.mapper",
    sqlSessionFactoryRef = "sqlSessionFactory"
)
@Configuration
public class DataSourceConfiguration {

  @Bean
  @ConfigurationProperties(prefix = "spring.datasource.hikari")
  public DataSource dataSource() {
    return DataSourceBuilder.create()
        .type(HikariDataSource.class)
        .build();
  }

  @Bean
  public SqlSessionFactory sqlSessionFactory(
      DataSource dataSource,
      ApplicationContext applicationContext
  ) throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(dataSource);
    sessionFactory.setMapperLocations(
        new PathMatchingResourcePatternResolver()
            .getResources("classpath:mapper/*.xml"));
    return sessionFactory.getObject();
  }

  @Bean
  public SqlSessionTemplate sqlSession(
      SqlSessionFactory sqlSessionFactory
  ) throws Exception {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

 

12. 패키지 및 폴더 구조

 

13. 결과 확인
/api/todos 호출 시 정상적으로 응답되는지 확인하세요.

 

소스 코드는 Github Repository - https://github.com/tychejin1218/springboot-rest-api (branch : section02) 를 참조하세요.

 

GitHub에서 프로젝트 복사하기(Get from Version Control) - https://tychejin.tistory.com/325