Knowledge Notes

1. MySQL 테이블 상태

CREATE TABLE `tb_board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `delete_yn` varchar(1) DEFAULT 'N',
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `writer_id` (`writer_id`),
  CONSTRAINT `tb_board_ibfk_1` FOREIGN KEY (`writer_id`) REFERENCES `tb_userinfo` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `tb_userinfo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(100) NOT NULL,
  `email` varchar(45) NOT NULL,
  `role` varchar(20) DEFAULT 'MEMBER',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2. build.gradle 의존성 추가

implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.4'

3. application.properties 작성

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/board?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
spring.datasource.username=아이디
spring.datasource.password=비밀번호
spring.devtools.livereload.enabled=true
spring.freemaker.cache=false
spring.thymeleaf.cache=false

# MyBatis
# mapper.xml 위치 src/main/resource/mapper/**/*.xml
mybatis.mapper-locations=classpath:mapper/**/*.xml

# camel case
mybatis.configuration.map-underscore-to-camel-case=true

# 패키지명 alias
mybatis.type-aliases-package=com.min.board.model

# mapper 로그정보
logging.level.com.min.board.model.repository=TRACE

4. 모델 추가 및 Mapper 인터페이스 추가

@Data
public class Board {

    private Long id;

    @NotNull
    @Size(min = 2, max = 30, message = "제목은 2자 이상 30자 이하입니다.")
    private String title;

    @NotNull
    @NotBlank(message = "내용을 입력하세요.")
    private String content;
    private Long writerId;
    private String writer;
    private String deleteYN;
    private Timestamp createDate;

    private String image;
}

@Mapper
public interface BoardMapper {
    // 모든 글 조회
    List<Board> selectAllBoards();

    // 게시글 개수 반환 (메인 게시글, 글 관리, 휴지통)
    int selectBoardTotalCount(Pagination pagination);

    // 삭제된 글 제외 모두 조회 (메인 게시글, 글 관리, 휴지통)
    List<Board> selectBoardList(Pagination pagination);

    // 게시글 수정
    void updateBoard(Board board);

    // 아이디로 글 찾기
    Board findById(Long id);

    // 휴지통으로 이동 (임시삭제)
    void temporaryDeleteById(Long id);

    // 글 작성
    void insertBoard(Board board);

    // 휴지통 비우기
    void permanentlyDeleteById(Long boardId);
}

5. resources/mapper 에 mapper.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="CommonMapper">
    <sql id="paging">
        LIMIT
            #{startList}, #{listSize}
    </sql>

    <sql id="search">
        delete_yn = 'N'
        <if test="searchText != null and searchText !=''">
            AND
            (
            title LIKE CONCAT('%', #{searchText}, '%')
            OR content LIKE CONCAT('%', #{searchText}, '%')
            )
        </if>
    </sql>

    <sql id="myPost">
        delete_yn = 'N'
        <if test="writer != null and writer !=''">
            AND writer = #{writer}
        </if>
    </sql>

    <sql id="trash">
        delete_yn = 'Y'
        <if test="writer != null and writer !=''">
            AND writer = #{writer}
        </if>
    </sql>
</mapper>

• <sql> : <include>를 통해 다른 쿼리에 활용 가능
• refid : 참조
• (select, update, insert, delete의) id : Mapper인터페이스 함수명과 동일
• parameterType : #{}에 들어갈 파라미터 타입 (사용 권장하지 않음.)
• resultType : 쿼리 결과 타입
• #{} : 파리미터(get을 의미) -> DTO(모델)과 똑같은 필드명 작성
  • #{id} => getId
• <if> : if문. test="조건"

참조 매퍼

<?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="CommonMapper">
    <sql id="paging">
        LIMIT
            #{startList}, #{listSize}
    </sql>

    <sql id="search">
        delete_yn = 'N'
        <if test="searchText != null and searchText !=''">
            AND
            (
            title LIKE CONCAT('%', #{searchText}, '%')
            OR content LIKE CONCAT('%', #{searchText}, '%')
            )
        </if>
    </sql>

    <sql id="myPost">
        delete_yn = 'N'
        <if test="writer != null and writer !=''">
            AND writer = #{writer}
        </if>
    </sql>

    <sql id="trash">
        delete_yn = 'Y'
        <if test="writer != null and writer !=''">
            AND writer = #{writer}
        </if>
    </sql>
</mapper>

6. Service에서 사용

    private final BoardMapper boardMapper;

    @Autowired
    public BoardService(BoardMapper boardMapper) {
        this.boardMapper = boardMapper;
    }

    // id를 이용해서 해당 글 수정
    public Board contentLoad(Long id) {
        Board board = boardMapper.findById(id);
        return board;
    }