Java实现search_after的使用分页查询 java分页查询怎么实现

转载

mob64ca140a1f7c 2024-06-27 18:31:07

文章标签 List spring java 文章分类 Java 后端开发

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

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

前言

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

一、添加pagehelper相关依赖

在 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;
    }
}

六、测试接口是否成功

使用 postman 验证接口。

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

翻页查询第二页数据，pageNum=2&pageSize=1，并验证数据是否为第二条。

3）由上面两步验证，可以说明分页查询已经生效，就不需要再往后翻页了。

七、总结

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java 实体占用空间大小 java占用内存过高

下一篇：springboot restful converter设置 springboot restful传参

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

Java实现search_after的使用分页查询 java分页查询怎么实现

Java实现search_after的使用分页查询 java分页查询怎么实现

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

前言

一、添加pagehelper相关依赖

二、在 ProductService 类中添加接口

三、在 ProductMapper 类中添加接口

四、增加 sql 语句

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

六、测试接口是否成功

七、总结

51CTO博客