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

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

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

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

前言

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

一、添加pagehelper相关依赖

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


    com.github.pagehelper
    pagehelper-spring-boot-starter
    1.2.5

二、在 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 { 

    1. int addProduct(Product product);
    2. List<Product> getProductList();
    3. List<Product> getProductByKey(String productName);
    4. List<Product> getProductByCondition(String productName,int productType);
    5. int updateProduct(@Param("pro") Product product);
    6. int deleteProduct(int productId);
    7. Product getProductDetailById(int productId);
    8. List<Map<String, Object>> statisticProductNum();
    9. 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 { 

    1. @Autowired
    2. ProductMapper productMapper;
    3. public int addProduct(Product product){ 
    4.     int count = 0;
    5.     try { 
    6.         count = productMapper.addProduct(product);
    7.     }catch (Exception err){ 
    8.        System.out.println(err);
    9.     }
    10.     return count;
    11. }
    12. public List<Product> getProductList(){ 
    13.     List<Product> proList = productMapper.getProductList();
    14.     return  proList;
    15. }
    16. public List<Product> getProductByKey(String productName){ 
    17.     List<Product> proList1 = productMapper.getProductByKey(productName);
    18.     return  proList1;
    19. }
    20. public List<Product> getProductByCondition(String productName,int productType){ 
    21.     List<Product> proList2 = productMapper.getProductByCondition(productName,productType);
    22.     return  proList2;
    23. }
    24. public int updateProduct(Product product){ 
    25.     int count = 0;
    26.     try { 
    27.         count = productMapper.updateProduct(product);
    28.     }catch (Exception err){ 
    29.         System.out.println(err);
    30.     }
    31.     return count;
    32. }
    33. public int deleteProduct(int productId){ 
    34.     int count = 0;
    35.     try { 
    36.         count = productMapper.deleteProduct(productId);
    37.     }catch (Exception err){ 
    38.         System.out.println(err);
    39.     }
    40.     return count;
    41. }
    42. public Product getProductDetailById(int productId) { 
    43.     return productMapper.getProductDetailById(productId);
    44. }
    45. public List<Map<String, Object>> statisticProductNum() { 
    46.     return productMapper.statisticProductNum();
    47. }
    48. public List<Product> getProductPageList() { 
    49.     return productMapper.getProductPageList();
    50. }

    }

三、在 ProductMapper 类中添加接口

在 ProductMapper 类中添加 getProductPageList 接口。

  1. package com.dingding.mapper;
  2. import com.dingding.entity.Product;
  3. import org.springframework.stereotype.Repository;
  4. import java.util.List;
  5. import java.util.Map;
  7. /** * Created by xpwu on 2019/7/10. */
  8. @Repository
  9. public interface ProductMapper { 
  10.     int addProduct(Product product);
  11.     List<Product> getProductList();
  12.     List<Product>getProductByKey(String productName);
  13.     List<Product>getProductByCondition(String productName,int productType);
  14.     int updateProduct(@Param("pro") Product product);
  15.     int deleteProduct(int productId);
  16.     Product getProductDetailById(int productId);
  17.     List<Map<String, Object>> statisticProductNum();
  18.     List<Product> getProductPageList();
  19. }

四、增加 sql 语句

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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  3. <mapper namespace="com.dingding.mapper.ProductMapper">
  4.     <resultMap id="BaseResultMap" type="com.dingding.entity.Product">
  5.         <result column="product_id" jdbcType="VARCHAR" property="productId" />
  6.         <result column="product_name" jdbcType="VARCHAR" property="productName" />
  7.         <result column="product_price" jdbcType="DOUBLE" property="productPrice" />
  8.         <result column="product_type" jdbcType="INTEGER" property="productType" />
  9.         <result column="product_img" jdbcType="VARCHAR" property="productImg" />
  10.         <result column="product_des" jdbcType="VARCHAR" property="productDes" />
  11.     </resultMap>
  12.     <insert id="addProduct" parameterType="com.dingding.entity.Product">
  13.         INSERT INTO `product` (`product_name`,`product_price`,`product_type`,`product_img`,`product_des`) VALUES(#{ productName},#{ productPrice},#{ productType},#{ productImg},#{ productDes})
  14.     </insert>
  15.     <select id="getProductList" resultMap="BaseResultMap">
  16.         SELECT * FROM `product`
  17.     </select >
  18.     <select id="getProductByKey" resultMap="BaseResultMap">
  19.         SELECT * FROM `product` where product_name like concat('%',#{ productName},'%') or product_des like  concat('%',#{ productName},'%')
  20.     </select >
  21.     <select id="getProductByCondition" resultMap="BaseResultMap">
  22.         SELECT * FROM `product`
  23.         <where>
  24.         <if test="productName != null and productName != ''">
  25.             and product_name like concat('%',#{ productName},'%')
  26.         </if>
  27.         <if test="productType != null and productType != -1">
  28.             and product_type = #{ productType}
  29.         </if>
  30.         </where>
  31.     </select>
  32.     <update id="updateProduct"  parameterType="com.dingding.entity.Product">
  33.         update product
  34.         <trim prefix="SET" suffixOverrides=",">
  35.             <if test="null != pro.productName and '' != pro.productName">
  36.                 product_name=#{ pro.productName},
  37.             </if>
  38.             <if test="null != pro.productType and -1!= pro.productType">
  39.                 product_type=#{ pro.productType},
  40.             </if>
  41.             <if test="null != pro.productPrice and -1!= pro.productPrice">
  42.                 product_price=#{ pro.productPrice},
  43.             </if>
  44.             <if test="null != pro.productImg and '' != pro.productImg">
  45.                 product_img=#{ pro.productImg},
  46.             </if>
  47.             <if test="null != pro.productDes and '' != pro.productDes">
  48.                 product_des=#{ pro.productDes},
  49.             </if>
  50.         </trim>
  51.         where product_id=#{ pro.productId}
  52. </update>
  53.     <delete id="deleteProduct">
  54.         DELETE FROM product WHERE product_id = #{ productId}
  55.     </delete>
  56.     <select id="getProductDetailById" resultMap="BaseResultMap">
  57.         select * from `product` where product_id = #{ productId} limit 1
  58.     </select>
  59.      <select id="statisticProductNum" resultType="java.util.Map">
  60.         SELECT product_type productType, COUNT(1) productNum FROM product GROUP BY product_type
  61.     </select>
  62.     <select id="getProductPageList" resultMap="BaseResultMap">
  63.         select * from `product`
  64.     </select>
  65. </mapper>

五、在 ProductController 类中添加业务逻辑

  1. package com.dingding.controller;
  2. import com.dingding.entity.Product;
  3. import com.dingding.entity.Response;
  4. import com.dingding.service.ProductService;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.web.bind.annotation.RequestBody;
  7. import org.springframework.web.bind.annotation.RequestMapping;
  8. import org.springframework.web.bind.annotation.RequestMethod;
  9. import org.springframework.web.bind.annotation.RestController;
  10. import java.util.List;
  11. import java.util.Map;
  13. /** * Created by xpwu on 2019/7/10. */
  14. @RestController
  15. public class ProductController { 
  16.     @Autowired
  17.     ProductService productService;
  18.     @RequestMapping(value = "/addProduct",method = RequestMethod.POST)
  19.     public Response addProduct(@RequestBody Product product){ 
  20.         if(product.getProductName()!=null && product.getProductPrice()!=0 && product.getProductType()!=0 && product.getProductImg()!=null && product.getProductDes()!=null){ 
  21.             int count = productService.addProduct(product);
  22.             if(count >  0){ 
  23.                 Response response = new Response(true,"添加成功",1);
  24.                 return response;
  25.             }else { 
  26.                 Response response = new Response(false,"添加失败",-1);
  27.                 return response;
  28.             }
  29.         }else { 
  30.             Response response = new Response(false,"有参数为空",-1);
  31.             return response;
  32.         }
  33.     }
  34.     @RequestMapping(value = "/getProductList",method = RequestMethod.POST)
  35.     public Response getProductList(){ 
  36.         Response response = new Response();
  37.         List<Product> productList = productService.getProductList();
  38.         response.setResponse(true,"查询成功",1,productList);
  39.         return response;
  40.     }
  41.     @RequestMapping(value = "/getProductByKey",method = RequestMethod.POST)
  42.     public Response getProductByKey(@RequestBody Map<String,String> product){ 
  43.         String productName = product.get("productName");
  44.         String productDes= product.get("productDes");
  45.         if(productDes!=null){ 
  46.            productName = productDes;
  47.         }
  48.         Response response = new Response();
  49.         List<Product> productList = productService.getProductByKey(productName);
  50.         response.setResponse(true,"查询成功",1,productList);
  51.         return response;
  52.     }
  53.     @RequestMapping(value = "/getProductByCondition",method = RequestMethod.POST)
  54.     public Response getProductByCondition(@RequestBody Product product){ 
  55.         String productName = product.getProductName();
  56.         int productType = product.getProductType();
  57.         Response response = new Response();
  58.         List<Product> productList = productService.getProductByCondition(productName,productType);
  59.         response.setResponse(true,"查询成功",1,productList);
  60.         return response;
  61.     }
  62.     @RequestMapping(value = "/updateProduct",method = RequestMethod.POST)
  63.     public Response updateProduct(@RequestBody Product product){ 
  64.         int productId = product.getProductId();
  65.         if(productId!=0){ 
  66.             int count = productService.updateProduct(product);
  67.             if(count>0){ 
  68.                 Response response =  new Response(true,"更新成功",1);
  69.                 return  response;
  70.             }else { 
  71.                 Response response = new Response(false,"更新失败",-1);
  72.                 return  response;
  73.             }
  74.         }else { 
  75.             Response response = new Response(false,"请传入商品id",-1);
  76.             return  response;
  77.         }
  78.     }
  79.     @RequestMapping(value = "/deleteProduct",method = RequestMethod.POST)
  80.     public Response deleteProduct(@RequestBody Product product){ 
  81.         int productId = product.getProductId();
  82.         if(productId!=0){ 
  83.             int count = productService.deleteProduct(productId);
  84.             if(count>0){ 
  85.                 Response response = new Response(true,"删除成功",1);
  86.                 return response;
  87.             }else { 
  88.                 Response response = new Response(false,"删除失败，请检查原因",-1);
  89.                 return response;
  90.             }
  91.         }else { 
  92.             Response response = new Response(false,"删除失败，请传入商品id",-1);
  93.             return response;
  94.         }
  95.     }
  96.     @RequestMapping(value = "/getProductDetailById",method = RequestMethod.GET)
  97.     public Response getProductDetailById(@RequestParam("productId") Integer productId){ 
  98.         Response response = new Response();
  99.         Product product = productService.getProductDetailById(productId);
  100.         response.setResponse(true,"查询成功",1,product);
  101.         return response;
  102.     }
  103.      @RequestMapping(value = "/statisticProductNum",method = RequestMethod.GET)
  104.     public Response statisticProductNum(){ 
  105.         Response response = new Response();
  106.         List<Map<String, Object>> resList = productService.statisticProductNum();
  107.         response.setResponse(true,"查询成功",1, resList);
  108.         return response;
  109.     }
  110.     @RequestMapping(value = "/getProductPageList", method = RequestMethod.GET)
  111.     public Response getProductPageList(@RequestParam("pageNum") Integer pageNum,
  112.                                        @RequestParam("pageSize") Integer pageSize) { 
  113.         // 借助pagehelper插件开启分页
  114.         // 下面这行代码的位置不能随便放，
  115.         // pagehelper会对此代码之后的第一个查询进行分页，
  116.         // 如果涉及多个查询语句的业务代码，注意此行代码的摆放位置
  117.         PageHelper.startPage(pageNum, pageSize);
  118.         List<Product> list = productService.getProductPageList();
  119.         // 组装分页数据，主要包含分页列表数据及总记录数
  120.         Map<String, Object> map = new HashMap<>();
  121.         map.put("list", list);
  122.         map.put("total", new PageInfo<>(list).getTotal());
  123.         Response response = new Response();
  124.         response.setResponse(true, "查询成功", 1, map);
  125.         return response;
  126.     }
  127. }

六、测试接口是否成功

  1. 使用 postman 验证接口。

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

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

七、总结

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

发表评论

表情：
评论列表 （有 0 条评论，269人围观）

还没有评论，来说两句吧...

相关阅读