商品评价等级数量查询
一、数据库结构
create table `foodie-shop-dev`.items_comments
(
id varchar(64) not null comment 'id主键'
primary key,
user_id varchar(64) null comment '用户id 用户名须脱敏',
item_id varchar(64) not null comment '商品id',
item_name varchar(64) null comment '商品名称',
item_spec_id varchar(64) null comment '商品规格id 可为空',
sepc_name varchar(32) null comment '规格名称 可为空',
comment_level int not null comment '评价等级 1:好评 2:中评 3:差评',
content varchar(128) not null comment '评价内容',
created_time datetime null comment '创建时间',
updated_time datetime null comment '更新时间'
)
comment '商品评价表 ' charset = utf8mb4;
商品评价表 items_comments
二、Service模块
1.接口定义
路径: com/imooc/service/ItemService.java
package com.imooc.service;
import com.imooc.pojo.Items;
import com.imooc.pojo.ItemsImg;
import com.imooc.pojo.ItemsParam;
import com.imooc.pojo.ItemsSpec;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import java.util.List;
public interface ItemService {
/**
* 根据商品ID查询详情
* @param itemId
* @return
*/
public Items queryItemById(String itemId);
/**
* 根据商品ID查询图片列表
* @param itemId
* @return
*/
public List<ItemsImg> queryItemImgList(String itemId);
/**
* 根据商品ID查询商品规格列表
* @param itemId
* @return
*/
public List<ItemsSpec> queryItemSpecList(String itemId);
/**
* 根据商品ID查询商品参数
* @param itemId
* @return
*/
public ItemsParam queryItemParam(String itemId);
/**
* 根据商品id查询商品的评价等级数量
* @param itemId
*/
public CommentLevelCountsVO queryItemCommentCounts(String itemId);
}
二、Service模块
1.接口定义
路径: com/imooc/service/ItemService.java
package com.imooc.service;
import com.imooc.pojo.Items;
import com.imooc.pojo.ItemsImg;
import com.imooc.pojo.ItemsParam;
import com.imooc.pojo.ItemsSpec;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import java.util.List;
public interface ItemService {
/**
* 根据商品ID查询详情
* @param itemId
* @return
*/
public Items queryItemById(String itemId);
/**
* 根据商品ID查询图片列表
* @param itemId
* @return
*/
public List<ItemsImg> queryItemImgList(String itemId);
/**
* 根据商品ID查询商品规格列表
* @param itemId
* @return
*/
public List<ItemsSpec> queryItemSpecList(String itemId);
/**
* 根据商品ID查询商品参数
* @param itemId
* @return
*/
public ItemsParam queryItemParam(String itemId);
/**
* 根据商品id查询商品的评价等级数量
* @param itemId
*/
public CommentLevelCountsVO queryItemCommentCounts(String itemId);
}
2.VO定义 (返回到前端)
路径:com/imooc/pojo/vo/CommentLevelCountsVO.java
接口方法:queryItemCommentCounts
package com.imooc.pojo.vo;
/**
*
*/
public class CommentLevelCountsVO {
public Integer totalCounts;
public Integer goodCounts;
public Integer normalCounts;
public Integer badCounts;
public Integer getTotalCounts() {
return totalCounts;
}
public void setTotalCounts(Integer totalCounts) {
this.totalCounts = totalCounts;
}
public Integer getGoodCounts() {
return goodCounts;
}
public void setGoodCounts(Integer goodCounts) {
this.goodCounts = goodCounts;
}
public Integer getNormalCounts() {
return normalCounts;
}
public void setNormalCounts(Integer normalCounts) {
this.normalCounts = normalCounts;
}
public Integer getBadCounts() {
return badCounts;
}
public void setBadCounts(Integer badCounts) {
this.badCounts = badCounts;
}
}
3、接口实现
路径:com/imooc/service/impl/ItemServiceImpl.java
方法:queryItemCommentCounts
package com.imooc.service.impl;
import com.imooc.enums.CommentLevel;
import com.imooc.mapper.*;
import com.imooc.pojo.*;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.service.ItemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;
import java.util.List;
@Service
public class ItemServiceImpl implements ItemService {
@Autowired
ItemsMapper itemsMapper;
@Autowired
ItemsImgMapper itemsImgMapper;
@Autowired
ItemsSpecMapper itemsSpecMapper;
@Autowired
ItemsParamMapper itemsParamMapper;
@Autowired
ItemsCommentsMapper itemsCommentsCommentsMapper;
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public Items queryItemById(String itemId) {
return itemsMapper.selectByPrimaryKey(itemId);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<ItemsImg> queryItemImgList(String itemId) {
Example itemsImgExp = new Example(ItemsImg.class);
Example.Criteria criteria =itemsImgExp.createCriteria();
criteria.andEqualTo("itemId",itemId);
return itemsImgMapper.selectByExample(itemsImgExp);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<ItemsSpec> queryItemSpecList(String itemId) {
Example itemsSpecExp = new Example(ItemsSpec.class);
Example.Criteria criteria =itemsSpecExp.createCriteria();
criteria.andEqualTo("itemId",itemId);
return itemsSpecMapper.selectByExample(itemsSpecExp);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public ItemsParam queryItemParam(String itemId) {
Example itemsParamExp = new Example(ItemsParam.class);
Example.Criteria criteria =itemsParamExp.createCriteria();
criteria.andEqualTo("itemId",itemId);
return itemsParamMapper.selectOneByExample(itemsParamExp);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public CommentLevelCountsVO queryItemCommentCounts(String itemId) {
//Integer totalCounts=getCommentCounts(itemId);
Integer goodCounts=getCommentCounts(itemId, CommentLevel.Good.type);
Integer normalCounts=getCommentCounts(itemId, CommentLevel.NORMAL.type);
Integer badCounts=getCommentCounts(itemId, CommentLevel.BAD.type);
Integer totalCounts=goodCounts+normalCounts+badCounts;
CommentLevelCountsVO commentLevelCountsVO=new CommentLevelCountsVO();
commentLevelCountsVO.setTotalCounts(totalCounts);
commentLevelCountsVO.setGoodCounts(goodCounts);
commentLevelCountsVO.setNormalCounts(normalCounts);
commentLevelCountsVO.setBadCounts(badCounts);
return commentLevelCountsVO;
}
@Transactional(propagation = Propagation.SUPPORTS)
Integer getCommentCounts(String itemId,Integer level){
ItemsComments confdition =new ItemsComments();
confdition.setItemId(itemId);
if (level != null) {
confdition.setCommentLevel(level);
}
return itemsCommentsCommentsMapper.selectCount(confdition);
}
}
三、Api模块实现
路径:com/imooc/service/impl/ItemServiceImpl.java
方法:queryItemCommentCounts
参数是 @RequestParam, 不是路径参数 /{itemId}
package com.imooc.service.impl;
import com.imooc.enums.CommentLevel;
import com.imooc.mapper.*;
import com.imooc.pojo.*;
import com.imooc.pojo.vo.CommentLevelCountsVO;
import com.imooc.service.ItemService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;
import java.util.List;
@Service
public class ItemServiceImpl implements ItemService {
@Autowired
ItemsMapper itemsMapper;
@Autowired
ItemsImgMapper itemsImgMapper;
@Autowired
ItemsSpecMapper itemsSpecMapper;
@Autowired
ItemsParamMapper itemsParamMapper;
@Autowired
ItemsCommentsMapper itemsCommentsCommentsMapper;
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public Items queryItemById(String itemId) {
return itemsMapper.selectByPrimaryKey(itemId);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<ItemsImg> queryItemImgList(String itemId) {
Example itemsImgExp = new Example(ItemsImg.class);
Example.Criteria criteria =itemsImgExp.createCriteria();
criteria.andEqualTo("itemId",itemId);
return itemsImgMapper.selectByExample(itemsImgExp);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public List<ItemsSpec> queryItemSpecList(String itemId) {
Example itemsSpecExp = new Example(ItemsSpec.class);
Example.Criteria criteria =itemsSpecExp.createCriteria();
criteria.andEqualTo("itemId",itemId);
return itemsSpecMapper.selectByExample(itemsSpecExp);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public ItemsParam queryItemParam(String itemId) {
Example itemsParamExp = new Example(ItemsParam.class);
Example.Criteria criteria =itemsParamExp.createCriteria();
criteria.andEqualTo("itemId",itemId);
return itemsParamMapper.selectOneByExample(itemsParamExp);
}
@Transactional(propagation = Propagation.SUPPORTS)
@Override
public CommentLevelCountsVO queryItemCommentCounts(String itemId) {
//Integer totalCounts=getCommentCounts(itemId);
Integer goodCounts=getCommentCounts(itemId, CommentLevel.Good.type);
Integer normalCounts=getCommentCounts(itemId, CommentLevel.NORMAL.type);
Integer badCounts=getCommentCounts(itemId, CommentLevel.BAD.type);
Integer totalCounts=goodCounts+normalCounts+badCounts;
CommentLevelCountsVO commentLevelCountsVO=new CommentLevelCountsVO();
commentLevelCountsVO.setTotalCounts(totalCounts);
commentLevelCountsVO.setGoodCounts(goodCounts);
commentLevelCountsVO.setNormalCounts(normalCounts);
commentLevelCountsVO.setBadCounts(badCounts);
return commentLevelCountsVO;
}
@Transactional(propagation = Propagation.SUPPORTS)
Integer getCommentCounts(String itemId,Integer level){
ItemsComments confdition =new ItemsComments();
confdition.setItemId(itemId);
if (level != null) {
confdition.setCommentLevel(level);
}
return itemsCommentsCommentsMapper.selectCount(confdition);
}
}