利用Spring MVC+Mybatis实现Mysql分页数据查询的过程详解

").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 "Phttp://ager [curPage=" + curPage + ", pageSize=" + pageSize

+ ", start=" + start + ", end=" + end + ", pageCount="

+ pageCount + ", rowCount=" + rowCount + "]";

}

}

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

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

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

总结

分页查询大概就是这些了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

").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 "Phttp://ager [curPage=" + curPage + ", pageSize=" + pageSize

+ ", start=" + start + ", end=" + end + ", pageCount="

+ pageCount + ", rowCount=" + rowCount + "]";

}

}

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

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

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

总结

分页查询大概就是这些了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

").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 "Phttp://ager [curPage=" + curPage + ", pageSize=" + pageSize

+ ", start=" + start + ", end=" + end + ", pageCount="

+ pageCount + ", rowCount=" + rowCount + "]";

}

}

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

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

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

总结

分页查询大概就是这些了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对我们的支持。