SpringMVC+Mybatis實(shí)現(xiàn)的Mysql分頁數(shù)據(jù)查詢的示例

更新時(shí)間：2017年08月14日 09:20:48 作者：blue_wz

本篇文章主要介紹了SpringMVC+Mybatis實(shí)現(xiàn)的Mysql分頁數(shù)據(jù)查詢的示例，具有一定的參考價(jià)值，有興趣的可以了解一下

周末這天手癢，正好沒事干，想著寫一個(gè)分頁的例子出來給大家分享一下。

這個(gè)案例分前端和后臺(tái)兩部分，前端使用面向?qū)ο蟮姆绞綄懙?，里面用到了一些回調(diào)函數(shù)和事件代理，有興趣的朋友可以研究一下。后臺(tái)的實(shí)現(xiàn)技術(shù)是將分頁P(yáng)ager作為一個(gè)實(shí)體對(duì)象放到domain層，當(dāng)前頁、單頁數(shù)據(jù)量、當(dāng)前頁開始數(shù)、當(dāng)前頁結(jié)束數(shù)、總數(shù)據(jù)條數(shù)、總頁數(shù)都作為成員屬性放到實(shí)體類里面。

以前項(xiàng)目數(shù)據(jù)庫用的是oracle，sql語句的寫法會(huì)從當(dāng)前頁開始數(shù)到當(dāng)前頁結(jié)束數(shù)查詢數(shù)據(jù)。剛剛在這糾結(jié)了很長時(shí)間，查詢到的數(shù)據(jù)顯示數(shù)量總是有偏差，后來發(fā)現(xiàn)mysql的語句limit用的是當(dāng)前頁開始數(shù)到查詢的條數(shù)，the fuck，我還一直以為它也是到當(dāng)前頁結(jié)束數(shù)呢。

第一步，搭建這個(gè)小案例，引入spring和mybtis的jar包，配置對(duì)應(yīng)的配置文件：

第二步，前端頁面和數(shù)據(jù)的處理：

頁面布局很簡單。我將table和pager單獨(dú)作為對(duì)象來處理，各自處理各自該干的事情，做到了很好的封裝處理。個(gè)人認(rèn)為這兩個(gè)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">顯示數(shù)據(jù)</button>
   </div>
 </body>
</html>

頁面引入了Mypager.js和MyTable.js，Mypager這套東西是封裝的比較好的，有興趣的朋友可以直接拿去用?，F(xiàn)在插件滿天飛，自己造的輪子肯定會(huì)遜色很多，但是這里涉及到j(luò)s很多基礎(chǔ)的知識(shí)點(diǎn)，初學(xué)的朋友可以當(dāng)做學(xué)習(xí)參考使用;

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+"條數(shù)據(jù)");
  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的實(shí)現(xiàn)代碼：

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();
}

前端頁面的處理就是這些，展示效果如下：

第三步：后臺(tái)的處理

后臺(tái)的處理很簡單，因?yàn)槭亲约簩懙臄?shù)據(jù)，所以沒有做太復(fù)雜的處理，首先我先把數(shù)據(jù)庫的數(shù)據(jù)貼出來

一共18條數(shù)據(jù)，四個(gè)字段，id為主鍵。下面是controller處理前端請(qǐng)求的代碼：

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("參數(shù)錯(cuò)誤",response);
    }
    sendJsonPager(pager, baseService.queryListPage(employee,pager), response);
  }
}

這個(gè)頁面涉及到了前端返回值得處理，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, "查無數(shù)據(jù)", 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的處理，我們可以看到它調(diào)用了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;

@Service
public 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方法先查詢了一下數(shù)據(jù)的總條數(shù)，然后調(diào)用了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);
}

這個(gè)BaseMapper對(duì)應(yīng)的是mybatis的xml文件，它負(fù)責(zé)編寫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的實(shí)體類把：

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的處理方法里面調(diào)用了pager的initRowCount方法沒，這個(gè)方法就是判斷當(dāng)前執(zhí)行到第幾頁，從哪個(gè)數(shù)字開始，到那個(gè)數(shù)字結(jié)束，是分頁查詢里面一個(gè)很關(guān)鍵的方法。

第四步：通過前后端的配合，看下實(shí)現(xiàn)后效果：