sql语句
SELECT product.spu_id,ag.attr_group_id, ag.attr_group_name ,relation.attr_id,product.attr_name,product.attr_value
FROM pms_attr_group ag
LEFT JOIN pms_attr_attrgroup_relation relation ON relation.attr_group_id=ag.attr_group_id
LEFT JOIN pms_product_attr_value product ON product.attr_id=relation.attr_id
WHERE ag.catelog_id=225 AND product.spu_id=7
AttrGroupDao.xml
205集 我们需要自定义结果集
因为我们返回的类型 SpuItemAttrGroupVo 里 还有一个实体类 SpuBaseAttrVo
识别不出来 只要有嵌套属性 就要封装自定义结果集
注意 如果是内部类 在xml文件里用$代替.
比如 SkuItemVo . SpuItemAttrGroupVo 不然识别不了 也可以像老师那样把内部类抽出来和SkuItemVo 平级 但是感觉这样破坏结构了
<!--resultType 是 返回集合里元素的类型 只要有嵌套属性 就要封装自定义结果集-->
<resultMap id="SpuItemAttrGroupVo" type="com.kaki.gulimall.product.vo.SkuItemVo$SpuItemAttrGroupVo">
<result property="groupName" column="attr_group_name"></result>
<collection property="attrs" ofType="com.kaki.gulimall.product.vo.SkuItemVo$SpuBaseAttrVo">
<result property="attrName" column="attr_name"></result>
<result property="attrValue" column="attr_value"></result>
</collection>
</resultMap>
<select id="getAttrGroupWithAttrsBySpuId"
resultMap="SpuItemAttrGroupVo">
SELECT product.spu_id,ag.attr_group_id, ag.attr_group_name ,relation.attr_id,product.attr_name,product.attr_value
FROM pms_attr_group ag
LEFT JOIN pms_attr_attrgroup_relation relation ON relation.attr_group_id=ag.attr_group_id
LEFT JOIN pms_product_attr_value product ON product.attr_id=relation.attr_id
WHERE ag.catelog_id=#{catalogId} AND product.spu_id=#{spuId}
</select>
测试
com/kaki/gulimall/product/GulimallProductApplicationTests.java
@Test
public void testattrGroupDao(){
List<SkuItemVo.SpuItemAttrGroupVo> attrGroupWithAttrsBySpuId = attrGroupDao.getAttrGroupWithAttrsBySpuId(225L, 7L);
System.out.println("attrGroupWithAttrsBySpuId :"+attrGroupWithAttrsBySpuId);
}