MyBatis) resultMap / collection / association 사용법 + 특정 column의 값을 여러 행으로 출력해 List 변수로 받기

https://europani.tistory.com/m/212

• resultMap
  : 복잡한, 계층적 매핑을 위해 사용
  -. 속성 id : resultMap의 아이디
  -> resultMap 사용 시 sql 태그의 속성 resultType이 아닌 resultMap을 쓰고 resultMap의 id값을 입력
  -. 속성 type: 자료형 (클래스의 주소)
  -. 태그 id : 구분자 역할하는 속성 (PK)
  -> result도 가능하나 성능에 도움 줌
  -. 태그 result: PK가 아닌 일반 속성
  -.. property : java 속성명
  -.. column : table 컬럼명
  

• collection
  : 1:M관계의 테이블 조인 시 사용
  -> M의 클래스를 선언할 땐 List<클래스>로 선언해야함

• association
  : 1:1 (has one) 관계의 테이블 조인 시 사용
  -. property: 조인할 객체명(변수명)
  -. javaType: 조인할 객체의 주소

@Data
public class CommentUserDTO {

	private String userName;
	
	private double starRating;
	
	private CommentVO commentVO;
}

@Data
public class CommentVO {

	private Long commentCd;
	private String movieCd;
	
	private String userid;
	private String contents;
	private Date commentDate;
	private Date updateDate;
	
	private int replyCnt;
	private int likeCnt;
	
}

	<resultMap type="com.moviepedia.domain.CommentUserDTO" id="commentUserDTO">
	
		<result property="userName" column="username"/>

		<result property="starRating" column="starrating"/>

		<association property="commentVO" javaType="com.moviepedia.domain.CommentVO">
			<id property="commentCd" column="commentcd"/>

			<result property="movieCd" column="moviecd"/>
			<result property="userid" column="userid"/>
			<result property="contents" column="contents"/>
			<result property="commentDate" column="commentdate"/>
			<result property="updateDate" column="updatedate"/>
			<result property="replyCnt" column="replycnt"/>
			<result property="likeCnt" column="likecnt"/>
		</association>
	</resultMap>

	<select id="getAdditionalList" resultMap="commentUserDTO">

		SELECT c.*, m.userName, s.starRating
		FROM (SELECT ROWNUM AS RANK, sc.* 
        		FROM (SELECT tbl_comment.* 
        				FROM tbl_comment         				
        				WHERE movieCd = #{movieCd} 
        				<choose>
							<when test="orderBy == 0">
								ORDER BY likeCnt DESC, commentdate DESC
							</when>
							<when test="orderBy == 1">
								ORDER BY commentdate DESC, likeCnt DESC
							</when>
						</choose>) sc) c 
		JOIN tbl_member m
		ON c.userid = m.userid
		LEFT OUTER JOIN tbl_starRating s
		ON c.userid = s.userid AND c.movieCd = s.movieCd
		WHERE c.rank BETWEEN #{currentCnt} + 1 AND #{currentCnt} + #{additionalCnt}
		ORDER BY c.rank

	</select>
	

• 특정 column의 값을 여러 행으로 출력해 List 변수로 받기
  resultMap을 이용해 VO나 DTO를 Mapping할때 DB 특정 컬럼은 여러 행의 값을 한번에 조회해List 변수에 받고 싶을 때가 있다.
  ex) user의 정보 중 권한은 한개만 존재하지 않으므로 List로 모두 들고와야함

=> 여러 행을 받을 특정 컬럼(outer join 등 이용)을 함께 조회해 resultMap과 Collection을 통해 정의한 객체에 담으면 List 타입이 아닌 변수엔 정상적으로 하나의 값이 들어가고, 여러 행의 값을 받을 List 타입 변수엔 여러 값들이 순차적으로 들어감.

<resultMap type="com.moviepedia.domain.MemberVO" id="memberMap">
	
		<id property="userid" column="userid"/>
		
		<result property="userpw" column="userpw"/>
		<result property="userName" column="username"/>
		<result property="enabled" column="enabled"/>
		<result property="accountNonLocked" column="accountnonlocked"/>
			
		<result property="regDate" column="regdate"/>
		<result property="updateDate" column="updatedate"/>
		
		<result property="randomString" column="randomstring"/>
		
		<collection property="authList" resultMap="authMap"></collection>
		
	</resultMap>
	
	<resultMap type="com.moviepedia.domain.AuthVO" id="authMap">
	
<!-- 		<id property="userid" column="userid"/> -->
		<result property="userid" column="userid"/>
		<result property="auth" column="auth"/>
	
	</resultMap>

	<select id="read" resultMap="memberMap">
		SELECT mem.userid, mem.userpw, mem.username, mem.enabled, 
			mem.accountnonlocked, mem.regdate, mem.updatedate, mem.randomstring, auth.auth
		FROM tbl_member mem LEFT OUTER JOIN tbl_member_auth auth
		ON mem.userid = auth.userid
		WHERE mem.userid = #{userid}
	</select>

