Thymeleaf+Mybatis+Mysql+SSM分页查询-分页工具类 分页插件

Thymeleaf+Mybatis+Mysql+SSM分页查询

---

一、分页查询大致分为两种:

1. **物理查询:**根据页面只取一页的数据显示,需要构造SQL语句

   **优缺点:**事实查看,数据不会溢出,性能降低

2. **逻辑查询:**每次取查询结果的所有数据，然后根据页面显示指定的记录

   **优缺点:**查询快,数据会溢出

二、本文只叙述物理查询的两种方法,实现以下步骤:

1、第一种用工具类

• 通过工具类pageUtil实现,工具类负责接收前端的起始页码和一页要显示的数据通过逻辑计算得到每一页的第一条数据和最后一条数据等等,反馈给数据库每一页第一条数据的ID和一页要显示的数据,让数据库查一页的数据返回.

• 实体类

  @Data
  public class PageInfo implements Serializable {
      private Integer id;
  
      private String name;
  
      private String sex;
  
      private String note;
  
      private static final long serialVersionUID = 1L;
  }
  

• 工具类pageUtil

  /**
   * 封装分页相关的信息.
   */
  public class pageUtil {
      // 当前页码
      private int current = 1;
      // 显示上限
      private int limit = 5;
      // 数据总数(用于计算总页数)
      private int rows;
      // 查询路径(用于复用分页链接)
      private String path;
  
      public int getCurrent() {
          return current;
      }
  
      public void setCurrent(int current) {
          if (current >= 1) {
              this.current = current;
          }
      }
  
      public int getLimit() {
          return limit;
      }
  
      public void setLimit(int limit) {
          if (limit >= 1 && limit <= 100) {
              this.limit = limit;
          }
      }
  
      public int getRows() {
          return rows;
      }
  
      public void setRows(int rows) {
          if (rows >= 0) {
              this.rows = rows;
          }
      }
  
      public String getPath() {
          return path;
      }
  
      public void setPath(String path) {
          this.path = path;
      }
  
      /**
       * 获取当前页的起始行
       *
       * @return
       */
      public int getOffset() {
          // current * limit - limit
          return (current - 1) * limit;
      }
  
      /**
       * 获取总页数
       *
       * @return
       */
      public int getTotal() {
          // rows / limit [+1]
          if (rows % limit == 0) {
              return rows / limit;
          } else {
              return rows / limit + 1;
          }
      }
  
      /**
       * 获取起始页码
       *
       * @return
       */
      public int getFrom() {
          int from = current - 2;
          return from < 1 ? 1 : from;
      }
  
      /**
       * 获取结束页码
       *
       * @return
       */
      public int getTo() {
          int to = current + 2;
          int total = getTotal();
          return to > total ? total : to;
      }
  
      @Override
      public String toString() {
          return "pageUtil{" +
                  "current=" + current +
                  ", limit=" + limit +
                  ", rows=" + rows +
                  ", path='" + path + '\'' +
                  ",每页第一行=" + getOffset() +
                  ",总页数=" + getTotal() +
                  ",起始页码=" + getFrom() +
                  ",结束页码=" + getTo() +
                  '}';
      }
  }
  

• Controller层

  //跳转功能页,返回页面数据
      @RequestMapping(path = "/pageList", method = RequestMethod.GET)
      public String pageList(Model model, pageUtil page){
          //数据总数
          page.setRows(pageService.pageInfoRows(0));
          //跳转网址
          page.setPath("/pageList");
          //一整页的数据集
          List<PageInfo> pageInfoList = pageService.pageInfoList(0, page.getOffset(), page.getLimit());
  //        System.out.println(page.toString());
  //        System.out.println(pageInfoList.toString());
          model.addAttribute("pageList",pageInfoList);
          model.addAttribute("page",page);
          return "admin-list";
      }
  

• Service层

  @Service
  public class PageService {
  
      @Autowired
      PageInfoMepper pageInfoMepper;
  	//查询一页数据
      public List<PageInfo> pageInfoList(Integer userId, Integer offset, Integer limit){
          return pageInfoMepper.selectPageInfos( userId, offset, limit);
      }
  	//查询数据总数
      public int pageInfoRows(Integer userId){
          return pageInfoMepper.selectPageInfoRows(userId);
      }
  
  }
  

• Dao层(Mapper)

  @Mapper
  @Repository
  public interface PageInfoMepper {
      List<PageInfo> selectPageInfos(@Param("userId") Integer userId, @Param("offset") Integer offset, @Param("limit") Integer limit);
  
      // @Param注解用于给参数取别名,尽量加别名以免找不到
      // 如果只有一个参数,并且在<if>里使用,则必须加别名.
      int selectPageInfoRows(@Param("userId") Integer userId);
  }
  
  

  <select id="selectPageInfos" parameterType="integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List"></include>
    from fruits.page_info
    <where>
      <if test="userId!=0">
        id = #{userId}
      </if>
    </where>
    order by id asc
    limit #{offset}, #{limit}
  </select>
  
  <select id="selectPageInfoRows" parameterType="integer" resultType="integer">
    select count(*)
    from fruits.page_info
    <where>
      <if test="userId!=0">
        id = #{userId}
      </if>
    </where>
  </select>
  

