Mybatis之collection标签嵌套查询(select)的写法

╰+哭是因爲堅強的太久メ 2023-06-17 14:57 83阅读 0赞

业务：查询一种商品，展示该商品多种规格。

GroupDetailsVo ：

package com.community.api.vo;
    
    import java.io.Serializable;
    import java.math.BigDecimal;
    import java.util.Date;
    import java.util.List;
    
    import lombok.Data;
    
    
    /** * 团购详情vo * * @author admin * @date 2019 -11-20 15:29:51 */
    @Data
    public class GroupDetailsVo implements Serializable{ 
    
        private Long groupById;
    
        /** * 团购状态 2:未开始 3:团购中 4:已结束 */
        private Byte groupBuyStatus;
    
        /** * 主图 */
        private String mainPicture;
    
        /** * 商品名称 */
        private String name;
    
        /** * 详情 */
        private String content;
    
        /** * 配送方式 1:自提 2:上门 */
        private String deliveryType;
    
        /** *满额免费配送 */
        private BigDecimal freeDeliveryPrice;
    
        /** *已卖总数量 */
        private Integer sellNumber;
    
        /** * 原价 */
        private BigDecimal originalPrice;
    
        /** * 团购价 */
        private BigDecimal groupByPrice;
    
        /** * 开始时间 */
        private Date startTime;
    
        /** * * 结束时间 */
        private Date endTime;
    
        /** * 单、多规格详情vo */
        private List<StandardDetailsVo> standardDetailsVos;
    
    }

StandardDetailsVo ：

package com.community.api.vo;
    
    import java.io.Serializable;
    
    import lombok.Data;
    
    
    /** * 单、多规格详情vo * * @author admin * @date 2019 -11-25 09:36:39 */
    @Data
    public class StandardDetailsVo implements Serializable{ 
    
        /** * 规格id */
        private Long standardId;
    
        /** * 规格名称 */
        private String standardName;
    
    }

接口名：

/** * 获取团购详情 * @param groupBuyId * @return * @author admin * @date 2019 -11-21 17:05:20 */
        List<GroupDetailsVo> getGroupDetails (@Param("groupBuyId")Long groupBuyId);

mapper：

//collection标签内的select为本mapper内的方法名、column为查询条件的列名、property为实体类List名字，见上面实体类<单、多规格详情vo>字段名字。
    <resultMap id="StandardResultMap" type="com.community.api.vo.GroupDetailsVo">
        <result column="group_buy_status" property="groupBuyStatus"/>
        <result column="name" property="name"/>
        <result column="main_picture" property="mainPicture"/>
        <result column="content" property="content"/>
        <result column="delivery_type" property="deliveryType"/>
        <result column="free_delivery_price" property="freeDeliveryPrice"/>
        <result column="start_time" property="startTime"/>
        <result column="end_time" property="endTime"/>
        <result column="group_by_id" property="groupById"/>
        <result column="sell_number" property="sellNumber"/>
        <result column="original_price" property="originalPrice"/>
        <result column="group_by_price" property="groupByPrice"/>
        <collection property="standardDetailsVos" column="group_by_id" javaType="ArrayList"
                    ofType="com.community.api.vo.StandardDetailsVo" select="getStandardId" />
      </resultMap>
    
    <resultMap id="StandardIdMap" type="com.community.api.vo.StandardDetailsVo">
        <id property="standardId" column="standard_id" />
        <result property="standardName" column="standard_name" />
      </resultMap>
    
    
      <select id="getGroupDetails" resultMap="StandardResultMap">
        SELECT
          ANY_VALUE(a.group_buy_status) AS group_buy_status,
          ANY_VALUE(a.name) AS `name`,
          ANY_VALUE(a.main_picture) AS main_picture,
    	  ANY_VALUE(a.content) AS content,
    	  ANY_VALUE(a.delivery_type) AS delivery_type,
    	  ANY_VALUE(a.free_delivery_price) AS free_delivery_price,
          ANY_VALUE(a.start_time) AS start_time,
          ANY_VALUE(a.end_time) AS end_time,
          ANY_VALUE(gbs.group_by_id) AS group_by_id,
    	  ANY_VALUE(SUM(gbs.sell_number)) AS sell_number,
          ANY_VALUE(MIN(gbs.original_price)) AS original_price,
          ANY_VALUE(MIN(gbs.group_by_price)) AS group_by_price,
          ANY_VALUE(gbs.standard_id) AS standard_id,
          ANY_VALUE(gbs.standard_name) AS standard_name
        FROM
          group_buy a
        LEFT JOIN group_buy_standard gbs ON a.group_buy_id = gbs.group_by_id
        WHERE gbs.group_by_id = #{ groupBuyId}
        GROUP BY gbs.group_by_id
      </select>
    
      
      <select id="getStandardId" resultMap="StandardIdMap" parameterType="java.lang.Long" >
      SELECT
        standard_id,
        standard_name
      FROM group_buy_standard
      WHERE group_by_id = #{ groupBuyId}
      </select>