千峰商城-springboot项目搭建-67-商品评论-数据库查询实现

一、数据库实现

1.数据表分析及数据准备

2.SQL(关联用户和评论信息)

#查询商品的评价信息，关联查询评价用户的信息
SELECT c.*,u.nickname,u.user_img,u.username 
FROM product_comments c 
INNER JOIN users u
ON u.user_id = c.user_id
WHERE c.product_id=3;

1.实体类封装：

ProductCommentsVO ：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProductCommentsVO {
 
    private String commId;
    private String productId;
    private String productName;
    private String orderItemId;
   
    private Integer isAnonymous;
    private Integer commType;
    private Integer commLevel;
    private String commContent;
    private String commImgs;
    private Date sepcName;
    private Integer replyStatus;
    private String replyContent;
    private Date replyTime;
    private Integer isShow;
    //添加属性用于评论对应的用户数据
    private String userId;
    private String username;
    private String nickname;
    private String userImg;

}

2.在mapper接口定义查询方法：

ProductCommentsMapper ：

@Repository
public interface ProductCommentsMapper extends GeneralDAO<ProductComments> {
    public List<ProductCommentsVO> selectCommontsByProductId(String productId);
}

3.映射配置：

<?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.qfedu.fmmall.dao.ProductCommentsMapper">
  <resultMap id="BaseResultMap" type="com.qfedu.fmmall.entity.ProductComments">

    <id column="comm_id" jdbcType="VARCHAR" property="commId" />
    <result column="product_id" jdbcType="VARCHAR" property="productId" />
    <result column="product_name" jdbcType="VARCHAR" property="productName" />
    <result column="order_item_id" jdbcType="VARCHAR" property="orderItemId" />
    <result column="user_id" jdbcType="VARCHAR" property="userId" />
    <result column="is_anonymous" jdbcType="INTEGER" property="isAnonymous" />
    <result column="comm_type" jdbcType="INTEGER" property="commType" />
    <result column="comm_level" jdbcType="INTEGER" property="commLevel" />
    <result column="comm_content" jdbcType="VARCHAR" property="commContent" />
    <result column="comm_imgs" jdbcType="VARCHAR" property="commImgs" />
    <result column="sepc_name" jdbcType="TIMESTAMP" property="sepcName" />
    <result column="reply_status" jdbcType="INTEGER" property="replyStatus" />
    <result column="reply_content" jdbcType="VARCHAR" property="replyContent" />
    <result column="reply_time" jdbcType="TIMESTAMP" property="replyTime" />
    <result column="is_show" jdbcType="INTEGER" property="isShow" />
  </resultMap>

  <resultMap id="ProductCommentsVOMap" type="com.qfedu.fmmall.entity.ProductCommentsVO">

    <id column="comm_id" jdbcType="VARCHAR" property="commId" />
    <result column="product_id" jdbcType="VARCHAR" property="productId" />
    <result column="product_name" jdbcType="VARCHAR" property="productName" />
    <result column="order_item_id" jdbcType="VARCHAR" property="orderItemId" />
    <result column="is_anonymous" jdbcType="INTEGER" property="isAnonymous" />
    <result column="comm_type" jdbcType="INTEGER" property="commType" />
    <result column="comm_level" jdbcType="INTEGER" property="commLevel" />
    <result column="comm_content" jdbcType="VARCHAR" property="commContent" />
    <result column="comm_imgs" jdbcType="VARCHAR" property="commImgs" />
    <result column="sepc_name" jdbcType="TIMESTAMP" property="sepcName" />
    <result column="reply_status" jdbcType="INTEGER" property="replyStatus" />
    <result column="reply_content" jdbcType="VARCHAR" property="replyContent" />
    <result column="reply_time" jdbcType="TIMESTAMP" property="replyTime" />
    <result column="is_show" jdbcType="INTEGER" property="isShow" />

    <result column="user_id" jdbcType="VARCHAR" property="userId" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="nickname" jdbcType="VARCHAR" property="nickname" />
    <result column="user_img" jdbcType="VARCHAR" property="userImg" />
    
  </resultMap>

  <select id="selectCommontsByProductId" resultMap="ProductCommentsVOMap">
    SELECT u.nickname,
           u.user_img,
           u.username,
           c.comm_id,
           c.product_id,
           c.product_name,
           c.order_item_id,
           c.user_id,
           c.is_anonymous,
           c.comm_type,
           c.comm_level,
           c.comm_content,
           c.comm_imgs,
           c.sepc_name,
           c.reply_status,
           c.reply_content,
           c.reply_time,
           c.is_show
    FROM product_comments c
    INNER JOIN users u
    ON u.user_id = c.user_id
    WHERE c.product_id=#{productId};
  </select>
</mapper>

4.测试：

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ApiApplication.class)

public class ApiApplicationTests {

    @Autowired
    private CategoryMapper categoryMapper;

    @Autowired
    private ProductMapper productMapper;

    @Autowired
    private ProductCommentsMapper productCommentsMapper;


    @Test
    public void contextLoads() {
        List<CategoryVO> categoryVOS = categoryMapper.selectAllCategories2(0);
        for (CategoryVO c1:categoryVOS){
            System.out.println(c1);
            for (CategoryVO c2: c1.getCategories()){
                System.out.println("\t"+c2);
                for (CategoryVO c3: c2.getCategories()){
                    System.out.println("\t\t"+c3);

                }
            }
        }
    }

    @Test
    public void testRecommend(){
        List<ProductVO> productVOS = productMapper.selectRecommendProducts();
        for (ProductVO p:productVOS) {
            System.out.println(p);
        }
    }

    @Test
    public void testSelectFirstLevelCategory(){
        List<CategoryVO> categoryVOS = categoryMapper.selectFirstLevelCategories();
        for (CategoryVO categoryVO:categoryVOS){
            System.out.println(categoryVO);
        }
    }

    @Test
    public void testSelectComments(){
        List<ProductCommentsVO> productCommentsVOS = productCommentsMapper.selectCommontsByProductId("3");
        for (ProductCommentsVO p:productCommentsVOS){
            System.out.println(p);
        }
    }

}