• 页面数据展示，用的thymeleaf模板引擎

  <tr th:each="mypage : ${pageList}">
    <td>
      <input type="checkbox" name=""  lay-skin="primary">
    </td>
    <td th:text="${mypage.id}">ID</td>
    <td th:text="${mypage.name}">登录名</td>
    <td th:text="${mypage.sex}">SEX</td>
    <td th:text="${mypage.note}">备注</td>
    <td class="td-manage">
      <a onclick="member_stop(this,'10001')" href="javascript:;"  title="启用">
        <i class="layui-icon">&#xe601;</i>
      </a>
      <a title="编辑"  onclick="xadmin.open('编辑','admin-edit.html')" href="javascript:;">
        <i class="layui-icon">&#xe642;</i>
      </a>
      <a title="删除" onclick="member_del(this,'要删除的id')" href="javascript:;">
        <i class="layui-icon">&#xe640;</i>
      </a>
    </td>
  </tr>
  

• 页面页码导航

  <!--分页页码-->
  <div th:if="${page.rows > 0}">
    <!-- 上一页 ===当前页-1 -->
    <a th:class="|prev ${page.current==1?'disabled':''}|" th:style="${page.current==1?'pointer-events: none':''}"
       th:href="@{${page.path}(current=${page.current-1})}">&lt;&lt;</a>
    <!--首页 第一页-->
    <a class="num" th:href="@{${page.path}(current=1)}" th:if="${page.current!=1}">1</a>
    <!--当前页的前一页-->
    <a class="num" th:href="@{${page.path}(current=${page.current-1})}" th:text="${page.current-1}" th:if="${page.current-1>1}">当前页-1</a>
    <!--当前页-->
    <span class="current" th:text="${page.current}">当前页</span>
    <!--当前页的后一页-->
    <a class="num" th:text="${page.current+1}" th:href="@{${page.path}(current=${page.current+1})}" th:if="${page.current+1<page.total}">当前页+1</a>
    <!--末尾页-->
    <a class="num" th:href="@{${page.path}(current=${page.total})}" th:if="${page.current!=page.total}" th:text="${page.getTotal()}">489</a>
    <!--下一页-->
    <a th:class="|next ${page.current==page.total?'disabled':''}|" th:style="${page.current==page.total?'pointer-events: none':''}"
       th:href="@{${page.path}(current=${page.current+1})}">&gt;&gt;</a>
  </div>
  

• 效果图:

2、第二种用Mybaits分页插件

• Mybats的一个分页插件pagehelper，操作起来还是比较简便的上代码

• 首先是导入pom.xml依赖

  <!--Mybatis 分页插件pagehelper-->
          <dependency>
              <groupId>com.github.pagehelper</groupId>
              <artifactId>pagehelper</artifactId>
              <version>5.1.2</version>
          </dependency>
  

• 引入分页插件,我的是在mybatis配置文件里配的,mybatis-config.xml

  <configuration>
      <!-- 引入分页插件-->
      <!--注意这里要写成PageInterceptor, 5.0之前的版本都是写PageHelper, 5.0之后要换成PageInterceptor-->
      <plugins>
          <plugin interceptor="com.github.pagehelper.PageInterceptor">
              <!-- 开启合理化分页-->
              <property name="reasonable" value="true"/>
          </plugin>
      </plugins>
  </configuration>
  

• 实体类和上一个方法的类一样,这里就不多说啦

• Dao层的接口和mapper文件

  @Mapper
  @Repository
  public interface PageInfoMepper {
      /**
       * 多条件分页查询
       * @param pageInfo
       * 查询的数据集
       * @return List<PageInfo>
       */
      List<PageInfo> queryByPage(PageInfo pageInfo);
  
  }
  

  <!-- 这是一个根据条件查找全部记录的查询语句，并不需要写分页SQL，分页插件会拦截查询请求，并读取前台传来的分页查询参数重新生成分页查询语句。-->
  <select id="queryByPage" parameterType="com.liglei.pojo.PageInfo" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"></include>
    from fruits.page_info
    <where>
      <if test="name != null and name != ''">
        and name like '%${name}%'
      </if>
      <if test="sex != null and sex != ''">
        and sex like '%${sex}%'
      </if>
      <if test="note != null and note != ''">
        and note like '%${note}%'
      </if>
    </where>
  </select>
  

• Service层

  @Service
  public class PageService {
  
      @Autowired
      PageInfoMepper pageInfoMepper;
  
   		 //查询搜索数据
      public List<PageInfo> queryByPage(PageInfo pageInfo){
          return pageInfoMepper.queryByPage(pageInfo);
      }
  
  }
  
  

