springmvc+mybatis 实现分页查询

一、开发前准备

　　1.参考：https://www.cnblogs.com/liandy0906/p/9985453.html

　　2.目的：为简化分页功能，设计了一个分页的JSP标签，只需要在页面使用分页标签，就可以完成所有页面的分页功能。

 

二、创建类　　

　　1.实体类

package com.geely.entity;

import java.io.Serializable;
import java.util.Date;

/*
 * 电池包 
 */
public class BatteryPackage implements Serializable {
    //主键
    private int id;
    //订单编号
    private String orderNo;
    //储能装置型号
    private String systemModelId;
    //所属储能装置编码
    private String systemId;    
    //文件名
    private String fileName;
    //储能装置中的编号
    private String serial;
    //电池包编码
    private String code;
    //电池包型号
    private String modelId;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getOrderNo() {
        return orderNo;
    }
    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }
    public String getSystemModelId() {
        return systemModelId;
    }
    public void setSystemModelId(String systemModelId) {
        this.systemModelId = systemModelId;
    }
    public String getSystemId() {
        return systemId;
    }
    public void setSystemId(String systemId) {
        this.systemId = systemId;
    }
    public String getFileName() {
        return fileName;
    }
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }
    public String getSerial() {
        return serial;
    }
    public void setSerial(String serial) {
        this.serial = serial;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getModelId() {
        return modelId;
    }
    public void setModelId(String modelId) {
        this.modelId = modelId;
    }
    
    
}

　　2.工具类　　

package com.geely.util;

/**
 *  分页实体 
 */
public class PageModel {
    
    // 默认每页4条数据
    public static int PAGE_DEFAULT_SIZE = 4;
    
    /** 分页总数据条数  */
    private int recordCount;
    /** 当前页面 */
    private int pageIndex ;
    /** 每页分多少条数据   */
    private int pageSize = PAGE_DEFAULT_SIZE = 4;
    
    /** 总页数  */
    private int totalSize;

    public int getRecordCount() {
        this.recordCount = this.recordCount <= 0 ? 0:this.recordCount;
        return recordCount;
    }
    public void setRecordCount(int recordCount) {
        this.recordCount = recordCount;
    }
    public int getPageIndex() {
        this.pageIndex = this.pageIndex <= 0?1:this.pageIndex;
        /** 判断当前页面是否超过了总页数:如果超过了默认给最后一页作为当前页  */
        this.pageIndex = this.pageIndex>=this.getTotalSize()?this.getTotalSize():this.pageIndex;
        
        return pageIndex;
    }
    public void setPageIndex(int pageIndex) {
        this.pageIndex = pageIndex;
    }
    public int getPageSize() {
        this.pageSize = this.pageSize <= PAGE_DEFAULT_SIZE?PAGE_DEFAULT_SIZE:this.pageSize;
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    
    public int getTotalSize() {
        if(this.getRecordCount() <=0){
            totalSize = 0 ;
        }else{
            totalSize = (this.getRecordCount() -1)/this.getPageSize() + 1;
        }
        return totalSize;
    }
    
    
    public int getFirstLimitParam(){
        return (this.getPageIndex()-1)*this.getPageSize() ;
    }

}

package com.geely.util;


import java.io.IOException;

import javax.servlet.jsp.JspException;
import javax.servlet.jsp.tagext.SimpleTagSupport;

/**
 * 分页标签
 */
public class PagerTag extends SimpleTagSupport {
    /** 定义请求URL中的占位符常量 */
    private static final String TAG = "{0}";
    
    /** 当前页码 */
    private int pageIndex;
    /** 每页显示的数量 */
    private int pageSize;
    /** 总记录条数 */
    private int recordCount;
    /** 请求URL page.action?pageIndex={0}*/
    private String submitUrl;
    /** 样式 */
    private String style = "sabrosus";
    
    /** 定义总页数 */
    private int totalPage = 0;
    
