SpringMVC+Mybatis实现的Mysql分页数据查询的示例

时间:2021-12-12 07:41:52

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

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

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

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

SpringMVC+Mybatis实现的Mysql分页数据查询的示例  

第二步,前端页面和数据的处理:

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

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

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<%@ 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很多基础的知识点,初学的朋友可以当做学习参考使用;

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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的实现代码:

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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处理前端请求的代码:

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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方法在它的父类中有声明,代码如下:

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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);
}
 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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方法, 

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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方法,我们来看一下:

 
?
1
 
2
3
4
5
6
7
8
9
10
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语句:

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?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的实体类把: 

 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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;
  }
}
 
?
1
 
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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分页数据查询的示例

  SpringMVC+Mybatis实现的Mysql分页数据查询的示例

  SpringMVC+Mybatis实现的Mysql分页数据查询的示例
  

很low,页面我没做太多处理,这其实是一个table哈哈。分页查询大概就是这些了

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://www.cnblogs.com/blue-wz/p/7353276.html?utm_source=tuicool&utm_medium=referral