MyBatis 使用一对多查询一级分类下的所有二级分类以及子分类

实体类

/**
 * 小美团：电商首页的三级分类列表
 * @author songjiaming
 * @date 2021/12/6 11:06 上午
 */
@Data
public class EcoHomeThirdCategoryListView {
    @ApiModelProperty("分类id")
    private Long id;
    @ApiModelProperty("分类名称")
    private String name;
    @ApiModelProperty("分类图标")
    private String icon;
}

/**
 * 小美团：电商首页更多分类（二级+三级）
 *
 * @author songjiaming
 * @date 2021/12/6 11:10 上午
 */
@Data
@JsonIgnoreProperties(value = { "hibernateLazyInitializer", "handler" })
public class EcoMoreCategoryListView {
    @ApiModelProperty("二级id")
    private Long id;
    @ApiModelProperty("二级name")
    private String name;
    @ApiModelProperty("分类列表")
    private List<EcoHomeThirdCategoryListView> children;
}

DAO

@Mapper
public interface PmsProductCategoryMapper extends BaseMapper<PmsProductCategory> {    
    /**
     * 小美团/电商：获取商品分类及其子分类
     */
    @Select("<script> " +
            " select id,name" +
            "        from pms_product_category  " +
            "        where  company_id = #{companyId} and  parent_id = #{parentId} and show_status=1 " +
            " <if test='mchId != null'> and mch_id = #{mchId} </if>" +
            "</script>")
    @Results(value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "id",
                    property = "children",
                    many = @Many(
                            select = "net.super0.merchant.meta.mapper.PmsProductCategoryMapper.getByParentIdByRetail",
                            fetchType = FetchType.LAZY
                    ))
    })
    List<EcoMoreCategoryListView> listWithChildrenByRetail(Integer companyId, Long mchId, Long parentId);
    /**
     * 小美团/电商：查询子分类
     * @param parentId
     * @return
     */
    @Select("<script>" +
            "select id,name,icon from pms_product_category where parent_id = #{parentId} and show_status=1 " +
            "order by sort" +
            "</script>")
    List<EcoHomeThirdCategoryListView> getByParentIdByRetail(Long parentId);
}