@Data
public class MemberVO {

	private String userid;
	private String userpw;
	private String userName;
	private boolean enabled;
		// : 계정이 어떠한 이유로 인해 관리 상 또는 자동으로 비활성화됨을 나타냄
		// : 해제를 하기위해선 몇가지 조치가 필요
	private boolean accountNonLocked; 
	// 잘못된 로그인 시도로 인해 계정이 자동으로일시 중지
	// -> 시간 지남 또는 수동 잠금 해제
	// but, 일반적으론 enabled 하나만으로 사용하는 경우 많음
	// 여기선 휴면계정과 비밀번호 오류 반복으로 인한 계정 잠금을 구분할 것
	
//	accountNonExpired
//	credentialsNonExpired
	
	private String randomString;
	
	private int failureCnt;
	
	private Date regDate;
	private Date updateDate;
	
	private List<AuthVO> authList;

}

// tbl_member_auth 테이블 그대로 반영
@Data
public class AuthVO {
	
	private String userid;
	private String auth;
}

@Test
	public void Testme() {
		
		String userid = "admin91";
		MemberVO memberVO = memberMapper.read(userid);
		
		log.info(memberVO);
		log.info(memberVO.getAuthList());
	}
    
// > 
INFO : jdbc.resultsettable - 
|--------|-------------------------------------------------------------|---------|---------|-----------------|----------------------|----------------------|--------------|------------|
|userid  |userpw                                                       |username |enabled  |accountnonlocked |regdate               |updatedate            |randomstring  |auth        |
|--------|-------------------------------------------------------------|---------|---------|-----------------|----------------------|----------------------|--------------|------------|
|admin91 |$2a$10$NyV1wuWNVShhuB01aEzVfekVMyUOcdlE/eTsBqZ5IHlKLespumm66 |관리자91    |true     |true             |2022-06-22 20:03:44.0 |2022-06-22 20:03:44.0 |DJxYR6cn7rAlx |ROLE_ADMIN  |
|admin91 |[unread]                                                     |[unread] |[unread] |[unread]         |[unread]              |[unread]              |[unread]      |ROLE_MEMBER |
|--------|-------------------------------------------------------------|---------|---------|-----------------|----------------------|----------------------|--------------|------------|

// > MemberVO(userid=admin91, userpw=$2a$10$NyV1wuWNVShhuB01aEzVfekVMyUOcdlE/eTsBqZ5IHlKLespumm66, userName=관리자91, enabled=true, accountNonLocked=true, randomString=DJxYR6cn7rAlx, failureCnt=0, regDate=Wed Jun 22 20:03:44 KST 2022, updateDate=Wed Jun 22 20:03:44 KST 2022, authList=[AuthVO(userid=admin91, auth=ROLE_ADMIN), AuthVO(userid=admin91, auth=ROLE_MEMBER)])
// > [AuthVO(userid=admin91, auth=ROLE_ADMIN), AuthVO(userid=admin91, auth=ROLE_MEMBER)]

• trialNerror) association과 collection 동시에 사용하기

nested exception is org.apache.ibatis.exceptions.TooManyResultsException: 
Expected one result (or null) to be returned by selectOne(), but found: 7

-> 에러 발생. 개인적인 생각으론 각 태그는 1:1 관계, 1:N 관계를 표현할 수 있도록 하는데 기준이 되는 1의 파라미터가 없어 그런게 아닌가 싶다.
association으로 표현한 VO 객체의 변수 중 필요한 변수만 골라 클래스에 선언하자 그냥.

@Data
public class PeopleInfoDTO {
	private PeopleVO peopleVO;
	
	private List<MovieWithStarDTO> movieWithStarDTOList;

}

