REST를 이용한 간단한 게시판(2) - DB설계&DB연결

1. MySQL 스키마 작성

 

-- 스키마 조회
SHOW DATABASES;
 
-- 스키마 생성
CREATE DATABASE board; -- CREATE SCHEMA board; 
 
-- RA_BOARD 테이블 삭제
DROP TABLE RA_board;
 
-- RA_BOARD 테이블 생성 
CREATE TABLE board.RA_board (
BOARD_SEQ INT UNSIGNED NOT NULL AUTO_INCREMENT 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 '게시글의 조회수',
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 '게시판';
 
-- RA_BOARD 테이블 컬럼 정보 확인
SHOW FULL COLUMNS FROM board.RA_BOARD;
 
-- RA_BOARD 테이블 샘플 데이터 입력
INSERT INTO board.RA_BOARD (BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES ('게시글 작성자1', '게시글 제목1', '게시글 내용1', 'TEST01', NOW() );
INSERT INTO board.RA_BOARD (BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES ('게시글 작성자2', '게시글 제목2', '게시글 내용2', 'TEST02', NOW() );
INSERT INTO board.RA_BOARD (BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES ('게시글 작성자3', '게시글 제목3', '게시글 내용3', 'TEST03', NOW() );
INSERT INTO board.RA_BOARD (BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES ('게시글 작성자4', '게시글 제목4', '게시글 내용4', 'TEST04', NOW() );
INSERT INTO board.RA_BOARD (BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES ('게시글 작성자5', '게시글 제목5', '게시글 내용5', 'TEST05', NOW() );
INSERT INTO board.RA_BOARD (BOARD_WRITER, BOARD_SUBJECT, BOARD_CONTENT, INS_USER_ID, INS_DATE) VALUES ('게시글 작성자6', '게시글 제목6', '게시글 내용6', 'TEST06', NOW() );
 
-- RA_BOARD 테이블 샘플 데이터 조회
SELECT *FROM board.RA_BOARD;

 

2. 테이블 확인

 

3. application.properties 수정

 

# datasource 설정
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.jdbc-url=jdbc:mysql://localhost:3306/board?&serverTimezone=UTC&autoReconnect=true&allowMultiQueries=true&characterEncoding=UTF-8
spring.datasource.username=dlrms
spring.datasource.password=mylee45!
spring.datasource.mapper-locations=classpath:/mapper/**/*.xml
 
# mybatis 설정
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.config-location=classpath:/mybatis/mybatis-config.xml
 

 

4. DbConfig 클래스 작성

DbConfig 클래스는 application.properties에서 데이터베이스 접속 정보를 읽어 DataSource 설정과 SqlSessionFactory, SqlSessionTemplate을 설정하는 역할을 한다.

 

package com.example.board.config;
 
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.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.Configuration;
 
import javax.sql.DataSource;
 
@Configuration
@MapperScan(value = "com.example.board", sqlSessionFactoryRef = "SqlSessionFactory")
public class DbConfig {
 
    @Value("${spring.datasource.mapper-locations}")
    String mPath;
 
    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource DataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "SqlSessionFactory")
    public SqlSessionFactory SqlSessionFactory(@Qualifier("dataSource") DataSource DataSource, ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(DataSource);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources(mPath));
        return sqlSessionFactoryBean.getObject();
    }
 
    @Bean(name = "SessionTemplate")
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("SqlSessionFactory") SqlSessionFactory firstSqlSessionFactory) {
        return new SqlSessionTemplate(firstSqlSessionFactory);
    }
}
 

 

 

5. Board 클래스 작성

DB에 담겨있는 게시판 테이블의 정보를 담기 위한 자바 클래스

 

package com.example.board.domain;
 
import lombok.*;
 
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Board {
 
    int board_seq;
    String board_writer;
    String board_subject;
    String board_content;
    int board_hits;
    String ins_user_id;
    String ins_date;
    String upd_user_id;
    String upd_date;
 
}
 

 

@Getter @Setter 어노테이션을 사용하기 위한 롬복 설치

경로 : build.gradle

 

 

6.  BoardMapper 인터페이스 와 BoardMapper.xml 작성

 

BoardMapper.java

 

package com.example.board.mapper;
 
import com.example.board.domain.Board;
import org.apache.ibatis.annotations.Mapper;
 
import java.util.List;
 
@Mapper
public interface BoardMapper {
 
    // 게시글 목록 조회
    List<Board> getBoardList();
 
}
 

 

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.example.board.mapper.BoardMapper">
 
<select id="getBoardList" resultType="com.example.board.domain.Board">
 
        SELECT * FROM RA_BOARD ORDER BY BOARD_SEQ ASC
 
    </select>
 
</mapper>

 

7. BoardService 클래스 작성

 

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

 

8. BoardController 클래스 작성

 

package com.example.board.controller;
 
import com.example.board.domain.Board;
import com.example.board.service.BoardService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
 
import java.util.List;
 
@RequestMapping("/board")
@RestController
public class BoardController {
 
    @Autowired
    private BoardService boardService;
 
    //게시글 목록 조회
    @GetMapping
    public List<Board> getBoardList(){
        return boardService.getBoardList();
    }
 
 
}
 

 

9. 결과화면(Talend API Tester,Web)

아래와 같이 json 형식으로 조회된다