[Mybatis] resultMap collection을 이용한 중첩 데이터 출력하기

가끔 사용하게 되는 테크닉인데 항상 같은 삽질을 반복하는 것 같아 히스토리를 남긴다.

 

ResponseDto.java

@Data
@JsonNaming(value = PropertyNamingStrategies.SnakeCaseStrategy.class)
public class WarrantyPackResponseDto {

    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @JsonNaming(value = PropertyNamingStrategies.SnakeCaseStrategy.class)
    public static class Detail {
        private Integer warrantyId;
        private String warrantyknm;
        private String warrantyenm;
        private int totalPrice;
        private List<Parts> partsList;
    }

    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    @JsonNaming(value = PropertyNamingStrategies.SnakeCaseStrategy.class)
    public static class Parts {
        private Integer partsId;
        private String ptno;
        private String bpno;
        private String ptknm;
        private String ptenm;
        private List<String> imagePathList;
        private int price;
        private List<HashSet<String>> carNameList;
    }

}

 

Mapper.xml

<resultMap id="shopWarrantyPartDetailMap" type="com.server.tips.shop.model.WarrantyPackResponseDto$Detail">
    <id property="warrantyId" column="warranty_id"/>
    <result property="warrantyknm" column="warrantyknm"/>
    <result property="warrantyenm" column="warrantyenm"/>
    <result property="totalPrice" column="total_price"/>
    <collection property="partsList" resultMap="partsMap"/>
</resultMap>

<resultMap id="partsMap" type="com.server.tips.shop.model.WarrantyPackResponseDto$Parts">
    <id property="partsId" column="parts_id"/>
    <result property="ptno" column="ptno"/>
    <result property="bpno" column="bpno"/>
    <result property="ptknm" column="ptknm"/>
    <result property="ptenm" column="ptenm"/>
    <result property="price" column="price"/>
    <collection property="imagePathList" javaType="java.util.ArrayList" ofType="string">
        <result column="file_path"/>
    </collection>
    <collection property="carNameList" ofType="string">
        <result column="car_nm"/>
    </collection>
</resultMap>

 

Description

• 기본적으로 중첩 리스트 데이터를 1:N 구조라고 가정할 때, resultMap 내부에 <id> 태그가 붙은 column value를 기준으로 N개의 데이터가 있으면 <collection> 태그를 사용해 resultMap을 붙여준다.
• 컬렉션이 이루어지는 데이터의 형식이 객체 타입 (List<Object>)이 아니라 기본 데이터 타입의 래퍼 클래스 혹은 String이 들어가는 단순 타입 컬렉션 형식의 경우 위와 같이 <collection> 안에 들어가는 <result> 태그의 property 값을 생략한다.
• 주의사항은 해당 컬렉션의 타입을 ofType을 통해 명시적으로 매핑을 해주어야 한다는 점이다.

 

Response Body Example

