08-4. SimpleBoard (대댓글(미완), 테스트, History) [Spring]

대댓글

<resultMap type="ReplyVO" id="replyVOMap">

<id column="RPLY_ID" property="replyId" />

<result column="BRD_ID" property="boardId" />

<result column="USR_ID" property="userId" />

<result column="PRNT_RPLY_ID" property="parentReplyId" />

<result column="RPLY" property="reply" />

<result column="CRT_DT" property="createdDate" />

<result column="LEVEL" property="level" />

<association property="board" javaType="BoardVO" />

<id column="B_BRD_ID" property="boardId"/>

   <result column="SBJ" property="subject"/>

<result column="CONT" property="content"/>

<result column="B_USR_ID" property="userId"/>

<result column="B_CRT_DT" property="createdDate"/>

<result column="HIT" property="hit"/>

<result column="RCMD" property="recommend"/>

<result column="DP_FL_NM" property="displayFileName"/>

<result column="RL_FL_NM" property="realFileName"/>

</association>

<association property="user" javaType="UserVO">

<id column="U_USR_ID" property="userId" />

<result column="USR_NICK_NAME" property="userNickName"/>

<result column="U_CRT_DT" property="createdDate"/>

<result column="PNT" property="point"/>

</association>

<association property="parent" javaType="Reply">

<result column="P_BRD_ID" property="boardId" />

<result column="P_USR_ID" property="userId" />

<result column="P_PRNT_RPLY_ID" property="parentReplyId" />

<result column="P_RPLY" property="reply" />

<result column="P_CRT_DT" property="createdDate" />

<result column="P_LEVEL" property="level" />

</association>

</resultMap

CONNECT    BY    PRIOR : 계층구조를 만들어 준다 (누구 밑에는 누구 누구 밑에는 누구 )

<select id="getAllReply" resultMap="replyVOMap" parameterType="string">

SELECT LEVEL

, R.RPLY_ID

, R.BRD_ID

, R.USR_ID

, R.PRNT_RPLY_ID

, R.RPLY

, R.CRT_DT

, U.USR_ID U_USR_ID

, U.USR_NICK_NAME

, U.PNT

,BRD_ID B_BRD_ID

, SBJ

, CONT

, USR_ID B_USR_ID

, CRT_DT B_CRT_DT

, HIT

, RCMD

, P.RPLY_ID P_RPLY_ID

, P.BRD_ID P_BRD_ID

, P.USR_ID P_USR_ID

, P.PRNT_RPLY_ID P_PRNT_RPLY_ID

, P.RPLY P_RPLY

, P.CRT_DT P_CRT_DT

FROM         RPLY R

 , RPLY P

, USR U

, BRD B

WHERE R.USR_ID = U.USR_ID

AND R.BRD_ID = B.BRD_ID

  AND          R.PRNT_RPLY_ID = P.RPLY_ID(+)

 -> 상위 댓글이 무엇인지 알기위해서 ( + (아우터조인) )

AND U.USR_ID = B.USR_ID -> 자신이 한 댓글만 나오므로 제외

AND R.BRD_ID = #{boardId}

CONNECT BY PRIOR R.RPLY_ID = R.PRNT_RPLY_ID

START WITH R.PRNT_RPLY_ID = '0'

</select>

↓↓↓↓↓

SimpleBoard04.zip