实战 Java 第9天：统计各类商品数量

ゝ一世哀愁。 2022-12-24 15:47 211阅读 0赞

### 实战 Java 第9天：统计各类商品数量 ###

*   *   *  前言
         *  一、在 ProductService 类中添加接口
         *  二、在 ProductMapper 类中添加接口
         *  三、增加 sql 语句
         *  四、在 ProductController 类中添加业务逻辑
         *  五、测试接口是否成功
         *  六、总结

### 前言 ###

对商品进行分类统计也是很常见的业务场景。今天开始编写统计各类商品数量接口。本文的内容只是业务逻辑，完整的项目需结合前面的内容一起看。

### 一、在 ProductService 类中添加接口 ###

*  在 ProductService 类中添加 statisticProductNum 接口，实现统计各类商品数量。

package com.dingding.service;
    import com.dingding.entity.Product;
    import java.util.List;
    import java.util.Map;
    
    /** * Created by xpwu on 2019/7/10. */
    public interface ProductService { 
        int addProduct(Product product);
        List<Product> getProductList();
        List<Product> getProductByKey(String productName);
        List<Product> getProductByCondition(String productName,int productType);
        int updateProduct(@Param("pro") Product product);
        int deleteProduct(int productId);
        Product getProductDetailById(int productId);
        List<Map<String, Object>> statisticProductNum();
    }

*  在 ProductServiceImpl 类中添加实现。

package com.dingding.service.impl;
    import com.dingding.entity.Product;
    import com.dingding.mapper.ProductMapper;
    import com.dingding.service.ProductService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    
    /** * Created by xpwu on 2019/7/10. */
    @Service
    public class ProductServiceImpl implements ProductService { 
        @Autowired
        ProductMapper productMapper;
        public int addProduct(Product product){ 
            int count = 0;
            try { 
                count = productMapper.addProduct(product);
            }catch (Exception err){ 
               System.out.println(err);
            }
            return count;
        }
        public List<Product> getProductList(){ 
            List<Product> proList = productMapper.getProductList();
            return  proList;
        }
        public List<Product> getProductByKey(String productName){ 
            List<Product> proList1 = productMapper.getProductByKey(productName);
            return  proList1;
        }
        public List<Product> getProductByCondition(String productName,int productType){ 
            List<Product> proList2 = productMapper.getProductByCondition(productName,productType);
            return  proList2;
        }
        public int updateProduct(Product product){ 
            int count = 0;
            try { 
                count = productMapper.updateProduct(product);
            }catch (Exception err){ 
                System.out.println(err);
            }
            return count;
        }
        public int deleteProduct(int productId){ 
            int count = 0;
            try { 
                count = productMapper.deleteProduct(productId);
            }catch (Exception err){ 
                System.out.println(err);
            }
            return count;
        }
        public Product getProductDetailById(int productId) { 
    		return productMapper.getProductDetailById(productId);
    	}
        public List<Map<String, Object>> statisticProductNum() { 
            return productMapper.statisticProductNum();
        }
    }

### 二、在 ProductMapper 类中添加接口 ###

在 ProductMapper 类中添加 statisticProductNum 接口。

package com.dingding.mapper;
    import com.dingding.entity.Product;
    import org.springframework.stereotype.Repository;
    import java.util.List;
    import java.util.Map;
    
    /** * Created by xpwu on 2019/7/10. */
    @Repository
    public interface ProductMapper { 
        int addProduct(Product product);
        List<Product> getProductList();
        List<Product>getProductByKey(String productName);
        List<Product>getProductByCondition(String productName,int productType);
        int updateProduct(@Param("pro") Product product);
        int deleteProduct(int productId);
        Product getProductDetailById(int productId);
        List<Map<String, Object>> statisticProductNum();
    }

### 三、增加 sql 语句 ###

添加 statisticProductNum 的查询语句。