{
    "success": true,
    "code": "200",
    "data": {
        "warranty_id": 1,
        "warrantyknm": "타이밍 벨트",
        "warrantyenm": "TAIMING BELT",
        "total_price": 364770,
        "parts_list": [
            {
                "parts_id": 15385,
                "ptno": "2431223201",
                "bpno": "DOM2709700",
                "ptknm": "벨트－밸브 타이밍",
                "ptenm": "BELT-VALVE TIMING",
                "image_path_list": [
                    "2431223201/2431223201.3.jpg",
                    "2431223201/2431223201.1.jpg"
                ],
                "price": 114596,
                "car_list": null
            },
            {
                "parts_id": 15387,
                "ptno": "2441023500",
                "bpno": "DOM2709700",
                "ptknm": "텐셔너 어셈블리－밸브 타이밍 벨트",
                "ptenm": "TENSIONER ASSY-VLV TIMING BELT",
                "image_path_list": [
                    "2441023500/2441023500.4.jpg",
                    "2441023500/2441023500.5.jpg",
                    "2441023500/2441023500.1.jpg"
                ],
                "price": 84494,
                "car_list": null
            },
            {
                "parts_id": 15389,
                "ptno": "2481023500",
                "bpno": "DOM2709700",
                "ptknm": "아이들러 어셈블리－타이밍 벨트",
                "ptenm": "IDLER ASSY-TIMING BELT",
                "image_path_list": [
                    "2481023500/2481023500.4.jpg",
                    "2481023500/2481023500.1.jpg",
                    "2481023500/2481023500.2.jpg"
                ],
                "price": 135083,
                "car_list": null
            },
            {
                "parts_id": 15395,
                "ptno": "2512423000",
                "bpno": "DOM2709700",
                "ptknm": "가스켓－워터 펌프",
                "ptenm": "GASKET-WATER PUMP",
                "image_path_list": [
                    "2512423000/2512423000.4.jpg",
                    "2512423000/2512423000.1.jpg",
                    "2512423000/2512423000.2.jpg"
                ],
                "price": 1430,
                "car_list": null
            },
            {
                "parts_id": 15400,
                "ptno": "5723128410",
                "bpno": "DOM2709700",
                "ptknm": "벨트 V－파워 스티어링 펌프",
                "ptenm": "BELT\"V\"-POWER STEERING PUMP",
                "image_path_list": [
                    "5723128410/5723128410.3.jpg",
                    "5723128410/5723128410.8.jpg"
                ],
                "price": 18937,
                "car_list": null
            },
            {
                "parts_id": 15403,
                "ptno": "9771329000",
                "bpno": "DOM2709700",
                "ptknm": "벨트",
                "ptenm": "BELT\"V\"",
                "image_path_list": [
                    "9771329000/9771329000.5.jpg",
                    "9771329000/9771329000.8.jpg"
                ],
                "price": 10230,
                "car_list": null
            }
        ]
    }
}

 

Conclusion

1. 일반적인 객체 컬렉션 <-> Mybatis와의 매핑과 프리미티브 타입(래퍼 클래스) 의 컬렉션 매핑의 차이를 인지
2. JPA와 Mybatis를 혼용해서 쓰는 프로젝트 이외에는 어지간하면 JPA로 통일해서 사용하자.

ps. 추가 참고사항

Dto를 사용하지 않고 Map만으로 Response 처리하기

<resultMap id="OrderDetailMap" type="map">
        <result property="order_id" column="order_id"/>
        <result property="user_name" column="user_name"/>
        <result property="mobile_number" column="mobile_number"/>
        <result property="status_code" column="status_code"/>
        <result property="status_name" column="status_name"/>
        <result property="address" column="address"/>
        <result property="buy_comment" column="buy_comment"/>
        <result property="total_price" column="total_price"/>
        <result property="purchased_at" column="purchased_at"/>
        <result property="cancelled_price" column="cancelled_price"/>
        <result property="cancelled_at" column="cancelled_at"/>
        <result property="method" column="method"/>
        <collection property="parts_list" javaType="java.util.ArrayList" ofType="map">
            <result property="ptno" column="parts_no"/>
            <result property="ptknm" column="ptknm"/>
            <result property="ptenm" column="ptenm"/>
            <result property="price" column="parts_price"/>
            <result property="purchased_price" column="purchased_parts_price"/>
            <result property="qty" column="qty"/>
        </collection>
    </resultMap>
    
    <select id="selectOrderDetail" resultMap="OrderDetailMap">
        select
            b.ORDER_ID
             ,bp.PART_NO as PARTS_NO
             ,MAX(spt.price) as PARTS_PRICE
             ,spt.ptknm
             ,spt.ptenm
             ,bp.QTY
             ,bp.AMOUNT as PERCHASED_PARTS_PRICE
             ,pi.PRICE as TOTAL_PRICE
             ,pi.PURCHASED_AT
             ,pi.CANCELLED_PRICE
             ,pi.CANCELLED_AT
             ,pi.`METHOD`
             ,pism.STATUS_CODE
             ,pism.STATUS_NAME
             ,u.NAME as USER_NAME
             ,u.MOBILE_NUMBER
             ,u.ADDRESS
             ,b.BUY_COMMENT
        from buy b
                 inner join buy_product bp on b.ID  = bp.BUY_ID
                 inner join `user` u on b.BUY_USER_ID = u.ID
                 inner join pay_info pi on b.ORDER_ID = pi.ORDER_ID
                 left join pay_info_status_meta pism on pi.STATUS = pism.STATUS_CODE
                 left join shop_part_total spt on bp.PART_NO = spt.ptno
        where b.BUY_USER_ID = #{userId}
          and b.ORDER_ID = #{orderId}
        group by spt.ptno
    </select>