mybatis的mysql分页_SpringMVC+Mybatis实现的Mysql分页数据查询

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

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

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

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

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

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

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

.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;}

$(function(){

var $btn = $(".sub_btn");

$btn.click(function(){

$(this).addClass("hide");

new MyTable("employeeTab","");

})

})

ID	姓名	年龄	性别

显示数据

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

Pager.getSpan = function(value,className){

return $(""+value+"");

}

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 = $("

this.$div = $("

").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 = $("

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

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 && icurEnd && i

continue;

}

if((i==curStar && i>this.spaceStep) || (i==curEnd && i

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($("

").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

var $tr = $("

");

var $id = $("

"+list[i].id+"").appendTo($tr);

var $name = $("

"+list[i].name+"").appendTo($tr);

var $age = $("

"+list[i].age+"").appendTo($tr);

var $sex = $("

"+list[i].sex+"").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 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 {

@Autowired

private BaseMapper baseMapper;

public Pager queryRowCount(T t, Pager pager){

return pager.initRowCount(baseMapper.queryRowCount(t));

}

public List 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 {

public int queryRowCount(T t);

public List queryListPage(@Param("t") T t,@Param("end") Integer end,@Param("start") Integer start);

}

这个BaseMapper对应的是mybatis的xml文件，它负责编写sql语句：

id,name,age,sex

t.id,t.name,t.age,t.sex

#{id},#{name},#{age},#{sex}

where 1=1

and id=#{id}

and name=#{name}

and age=#{age}

and sex=#{sex}

where 1=1

and t.id=#{t.id}

and t.name=#{t.name}

and t.age=#{t.age}

and t.sex=#{t.sex}

set

id=#{id},

name=#{name},

age=#{age},

sex=#{sex},

select count(1) from employee

select from employee limit #{start},#{end};

最后我们看下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方法没，这个方法就是判断当前执行到第几页，从哪个数字开始，到那个数字结束，是分页查询里面一个很关键的方法。

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

很low，页面我没做太多处理，这其实是一个table哈哈。

分页查询大概就是这些了，肚子很饿，还没有吃饭，先写到这里把。