利用Spring MVC+Mybatis实现Mysql分页数据查询的过程详解
程序员文章站
2024-02-15 21:30:34
前言
因为最近没什么事，所以想着写一个分页的例子出来给大家分享一下。这个案例分前端和后台两部分，前端使用面向对象的方式写的，里面用到了一些回调函数和事件代理，有兴趣的朋友...
前言
因为最近没什么事，所以想着写一个分页的例子出来给大家分享一下。这个案例分前端和后台两部分，前端使用面向对象的方式写的，里面用到了一些回调函数和事件代理，有兴趣的朋友可以研究一下。后台的实现技术是将分页pager作为一个实体对象放到domain层，当前页、单页数据量、当前页开始数、当前页结束数、总数据条数、总页数都作为成员属性放到实体类里面。
以前项目数据库用的是oracle，sql语句的写法会从当前页开始数到当前页结束数查询数据。刚刚在这纠结了很长时间，查询到的数据显示数量总是有偏差，后来发现mysql的语句limit用的是当前页开始数到查询的条数，the fuck，我还一直以为它也是到当前页结束数呢。下面话不多说了，来一起看看详细的实现过程吧：
第一步，搭建这个小案例，引入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;
@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 <!-- <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方法没，这个方法就是判断当前执行到第几页，从哪个数字开始，到那个数字结束，是分页查询里面一个很关键的方法。
第四步：通过前后端的配合，看下实现后效果：
很low，页面我没做太多处理，这其实是一个table哈哈。
总结
分页查询大概就是这些了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。