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

MySQL, MyBatis를 연동한 후 게시글 목록을 조회할 수 있도록 Controller, Service, Mapper를 추가하세요.

MySQL에 관한 내용은 다음 글을 참고하세요.  

MySQL 다운로드 및 설치 - https://tychejin.tistory.com/15 

MySQL SCHEMA 및 TALBE 생성 - https://tychejin.tistory.com/16

 

1. pom.xml 의존성 추가

pom.xml 파일에 MyBatis, MySQL 및 @ConfigurationProperties을 사용하기 위해 의존성을 추가하세요.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.8</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.api</groupId>
    <artifactId>board</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>board</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-configuration-processor -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
         
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 
</project>

 

2. application.properties 수정

SpringBoot 2.x 부터 기본 jdbc로 hikari를 사용하고 있기 때문에 spirng.datasource.hikari.url아 아닌 spirng.datasource.hikari.jdbc-url로 한 후 dataSource 정보를 추가하세요.

application.properties

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

 

3. Board 클래스 추가

게시판 테이블(TB_BOARD)의 정보를 담기 위한 자바 객체를 com.api.board.domain 패키지를 생성한 후 Board 클래스를 추가하세요.

Board.java

package com.api.board.domain;
 
public class Board {
 
    int board_seq;
    int board_re_ref;
    int board_re_lev;
    int board_re_seq;
    String board_writer;
    String board_subject;
    String board_content;
    int board_hits;
    String del_yn;
    String ins_user_id;
    String ins_date;
    String upd_user_id;
    String upd_date;
 
    public int getBoard_seq() {
        return board_seq;
    }
 
    public void setBoard_seq(int board_seq) {
        this.board_seq = board_seq;
    }
 
    public int getBoard_re_ref() {
        return board_re_ref;
    }
 
    public void setBoard_re_ref(int board_re_ref) {
        this.board_re_ref = board_re_ref;
    }
 
    public int getBoard_re_lev() {
        return board_re_lev;
    }
 
    public void setBoard_re_lev(int board_re_lev) {
        this.board_re_lev = board_re_lev;
    }
 
    public int getBoard_re_seq() {
        return board_re_seq;
    }
 
    public void setBoard_re_seq(int board_re_seq) {
        this.board_re_seq = board_re_seq;
    }
 
    public String getBoard_writer() {
        return board_writer;
    }
 
    public void setBoard_writer(String board_writer) {
        this.board_writer = board_writer;
    }
 
    public String getBoard_subject() {
        return board_subject;
    }
 
    public void setBoard_subject(String board_subject) {
        this.board_subject = board_subject;
    }
 
    public String getBoard_content() {
        return board_content;
    }
 
    public void setBoard_content(String board_content) {
        this.board_content = board_content;
    }
 
    public int getBoard_hits() {
        return board_hits;
    }
 
    public void setBoard_hits(int board_hits) {
        this.board_hits = board_hits;
    }
 
    public String getDel_yn() {
        return del_yn;
    }
 
    public void setDel_yn(String del_yn) {
        this.del_yn = del_yn;
    }
 
    public String getIns_user_id() {
        return ins_user_id;
    }
 
    public void setIns_user_id(String ins_user_id) {
        this.ins_user_id = ins_user_id;
    }
 
    public String getIns_date() {
        return ins_date;
    }
 
    public void setIns_date(String ins_date) {
        this.ins_date = ins_date;
    }
 
    public String getUpd_user_id() {
        return upd_user_id;
    }
 
    public void setUpd_user_id(String upd_user_id) {
        this.upd_user_id = upd_user_id;
    }
 
    public String getUpd_date() {
        return upd_date;
    }
 
    public void setUpd_date(String upd_date) {
        this.upd_date = upd_date;
    }
}

 

4. BoardMapper.xml 추가

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

<mapper> 태그의 속성인 namespace의 값은 다음에 추가할 Mapper Interface의 패키지명+클래스명으로 지정해야 Mybatis에서 자동으로 Mapper XML과 Mapper Interface를 매핑할 수 있습니다.

