Knowledge Notes

게시판 댓글관리 구현 중 삭제된 게시글(tb_board.delete_yn = 'Y')에 달렸던 댓글은 댓글관리에서 볼 수 없게 하기위한 처리를 진행하다가 조인이 불가피하여 사용하였다. (조인은 속도를 늦추니 불가피한 경우에만 사용하면 좋다고 기억)

그 과정에서 탐색한 조인 방법을 정리한다.

1. 테이블

CREATE TABLE `tb_board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `delete_yn` varchar(1) DEFAULT 'N',
  `create_date` datetime DEFAULT NULL,
  `views` int DEFAULT '0',
  `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
)

CREATE TABLE `tb_comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `board_id` int NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `board_id` (`board_id`),
  KEY `writer_id` (`writer_id`),
  CONSTRAINT `tb_comment_ibfk_1` FOREIGN KEY (`board_id`) REFERENCES `tb_board` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tb_comment_ibfk_2` FOREIGN KEY (`writer_id`) REFERENCES `tb_userinfo` (`id`) ON DELETE CASCADE
)

2. Inner Join 예시

select c.*
from board.tb_board b join board.tb_comment c 
	on b.delete_yn='y' and b.id = c.board_id and c.writer = 'rhkdals'
order by b.id;

• select * from [ ] join [ ] on [조건]
• select c.* : tb_comment 테이블의 컬럼만 모두 조회

1. 테이블

CREATE TABLE `tb_board` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `delete_yn` varchar(1) DEFAULT 'N',
  `create_date` datetime DEFAULT NULL,
  `views` int DEFAULT '0',
  `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
)

CREATE TABLE `tb_comment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `board_id` int NOT NULL,
  `content` text NOT NULL,
  `writer_id` int NOT NULL,
  `writer` varchar(20) NOT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

2. 외래키(FK) 추가

ALTER TABLE board.tb_comment ADD FOREIGN KEY(board_id) REFERENCES board.tb_board(id);

• tb_comment의 board_id컬럼이 tb_board 테이블의 id 값을 참조하도록 설정
• ALTER TABLE 참조하는 테이블 ADD FOREIGN KEY(참조하는 컬럼명) REFERENCES 참조받는 테이블(참조받는 컬럼명);

ALTER TABLE board.tb_comment ADD FOREIGN KEY(board_id) REFERENCES board.tb_board(id) ON DELETE CASCADE;

• 참조받는 테이블의 데이터가 삭제되면 참조하는 테이블의 데이터도 삭제하고 싶을 때 ON DELETE CASCADE를 맨 뒷 부분에 추가하면 된다.

3. 제약조건 확인

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'board';

1. MySQL 시간을 담을 컬럼은 DATETIME으로 설정

2. DTO에 Timestamp 타입으로 선언

private Timestamp createDate;

3. 생성 시점에 시간 셋팅해주기

member.setCreateDate(Timestamp.valueOf(LocalDateTime.now())); // 회원가입 시간

• Member member
• Timestamp.valueOf(LocalDateTime.now()) : 해당 로직 실행 당시 시간을 리턴

오류 발생 : 생성 시간보다 3시간 추가돼서 DB에 저장되는 것을 발견.

해결 : application.properties 파일에서 UTC부분을 Asia/Seoul로 수정

spring.datasource.url=jdbc:mysql://localhost:3306/board?serverTimezone=UTC&characterEncoding=UTF-8

spring.datasource.url=jdbc:mysql://localhost:3306/board?serverTimezone=Asia/Seoul&characterEncoding=UTF-8

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;
    }

1. 테이블 생성 시 바로 적용하기

- 참조 테이블의 데이터가 삭제되면 그 데이터와 연관있던 데이터를 함께 삭제하는 내용이다.

예를 들어 A가 게시글을 썼는데 회원탈퇴를 진행했다. 그럼 A가 썼던 게시글은 자동 삭제된다.

CREATE TABLE `board`.`tb_userinfo` (
  `id` bigint 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`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `board`.`tb_board` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `writer_id` bigint NOT NULL,
  `writer` varchar(20) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`writer_id`, `writer`) REFERENCES `board`.`tb_userinfo` (`id`, `username`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• FK 추가 뒤에 ON DELETE CASCADE만 붙여주면 된다.

문제점 : USERINFO의 PK(기본키)를 참조해서 BOARD에 FK를 만들려고하는데 지속적으로 오류가 발생했다.

• 오류의 이유는 기존 USERINFO테이블의 PK를 id컬럼에만 적용하고는 BOARD 테이블에서 USERINFO테이블의 username컬럼을 참조하려고해서 그랬다.

해결 : USERINFO테이블의 기본키를 id와 username 두 개를 설정하여 해결했다. (id를 해제하려했으나 Auto Increment는 PK가 적용된 컬럼만 된다고 한다.)

* 복합키 : 이렇게 2개의 컬럼을 PK로 지정하면 이를 복합키라고 한다.

수정 후 테이블 상태 (BOARD 테이블의 writer_id와 writer는 각각 USERINFO의 id와 username 컬럼을 참조한다.)

USERINFO 테이블

BOARD 테이블

문제점 : MySQL 데이터데이스에 defalut값을 설정해놨으나, SpringBoot에서 JPA로 데이터를 Insert할 때 값을 넣지 않으면 default값이 아닌 null이 들어갔다. 음.. 빈 값이 아닌 null이 들어가서 그런 것 같다.

해결 : @DynamicInsert와 @DynamicUpdate를 @Entity에 같이 작성해준다.

@DynamicInsert : insert 시 null인 필드 제외

@DynamicUpdate : update 시 null인 필드 제외

1. 테이블 데이터 포함 테이블 삭제

사용법 : DROP TABLE 테이블명;

DROP TABEL 'board'.'tb_userinfo';

2. 테이블 데이터 삭제

사용법 : TRUNCATE 테이블명;

TRUNCATE 'board'.'tb_userinfo';

문제점 : SpringBoot 프로젝트 진행 중 테스트 데이터를 넣고 삭제하면 id 값이 1부터 다시 시작이 아닌

(삭제 전의 마지막 값 + 1) 로 시작됐다.

보기 좋지 않아서 초기화 하는 방법을 찾아봤다.

사용법 : ALTER TABLE 테이블명 AUTO_INCREMENT=1;

1. MySQL에서 테이블을 만들어준다.

ID에 적용할 것이므로 AI(Auto Increment) 체크!

2. 자바 코드에서 Model에 @GeneratedValue 어노테이션을 추가해준다.

@GeneratedValue(strategy = GenerationType.IDENTITY)

다음은 코드에 적용한 모습이다.