SpringBoot+MyBatisPlus（4）—— 分页查询

大大源码 • 2023年3月9日 am12:56 • 其他

一. 创建数据库

在数据库springboot下创建一个book表

CREATE TABLE `book` (
  `id` int NOT NULL AUTO_INCREMENT,
  `book_name` varchar(45) DEFAULT NULL,
  `price` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

二. 编写实体类对象

package com.stu.springboot.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

@Data
public class Book {
    @TableId(type = IdType.AUTO)
    private Integer id;

    private String bookName;

    private Integer price;
}

三. Mapper层

package com.stu.springboot.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.stu.springboot.pojo.Book;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface BookMapper extends BaseMapper<Book> {
}

四. Service层

package com.stu.springboot.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.stu.springboot.pojo.User;

public interface UserService extends IService<User> {
}

package com.stu.springboot.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.stu.springboot.mapper.BookMapper;
import com.stu.springboot.pojo.Book;
import com.stu.springboot.service.BookService;
import org.springframework.stereotype.Service;

@Service
public class BookServiceImpl extends ServiceImpl<BookMapper, Book> implements BookService {
}

五. 添加分页插件

package com.stu.springboot.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        //添加分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

六. Controller层

package com.stu.springboot.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.stu.springboot.common.R;
import com.stu.springboot.pojo.Book;
import com.stu.springboot.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class BookController {
    @Autowired
    private BookService bookService;

    @GetMapping("/manage/bookPage")
    public String bookPage(){
        return "manage/book";
    }

    @PostMapping("/manage/book")
    @ResponseBody
    public R<Page> book(int pageNo, int pageSize, String name, String lowPrice, String highPrice){
        Page<Book> pageInfo = new Page<>(pageNo, pageSize);   //第X页，每页显示X条

        LambdaQueryWrapper<Book> queryWrapper = new LambdaQueryWrapper<>();
        //添加过滤条件：如果name不为空时，根据书名模糊查询
        queryWrapper.like(StringUtils.isNotEmpty(name), Book::getBookName, name);

        //添加过滤条件：根据价格区间查找，>=lowPrice, <=highPrice
        queryWrapper.ge(StringUtils.isNotEmpty(lowPrice) && StringUtils.isEmpty(highPrice), Book::getPrice, lowPrice);
        queryWrapper.le(StringUtils.isEmpty(lowPrice) && StringUtils.isNotEmpty(highPrice), Book::getPrice, highPrice);
        queryWrapper.between(StringUtils.isNotEmpty(lowPrice) && StringUtils.isNotEmpty(highPrice),Book::getPrice, lowPrice, highPrice);

        //添加排序条件：根据价格降序
        queryWrapper.orderByDesc(Book::getPrice);

        //执行查询
        bookService.page(pageInfo, queryWrapper);

        return R.success(pageInfo);
    }
}

七. 页面

<!DOCTYPE html>
<!--suppress ThymeleafVariablesResolveInspection -->
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>图书管理</title>

    <!--引入jQuery-->
    <script type="text/javascript" src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>  <!--引入jquery -->

    <!--引入BootStrap框架-->
    <link rel="stylesheet" type="text/css" href="../../static/jquery/bootstrap_3.3.0/css/bootstrap.min.css">
    <script type="text/javascript" src="../../static/jquery/bootstrap_3.3.0/js/bootstrap.min.js"></script>
    <!--引入BootStrap框架的分页插件-->
    <link rel="stylesheet" type="text/css" href="../../static/jquery/bs_pagination-master/css/jquery.bs_pagination.min.css">
    <script type="text/javascript" src="../../static/jquery/bs_pagination-master/js/jquery.bs_pagination.min.js"></script>
    <script type="text/javascript" src="../../static/jquery/bs_pagination-master/localization/en.js"></script>

    <script type="text/javascript">
        $(function(){
            //当图书管理页面加载完成后，默认显示第1页，每页显示5条（即查询所有数据的第1页的数据，每页显示5条）
            queryBookForPage(1, 5);

            //给查询按钮添加单击事件
            $("#queryBookBtn").click(function (){
                //查询符合条件的所有数据，默认显示第1页，每页显示5条数据：queryBookForPage(1, 5);
                queryBookForPage(1, $("#page").bs_pagination('getOption', 'rowsPerPage'));
                //$("#page").bs_pagination('getOption', 'rowsPerPage')获取上次显示的每页显示的条数
                //当修改每页显示的条数时，可以通过该方式显示当前页面需要展示的条数
            });
        });


        function queryBookForPage(pageNo, pageSize){
            var name = $("#bookName").val();
            var lowPrice = $("#lowPrice").val();
            var highPrice = $("#highPrice").val();
            //发送请求
            $.ajax({
                url:'[[@{/manage/book}]]',
                data: {
                    name:name,
                    lowPrice:lowPrice,
                    highPrice:highPrice,
                    pageNo:pageNo,
                    pageSize:pageSize
                },
                type: 'post',
                dataType: 'json',
                success:function (data){   //data是后端的R对象，R.objectData是后端的Page对象
                    //显示书的列表，将后端传回来的数据变成html写在相应的表格里
                    var htmlStr = "";
                    $.each(data.objectData.records, function (index, obj){
                        htmlStr += "<tr>";
                        htmlStr += "<td><input type=\"checkbox\" value=\""+obj.id+"\"/></td>";
                        htmlStr += "<td>"+obj.bookName+"</td>";
                        htmlStr += "<td>"+obj.price+"</td>";
                        htmlStr += "</tr>";
                    });
                    $("#bookTableRow").html(htmlStr);

                    
                    
                    //【 切换页面时，刷新页面显示的内容 】
                    //计算总页数
                    var totalPages = 1;
                    if(data.objectData.total % pageSize == 0){
                        totalPages = data.objectData.total / pageSize;
                    }else{
                        totalPages = parseInt(data.objectData.total / pageSize) + 1;
                    }
                    //调用BootStrap框架的分页插件，显示翻页信息
                    $("#page").bs_pagination({
                        currentPage: pageNo,           //当前页号
                        rowsPerPage: pageSize,         //每页显示的数据个数
                        totalRows: data.objectData.total,         //数据的总条数
                        totalPages: totalPages,       //总页数，必须赋值

                        visiblePageLinks: 5,   //分页条中显示的页面卡片个数

                        showGoToPage: true,    //是否显示 跳转 到某个页面， 默认是true
                        showRowsPerPage: true,  //是否显示 每页显示条数， 默认是true
                        showRowsInfo: false,    //是否显示 记录的信息， 默认是true

                        //用户每次切换页号，都会自动出发本函数， 返回切换页号之后的pageNo和pageSize
                        onChangePage:function (event, pageObj){
                            queryBookForPage(pageObj.currentPage, pageObj.rowsPerPage);
                        }
                    });
                }
            });
        }
    </script>

</head>
<body>
    <div>
        <span th:text="|欢迎${session.loginUser.username}|"></span>
        <a th:href="@{/user/logout}"><input type="button" value="退出"/></a>
    </div>

    <br>

    <!-- 查询条 -->
    <div>
        书名: <input type="text" id="bookName">
        价格: <input type="text" id="lowPrice"> — <input type="text" id="highPrice">
        <input type="button" value="查询" id="queryBookBtn">
    </div>
    <br>

    <!-- 显示具体内容 -->
    <div>
        <table>
            <thead>
            <tr>
                <td><input type="checkbox" /></td>
                <td>书名</td>
                <td>价格</td>
            </tr>
            </thead>
            <tbody id="bookTableRow">
            <tr>
                <td><input type="checkbox" /></td>
                <td></td>
                <td></td>
            </tr>
            </tbody>
        </table>
    </div>
    <br>

    <!-- 分页条 -->
    <div id="page"></div>
</body>
</html>

原文链接：https://blog.csdn.net/Archer__13/article/details/127144489

THE END