    /**  在页面上引用自定义标签就会触发一个标签处理类   */
    @Override
    public void doTag() throws JspException, IOException {
        /** 定义它拼接是终的结果 */
        StringBuilder res = new StringBuilder();
        /** 定义它拼接中间的页码 */
        StringBuilder str = new StringBuilder();
        /** 判断总记录条数 */
        if (recordCount > 0){   //1499 / 15  = 100
            /** 需要显示分页标签，计算出总页数 需要分多少页 */
            totalPage = (this.recordCount - 1) / this.pageSize + 1; 
            
            /** 判断上一页或下一页需不需要加a标签 */
            if (this.pageIndex == 1){ // 首页
                str.append("<span class='disabled'>上一页</span>");
                
                /** 计算中间的页码 */
                this.calcPage(str);
                
                /** 下一页需不需要a标签 */
                if (this.pageIndex == totalPage){
                    /** 只有一页 */
                    str.append("<span class='disabled'>下一页</span>");
                }else{
                    String tempUrl = this.submitUrl.replace(TAG, String.valueOf(pageIndex + 1));
                    str.append("<a href='"+ tempUrl +"'>下一页</a>");
                }
            }else if (this.pageIndex == totalPage){ // 尾页
                String tempUrl = this.submitUrl.replace(TAG, String.valueOf(pageIndex - 1));
                str.append("<a href='"+ tempUrl +"'>上一页</a>");
                
                /** 计算中间的页码 */
                this.calcPage(str);
                
                str.append("<span class='disabled'>下一页</span>");
            }else{ // 中间
                String tempUrl = this.submitUrl.replace(TAG, String.valueOf(pageIndex - 1));
                str.append("<a href='"+ tempUrl +"'>上一页</a>");
                
                /** 计算中间的页码 */
                this.calcPage(str);
                
                tempUrl = this.submitUrl.replace(TAG, String.valueOf(pageIndex + 1));
                str.append("<a href='"+ tempUrl +"'>下一页</a>");
            }
            
            /** 拼接其它的信息 */
            res.append("<table width='100%' align='center' style='font-size:13px;' class='"+ style +"'>");
            res.append("<tr><td style='COLOR: #0061de; MARGIN-RIGHT: 3px; PADDING-TOP: 2px; TEXT-DECORATION: none'>" + str.toString());
            res.append("&nbsp;跳转到&nbsp;&nbsp;<input style='text-align: center;BORDER-RIGHT: #aaaadd 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #aaaadd 1px solid; PADDING-LEFT: 5px; PADDING-BOTTOM: 2px; MARGIN: 2px; BORDER-LEFT: #aaaadd 1px solid; COLOR: #000099; PADDING-TOP: 2px; BORDER-BOTTOM: #aaaadd 1px solid; TEXT-DECORATION: none' type='text' size='2' id='pager_jump_page_size'/>");
            res.append("&nbsp;<input type='button' style='text-align: center;BORDER-RIGHT: #dedfde 1px solid; PADDING-RIGHT: 6px; BACKGROUND-POSITION: 50% bottom; BORDER-TOP: #dedfde 1px solid; PADDING-LEFT: 6px; PADDING-BOTTOM: 2px; BORDER-LEFT: #dedfde 1px solid; COLOR: #0061de; MARGIN-RIGHT: 3px; PADDING-TOP: 2px; BORDER-BOTTOM: #dedfde 1px solid; TEXT-DECORATION: none' value='确定' id='pager_jump_btn'/>");
            res.append("</td></tr>");
            res.append("<tr align='center'><td style='font-size:13px;'><tr><td style='COLOR: #0061de; MARGIN-RIGHT: 3px; PADDING-TOP: 2px; TEXT-DECORATION: none'>");
            /** 开始条数 */
            int startNum = (this.pageIndex - 1) * this.pageSize + 1;
            /** 结束条数 */
            int endNum = (this.pageIndex == this.totalPage) ? this.recordCount : this.pageIndex * this.pageSize;
            
            res.append("总共<font color='red'>"+ this.recordCount +"</font>条记录，当前显示"+ startNum +"-"+ endNum +"条记录。");
            res.append("</td></tr>");
            res.append("</table>");
            res.append("<script type='text/javascript'>");
            res.append("   document.getElementById('pager_jump_btn').onclick = function(){");
            res.append("      var page_size = document.getElementById('pager_jump_page_size').value;");
            res.append("      if (!/^[1-9]\\d*$/.test(page_size) || page_size < 1 || page_size > "+ this.totalPage +"){");
            res.append("          alert('请输入[1-"+ this.totalPage +"]之间的页码！');");
            res.append("      }else{");
            res.append("         var submit_url = '" + this.submitUrl + "';");
            res.append("         window.location = submit_url.replace('"+ TAG +"', page_size);");
            res.append("      }");
            res.append("}");
            res.append("</script>");
            
            
        }else{
            res.append("<table align='center' style='font-size:13px;'><tr><td style='COLOR: #0061de; MARGIN-RIGHT: 3px; PADDING-TOP: 2px; TEXT-DECORATION: none'>总共<font color='red'>0</font>条记录，当前显示0-0条记录。</td></tr></table>");
        }
        this.getJspContext().getOut().print(res.toString());
    }
    
    
    /** 计算中间页码的方法 */
    private void calcPage(StringBuilder str) {
        /** 判断总页数 */
        if (this.totalPage <= 11){
            /** 一次性显示全部的页码 */
            for (int i = 1; i <= this.totalPage; i++){
                if (this.pageIndex == i){
                    /** 当前页码 */
                    str.append("<span class='current'>"+ i +"</span>");
                }else{
                    String tempUrl = this.submitUrl.replace(TAG, String.valueOf(i));
                    str.append("<a href='"+ tempUrl +"'>"+ i +"</a>");
                }
            }
        }else{
            /** 靠近首页 */
            if (this.pageIndex <= 8){
                for (int i = 1; i <= 10; i++){
                    if (this.pageIndex == i){
                        /** 当前页码 */
                        str.append("<span class='current'>"+ i +"</span>");
                    }else{
                        String tempUrl = this.submitUrl.replace(TAG, String.valueOf(i));
                        str.append("<a href='"+ tempUrl +"'>"+ i +"</a>");
                    }
                }
                str.append("...");
                String tempUrl = this.submitUrl.replace(TAG, String.valueOf(this.totalPage));
                str.append("<a href='"+ tempUrl +"'>"+ this.totalPage +"</a>");
            }
            /** 靠近尾页 */
            else if (this.pageIndex + 8 >= this.totalPage){
                String tempUrl = this.submitUrl.replace(TAG, String.valueOf(1));
                str.append("<a href='"+ tempUrl +"'>1</a>");
                str.append("...");
                
                for (int i = this.totalPage - 10; i <= this.totalPage; i++){
                    if (this.pageIndex == i){
                        /** 当前页码 */
                        str.append("<span class='current'>"+ i +"</span>");
                    }else{
                        tempUrl = this.submitUrl.replace(TAG, String.valueOf(i));
                        str.append("<a href='"+ tempUrl +"'>"+ i +"</a>");
                    }
                }
            }
            /** 在中间 */
            else{
                String tempUrl = this.submitUrl.replace(TAG, String.valueOf(1));
                str.append("<a href='"+ tempUrl +"'>1</a>");
                str.append("...");
                
                for (int i = this.pageIndex - 4; i <= this.pageIndex + 4; i++){
                    if (this.pageIndex == i){
                        /** 当前页码 */
                        str.append("<span class='current'>"+ i +"</span>");
                    }else{
                        tempUrl = this.submitUrl.replace(TAG, String.valueOf(i));
                        str.append("<a href='"+ tempUrl +"'>"+ i +"</a>");
                    }
                }
                
                str.append("...");
                tempUrl = this.submitUrl.replace(TAG, String.valueOf(this.totalPage));
                str.append("<a href='"+ tempUrl +"'>"+ this.totalPage +"</a>");
            }
        }
    }