• Controller控制层

  @RequestMapping("/pageMy")
  public String pageMy(PageInfo pageInfo,Integer pageNum,Integer pageSize, Model model){
    //        pageSize = 3;
    //一页的数据集合
    PageHelper.startPage(pageNum, pageSize);
    List<PageInfo> pageInfos = pageService.queryByPage(pageInfo);
  
    //分页的page数据,比如：几页，每页几条数据pageNum=1, pageSize=5, startRow=0, endRow=5, total=6, pages=2
    //此处用的绝对名称，因为自己建的实体类与此类重名，com.github.pagehelper.PageInfo
    com.github.pagehelper.PageInfo<PageInfo> pageList = new com.github.pagehelper.PageInfo<PageInfo>(pageInfos);
  
    model.addAttribute("pageList",pageInfos);
    model.addAttribute("pageInfo",pageList);
    return "admin-list";
  }
  

• 展示页面代码,用的thymeleaf模板引擎

  
  <tr th:each="mypage : ${pageList}">
    <td>
      <input type="checkbox" name=""  lay-skin="primary">
    </td>
    <td th:text="${mypage.id}">ID</td>
    <td th:text="${mypage.name}">登录名</td>
    <td th:text="${mypage.sex}">SEX</td>
    <td th:text="${mypage.note}">备注</td>
    <td class="td-manage">
      <a onclick="member_stop(this,'10001')" href="javascript:;"  title="启用">
        <i class="layui-icon">&#xe601;</i>
      </a>
      <a title="编辑"  onclick="xadmin.open('编辑','admin-edit.html')" href="javascript:;">
        <i class="layui-icon">&#xe642;</i>
      </a>
      <a title="删除" onclick="member_del(this,'要删除的id')" href="javascript:;">
        <i class="layui-icon">&#xe640;</i>
      </a>
    </td>
  </tr>
  

• 页码测试

  <div class="page">
    <span >共<i th:text="${pageInfo.pages}">  </i>页 当前第<i th:text="${pageInfo.pageNum}">  </i>页</span>
    <ul class="pagination mb-0">
  
      <li class="page-item"><a th:href="@{/pageMy?pageNum=}+${pageInfo.pageNum <= 1 ? 1 : pageInfo.pageNum - 1}+'&username='+${username == null ? '' : username}" class="page-link" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>
      <!--   当页数小于7页-->
      <li class="page-item" th:classappend="${i == pageInfo.pageNum} ? 'active' : 'a'" th:if="${pageInfo.pages} < 7" th:each="i:${#numbers.sequence(1,pageInfo.pages)}"><a class="page-link" th:href="@{pageMy?pageNum=}+${i}+'&username='+${username == null ? '' : username}"> <span class="sr-only">[[${i}]]</span></a></li>
  
      <!-- 当页数大于或等于7页-->
      <!--  当前页小于5页   -->
      <li class="page-item" th:classappend="${i == pageInfo.pageNum} ? 'active' : 'a'" th:if="${pageInfo.pages >= 7} and ${pageInfo.pageNum < 5}" th:each="i:${#numbers.sequence(1,7)}"><a class="page-link" th:href="@{pageMy?pageNum=}+${i}+'&username='+${username == null ? '' : username}"> <span class="sr-only">[[${i}]]</span></a></li>
      <!--  当前页大于等于5页   -->
      <li class="page-item" th:classappend="${i == pageInfo.pageNum} ? 'active' : 'a'" th:if="${pageInfo.pages >= 7} and ${pageInfo.pageNum >= 5} and ${i <= pageInfo.pages}" th:each="i : ${#numbers.sequence(pageInfo.pageNum - 3, pageInfo.pageNum + 3)}"><a class="page-link" th:href="@{pageMy?pageNum=}+${i}+'&username='+${username == null ? '' : username}"> <span class="sr-only">[[${i}]]</span></a></li>
  
      <li class="page-item"><a th:href="@{/pageMy?pageNum=}+${pageInfo.pageNum < pageInfo.pages ? pageInfo.pageNum + 1 : pageInfo.pages}+'&username='+${username == null ? '' : username}" class="page-link" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>
    </ul>
    <span>共有<i th:text="${pageInfo.total}">  </i>条</span>
  </div>
  

• 页面效果图

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NgyK7eKO-1611476595146)(…/…/Program%20Files%20(x86)]/Typora/image/image-20210124161148992.png)

完结撒花!

参考博主链接：https://blog.csdn.net/m0_46669446/article/details/109921024

eInfo.pageNum < pageInfo.pages ? pageInfo.pageNum + 1 : pageInfo.pages}+’&username=’+KaTeX parse error: Expected 'EOF', got '&' at position 98: …-hidden="true">&̲raquo;</span></…{pageInfo.total}"> 条

```

• 页面效果图

完结撒花!

参考博主链接：https://blog.csdn.net/m0_46669446/article/details/109921024

等…