Spring boot REST API + MySQL(오라클, mybatis xml 작성)

Web/Spring

Spring boot REST API + MySQL(오라클, mybatis xml 작성)

dev_sr 2020. 9. 20. 19:10

이분이 쓰신 글을 그대로 따라해봤다.

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

MySQL, MyBatis를 연동한 후 게시글 목록을 조회할 수 있도록 Controller, Service, Mapper를 추가하겠습니다. MySQL에 관한 내용은 다음 글을 참고하세요. MySQL 다운로드 및 설치 - https://tychejin.tistory...

tychejin.tistory.com

너무 어려워서 그대로 따라서 GET 까지만 구현함..

워크벤치 쿼리에서 이렇게 DB를 하나 만들고

SHOW DATABASES;

CREATE TABLE board.TB_BOARD(
BOARD_SEQ INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '게시글 번호',
BOARD_RE_REF INT COMMENT '글의 그룹 번호',
BOARD_RE_LEV INT COMMENT '답변 글의 깊이',
BOARD_RE_SEQ INT COMMENT '답변 글의 순서',
BOARD_WRITER VARCHAR(20) COMMENT '게시글의 작성자',
BOARD_SUBJECT VARCHAR(50) COMMENT '게시글의 제목',
BOARD_CONTENT VARCHAR(2000) COMMENT '게시글의 내용',
BOARD_HITS INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '게시글의 조회수',
DEL_YN VARCHAR(1) NOT NULL DEFAULT 'N' COMMENT '삭제유무',
INS_USER_ID VARCHAR(20) COMMENT '입력자ID',
INS_DATE DATETIME COMMENT '입력일시',
UPD_USER_ID VARCHAR(20) COMMENT '수정자ID',
UPD_DATE DATETIME COMMENT '수정일시',
PRIMARY KEY(BOARD_SEQ)
) COMMENT '게시판';

USE board;

DESC tb_board;

INSERT INTO board.tb_board (BOARD_RE_REF, BOARD_RE_LEV, BOARD_RE_SEQ, BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES (0, 0, 0, '게시글 작성자1', '게시글 제목1', '게시글 내용1', 'TEST01', NOW() );
INSERT INTO board.tb_board (BOARD_RE_REF, BOARD_RE_LEV, BOARD_RE_SEQ, BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES (0, 0, 0, '게시글 작성자2', '게시글 제목2', '게시글 내용2', 'TEST02', NOW() );
INSERT INTO board.tb_board (BOARD_RE_REF, BOARD_RE_LEV, BOARD_RE_SEQ, BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES (0, 0, 0, '게시글 작성자3', '게시글 제목3', '게시글 내용3', 'TEST03', NOW() );
INSERT INTO board.tb_board (BOARD_RE_REF, BOARD_RE_LEV, BOARD_RE_SEQ, BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES (0, 0, 0, '게시글 작성자4', '게시글 제목4', '게시글 내용4', 'TEST04', NOW() );
INSERT INTO board.tb_board (BOARD_RE_REF, BOARD_RE_LEV, BOARD_RE_SEQ, BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES (0, 0, 0, '게시글 작성자5', '게시글 제목5', '게시글 내용5', 'TEST05', NOW() );

SELECT * FROM tb_board;

pom.xml엔 sql과 mybatis 추가

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</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.3</version>
		</dependency>

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=root
spring.datasource.hikari.password=123456

매핑될 board클래스를 만들어줌

lombok을 쓰면 @getter, @setter로 자동 생성해줘서 이렇게 안써도 된다고 알았는데

그렇게 쓰면 매핑할 데이터가 없다고 오류가 생김

뭐가 문제일까..

이렇게 안만들고 싶었는데 ㅋㅋㅋ

package com.board.board.domain;

public class Board {

	int board_seq;
	int board_re_ref;
	int board_re_lev;
	int board_re_req;
	String board_writer;
	String board_subject;
	String board_content;
	int board_hits;
	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_req() {
		return board_re_req;
	}
	public void setBoard_re_req(int board_re_req) {
		this.board_re_req = board_re_req;
	}
	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_data() {
		return ins_data;
	}
	public void setIns_data(String ins_data) {
		this.ins_data = ins_data;
	}
	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;
	}
	String del_yn;
	String ins_user_id;
	String ins_data;
	String upd_user_id;
	String upd_date;
	
}

BoardMapper.xml을 만들어서 전글과 다르게 여기에 sql문을 써준다!

<?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.board.board.mapper.BoardMapper">
	<!-- 게시글 목록 조회 -->
	<select id="getBoardList"
		resultType="com.board.board.domain.Board">

		 SELECT *
        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
               ORDER BY BOARD_RE_REF DESC, BOARD_RE_SEQ ASC
              ) T1

	</select>
</mapper>

매핑 기능을 명시한 인터페이스를 만들어줌

package com.board.board.mapper;

import java.util.List;

import com.board.board.domain.Board;

public interface BoardMapper {

	public List<Board> getBoardList() throws Exception;
}

서비스 클래스를 만듦

package com.board.board.service;

import java.util.List;

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

import com.board.board.domain.Board;
import com.board.board.mapper.BoardMapper;

@Service //서비스 레이어, 비지니스 로직을 가진 클래스에 사용한다
public class BoardService {

	@Autowired //생성자나 세터 등을 사용하여 의존성 주입을 하려고 할 때, 해당 빈을 찾아서 주입해주는 annotation
	private BoardMapper boardMapper;
	
	public List<Board> getBoardList() throws Exception{
		return boardMapper.getBoardList();
	}
}

@Autowired -> 의존성 주입을 자동으로 해줌

객체의존성(DI), 의존성 주입에 관한 글

[Design Pattern] DI란 (Dependency Injection) - Heee's Development Blog

Step by step goes a long way.

gmlwjd9405.github.io

컨트롤러 추가

package com.board.board.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.board.board.domain.Board;
import com.board.board.service.BoardService;

@RequestMapping(value = "/board")
@Controller
public class BoardController {
 
    @Autowired
    private BoardService boardService;
 
    @RequestMapping(method = RequestMethod.GET)
    @ResponseBody
    public List<Board> getBoardList() throws Exception {
 
        List<Board> boardList = boardService.getBoardList();
 
        return boardList;
    }
}

DataSourceConfig추가

아직 설명을 봐도 제일 이해 안가는 부분..

그냥 설정을 한다고 알아들었다.

package com.board.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.board.board.service")
@MapperScan(basePackages = "com.board.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);
	}
}

잘 따라했더니 나오긴 함

sql+mybatis (xml) 부분은 더 찾아보고 다시 해봐야할 것 같다.

찾는 글마다 코드가 다 다르니 이게 맞나 싶기도 하고 ㅋㅋ

많이 찾아보고 많이 따라해봐야겠다

일단 jpa를 따라해보고 프론트 공부하면서 간간이 다시 해봐야겠다.

'Web > Spring' 카테고리의 다른 글

sts4 jdk 적용하기 (explorer 날라가는 에러) (0)	2020.09.30
Spring boot REST API, H2 DB(sql), JPA (1) + Entity, Lombok 생성자 에러 (0)	2020.09.21
Spring boot REST API + MySQL(오라클, mybatis) (0)	2020.09.18
Spring boot - RESTful API(2) (0)	2020.09.17
Spring boot - RESTful API(1) (0)	2020.09.16

현재글Spring boot REST API + MySQL(오라클, mybatis xml 작성)

STUDY