    /** setter 方法 */
    public void setPageIndex(int pageIndex) {
        this.pageIndex = pageIndex;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public void setRecordCount(int recordCount) {
        this.recordCount = recordCount;
    }
    public void setSubmitUrl(String submitUrl) {
        this.submitUrl = submitUrl;
    }
    public void setStyle(String style) {
        this.style = style;
    }


}

三、自定义jsp标签

　　1.在WEB-INF下增加一个tld标签文件：page.tld　　

<?xml version="1.0" encoding="utf-8"?>
<taglib xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
                        http://java.sun.com/xml/ns/javaee/web-jsptaglibrary_2_1.xsd"
                           version="2.1">
   
  <!-- 描述 自定义标签版本的一种描述 --> 
  <description>Pager 1.0 core library</description>
  <!-- 显示的名称 导包进行的一个展示 -->
  <display-name>Pager core</display-name>
  <!-- 版本号 -->
  <tlib-version>1.0</tlib-version>
  <!-- 短名 -->
  <short-name>fkjava</short-name>
  <!-- uri ：导包 -->
  <uri>/pager-tags</uri>
  
  <!-- 定义一个标签 -->
  <tag>
          <!-- 标签名 -->
          <name>pager</name>
          <!-- 标签处理类，根据自己的实际文件目录更改 -->
          <tag-class>com.geely.util.PagerTag</tag-class>
          <!-- 设置标签为空 -->
          <body-content>empty</body-content>
          
