一、表
1.第一张表
表名:goods_sell_attribute
2.第二张表
表名:goods_sell_attribute_value
以第一张表的name_id关联
3.第三张表
表名:goods_sub_attribute_value
以第二张表的value_id关联
二、实体类
1.第一张表的实体类
// lombok
@Data
public class GoodsSellAttributeDTO {
private Integer nameId;
private Integer valueId;
private Integer firstLevelId;
private String firstLevelName;
private Integer secondLevelId;
private String secondLevelName;
private Integer thirdLevelId;
private String thirdLevelName;
private String attributeName;
private String required;
// 放入第二张表的DTO
private List<GoodsSellAttributeValueDTO> goodsSellAttributeValueDTO;
2.第二张表的实体类
@Data
public class GoodsSellAttributeValueDTO {
private Integer nameId;
private String attributeValue;
private Integer valueId;
// 放入第三张表的DTO
private List<GoodsSubAttributeValueDTO> goodsSubAttributeValueDTO;
}
3.第三张表的是实体类
@Data
public class GoodsSubAttributeValueDTO {
private Integer valueId;
private String subValue;
}
三、在xml中sql的编写
1.第一张表的新增sql
<!--第一张表的新增 -->
<insert id="insertGoodsSellAttributes" keyProperty="nameId" useGeneratedKeys="true" parameterType="com.dy.mallConfig.pojo.dto.GoodsSellAttributeDTO">
INSERT INTO goods_sell_attribute
(first_level_id,
second_level_id,
third_level_id,
first_level_name,
second_level_name,
third_level_name,
attribute_name,
required,
status)
VALUES
<foreach collection="list" separator="," item="item" index="index">
(#{item.firstLevelId},
#{item.secondLevelId},
#{item.thirdLevelId},
#{item.firstLevelName},
#{item.secondLevelName},
#{item.thirdLevelName},
#{item.attributeName},
#{item.required},
'1')
</foreach>
</insert>
2.第二张表的新增sql
<!-- 第二张表-->
<insert id="insertGoodsSellAttributeValues" keyProperty="valueId" useGeneratedKeys="true" parameterType="com.dy.mallConfig.pojo.dto.GoodsSellAttributeValueDTO">
INSERT INTO goods_sell_attribute_value
(name_id,attribute_value,status) VALUES
<foreach collection="list" index="goodsIndex" item="item" separator="),(" open="(" close=")" >
#{item.nameId},
#{item.attributeValue,jdbcType=VARCHAR},
'1'
</foreach>
</insert>
3.第三张表的新增sql
<!-- 第三张表-->
<insert id="insertGoodsSubAttributeValue" parameterType="com.dy.mallConfig.pojo.dto.GoodsSellAttributeValueDTO">
INSERT INTO goods_sub_attribute_value
(value_id,
sub_value,
status)
VALUES
<foreach collection="list" separator="," index="index" item="items">
<foreach collection="items.goodsSubAttributeValueDTO" item="items1" index="index" separator="),(" open="(" close=")">
#{items.valueId},
#{items1.subValue},
'1'
</foreach>
</foreach>
</insert>
四、mapper层
// 第一张表
int insertGoodsSellAttributes(List<GoodsSellAttributeDTO> goodsSellAttributeDTO);
// 第二张表
int insertGoodsSellAttributeValues(List<GoodsSellAttributeValueDTO> goodsSellAttributeValueDTO);
// 第三张表
int insertGoodsSubAttributeValue(List<GoodsSellAttributeValueDTO> goodsSellAttributeValueDTO);
五、service层
// 接口
OutputObject insertGoodsSellAttributes(List<GoodsSellAttributeDTO> goodsSellAttributeDTO);
六、serviceImpl层
@Service
public class GoodsSellAttributeServiceImpl implements GoodsSellAttributeService {
/**
* 添加Log日志
* @author zhushaojie
*/
private static final Logger LOGGER = LoggerFactory.getLogger(com.dy.mallConfig.service.impl.GoodsSellAttributeServiceImpl.class);
@Autowired
private GoodsSellAttributeMapper goodsSellAttributeMapper;
@Override
// 事务注解
@Transactional(rollbackFor = Exception.class)
public OutputObject insertGoodsSellAttributes(List<GoodsSellAttributeDTO> goodsSellAttributeDTO) {
try {
// 批量添加一级属性
int i = goodsSellAttributeMapper.insertGoodsSellAttributes(goodsSellAttributeDTO);
// 将商品属性和属性子属性中 抽离出来
List<GoodsSellAttributeValueDTO> goodsSellAttributeValueDTO = goodsSellAttributeDTO.stream()
.flatMap(o -> o.getGoodsSellAttributeValueDTO().stream().peek(s -> {
s.setNameId(o.getNameId());
})).collect(Collectors.toList());
if (i > 0 || goodsSellAttributeValueDTO.size()>0) {
// 一级属性添加完后,回调一级id添加到二级当中,进行新增操作
int i1 = goodsSellAttributeMapper.insertGoodsSellAttributeValues(goodsSellAttributeValueDTO);
List<List<GoodsSubAttributeValueDTO>> goodsSubAttributeValueDTO= goodsSellAttributeValueDTO.stream().map(GoodsSellAttributeValueDTO::getGoodsSubAttributeValueDTO)
.collect(Collectors.toList());
if (i1 > 0 && goodsSubAttributeValueDTO.get(0)!=null) {
// 二级属性添加完后,回调二级id添加到二级当中,进行新增操作
goodsSellAttributeMapper.insertGoodsSubAttributeValue(goodsSellAttributeValueDTO);
}
return new OutputObject(ReturnCode.SUCCESS, "添加成功", "添加成功");
}else {
return new OutputObject(ReturnCode.FAIL, "添加失败", goodsSellAttributeDTO);
}
}catch (Exception e){
// 事务回滚
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return new OutputObject(ReturnCode.FAIL, "添加失败", e.getMessage());
}
}
}
七、Controller
@RequestMapping("/insertGoodsSellAttributes")
@ResponseBody
public OutputObject insertGoodsSellAttributes(@RequestBody List<GoodsSellAttributeDTO> goodsSellAttributeDTO){
return goodsSellAttributeService.insertGoodsSellAttributes(goodsSellAttributeDTO);
}
欢迎收藏转发,有不懂的可以评论或者私聊