使用spring mvc与mybatis如何对Mysql数据库进行分页查询？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。第一步，搭建这个小案例，引入spring和mybtis的
使用spring mvc与mybatis如何对Mysql数据库进行分页查询？相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
第一步，搭建这个小案例，引入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方法没，这个方法就是判断当前执行到第几页，从哪个数字开始，到那个数字结束，是分页查询里面一个很关键的方法。
看完上述内容，你们掌握使用Spring MVC与Mybatis如何对Mysql数据库进行分页查询的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注编程网数据库频道，感谢各位的阅读！
--结束END--
本文标题: 使用Spring MVC与Mybatis如何对Mysql数据库进行分页查询
本文链接: https://www.lsjlt.com/news/224235.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0