#103. React DB연결 (추상화관련)

자바관점에서....

 

1. 테이블

RDBMS 특징 => 관계형 데이터베이스 관리 시스템

각각 장단점이 있지만 아래처럼 나누는 게 정석

CREATE TABLE PARENT_BOARD(
    BNO NUMBER PRIMARY KEY,
    TITLE VARCHAR2(200) NOT NULL,
    CONTENT VARCHAR2(2000) NOT NULL,
    TYPE NUMBER NOT NULL,
    CREATE_DATE DATE DEFAULT SYSDATE
);

CREATE TABLE USER_BOARD(
    BNO REFERENCES PARENT_BOARD,
    WRITER VARCHAR2(200) NOT NULL
);

CREATE TABLE PHOTO_BOARD(
    BNO REFERENCES PARENT_BOARD,
    IMAGE VARCHAR2(2000) NOT NULL
);

 

 

2. VO

버전1. 부모테이블(공유할 테이블)을 이용한 VO는 abstract 추상클래스로 만들고

자식테이블을 이용한 VO는 상속받아서 각각 추가할 필드만 추가해준다.

 

수정에 닫혀있고
확장에 열려있어야 한다 => 추상화 => 유지보수에 용이

@Getter @Setter @ToString @NoArgsConstructor
public abstract class ParentBoard {
	
	private int bno;
	private String title;
	private String content;
	private int type;
	private Date createDate;
	
	// 패러다임 불일치 => 
	// RDBMS는 각 테이블 간의 관계를 설정해서 저장하자
	// VO는 객체를 만들어 저장하자

}

 

@Getter @Setter @ToString @NoArgsConstructor @AllArgsConstructor
public class PhotoBoard extends ParentBoard {
	
	private String image;
	
	private ParentBoard parentBoard;

}

 

버전2. 부모테이블의 VO를 자식이 필드로 가짐

@Getter @Setter @ToString @NoArgsConstructor
public class ParentBoard {
	
	private int bno;
	private String title;
	private String content;
	private int type;
	private Date createDate;
}

 

@Getter @Setter @ToString @NoArgsConstructor @AllArgsConstructor
public class PhotoBoard {
	
	private String image;
	private ParentBoard parentBoard;

}

 

 

3. SQL (jstl 조건 처리)

<!-- category : user || photo -->
<select id="selectBoardList" parameterType="string" resultMap="renewalMap">
<!-- <select id="selectBoardList" parameterType="string" resultMap="boardMap"> -->
    SELECT
           *
      FROM
           PARENT_BOARD
      <if test="category == 'user'">
      JOIN
           USER_BOARD USING(BNO)
      </if>
      <if test="category == 'photo'">
      JOIN
           PHOTO_BOARD USING(BNO)
      </if>
</select>

 

버전 1.

<!-- 버전1 -->
<resultMap id="boardMap" type="parent">
    <result column="bno" property="bno" />
    <result column="title" property="title" />
    <result column="content" property="content" />
    <result column="create_date" property="createDate" />
    <discriminator column="type" javaType="_int">
        <case value="1" resultType="user">
            <result column="writer" property="writer" />
        </case>
        <case value="2" resultType="photo">
            <result column="image" property="image" />
        </case>
    </discriminator>
</resultMap>

 

버전2.

<!-- 버전2 -->
<resultMap id="renewalMap" type="parent">
    <association property="parentBoard" javaType="parent">
        <result column="bno" property="bno" />
        <result column="title" property="title" />
        <result column="content" property="content" />
        <result column="create_date" property="createDate" />
    </association>
    <discriminator javaType="_int" column="type">
        <case value="1" resultType="user">
            <result column="writer" property="writer" />
        </case>
        <case value="2" resultType="photo">
            <result column="image" property="image" />
        </case>
    </discriminator>
</resultMap>

 

 

Controller에서 category 넘겨줌

@GetMapping("/idolboard/{category}")
public ResponseEntity<List<ParentBoard>> selectBoardList(@PathVariable(name="category") String category){

    List<ParentBoard> list = idolService.selectBoardList(category);
    HttpHeaders header = new HttpHeaders();
    header.setContentType(new MediaType("application", "json", Charset.forName("UTF-8")));
    return new ResponseEntity<List<ParentBoard>>(list, header, HttpStatus.OK);
}

 

여기까지는 1:1관계였는데

 

만약 게시글과 댓글처럼 1:N관계인 경우

1. VO

부모에 List로 자식객체를 받음

@Getter @Setter @ToString @NoArgsConstructor
public class ParentBoard {
	
	private int bno;
	private String title;
	private String content;
	private int type;
	private Date createDate;

	private List<IdolReply> replyList;

}

 

@Getter @Setter @ToString @NoArgsConstructor @AllArgsConstructor
public class IdolReply {
	
	private int rno;
	private int bno;
	private String replyContent;

}

 

2. SQL

<resultMap id="parentAndReplyMap" type="parent">
    <result column="p.bno" property="bno" />
    <result column="title" property="title" />
    <result column="content" property="content" />
    <result column="create_date" property="createDate" />
    <collection property="replyList" resultMap="replyMap"></collection>
</resultMap>

<resultMap id="replyMap" type="com.kh.spring.idol.model.vo.IdolReply">
    <result column="rno" property="rno" />
    <result column="reply_content" property="replyContent" />
</resultMap>

<select id="selectBoard" parameterType="_int" resultMap="parentAndReplyMap">
    SELECT 
           P.BNO,
           TITLE,
           CONTENT,
           CREATE_DATE,
           RNO,
           REPLY_CONTENT
      FROM
           PARENT_BOARD P
      LEFT
      JOIN
           IDOL_REPLY I ON (P.BNO = I.BNO)
     WHERE
           P.BNO = #{boardNo}
</select>

 

 

Controller

@GetMapping("/boardDetail/{boardNo}")
public ResponseEntity<ParentBoard> selectBoard(@PathVariable(name="boardNo") int boardNo){

    ParentBoard board = idolService.selectBoard(boardNo);
    HttpHeaders header = new HttpHeaders();
    header.setContentType(new MediaType("application", "json", Charset.forName("UTF-8")));
    return new ResponseEntity<ParentBoard>(board, header, HttpStatus.OK);
}

 

 

추상화와 인터페이스가 중요하다~

인터페이스는 고치면 와장창됨 인터페이스 고칠 바에 다중 상속하는 게 낫다