SSM实现分页查询展示
首先分页展示会联想到SQL语句 limit 的用法,
下面是MySql数据库实现分页查询的SQL语句写法!
select * from table limit(curPage-1)*pageSize,pageSize;
例如:
select * from table limit 0,10;
解析:limit(curPage-1)*pageSize,pageSize;
curPage:当前页数
pageSize:每页显示条数
(curPage-1)*pageSize :这个公式表示起始页码,通俗的理解就是从第几页开始查询
总结:
根据curPage,pageSize这两个变量就可以实现分页查询的内容
所以下面的分页查询的内容就是围绕着这两个变量进行编写
废话不多说上步骤:
一:
在最底层的mybatis数据访问层中,为分页查询编写两个接口方法
第一个接口:是查询到总记录条数totalCount** 因为由此总条数totalCount
可以计算出总页数:totalCount/pagesize**
第二个接口就是:分页查询的接口方法
分别在Mybatis的映射文件中实现接口,说白了就是写SQL
第一个接口:得出总条数
<select id="sumCount" resultType="int">
SELECT COUNT(*) FROM smbms_user u,smbms_role r
<where>
u.userRole=r.id
<if test="userName!=null and userName!=''">
AND userName LIKE "%"#{userName}"%"
</if>
<if test="RoleId!=null and RoleId!=0">
AND u.userRole=#{RoleId}
</if>
</where>
</select>
第二个接口:分页查询信息
<resultMap id="limit" type="com.smbms.entity.User">
<id property="id" column="id"></id>
<result property="userCode" column="userCode"></result>
<result property="userName" column="userName"></result>
<result property="gender" column="gender"></result>
<result property="birthday" column="birthday" javaType="java.sql.Date"></result>
<result property="phone" column="phone"></result>
</resultMap>
<select id="PageSize" resultMap="limit">
SELECT u.id,userCode,userName,gender,birthday,roleName,phone FROM smbms_user u,smbms_role r
<where>
u.userRole=r.id
<if test="userName!=null and userName!=''">
AND userName LIKE "%"#{userName}"%"
</if>
<if test="RoleId!=null and RoleId!=0">
AND u.userRole=#{RoleId}
</if>
</where>
LIMIT #{beginPage},#{pageSize}
</select>
二:
在工具包或者实体类包内加入page类,便于管理变量,减少代码的耦合性,将用到的变量给封装到类中便于取用和管理
package com.smbms.until;
import java.util.List;
public class Page {
private Integer currPageNo=1;//当前页码
private Integer pageSize=10; //页面大小,就是每页显示多少
private Integer totalCount; //记录总数
private Integer totalPageCount; //总页数
List<Object> newsList=null;
public Page(Integer currPageNo, Integer pageSize,
Integer totalCount, Integer totalPageCount, List<Object> newsList) {
this.totalPageCount=totalPageCount;
this.currPageNo = currPageNo;
this.pageSize = pageSize;
this.totalCount = totalCount;
this.totalPageCount = totalPageCount;
this.newsList = newsList;
}
public Page(Integer currPageNo, Integer pageSize, Integer totalCount) {
this.totalPageCount=totalCount%pageSize==0?totalCount/pageSize:(totalCount/pageSize)+1;
this.currPageNo = currPageNo;
this.pageSize = pageSize;
this.totalCount = totalCount;
}
@Override
public String toString() {
return "Page{" +
"currPageNo=" + currPageNo +
", pageSize=" + pageSize +
", totalCount=" + totalCount +
", totalPageCount=" + totalPageCount +
'}';
}
public void setCurrPageNo(int currPageNo) {
if(currPageNo<=0){
currPageNo=1;
}else if(currPageNo>=totalPageCount){
currPageNo=totalPageCount;
}
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount=totalPageCount;
}
public int getCurrPageNo() {
return currPageNo;
}
public int getPageSize() {
return pageSize;
}
public int getTotalCount() {
return totalCount;
}
public int getTotalPageCount() {
return totalPageCount;
}
public List<Object> getNewsList() {
return newsList;
}
public void setNewsList(List<Object> newsList) {
this.newsList = newsList;
}
}
注意:
List集合是为了存放分页查询后的信息,service业务层处理分页后,直接将查出的信息集合放入page当中,便于管理和传递
@Override
public Page PageSize(String UserName,
Integer RoleId,
Integer currPageNo,
Integer pageSize){
//总条数
Integer totalcount = userMapper.sumCount(UserName, RoleId);
Page page1 = new Page(currPageNo, pageSize, totalcount);
//得到总页数
// Integer totalpage=totalcount%pageSize==0?totalcount/pageSize:(totalcount/pageSize)+1;
// System.out.println("总页数"+totalpage);
// //得到当前页码
// if(currPageNo<=0){
// currPageNo=1;
// }else if(currPageNo>=totalpage){
// currPageNo=totalpage;
// }
Integer beginPage=(currPageNo-1)*pageSize;
if(beginPage<0){
beginPage=0;
}
//当前页数:
List<Object> userList = userMapper.PageSize(UserName, RoleId, beginPage, pageSize);
Page page = new Page(currPageNo,pageSize,totalcount,page1.getTotalPageCount(),userList);
return page;
}
业务层处理完毕:
三:Controller层调用service层实现分页
@RequestMapping("/queryList.do")
public String queryList(Model model,
String queryname,
Integer queryUserRole,
@RequestParam(defaultValue ="1")Integer beginPage,
@RequestParam(defaultValue="5") Integer pagesize){
Page page = userMapperService.PageSize(queryname, queryUserRole,beginPage, pagesize);
List<Object> newsList = page.getNewsList();
for (Object o : newsList) {
System.out.println(o);
}
model.addAttribute("queryname",queryname);
model.addAttribute("queryUserRole",queryUserRole);
if(newsList.size()>0){
model.addAttribute("userList",newsList);
model.addAttribute("page",page);
}else{
model.addAttribute("data","暂无数据");
}
return "jsp/userlist";
}
写完收工…
注意点:
在page实体类中,我做了兼容性处理,当用户跳转到非法不存在的页面时做一些处理
1.
totalCount/pageSize 得到totalPageCount
this.totalPageCount=totalCount%pageSize==0?totalCount/pageSize:(totalCount/pageSize)+1;
三元表达式
没有余数可以整除:totalCount/pageSize直接赋值
不能整除 :totalCount/pageSize+1
2.currPageNo当前页面兼容性处理
if(currPageNo<=0){
currPageNo=1;
}else if(currPageNo>=totalPageCount){
currPageNo=totalPageCount;
}
这样防止了上一页下一页时currPageNo+1或者-1值大于最大值或者小于最小值
最后的最后,刚接触分页的同学们,一定要耐住性子,不要急躁。
可能这会报错了,吃个饭就想到解决办法了呢,所以一定要坚持。