          <!-- 定义标签的属性 -->
          <attribute>
              <!-- 属性名 表示分页的第几页 -->
              <name>pageIndex</name>
              <!-- 必须的 -->
              <required>true</required>
              <!-- run time expression value 为true支持EL表达式 -->
              <rtexprvalue>true</rtexprvalue>
          </attribute>
          
          <!-- 定义标签的属性 -->
          <attribute>
              <!-- 属性名 表示分页标签 ，每页显示多少条数据 -->
              <name>pageSize</name>
              <!-- 必须的 -->
              <required>true</required>
              <!-- run time expression value 为true支持EL表达式 -->
              <rtexprvalue>true</rtexprvalue>
          </attribute>
          <!-- 定义标签的属性 -->
          <attribute>
              <!-- 属性名  记录分页的总数 -->
              <name>recordCount</name>
              <!-- 必须的 -->
              <required>true</required>
              <!-- run time expression value 为true支持EL表达式 -->
              <rtexprvalue>true</rtexprvalue>
          </attribute>
          <!-- 定义标签的属性 -->
          <attribute>
              <!-- 属性名 -->
              <name>submitUrl</name>
              <!-- 必须的 -->
              <required>true</required>
              <!-- run time expression value 为true支持EL表达式 -->
              <rtexprvalue>true</rtexprvalue>
          </attribute>
          <!-- 定义标签的属性 -->
          <attribute>
              <!-- 属性名 -->
              <name>style</name>
              <!-- 必须的 -->
              <required>false</required>
              <!-- run time expression value 为true支持EL表达式 -->
              <rtexprvalue>true</rtexprvalue>
          </attribute>
  </tag>
</taglib>

　　2.jsp页面　　

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>   
<%   
String path = request.getContextPath();  
String basePath=request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+ "/";%> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">   
<html>   
  <head>   
    <base href="<%=basePath%>">   
    <%@ page language="java" contentType="text/html; charset=UTF-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="f" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <!-- 设置一个项目路径的变量  -->
    <c:set var="ctx" value="${pageContext.request.contextPath}"></c:set>
    <!-- 配置分页标签   -->
    <%@ taglib prefix="fkjava" uri="/pager-tags" %>
    </head>

<body>
    
    <table>
        <tr>
            <th>id</th>
            <th>systemModelId</th>
        </tr>
        <c:forEach items="${list }" var="item">
        <tr>
            <td>${item.id }</td>
            <td>${item.systemModelId }</td>
        </tr>
        </c:forEach>

    　　 <!-- 分页标签 -->
        <tr valign="top">
            <td align="center" class="font3">
                <fkjava:pager
                    pageIndex="${requestScope.pageModel.pageIndex}"
                    pageSize="${requestScope.pageModel.pageSize}"
                    recordCount="${requestScope.pageModel.recordCount}"
                    style="digg" 
                    submitUrl="${ctx}/package/index?pageIndex={0}" />
            </td>
    　　　　 <!-- 点击一次分页标签就重新查询一次数据库 -->
        </tr>
    </table> 
</body>   
</html>

 　　3.Mapper

         @SelectProvider(type=BatteryPackageSqlProvider.class,method="selectByPage")
         List<BatteryPackage> selectByPage(Map<String, Object> params);
         @SelectProvider(type=BatteryPackageSqlProvider.class,method="count")
         Integer count(Map<String, Object> params);

 　　4.Provider.

package com.geely.provider;

import java.util.Map;

import org.apache.ibatis.jdbc.SQL;

import com.geely.entity.BatteryPackage;

public class BatteryPackageSqlProvider {
    // 分页动态查询
    public String selectByPage(final Map<String, Object> params){
        String sql =  new SQL(){
            {
                SELECT("*");
                FROM("BatteryPackage");
                if(params.get("model") != null){
                    BatteryPackage model = (BatteryPackage)params.get("model");
                        if(model.getOrderNo() != null && !model.getOrderNo().equals("")){
                            WHERE("  orderNo  LIKE CONCAT('%',#{model.orderNo},'%') ");
                        }

                     }
                }
            }.toString();
                
        if(params.get("pageModel") != null){
            sql += " limit #{pageModel.firstLimitParam} , #{pageModel.pageSize}  ";
        }
        return sql;
     }    
            
