SpringMVC+Mybatis实现的Mysql分页数据查询的示例

2019-11-26 11:39:39

字体：大中小

来源：转载

供稿：网友

周末这天手痒，正好没事干，想着写一个分页的例子出来给大家分享一下。

这个案例分前端和后台两部分，前端使用面向对象的方式写的，里面用到了一些回调函数和事件代理，有兴趣的朋友可以研究一下。后台的实现技术是将分页Pager作为一个实体对象放到domain层，当前页、单页数据量、当前页开始数、当前页结束数、总数据条数、总页数都作为成员属性放到实体类里面。

以前项目数据库用的是oracle，sql语句的写法会从当前页开始数到当前页结束数查询数据。刚刚在这纠结了很长时间，查询到的数据显示数量总是有偏差，后来发现mysql的语句limit用的是当前页开始数到查询的条数，the fuck，我还一直以为它也是到当前页结束数呢。

第一步，搭建这个小案例，引入spring和mybtis的jar包，配置对应的配置文件：

第二步，前端页面和数据的处理：

页面布局很简单。我将table和pager单独作为对象来处理，各自处理各自该干的事情，做到了很好的封装处理。个人认为这两个js和java的类很相似。

其它的地方都是按照正常分页的流程走的，话不多说，看看代码吧。　

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head>   <style>     .hide{display:none}     .myPager{height:40px;border-bottom:1px solid #eee;}    .myPager .pagerRow{width:100%;float:left;height:30px;margin-top:10px;}    .myPager .showPage{width:100%;float:left;height:30px;margin-top:10px;text-align: left;}    .myPager .showPage .numDiv{display:inline-block;}    .myPager .showPage .toBtn{color:#fff;font-size:20px;}    .myPager .showPage .disable{background-color: #c9c9c9;}    .myPager .showPage .nable{background-color:rgb(10%,65%,85%);cursor:default;}    .myPager .showPage .numDiv .disable{color:#777;}    .myPager .showPage .numDiv .nable{color:#fff;}    .myPager .showPage .cursor_default{cursor:default;}    .myPager .showPage .cursor_pointer{cursor:pointer;}    .showPage span{display: inline-block;padding: 0px 0px 1px 1px;margin-left:5px;      width: 21px;height: 21px;border-radius: 12px;line-height: 22px;font-size: 12px;      text-align: center;overflow: hidden;}   </style>   <script type="text/javascript" src="<c:url value='/res/jquery.js'/>"></script>   <script type="text/javascript" src="<c:url value='/res/MyPager.js'/>"></script>   <script type="text/javascript" src="<c:url value='/res/MyTable.js'/>"></script>  <script>    $(function(){      var $btn = $(".sub_btn");      $btn.click(function(){        $(this).addClass("hide");        new MyTable("employeeTab","<c:url value='/mam/queryListPage'/>");      })    })  </script> </head>  <body>   <div class="wrap">     <table class="employeeTab">       <tr>         <th>ID</th>         <th>姓名</th>         <th>年龄</th>         <th>性别</th>       </tr>     </table>     <button class="sub_btn">显示数据</button>   </div> </body></html>

页面引入了Mypager.js和MyTable.js，Mypager这套东西是封装的比较好的，有兴趣的朋友可以直接拿去用。现在插件满天飞，自己造的轮子肯定会逊色很多，但是这里涉及到js很多基础的知识点，初学的朋友可以当做学习参考使用;

Pager.getSpan = function(value,className){  return $("<span class='"+className+"'>"+value+"</span>");}function Pager($parent){  this.$parent = $parent;  this.pageCallBack = $.noop;  this.preVal = "＜";  this.nextVal = "＞";  this.splitChar = "…";  this.init();  this.spaceStep = 2;}Pager.prototype.setPageCallBack = function(pageCallBack){  this.pageCallBack = pageCallBack;  return this;}Pager.prototype.init = function(){  if(this.$parent.length == 0){    alert("pagediv not exists ");  }  this.$divRow = $("<div class='pagerRow'></div>").appendTo(this.$parent);  this.$div = $("<div class='showPage'>").appendTo(this.$parent);}Pager.prototype.clear = function(){  this.$div.empty();  this.$divRow.empty();}Pager.prototype.addSpan = function(value,className){  var $span = Pager.getSpan(value,className).appendTo(this.$numdiv);  $span.css("width",this.getSpanWidth(value)+"px");  return $span;}Pager.prototype.getSpanWidth = function(value){  var width = 21;  var curNeed = 0;  if(!isNaN(value)){    curNeed = value.toString().length * 8;  }  return curNeed>width?curNeed:width;}Pager.prototype.disable = function($span,flag){  var removeClass = flag?"nable cursor_pointer":"disable cursor_default";  var addClass = flag?"disable cursor_default":"nable cursor_pointer";  $span.removeClass(removeClass).addClass(addClass);  return $span;}Pager.prototype.show = function(pageCount,curPage,rowCount){  alert(0)  this.clear();  this.$divRow.html("　　共有"+pageCount+"页，"+rowCount+"条数据");  pageCount = pageCount?pageCount-0:0;  if(pageCount<=0){    return;  }  var self = this;  this.$prev = Pager.getSpan(this.preVal,"toBtn").appendTo(this.$div);  this.$numdiv = $("<div class='numDiv'></div>").appendTo(this.$div);  this.$nextVal = Pager.getSpan(this.nextVal,"toBtn").appendTo(this.$div);  curPage = curPage?curPage-0:1;  curPage = curPage<1?1:curPage;  curPage = curPage>pageCount?pageCount:curPage;  this.disable(this.$prev,curPage == 1);  if(curPage>1){    this.$prev.click(function(){        self.pageCallBack(curPage-1);      });  }  this.disable(this.$nextVal,curPage == pageCount);  if(curPage<pageCount){    this.$nextVal.click(function(){        self.pageCallBack(curPage+1);      });  }  var steps = this.getSteps(pageCount,curPage);  for(var i in steps){    if(i == curPage){      this.addSpan(steps[i],"nable");      continue;    }    if(steps[i] == this.splitChar){      this.addSpan(steps[i]);      continue;    }        this.addSpan(steps[i],"disable").hover($.proxy(this.mouseover,this),$.proxy(this.mouseout,this))      .click(function(){        alert(0)        self.pageCallBack($(this).html());      });  }}Pager.prototype.mouseout = function(e){  var $span = $(e.target);  this.disable($span,true);}Pager.prototype.mouseover = function(e){  var $span = $(e.target);  this.disable($span,false);}Pager.prototype.getSteps = function (pageCount,curPage){  var steps = {};  var curStar = curPage-3;  var curEnd = curPage+3;  for(var i=1;i<=pageCount;i++){    if((i>this.spaceStep && i<curStar)||(i>curEnd && i<pageCount-1)){      continue;    }    if((i==curStar && i>this.spaceStep) || (i==curEnd && i<pageCount-1)){      steps[i]=this.splitChar;      continue;    }    steps[i]=i;  }  return steps;}

下面是Mytable的实现代码：

function MyTable(tabName,url){  this.$tab = $("."+tabName);  this.$wrap = this.$tab.parent();  this.queryURL = url;  this.queryData = null;  this.pager = null;  this.init();}MyTable.prototype.init = function(){  this.pager = new Pager($("<div class='myPager'><div>").insertAfter(this.$wrap))      .setPageCallBack($.proxy(this.gotoPage,this));  this.gotoPage(1);}MyTable.prototype.gotoPage = function(curPage){  if(curPage){    this.queryData = {"curPage":curPage};  }  $.post(this.queryURL,this.queryData,$.proxy(this.show,this),"json");}MyTable.prototype.show = function(data){  this.clear();  var list = data.list;  var len = list.length;  var df = document.createDocumentFragment();  for(var i=0;i<len;i++){    var $tr = $("<tr></tr>");    var $id = $("<td>"+list[i].id+"</td>").appendTo($tr);    var $name = $("<td>"+list[i].name+"</td>").appendTo($tr);    var $age = $("<td>"+list[i].age+"</td>").appendTo($tr);    var $sex = $("<td>"+list[i].sex+"</td>").appendTo($tr);    df.appendChild($tr[0]);  }  this.$tab[0].appendChild(df);  this.pager.show(data.pager.pageCount, data.pager.curPage, data.pager.rowCount);}MyTable.prototype.clear = function(){  this.$tab.empty();}

前端页面的处理就是这些，展示效果如下：

第三步：后台的处理

后台的处理很简单，因为是自己写的数据，所以没有做太复杂的处理，首先我先把数据库的数据贴出来

一共18条数据，四个字段，id为主键。下面是controller处理前端请求的代码：

package cn.wangze.controller;import javax.servlet.http.HttpServletResponse;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import cn.wangze.domain.Employee;import cn.wangze.domain.Pager;import cn.wangze.service.BaseService;@Controller@RequestMapping("/mam")public class BaseController extends SuperController{                                                                                                                                  @Autowired  private BaseService<Employee> baseService;    @RequestMapping(value="/queryListPage")  public void queryListPage(Employee employee, Pager pager, HttpServletResponse response){    if(employee == null || pager == null){      sendError("参数错误",response);    }    sendJsonPager(pager, baseService.queryListPage(employee,pager), response);  }}

这个页面涉及到了前端返回值得处理，sendError和sendJsonPager方法在它的父类中有声明，代码如下：

public void sendParam(boolean successFlag,Object key,Object value,HttpServletResponse response){    StringBuffer sb = append(null,SUCCESS,successFlag?SUCCESS:ERROR);    if(!isEmpty(key)){      append(sb,key,value);    }    if(!MESSAGE.equals(key)){      append(sb,MESSAGE,successFlag?"操作已成功":"操作以失败");    }    writeJsonBuffer(sb.append("}"),response);}public void sendMsg(boolean successFlag,String errmsg,HttpServletResponse response){    sendParam(successFlag,MESSAGE,errmsg,response);}public void sendError(String msg,HttpServletResponse response){    sendMsg(false,msg,response);}

public void sendJsonPager(Pager pager, List<? extends JsonEntity> list, int i, HttpServletResponse response){    StringBuffer sb = append(null, MESSAGE, "success");    if(list==null || list.size()==0){      sendMsg(false, "查无数据", response);    }else{      sb.append(",").append(getJsonList(list,i)).append(pager.toJsonString());    }    sb.append("}");    logger.debug(sb);    HtmlUtil.writer(response, sb.toString());  }    public void sendJsonPager(Pager pager, List<? extends JsonEntity> list, HttpServletResponse response){    sendJsonPager(pager, list, 0, response);  }

通过上面BaseController的处理，我们可以看到它调用了BaseService的queryListPager方法，

package cn.wangze.service;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import cn.wangze.domain.Pager;import cn.wangze.mapper.BaseMapper;@Servicepublic class BaseService<T> {    @Autowired  private BaseMapper<T> baseMapper;    public Pager queryRowCount(T t, Pager pager){    return pager.initRowCount(baseMapper.queryRowCount(t));  }    public List<T> queryListPage(T t, Pager pager){    pager = this.queryRowCount(t,pager);    if(pager == null) return null;    return baseMapper.queryListPage(t, pager.getPageSize(), pager.getStart());  }}

BaseServie的queryRowCount方法先查询了一下数据的总条数，然后调用了BaseMapper的queryListPage方法，我们来看一下：

package cn.wangze.mapper;import java.util.List;import org.apache.ibatis.annotations.Param;public interface BaseMapper<T> {  public int queryRowCount(T t);  public List<T> queryListPage(@Param("t") T t,@Param("end") Integer end,@Param("start") Integer start);}

这个BaseMapper对应的是mybatis的xml文件，它负责编写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="cn.wangze.mapper.BaseMapper">  <sql id="ColumnList">    id,name,age,sex  </sql>  <sql id="ColumnList_t" >    t.id,t.name,t.age,t.sex  </sql>  <sql id="ValueList">    #{id},#{name},#{age},#{sex}  </sql>  <sql id="WhereClause">    where 1=1     <if test="id!=null and id!=''">and id=#{id}</if>    <if test="name!=null and name!=''">and name=#{name}</if>    <if test="age!=null and age!=''">and age=#{age}</if>    <if test="sex!=null and sex!=''">and sex=#{sex}</if>  </sql>  <sql id="WhereClause_pager" >    where 1=1     <if test="t.id!=null and t.id!=''">and t.id=#{t.id}</if>    <if test="t.name!=null and t.name!=''">and t.name=#{t.name}</if>    <if test="t.age!=null">and t.age=#{t.age}</if>    <if test="t.sex!=null and t.sex!=''">and t.sex=#{t.sex}</if>  </sql>  <sql id="SetClause" >    set     <trim suffixOverrides="," >      <if test="id!=null">id=#{id},</if>      <if test="name!=null">name=#{name},</if>      <if test="pid!=null">age=#{age},</if>      <if test="url!=null">sex=#{sex},</if>    </trim>  </sql>    <select id="queryRowCount" resultType="int" parameterType="employee">    select count(1) from employee <!-- <include refid="WhereClause"/>-->  </select>  <select id="queryListPage" resultType="employee">  <!-- 0-4 3-7 6-10 -->    select <include refid="ColumnList"/> from employee limit #{start},#{end};  </select></mapper>

最后我们看下employee和pager的实体类把：

package cn.wangze.domain;public class Employee extends JsonEntity{  private int id;  private String name;  private String age;  private String sex;  public int getId() {    return id;  }  public void setId(int id) {    this.id = id;  }  public String getName() {    return name;  }  public void setName(String name) {    this.name = name;  }  public String getAge() {    return age;  }  public void setAge(String age) {    this.age = age;  }  public String getSalary() {    return sex;  }  public void setSalary(String sex) {    this.sex = sex;  }  @Override  protected void addJsonFields(int i) {    addField("id", id).addField("name",name).addField("age", age).addField("sex", sex);  }    @Override  public String toString() {    return "id:"+id+",name:"+name+",age:"+age+",sex:"+sex;  }}

package cn.wangze.domain;public class Pager {  private int curPage = 1;  private int pageSize = 5;  private int start = 0;  private int end = 0;  private int pageCount;  private int rowCount;  public int getCurPage() {    return curPage;  }  public void setCurPage(int curPage) {    this.curPage = curPage;  }  public int getPageSize() {    return pageSize;  }  public void setPageSize(int pageSize) {    this.pageSize = pageSize;  }  public int getStart() {    return start;  }  public void setStart(int start) {    this.start = start;  }  public int getEnd() {    return end;  }  public void setEnd(int end) {    this.end = end;  }  public int getPageCount() {    return pageCount;  }  public void setPageCount(int pageCount) {    this.pageCount = pageCount;  }  public int getRowCount() {    return rowCount;  }  public void setRowCount(int rowCount) {    this.rowCount = rowCount;  }    public Pager initRowCount(int rowCount) {    if (rowCount == 0) {      return null;    }    int ps = getPageSize();    if (ps == 0) {      ps = 5;    }    int pc = (rowCount + ps - 1) / ps;//    int cp = getCurPage();    cp = cp > pc ? pc : cp;    cp = cp < 1 ? 1 : cp;    this.setPageCount(pc);    this.setCurPage(cp);    this.setEnd(cp * ps );    this.setStart((cp - 1) * ps);    this.rowCount = rowCount;    return this;  }    public StringBuffer toJsonString() {    return new StringBuffer(","+"/"pager/":{/"curPage/":/"" + this.curPage        + "/",/"pageCount/":/"" + this.pageCount + "/",/"rowCount/":/""        + this.rowCount + "/"}");  }  @Override  public String toString() {    return "Pager [curPage=" + curPage + ", pageSize=" + pageSize        + ", start=" + start + ", end=" + end + ", pageCount="        + pageCount + ", rowCount=" + rowCount + "]";  }}

不知道你还记不记得在BaseService的处理方法里面调用了pager的initRowCount方法没，这个方法就是判断当前执行到第几页，从哪个数字开始，到那个数字结束，是分页查询里面一个很关键的方法。

第四步：通过前后端的配合，看下实现后效果：