BoardMapper.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.api.board.mapper.BoardMapper">
    
    <select id="getBoardList" resultType="com.api.board.domain.Board">
                                         
        SELECT T1.*
          FROM ( 
                    SELECT BOARD_SEQ 
                         , BOARD_RE_REF 
                         , BOARD_RE_LEV 
                         , BOARD_RE_SEQ 
                         , BOARD_WRITER 
                         , BOARD_SUBJECT 
                         , BOARD_CONTENT 
                         , BOARD_HITS
                         , DEL_YN 
                         , INS_USER_ID
                         , CAST( DATE_FORMAT( INS_DATE, '%Y-%m-%d %H:%i:%s' ) AS CHAR(19) ) AS INS_DATE
                         , UPD_USER_ID
                         , CAST( DATE_FORMAT( UPD_DATE, '%Y-%m-%d %H:%i:%s' ) AS CHAR(19) ) AS UPD_DATE  
                      FROM BOARD.TB_BOARD
                ) T1
         ORDER BY BOARD_RE_REF DESC, BOARD_RE_SEQ ASC                
         
    </select>
    
</mapper>

 

5. BoardMapper 인터페이스 추가

com.api.board.mapper 패키지를 생성한 후 BoardMapper 인터페이스를 추가하세요. BoardMapper XML의 id를 인터페이스 내의 메소드명으로 사용하면 메소드가 호출될 때 자동으로 해당 SQL 문이 실행됩니다.

BoardMapper.java

package com.api.board.mapper;
 
import java.util.List;
 
import com.api.board.domain.Board;
 
public interface BoardMapper {
    
    /** 게시글 목록 조회 */
    public List<Board> getBoardList() throws Exception;
}

 

6. BoardService 클래스 추가

com.api.board.service 패키지를 생성한 후 BoardService 클래스를 추가하세요.

BoardService.java

package com.api.board.service;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.api.board.domain.Board;
import com.api.board.mapper.BoardMapper;
 
@Service
public class BoardService {
 
    @Autowired
    private BoardMapper boardMapper;
 
    /** 게시글 목록 조회 */
    public List<Board> getBoardList() throws Exception {
        return boardMapper.getBoardList();
    }
}

 

7. BoardController 클래스 추가

com.api.board.controller 패키지에 BookController 클래스를 추가하세요.

BoardController.java

package com.api.board.controller;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import com.api.board.domain.Board;
import com.api.board.service.BoardService;
 
@RequestMapping(value = "/board")
@RestController
public class BoardController {
 
    @Autowired
    private BoardService boardService;
    
    @GetMapping
    public List<Board> getBoardList() throws Exception {
        return boardService.getBoardList();
    }
}

 

9. DataSourceConfig 클래스 추가 

com.api.board.config 패지키를 생성한 후 DataSourceConfig 클래스를 추가하세요.

DataSourceConfig 클래스는 application.properties에서 데이터베이스 접속 정보를 읽어 DataSource 설정과 SqlSessionFactory, SqlSessionTemplate 을 설정하는 역할을 합니다. application.properties에서 설정이 가능한 정보는 Spring Boot Reference Documentation를 참고하세요.

@ComponentScan

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

@MapperScane

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

@ConfigurationProperties

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

DataSourceConfig.java

package com.api.board.config;
 
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;
 
import com.zaxxer.hikari.HikariDataSource;
 
@ComponentScan(basePackages = "com.api.board.service")
@MapperScan(basePackages = "com.api.board.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
@Configuration
public class DataSourceConfig {
 
    @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);
    }
}

 

10. 디렉터리 구조

 

11. 결과 확인

http://localhost:8080/board 호출하여 게시글 목록이 응답되는지 확인하세요.

 

소스 코드는 Github Repository - https://github.com/tychejin1218/api-board_v1 (branch : section02) 를 참조하세요.
Github에서 프로젝트 가져오기 - https://tychejin.tistory.com/33