千峰商城-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;

 

二、dao
 
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);
        }
    }

}
复制代码

 

 

 

 

 

 

 
 
 
posted @   临易  阅读(72)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
点击右上角即可分享
微信分享提示