SpringMVC+Mybatis實現的Mysql分頁數據查詢

知識 08-13

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

這個案例分前端和後台兩部分，前端使用面向對象的方式寫的，裡面用到了一些回調函數和事件代理，有興趣的朋友可以研究一下。後台的實現技術是將分頁Pager作為一個實體對象放到domain層，當前頁、單頁數據量、當前頁開始數、當前頁結束數、總數據條數、總頁數都作為成員屬性放到實體類裡面。

以前項目資料庫用的是oracle，sql語句的寫法會從當前頁開始數到當前頁結束數查詢數據。剛剛在這糾結了很長時間，查詢到的數據顯示數量總是有偏差，後來發現mysql的語句limit用的是當前頁開始數到查詢的條數，the fuck，我還一直以為它也是到當前頁結束數呢。

第一步，搭建這個小案例，引入spring和mybtis的jar包，配置對應的配置文件：

SpringMVC+Mybatis實現的Mysql分頁數據查詢

第二步，前端頁面和數據的處理：

頁面布局很簡單。我將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>

頁面引入了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;
}

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

SpringMVC+Mybatis實現的Mysql分頁數據查詢

第三步：後台的處理

後台的處理很簡單，因為是自己寫的數據，所以沒有做太複雜的處理，首先我先把資料庫的數據貼出來

SpringMVC+Mybatis實現的Mysql分頁數據查詢

一共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;

@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方法先查詢了一下數據的總條數，然後調用了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 
</select>
<select id="queryListPage" resultType="employee">

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方法沒，這個方法就是判斷當前執行到第幾頁，從哪個數字開始，到那個數字結束，是分頁查詢裡面一個很關鍵的方法。
第四步：通過前後端的配合，看下實現後效果：

SpringMVC+Mybatis實現的Mysql分頁數據查詢