mybatis一对多双层嵌套查询

最近在做商城项目的时候，遇到了一个问题，就是产品分类的查询。产品分类表中有父级id，因此可以不断嵌套，查询就成了问题。翻阅资料博客，大多都是一层嵌套的联级查询，只适用于二级分类，因此三级分类，四级分类就无法查询出来了。OK，切入正题。
实体类：

@Data
@ToString
public class Goodscategory {
    private Integer id;

    private Integer pid;
}

@Data
@ToString
public class Goodc extends Goodscategory {
    private List<Goodscategory> list;
}

@Data
@ToString
public class GoodCc extends Goodscategory{
    List<Goodc> list;
}

三级分类，对应三个实体，来看mapper

public interface GoodCcMapper {
    List<GoodCc> list();
}

GoodCcMapper.xml

  <resultMap id="BaseResultMap" type="com.elite.unicom_pullnew_back.entity.Goodscategory">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="pid" jdbcType="INTEGER" property="pid" />
  </resultMap>

    <resultMap id="GoodcMap" type="com.elite.unicom_pullnew_back.entity.Goodc" extends="com.elite.unicom_pullnew_back.mapper.GoodscategoryMapper.BaseResultMap">
        <collection property="goodclist" resultMap="com.elite.unicom_pullnew_back.mapper.GoodscategoryMapper.BaseResultMap"
                    columnPrefix="child2_" >
        </collection>
    </resultMap>


    <resultMap id="GoodCcMap" type="com.elite.unicom_pullnew_back.entity.GoodCc"
               extends="com.elite.unicom_pullnew_back.mapper.GoodscategoryMapper.BaseResultMap">
        <collection property="list" resultMap="GoodcMap"
                    columnPrefix="child1_">
        </collection>
    </resultMap>



    <select id="list" resultMap="GoodCcMap">
        select
        c1.id,
        c1.name,
        c2.id child1_id,
        c2.name child1_name,
        c3.id child1_child2_id,
        c3.name child1_child2_name
        from s_goods_category c1 left join s_goods_category c2 on c1.id = c2.pid left join s_goods_category c3 on c2.id=c3.pid
        where c1.pid = 0
    </select>
</mapper>

打断点运行

image.png