实战 Java 第9天:统计各类商品数量
前言
对商品进行分类统计也是很常见的业务场景。今天开始编写统计各类商品数量接口。本文的内容只是业务逻辑,完整的项目需结合前面的内容一起看。
一、在 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;
}
}
五、测试接口是否成功
- 使用 postman 验证接口。
- 验证统计商品数量接口
1)选择请求方式为 GET, 在地址栏中输入 http://localhost:8080/statisticProductNum。
2)查看数据库数据是否相符,可以看到,商品类别为1的有1件商品,类别为2的有3件商品,查询结果与数据库相符。
六、总结
统计查询需要用到 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>