SELECT pav.`spu_id`,ag.`attr_group_name`,ag.`attr_group_id`,aar.`attr_id`,attr.`attr_name`,pav.`attr_value` FROM pms_attr_group ag LEFT JOIN pms_attr_attrgroup_relation AS aar ON aar.`attr_group_id` = ag.`attr_group_id` LEFT JOIN pms_attr AS attr ON attr.`attr_id` = aar.`attr_id` LEFT JOIN pms_product_attr_value AS pav ON pav.`attr_id` = attr.`attr_id` WHERE ag.catelog_id = 225 AND pav.`spu_id` = 8
SELECT pav.`spu_id`,ag.`attr_group_name`,ag.`attr_group_id`,aar.`attr_id`,attr.`attr_name`,pav.`attr_value` FROM pms_attr_group ag
LEFT JOIN pms_attr_attrgroup_relation AS aar ON aar.`attr_group_id` = ag.`attr_group_id`
LEFT JOIN pms_attr AS attr ON attr.`attr_id` = aar.`attr_id`
LEFT JOIN pms_product_attr_value AS pav ON pav.`attr_id` = attr.`attr_id` WHERE ag.catelog_id = 225 AND pav.`spu_id` = 8
package com.alatus.mall.product.service.impl; import com.alatus.mall.product.dao.SkuInfoDao; import com.alatus.mall.product.entity.SkuImagesEntity; import com.alatus.mall.product.entity.SkuInfoEntity; import com.alatus.mall.product.entity.SpuInfoDescEntity; import com.alatus.mall.product.service.*; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import com.alatus.mall.product.vo.SkuItemVo; import com.alatus.mall.product.vo.SpuItemAttrGroupVo; import org.apache.commons.lang.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.math.BigDecimal; import java.util.List; import java.util.Map; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.alatus.common.utils.PageUtils; import com.alatus.common.utils.Query; @Service("pmsSkuInfoService") public class SkuInfoServiceImpl extends ServiceImpl<SkuInfoDao, SkuInfoEntity> implements SkuInfoService { @Autowired private SkuImagesService skuImagesService; @Autowired private SpuInfoDescService spuInfoDescService; @Autowired private AttrGroupService attrGroupService; @Autowired private SkuSaleAttrValueService skuSaleAttrValueService; @Override public PageUtils queryPage(Map<String, Object> params) { QueryWrapper<SkuInfoEntity> queryWrapper = new QueryWrapper<>(); String key = (String) params.get("key"); if(!StringUtils.isEmpty(key) && !"0".equalsIgnoreCase(key)){ queryWrapper.and((wrapper) -> { wrapper.eq("sku_id",key).or().like("sku_name",key); }); } String catelogId = (String) params.get("catelogId"); if(!StringUtils.isEmpty(catelogId) && !"0".equalsIgnoreCase(catelogId)){ queryWrapper.eq("catalog_id",catelogId); } String brandId = (String) params.get("brandId"); if(!StringUtils.isEmpty(brandId) && !"0".equalsIgnoreCase(brandId)){ queryWrapper.eq("brand_id",brandId); } String min = (String) params.get("min"); if(!StringUtils.isEmpty(min) && !"0".equalsIgnoreCase(min)){ queryWrapper.ge("price",min); } String max = (String) params.get("max"); if(!StringUtils.isEmpty(max) && !"0".equalsIgnoreCase(max)){ try{ BigDecimal maxPrice = new BigDecimal(max); if(maxPrice.compareTo(new BigDecimal("0")) == 1){ queryWrapper.le("price",max); } } catch (Exception e){ e.printStackTrace(); } } IPage<SkuInfoEntity> page = this.page( new Query<SkuInfoEntity>().getPage(params), queryWrapper ); return new PageUtils(page); } @Override public void saveSkuInfo(SkuInfoEntity skuInfoEntity) { this.baseMapper.insert(skuInfoEntity); } @Override public List<SkuInfoEntity> getSkusBySpuId(Long spuId) { return this.list(new QueryWrapper<SkuInfoEntity>().eq("spu_id",spuId)); } @Override public SkuItemVo item(Long skuId) { SkuItemVo skuItemVo = new SkuItemVo(); // 设置基本信息 SkuInfoEntity skuInfoEntity = getById(skuId); Long catalogId = skuInfoEntity.getCatalogId(); Long spuId = skuInfoEntity.getSpuId(); skuItemVo.setInfo(skuInfoEntity); // 设置spu的销售属性组合 List<SkuItemSaleAttrVo> skuItemSaleAttrVos = skuSaleAttrValueService.getSaleAttrsBySpuId(spuId); skuItemVo.setSaleAttr(skuItemSaleAttrVos); // 设置sku的图片信息 List<SkuImagesEntity> images = skuImagesService.getBySkuId(skuId); skuItemVo.setImages(images); // 获取spu的介绍 SpuInfoDescEntity spuInfoDesc = spuInfoDescService.getById(spuId); skuItemVo.setDesc(spuInfoDesc); // 获取SPU的规格参数信息 List<SpuItemAttrGroupVo> attrGroupVos = attrGroupService.getAttrGroupWithAttrsBySpuId(spuId,catalogId); skuItemVo.setGroupAttrs(attrGroupVos); return skuItemVo; } }
package com.alatus.mall.product.service.impl; import com.alatus.mall.product.dao.SkuInfoDao; import com.alatus.mall.product.entity.SkuImagesEntity; import com.alatus.mall.product.entity.SkuInfoEntity; import com.alatus.mall.product.entity.SpuInfoDescEntity; import com.alatus.mall.product.service.*; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import com.alatus.mall.product.vo.SkuItemVo; import com.alatus.mall.product.vo.SpuItemAttrGroupVo; import org.apache.commons.lang.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.math.BigDecimal; import java.util.List; import java.util.Map; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.alatus.common.utils.PageUtils; import com.alatus.common.utils.Query; @Service("pmsSkuInfoService") public class SkuInfoServiceImpl extends ServiceImpl<SkuInfoDao, SkuInfoEntity> implements SkuInfoService { @Autowired private SkuImagesService skuImagesService; @Autowired private SpuInfoDescService spuInfoDescService; @Autowired private AttrGroupService attrGroupService; @Autowired private SkuSaleAttrValueService skuSaleAttrValueService; @Override public PageUtils queryPage(Map<String, Object> params) { QueryWrapper<SkuInfoEntity> queryWrapper = new QueryWrapper<>(); String key = (String) params.get("key"); if(!StringUtils.isEmpty(key) && !"0".equalsIgnoreCase(key)){ queryWrapper.and((wrapper) -> { wrapper.eq("sku_id",key).or().like("sku_name",key); }); } String catelogId = (String) params.get("catelogId"); if(!StringUtils.isEmpty(catelogId) && !"0".equalsIgnoreCase(catelogId)){ queryWrapper.eq("catalog_id",catelogId); } String brandId = (String) params.get("brandId"); if(!StringUtils.isEmpty(brandId) && !"0".equalsIgnoreCase(brandId)){ queryWrapper.eq("brand_id",brandId); } String min = (String) params.get("min"); if(!StringUtils.isEmpty(min) && !"0".equalsIgnoreCase(min)){ queryWrapper.ge("price",min); } String max = (String) params.get("max"); if(!StringUtils.isEmpty(max) && !"0".equalsIgnoreCase(max)){ try{ BigDecimal maxPrice = new BigDecimal(max); if(maxPrice.compareTo(new BigDecimal("0")) == 1){ queryWrapper.le("price",max); } } catch (Exception e){ e.printStackTrace(); } } IPage<SkuInfoEntity> page = this.page( new Query<SkuInfoEntity>().getPage(params), queryWrapper ); return new PageUtils(page); } @Override public void saveSkuInfo(SkuInfoEntity skuInfoEntity) { this.baseMapper.insert(skuInfoEntity); } @Override public List<SkuInfoEntity> getSkusBySpuId(Long spuId) { return this.list(new QueryWrapper<SkuInfoEntity>().eq("spu_id",spuId)); } @Override public SkuItemVo item(Long skuId) { SkuItemVo skuItemVo = new SkuItemVo(); // 设置基本信息 SkuInfoEntity skuInfoEntity = getById(skuId); Long catalogId = skuInfoEntity.getCatalogId(); Long spuId = skuInfoEntity.getSpuId(); skuItemVo.setInfo(skuInfoEntity); // 设置spu的销售属性组合 List<SkuItemSaleAttrVo> skuItemSaleAttrVos = skuSaleAttrValueService.getSaleAttrsBySpuId(spuId); skuItemVo.setSaleAttr(skuItemSaleAttrVos); // 设置sku的图片信息 List<SkuImagesEntity> images = skuImagesService.getBySkuId(skuId); skuItemVo.setImages(images); // 获取spu的介绍 SpuInfoDescEntity spuInfoDesc = spuInfoDescService.getById(spuId); skuItemVo.setDesc(spuInfoDesc); // 获取SPU的规格参数信息 List<SpuItemAttrGroupVo> attrGroupVos = attrGroupService.getAttrGroupWithAttrsBySpuId(spuId,catalogId); skuItemVo.setGroupAttrs(attrGroupVos); return skuItemVo; } }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alatus.mall.product.dao.AttrGroupDao"> <!-- 可根据自己的需求,是否要使用 --> <resultMap type="com.alatus.mall.product.entity.AttrGroupEntity" id="attrGroupMap"> <result property="attrGroupId" column="attr_group_id"/> <result property="attrGroupName" column="attr_group_name"/> <result property="sort" column="sort"/> <result property="descript" column="descript"/> <result property="icon" column="icon"/> <result property="catelogId" column="catelog_id"/> </resultMap> <!-- 只要有嵌套的属性,就一定要封装自定义结果集--> <resultMap id="SpuItemAttrGroupVo" type="com.alatus.mall.product.vo.SpuItemAttrGroupVo"> <result property="groupName" column="attr_group_name"></result> <collection property="attrs" ofType="com.alatus.mall.product.vo.Attr"> <result column="attr_id" property="attrId"></result> <result column="attr_name" property="attrName"></result> <result column="attr_value" property="attrValue"></result> </collection> </resultMap> <select id="getAttrGroupWithAttrsBySpuId" resultMap="SpuItemAttrGroupVo"> SELECT pav.`spu_id`,ag.`attr_group_name`,ag.`attr_group_id`,aar.`attr_id`, attr.`attr_name`,pav.`attr_value` FROM pms_attr_group ag LEFT JOIN pms_attr_attrgroup_relation AS aar ON aar.`attr_group_id` = ag.`attr_group_id` LEFT JOIN pms_attr AS attr ON attr.`attr_id` = aar.`attr_id` LEFT JOIN pms_product_attr_value AS pav ON pav.`attr_id` = attr.`attr_id` WHERE ag.catelog_id = #{catalogId} AND pav.`spu_id` = #{spuId} </select> </mapper>
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alatus.mall.product.dao.AttrGroupDao"> <!-- 可根据自己的需求,是否要使用 --> <resultMap type="com.alatus.mall.product.entity.AttrGroupEntity" id="attrGroupMap"> <result property="attrGroupId" column="attr_group_id"/> <result property="attrGroupName" column="attr_group_name"/> <result property="sort" column="sort"/> <result property="descript" column="descript"/> <result property="icon" column="icon"/> <result property="catelogId" column="catelog_id"/> </resultMap> <!-- 只要有嵌套的属性,就一定要封装自定义结果集--> <resultMap id="SpuItemAttrGroupVo" type="com.alatus.mall.product.vo.SpuItemAttrGroupVo"> <result property="groupName" column="attr_group_name"></result> <collection property="attrs" ofType="com.alatus.mall.product.vo.Attr"> <result column="attr_id" property="attrId"></result> <result column="attr_name" property="attrName"></result> <result column="attr_value" property="attrValue"></result> </collection> </resultMap> <select id="getAttrGroupWithAttrsBySpuId" resultMap="SpuItemAttrGroupVo"> SELECT pav.`spu_id`,ag.`attr_group_name`,ag.`attr_group_id`,aar.`attr_id`, attr.`attr_name`,pav.`attr_value` FROM pms_attr_group ag LEFT JOIN pms_attr_attrgroup_relation AS aar ON aar.`attr_group_id` = ag.`attr_group_id` LEFT JOIN pms_attr AS attr ON attr.`attr_id` = aar.`attr_id` LEFT JOIN pms_product_attr_value AS pav ON pav.`attr_id` = attr.`attr_id` WHERE ag.catelog_id = #{catalogId} AND pav.`spu_id` = #{spuId} </select> </mapper>
package com.alatus.mall.product.vo; import lombok.Data; import java.util.List; @Data public class SpuItemAttrGroupVo{ private String groupName; private List<Attr> attrs; }
package com.alatus.mall.product.vo; import lombok.Data; import java.util.List; @Data public class SpuItemAttrGroupVo{ private String groupName; private List<Attr> attrs; }
package com.alatus.mall.product.service; import com.alatus.mall.product.entity.SkuSaleAttrValueEntity; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import com.baomidou.mybatisplus.extension.service.IService; import com.alatus.common.utils.PageUtils; import java.util.List; import java.util.Map; /** * sku销售属性&值 * * @author Alatus * @email 1571345941@qq.com * @date 2024-06-08 16:06:24 */ public interface SkuSaleAttrValueService extends IService<SkuSaleAttrValueEntity> { PageUtils queryPage(Map<String, Object> params); List<SkuItemSaleAttrVo> getSaleAttrsBySpuId(Long spuId); }
package com.alatus.mall.product.service; import com.alatus.mall.product.entity.SkuSaleAttrValueEntity; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import com.baomidou.mybatisplus.extension.service.IService; import com.alatus.common.utils.PageUtils; import java.util.List; import java.util.Map; /** * sku销售属性&值 * * @author Alatus * @email 1571345941@qq.com * @date 2024-06-08 16:06:24 */ public interface SkuSaleAttrValueService extends IService<SkuSaleAttrValueEntity> { PageUtils queryPage(Map<String, Object> params); List<SkuItemSaleAttrVo> getSaleAttrsBySpuId(Long spuId); }
package com.alatus.mall.product.service.impl; import com.alatus.mall.product.dao.SkuSaleAttrValueDao; import com.alatus.mall.product.entity.SkuSaleAttrValueEntity; import com.alatus.mall.product.service.SkuSaleAttrValueService; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import org.springframework.stereotype.Service; import java.util.List; import java.util.Map; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.alatus.common.utils.PageUtils; import com.alatus.common.utils.Query; @Service("pmsSkuSaleAttrValueService") public class SkuSaleAttrValueServiceImpl extends ServiceImpl<SkuSaleAttrValueDao, SkuSaleAttrValueEntity> implements SkuSaleAttrValueService { @Override public PageUtils queryPage(Map<String, Object> params) { IPage<SkuSaleAttrValueEntity> page = this.page( new Query<SkuSaleAttrValueEntity>().getPage(params), new QueryWrapper<SkuSaleAttrValueEntity>() ); return new PageUtils(page); } @Override public List<SkuItemSaleAttrVo> getSaleAttrsBySpuId(Long spuId) { List<SkuItemSaleAttrVo> skuItemSaleAttrVos = this.baseMapper.getSaleAttrsBySpuId(spuId); return skuItemSaleAttrVos; } }
package com.alatus.mall.product.service.impl; import com.alatus.mall.product.dao.SkuSaleAttrValueDao; import com.alatus.mall.product.entity.SkuSaleAttrValueEntity; import com.alatus.mall.product.service.SkuSaleAttrValueService; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import org.springframework.stereotype.Service; import java.util.List; import java.util.Map; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.alatus.common.utils.PageUtils; import com.alatus.common.utils.Query; @Service("pmsSkuSaleAttrValueService") public class SkuSaleAttrValueServiceImpl extends ServiceImpl<SkuSaleAttrValueDao, SkuSaleAttrValueEntity> implements SkuSaleAttrValueService { @Override public PageUtils queryPage(Map<String, Object> params) { IPage<SkuSaleAttrValueEntity> page = this.page( new Query<SkuSaleAttrValueEntity>().getPage(params), new QueryWrapper<SkuSaleAttrValueEntity>() ); return new PageUtils(page); } @Override public List<SkuItemSaleAttrVo> getSaleAttrsBySpuId(Long spuId) { List<SkuItemSaleAttrVo> skuItemSaleAttrVos = this.baseMapper.getSaleAttrsBySpuId(spuId); return skuItemSaleAttrVos; } }
package com.alatus.mall.product.dao; import com.alatus.mall.product.entity.SkuSaleAttrValueEntity; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; /** * sku销售属性&值 * * @author Alatus * @email 1571345941@qq.com * @date 2024-06-08 16:06:24 */ @Mapper public interface SkuSaleAttrValueDao extends BaseMapper<SkuSaleAttrValueEntity> { List<SkuItemSaleAttrVo> getSaleAttrsBySpuId(@Param("spuId") Long spuId); }
package com.alatus.mall.product.dao; import com.alatus.mall.product.entity.SkuSaleAttrValueEntity; import com.alatus.mall.product.vo.SkuItemSaleAttrVo; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; /** * sku销售属性&值 * * @author Alatus * @email 1571345941@qq.com * @date 2024-06-08 16:06:24 */ @Mapper public interface SkuSaleAttrValueDao extends BaseMapper<SkuSaleAttrValueEntity> { List<SkuItemSaleAttrVo> getSaleAttrsBySpuId(@Param("spuId") Long spuId); }
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alatus.mall.product.dao.SkuSaleAttrValueDao"> <!-- 可根据自己的需求,是否要使用 --> <resultMap type="com.alatus.mall.product.entity.SkuSaleAttrValueEntity" id="skuSaleAttrValueMap"> <result property="id" column="id"/> <result property="skuId" column="sku_id"/> <result property="attrId" column="attr_id"/> <result property="attrName" column="attr_name"/> <result property="attrValue" column="attr_value"/> <result property="attrSort" column="attr_sort"/> </resultMap> <select id="getSaleAttrsBySpuId" resultType="com.alatus.mall.product.vo.SkuItemSaleAttrVo"> </select> </mapper>
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alatus.mall.product.dao.SkuSaleAttrValueDao"> <!-- 可根据自己的需求,是否要使用 --> <resultMap type="com.alatus.mall.product.entity.SkuSaleAttrValueEntity" id="skuSaleAttrValueMap"> <result property="id" column="id"/> <result property="skuId" column="sku_id"/> <result property="attrId" column="attr_id"/> <result property="attrName" column="attr_name"/> <result property="attrValue" column="attr_value"/> <result property="attrSort" column="attr_sort"/> </resultMap> <select id="getSaleAttrsBySpuId" resultType="com.alatus.mall.product.vo.SkuItemSaleAttrVo"> </select> </mapper>