<resultMap type="com.moviepedia.domain.PeopleInfoDTO" id="PeopleInfoDTOMap">
		
		<association property="peopleVO" javaType="com.moviepedia.domain.PeopleVO">
			<id property="peopleCd" column="peoplecd"/>
			
			<result property="peopleNm" column="peoplenm"/>
			<result property="peopleNmEn" column="peoplenmen"/>
			<result property="repRoleNm" column="reprolenm"/>
		</association>
			
		<collection property="movieWithStarDTOList" resultMap="movieWithStarDTOMap"></collection>
	
	</resultMap>
	
	<resultMap type="com.moviepedia.domain.MovieWithStarDTO" id="movieWithStarDTOMap">
		
		<result property="avgStarRating" column="avgstarrating"/>
		<result property="userStarRating" column="userstarrating"/>
		
		<association property="movieVO" javaType="com.moviepedia.domain.MovieVO">
			<id property="movieCd" column="moviecd"/>
			
			<result property="movieNm" column="movienm"/>
			<result property="prdtYear" column="prdtyear"/>
		</association>
	</resultMap>

	<select id="getPeopleDirectorFilmoList" resultMap="PeopleInfoDTOMap">
		SELECT ft.peopleCd, ft.peopleNm, ft.peopleNmEn, ft.repRoleNm, m.movieCd, m.movieNm, m.prdtYear
		    , ROUND(AVG(s.starRating) ,1) AS avgStarRating
		    ,(SELECT ss.starRating FROM tbl_starRating ss WHERE ss.userid = #{userid} AND ss.movieCd = m.movieCd) AS userStarRating
		FROM (
		    SELECT iv.peopleCd, iv.peopleNm, iv.peopleNmEn, iv.repRoleNm, REGEXP_SUBSTR(iv.directorFilmos, '[^,]+', 1, LEVEL) directorFilmo
		    FROM (
		        SELECT p.peopleCd, p.peopleNm, p.peopleNmEn, p.repRoleNm, p.directorFilmos, p.actorFilmos FROM tbl_people p WHERE p.peopleCd = #{peopleCd}
		    ) iv
		   
		    CONNECT BY	REGEXP_SUBSTR(iv.directorFilmos, '[^,]+', 1, LEVEL) IS NOT NULL
		) ft    
		INNER JOIN tbl_movie m /* Movie 테이블에 등록된 영화만 */
		ON (ft.directorFilmo = m.movieCd)
		LEFT OUTER JOIN tbl_starRating s
		ON (m.movieCd = s.movieCd)
		GROUP BY ft.peopleCd, ft.peopleNm, ft.peopleNmEn, ft.repRoleNm, m.movieCd, m.movieNm, m.prdtYear
		ORDER BY m.prdtYear DESC
	
	</select>	

• 시행착오) id 태그의 중요성
  resultMap 태그 내에 id 컬럼을 지정하지않으면 1:M 관계가 정확히 정의되지 않아 에러 발생할 때가 있음(코드 동일한데 어떨 땐 발생 어떨 땐 발생안함..)

  <resultMap type="com.moviepedia.domain.StarRatingAnalysisDTO" id="StarRatingAnalysisMap">
  		
  		<id property="movieCd" column="moviecd"/>
  		
  		<result property="starRatingCnt" column="starratingcnt"/>
  		<result property="starRatingAvg" column="starratingavg"/>
  		
  		<collection property="cntByStarDTOList" resultMap="CntByStarDTOMap"></collection>
  	
  	</resultMap>
  	
  	<resultMap type="com.moviepedia.domain.CntByStarDTO" id="CntByStarDTOMap">
  		<result property="star" column="star"/>
  		<result property="starCnt" column="starcnt"/>
  	</resultMap>
  	
  	<select id="getMovieStarRatingAnalysis" resultMap="StarRatingAnalysisMap">
  	
  		SELECT s.movieCd, ROUND(AVG(s.starRating), 1) starratingavg, count(s.starrating) starratingcnt, js.star, js.starCnt
  		FROM tbl_starrating s 
  		LEFT OUTER JOIN (
  		   SELECT movieCd,
  		        CASE starrating
  		        WHEN 0.5 THEN 'cnt0_5' WHEN 1 THEN 'cnt1_0' 
  		        WHEN 1.5 THEN 'cnt1_5' WHEN 2 THEN 'cnt2_0' 
  		        WHEN 2.5 THEN 'cnt2_5' WHEN 3 THEN 'cnt3_0' 
  		        WHEN 3.5 THEN 'cnt3_5' WHEN 4 THEN 'cnt4_0'
  		        WHEN 4.5 THEN 'cnt4_5' WHEN 5 THEN 'cnt5_0'
  		        END AS star,
  		        COUNT(starrating) starCnt
  		    FROM tbl_starrating
  		    GROUP BY movieCd, starrating
  		) js
  		ON (s.movieCd=js.movieCd)
  		WHERE s.movieCd = #{movieCd}
  		GROUP BY s.movieCd, js.star, js.starCnt
  		ORDER BY js.star ASC
  
  	</select>

  

  • error) resultMap 클래스의 하나의 객체에 저장되어야 하는데 6개의 row가 반환되었다는 에러 발생. id 역할을 할 movieCd 컬럼 SELECT문에 추가하고 id태그 함께 작성하니 해결됨
    .. 그런데 이를 다시 제거하니 에러 다시 발생안함..
    .. -> 그래도 어떤 컬럼이 1:M에서 1에 해당하는지 명확히 지정해주는 것 필요해보인다.