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

Kotlin으로 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을 사용하기 위해 의존성을 추가하세요.

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
  id("org.springframework.boot") version "2.7.4"
  id("io.spring.dependency-management") version "1.0.14.RELEASE"
  kotlin("jvm") version "1.6.21"
  kotlin("plugin.spring") version "1.6.21"
}

group = "com.example"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11

repositories {
  mavenCentral()
}

dependencies {
  implementation("org.springframework.boot:spring-boot-starter-web")
  implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
  implementation("org.jetbrains.kotlin:kotlin-reflect")
  implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
  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")
}

tasks.withType<KotlinCompile> {
  kotlinOptions {
    freeCompilerArgs = listOf("-Xjsr305=strict")
    jvmTarget = "11"
  }
}

tasks.withType<Test> {
  useJUnitPlatform()
}

 

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

package com.example.springbootrestapi.domain

import org.apache.ibatis.type.Alias

@Alias("TodoRequest")
data class TodoRequest(

  var id: Long? = null,
  var title: String = "",
  var description: String = "",
  var completed: Boolean = false
)

@Alias("TodoResponse")
data class TodoResponse(

  var id: Long? = null,
  var title: String = "",
  var description: String = "",
  var completed: Boolean = false
)

 

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

package com.example.springbootrestapi.mapper

import com.example.springbootrestapi.domain.TodoRequest
import com.example.springbootrestapi.domain.TodoResponse
import org.springframework.stereotype.Repository

@Repository
interface TodoMapper {

  /** To-Do 조회 */
  fun getTodos(todoRequest: TodoRequest): MutableList<TodoResponse>
}

 

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

• namespace는 TodoMapper 인터페이스의 패키지명 + 인터페이스명으로 작성
• id는 TodoMapper 인터페이스의 메소드명과 일치하도록 작성
• parameterType, resultType는 TodoMapper 인터페이스에 선언된 메소드의 파라미터, 리턴 타입과 동일하게 작성

<?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.TodoRequest"
    resultType="com.example.springbootrestapi.domain.TodoResponse">

    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.kt 추가
com.example.springbootrestapi.service 패키지를 생성한 후 TodoService 클래스를 추가하세요.

package com.example.springbootrestapi.service

import com.example.springbootrestapi.domain.TodoRequest
import com.example.springbootrestapi.domain.TodoResponse
import com.example.springbootrestapi.mapper.TodoMapper
import org.springframework.stereotype.Service

@Service
class TodoService(
  val todoMapper: TodoMapper
) {

  /** To-Do 조회  */
  fun getTodos(todoRequest: TodoRequest): MutableList<TodoResponse> {
    return todoMapper.getTodos(todoRequest)
  }
}

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

package com.example.springbootrestapi.web.controller

import com.example.springbootrestapi.domain.TodoRequest
import com.example.springbootrestapi.domain.TodoResponse
import com.example.springbootrestapi.service.TodoService
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
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

@RestController
class TodoController(
  val todoService: TodoService
) {

  /**
   * To-Do 조회
   */
  @PostMapping(
    value = ["/api/todos"],
    consumes = [MediaType.APPLICATION_JSON_VALUE],
    produces = [MediaType.APPLICATION_JSON_VALUE]
  )
  fun getTodos(
    request: HttpServletRequest,
    response: HttpServletResponse,
    @RequestBody todoRequest: TodoRequest
  ): MutableList<TodoResponse> {
    return todoService.getTodos(todoRequest)
  }
}

 

11. DataSourceConfig.kt 추가
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 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
import javax.sql.DataSource

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

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

  @Bean
  fun sqlSessionFactory(
    dataSource: DataSource,
    applicationContext: ApplicationContext
  ): SqlSessionFactory {
    val sessionFactory = SqlSessionFactoryBean()
    sessionFactory.setDataSource(dataSource)
    sessionFactory.setMapperLocations(
      *PathMatchingResourcePatternResolver()
        .getResources("classpath:mapper/*.xml")
    )
    return sessionFactory.`object`!!
  }

  @Bean
  fun sqlSession(
    sqlSessionFactory: SqlSessionFactory
  ): SqlSessionTemplate {
    return SqlSessionTemplate(sqlSessionFactory)
  }
}

 

12. 패키지 및 폴더 구조

 

13. To-Do 조회
/api/todos 호출 시 정상적으로 응답되는지 확인하세요.

 

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

 

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