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

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

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>

<!--获取团购详情-->
<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>

<!--collection嵌套查询规格信息-->
<select id="getStandardId" resultMap="StandardIdMap" parameterType="java.lang.Long" >
SELECT
standard_id,
standard_name
FROM group_buy_standard
WHERE group_by_id = #{groupBuyId}
</select>
————————————————
版权声明：本文为CSDN博主「菜鸟驿站ㅤ」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_39629277/java/article/details/103310917