    // 动态查询总数量
    public String count(final Map<String, Object> params){
        return new SQL(){
            {
                SELECT("count(*)");
                FROM("BatteryPackage");
                if(params.get("model") != null){
                    BatteryPackage model = (BatteryPackage)params.get("model");
                        if(model.getOrderNo() != null && !model.getOrderNo().equals("")){
                            WHERE("  orderNo  LIKE CONCAT('%',#{model.orderNo},'%') ");
                        }

                     }
                }
        }.toString();
    }
}

　　4.service

package com.geely.service;

import java.util.List;
import java.util.Map;

import com.geely.entity.BatteryPackage;
import com.geely.entity.User;
import com.geely.util.PageModel;

public interface IBatteryPackageService {
    List<BatteryPackage> pagedList(BatteryPackage model,PageModel pageModel);
}

package com.geely.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.geely.dao.IBatteryPackageDao;
import com.geely.dao.IUserDao;
import com.geely.entity.BatteryPackage;
import com.geely.entity.User;
import com.geely.util.PageModel;

@Service("batteryPackageService")   
public class BatteryPackageServiceImpl implements IBatteryPackageService {   
    @Resource   
    private IBatteryPackageDao batteryPackageDao;
    /*
     * 分页查询电池包
     * @see com.geely.service.IBatteryPackageService#pagedList(com.geely.entity.BatteryPackage, com.geely.util.PageModel)
     */
    public List<BatteryPackage> pagedList(BatteryPackage model,
            PageModel pageModel) {
        Map<String, Object> params=new HashMap<String, Object>();
        params.put("model", model);
        params.put("pageModel", pageModel);
        int recordCount=batteryPackageDao.count(params);
        List<BatteryPackage> list=null;
        if(recordCount>0)
        {
            pageModel.setRecordCount(recordCount);
            list= batteryPackageDao.selectByPage(params);
        }

        return list;
    }   
         
 
     
     

     
}

　　5.controller

package com.geely.controller;

import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;

import com.geely.entity.BatteryPackage;
import com.geely.entity.User;
import com.geely.service.IBatteryPackageService;
import com.geely.service.IUserService;
import com.geely.util.PageModel;

@Controller   
@RequestMapping("/package") 
public class BatteryPackageController {
    @Resource   
    private IBatteryPackageService batteryPackageService;   
    @RequestMapping("/index")   
    public String index(HttpServletRequest request,Model model,Integer pageIndex,@ModelAttribute BatteryPackage batteryPackage){
        
        PageModel pageModel=new PageModel();
        pageModel.setPageIndex(pageIndex);

        List<BatteryPackage>list= batteryPackageService.pagedList(batteryPackage, pageModel);
        model.addAttribute("list", list);   
        model.addAttribute("pageModel", pageModel);
        return "package/index";   
    }  
    

}

　　5.entity

package com.geely.entity;

import java.io.Serializable;
import java.util.Date;

/*
 * 电池包 
 */
public class BatteryPackage implements Serializable {
    //主键
    private int id;
    //订单编号
    private String orderNo;
    //储能装置型号
    private String systemModelId;
    //所属储能装置编码
    private String systemId;    
    //文件名
    private String fileName;
    //储能装置中的编号
    private String serial;
    //电池包编码
    private String code;
    //电池包型号
    private String modelId;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getOrderNo() {
        return orderNo;
    }
    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }
    public String getSystemModelId() {
        return systemModelId;
    }
    public void setSystemModelId(String systemModelId) {
        this.systemModelId = systemModelId;
    }
    public String getSystemId() {
        return systemId;
    }
    public void setSystemId(String systemId) {
        this.systemId = systemId;
    }
    public String getFileName() {
        return fileName;
    }
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }
    public String getSerial() {
        return serial;
    }
    public void setSerial(String serial) {
        this.serial = serial;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getModelId() {
        return modelId;
    }
    public void setModelId(String modelId) {
        this.modelId = modelId;
    }
    
    
}