实战 Java 第10天：商品分页查询

╰半橙微兮° 2022-12-31 02:18 156阅读 0赞

### 实战 Java 第10天：商品分页查询 ###

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

### 前言 ###

商品数量较多时，需要对商品列表进行分页查询展示。今天将通过mybatis+pagehelper插件实现列表分页查询。

### 一、添加pagehelper相关依赖 ###

1.  在 pom.xml 中添加pagehelper相关依赖。

<dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.5</version>
            </dependency>

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

*  在 ProductService 类中添加 getProductPageList 接口，实现商品分页查询。

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();
        List<Product> getProductPageList();
    }

*  在 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();
        }
        public List<Product> getProductPageList() { 
            return productMapper.getProductPageList();
        }
    }

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

在 ProductMapper 类中添加 getProductPageList 接口。

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();
        List<Product> getProductPageList();
    }

### 四、增加 sql 语句 ###

添加 getProductPageList 的查询语句，通过pagehelper实现分页时，sql与普通列表查询无异。

<?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>
        <select id="getProductPageList" resultMap="BaseResultMap">
            select * from `product`
        </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;
        }
        @RequestMapping(value = "/getProductPageList", method = RequestMethod.GET)
        public Response getProductPageList(@RequestParam("pageNum") Integer pageNum,
                                           @RequestParam("pageSize") Integer pageSize) { 
            // 借助pagehelper插件开启分页
            // 下面这行代码的位置不能随便放，
            // pagehelper会对此代码之后的第一个查询进行分页，
            // 如果涉及多个查询语句的业务代码，注意此行代码的摆放位置
            PageHelper.startPage(pageNum, pageSize);
            List<Product> list = productService.getProductPageList();
            // 组装分页数据，主要包含分页列表数据及总记录数
            Map<String, Object> map = new HashMap<>();
            map.put("list", list);
            map.put("total", new PageInfo<>(list).getTotal());
            Response response = new Response();
            response.setResponse(true, "查询成功", 1, map);
            return response;
        }
    }

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

1.  使用 postman 验证接口。

*  验证商品分页查询接口  
    1）选择请求方式为 GET, 在地址栏中输入 http://localhost:8080/getProductPageList?pageNum=1&pageSize=1，pageNum为页码，pageSzie为页大小（每页数据量）。考虑到截图展示，页大小设置为1，真实场景可根据自身需求改变该参数值。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEzNTEyMQ_size_16_color_FFFFFF_t_70]

1.  翻页查询第二页数据，pageNum=2&pageSize=1，并验证数据是否为第二条。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEzNTEyMQ_size_16_color_FFFFFF_t_70 1]  
    3）由上面两步验证，可以说明分页查询已经生效，就不需要再往后翻页了。

### 七、总结 ###

分页查询需要用到 pagehelper 插件，使用此插件并不影响业务查询代码，与普通查询无异，只需要在相应查询代码前加上一行代码：PageHelper.startPage(pageNum, pageSize);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEzNTEyMQ_size_16_color_FFFFFF_t_70]: /images/20221120/dad9b17ce5fc4aef802eca13728b3882.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEzNTEyMQ_size_16_color_FFFFFF_t_70 1]: /images/20221120/4721c541550844cc8ad502eb573fdfbb.png