<?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.dingding.mapper.ProductMapper">
        <resultMap id="BaseResultMap" type="com.dingding.entity.Product">
            <result column="product_id" jdbcType="VARCHAR" property="productId" />
            <result column="product_name" jdbcType="VARCHAR" property="productName" />
            <result column="product_price" jdbcType="DOUBLE" property="productPrice" />
            <result column="product_type" jdbcType="INTEGER" property="productType" />
            <result column="product_img" jdbcType="VARCHAR" property="productImg" />
            <result column="product_des" jdbcType="VARCHAR" property="productDes" />
        </resultMap>
        <insert id="addProduct" parameterType="com.dingding.entity.Product">
            INSERT INTO `product` (`product_name`,`product_price`,`product_type`,`product_img`,`product_des`) VALUES(#{ productName},#{ productPrice},#{ productType},#{ productImg},#{ productDes})
        </insert>
        <select id="getProductList" resultMap="BaseResultMap">
            SELECT * FROM `product`
        </select >
        <select id="getProductByKey" resultMap="BaseResultMap">
            SELECT * FROM `product` where product_name like concat('%',#{ productName},'%') or product_des like  concat('%',#{ productName},'%')
        </select >
        <select id="getProductByCondition" resultMap="BaseResultMap">
            SELECT * FROM `product`
            <where>
            <if test="productName != null and productName != ''">
                and product_name like concat('%',#{ productName},'%')
            </if>
            <if test="productType != null and productType != -1">
                and product_type = #{ productType}
            </if>
            </where>
        </select>
        <update id="updateProduct"  parameterType="com.dingding.entity.Product">
            update product
            <trim prefix="SET" suffixOverrides=",">
                <if test="null != pro.productName and '' != pro.productName">
                    product_name=#{ pro.productName},
                </if>
                <if test="null != pro.productType and -1!= pro.productType">
                    product_type=#{ pro.productType},
                </if>
                <if test="null != pro.productPrice and -1!= pro.productPrice">
                    product_price=#{ pro.productPrice},
                </if>
                <if test="null != pro.productImg and '' != pro.productImg">
                    product_img=#{ pro.productImg},
                </if>
                <if test="null != pro.productDes and '' != pro.productDes">
                    product_des=#{ pro.productDes},
                </if>
            </trim>
            where product_id=#{ pro.productId}
    </update>
        <delete id="deleteProduct">
            DELETE FROM product WHERE product_id = #{ productId}
        </delete>
        <select id="getProductDetailById" resultMap="BaseResultMap">
        	select * from `product` where product_id = #{ productId} limit 1
        </select>
         <select id="statisticProductNum" resultType="java.util.Map">
            SELECT product_type productType, COUNT(1) productNum FROM product GROUP BY product_type
        </select>
    </mapper>

### 四、在 ProductController 类中添加业务逻辑 ###

package com.dingding.controller;
    import com.dingding.entity.Product;
    import com.dingding.entity.Response;
    import com.dingding.service.ProductService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    import java.util.List;
    import java.util.Map;
    
    /** * Created by xpwu on 2019/7/10. */
    @RestController
    public class ProductController { 
        @Autowired
        ProductService productService;
        @RequestMapping(value = "/addProduct",method = RequestMethod.POST)
        public Response addProduct(@RequestBody Product product){ 
            if(product.getProductName()!=null && product.getProductPrice()!=0 && product.getProductType()!=0 && product.getProductImg()!=null && product.getProductDes()!=null){ 
                int count = productService.addProduct(product);
                if(count >  0){ 
                    Response response = new Response(true,"添加成功",1);
                    return response;
                }else { 
                    Response response = new Response(false,"添加失败",-1);
                    return response;
                }
            }else { 
                Response response = new Response(false,"有参数为空",-1);
                return response;
            }
        }
        @RequestMapping(value = "/getProductList",method = RequestMethod.POST)
        public Response getProductList(){ 
            Response response = new Response();
            List<Product> productList = productService.getProductList();
            response.setResponse(true,"查询成功",1,productList);
            return response;
        }
        @RequestMapping(value = "/getProductByKey",method = RequestMethod.POST)
        public Response getProductByKey(@RequestBody Map<String,String> product){ 
            String productName = product.get("productName");
            String productDes= product.get("productDes");
            if(productDes!=null){ 
               productName = productDes;
            }
            Response response = new Response();
            List<Product> productList = productService.getProductByKey(productName);
            response.setResponse(true,"查询成功",1,productList);
            return response;
        }
        @RequestMapping(value = "/getProductByCondition",method = RequestMethod.POST)
        public Response getProductByCondition(@RequestBody Product product){ 
            String productName = product.getProductName();
            int productType = product.getProductType();
            Response response = new Response();
            List<Product> productList = productService.getProductByCondition(productName,productType);
            response.setResponse(true,"查询成功",1,productList);
            return response;
        }
        @RequestMapping(value = "/updateProduct",method = RequestMethod.POST)
        public Response updateProduct(@RequestBody Product product){ 
            int productId = product.getProductId();
            if(productId!=0){ 
                int count = productService.updateProduct(product);
                if(count>0){ 
                    Response response =  new Response(true,"更新成功",1);
                    return  response;
                }else { 
                    Response response = new Response(false,"更新失败",-1);
                    return  response;
                }
            }else { 
                Response response = new Response(false,"请传入商品id",-1);
                return  response;
            }
        }
        @RequestMapping(value = "/deleteProduct",method = RequestMethod.POST)
        public Response deleteProduct(@RequestBody Product product){ 
            int productId = product.getProductId();
            if(productId!=0){ 
                int count = productService.deleteProduct(productId);
                if(count>0){ 
                    Response response = new Response(true,"删除成功",1);
                    return response;
                }else { 
                    Response response = new Response(false,"删除失败，请检查原因",-1);
                    return response;
                }
            }else { 
                Response response = new Response(false,"删除失败，请传入商品id",-1);
                return response;
            }
        }
        @RequestMapping(value = "/getProductDetailById",method = RequestMethod.GET)
        public Response getProductDetailById(@RequestParam("productId") Integer productId){ 
            Response response = new Response();
            Product product = productService.getProductDetailById(productId);
            response.setResponse(true,"查询成功",1,product);
            return response;
        }
         @RequestMapping(value = "/statisticProductNum",method = RequestMethod.GET)
        public Response statisticProductNum(){ 
            Response response = new Response();
            List<Map<String, Object>> resList = productService.statisticProductNum();
            response.setResponse(true,"查询成功",1, resList);
            return response;
        }
    }

### 五、测试接口是否成功 ###

1.  使用 postman 验证接口。

*  验证统计商品数量接口  
    1）选择请求方式为 GET, 在地址栏中输入 http://localhost:8080/statisticProductNum。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEzNTEyMQ_size_16_color_FFFFFF_t_70]  
    2)查看数据库数据是否相符，可以看到，商品类别为1的有1件商品，类别为2的有3件商品，查询结果与数据库相符。  
    ![在这里插入图片描述][20201129105330753.png]

### 六、总结 ###

统计查询需要用到 group by 函数，根据分类字段统计。基本格式为： select 分类字段 , count(1) from 表名 group by 分类字段。使用map对象接收返回值时，还需要根据具体情况给字段取别名，基本格式为： select 分类字段 别名 , count(1) 别名 from 表名 group by 分类字段。  
sql 语句如下：

<select id="statisticProductNum" resultType="java.util.Map">
            SELECT product_type productType, COUNT(1) productNum FROM product GROUP BY product_type
        </select>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEzNTEyMQ_size_16_color_FFFFFF_t_70]: /images/20221120/05e402c99ec947608de442cb3945cc43.png
[20201129105330753.png]: https://img-blog.csdnimg.cn/20201129105330753.png