mysql通过limit实现分页功能

心已赠人 2021-09-18 07:32 425阅读 0赞

注：通过sql语句查询实现java类的分页效果，主要可以分为以下步骤：登录界面--->controller--->首页--->controller(第一次页面传过去的页码为null，因此在controller中需要处理null)--->访问dao层通过limit实现分页查询--->返回所需分页的页面。下面请参考具体代码！

1、创建PagBean类，分页所用到的一些基本属性

package com.besttone.pojo;
    
    import java.util.List;
    
    import org.springframework.stereotype.Component;
    
    public class PageBean {
    
    	private int pageSize = 10;// 每页显示多少条数据
    	private int nowPage = 0;// 当前页码
    	private int rowCount = 0;// 总行数
    	private int pageCount = 0;// 总页数
    	private List<?> rowList;// 每页存放的结果集
    
    	// getter/setter
    	public int getPageSize() {
    		return pageSize;
    	}
    
    	public void setPageSize(int pageSize) {
    		this.pageSize = pageSize;
    	}
    
    	public int getNowPage() {
    		return nowPage;
    	}
    
    	public void setNowPage(int nowPage) {
    		this.nowPage = nowPage;
    	}
    
    	public int getRowCount() {
    		return rowCount;
    	}
    
    	public void setRowCount(int rowCount) {
    		this.rowCount = rowCount;
    	}
    
    	public int getPageCount() {
    		this.pageCount = rowCount % pageSize == 0 ? rowCount / pageSize
    				: rowCount / pageSize + 1;
    		return pageCount;
    	}
    
    	public List<?> getRowList() {
    		return rowList;
    	}
    
    	public void setRowList(List<?> rowList) {
    		this.rowList = rowList;
    	}
    
    	public PageBean(int pageSize, int nowPage, int rowCount, int pageCount,
    			List<?> rowList) {
    		super();
    		this.pageSize = pageSize;
    		this.nowPage = nowPage;
    		this.rowCount = rowCount;
    		this.pageCount = pageCount;
    		this.rowList = rowList;
    	}
    
    	public PageBean() {
    		super();
    	}
    	
    }

2、Dao层，sql语句查询

package com.besttone.dao;
    
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    import com.besttone.pojo.PageBean;
    
    @Repository
    public class PageDao {
    	
    	public PageBean getPageBean() {
    		return pageBean;
    	}
    
    	public void setPageBean(PageBean pageBean) {
    		this.pageBean = pageBean;
    	}
    
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    
    	public JdbcTemplate getJdbcTemplate() {
    		return jdbcTemplate;
    	}
    
    	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    		this.jdbcTemplate = jdbcTemplate;
    	}
    
    	/**
    	 * 
    	 * <p>
    	 * <h1>created by 周斌 at 2017-7-20  上午9:39:47</h1>
    	 * <p>
    	 * 	@describe 获取总行数
    	 *  @return
    	 */
    	public List<Map<String, Object>> getAllCount(){
    		String sql="select * from login";
    		return this.getJdbcTemplate().queryForList(sql);
    	}
    	
    	
    	public PageBean getNowPage(int pageSize,int nowPage){
                    PageBean pageBean = new PageBean();
    		pageBean.setPageSize(pageSize);
    		pageBean.setNowPage(nowPage);
    		pageBean.setRowCount(getAllCount().size());
    		String sql="select login.username,login.password from login limit "+(nowPage-1)*pageSize+","+pageSize;
    		List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql);
    		pageBean.setRowList(list);
    		return pageBean;
    	}
    
    }

3、Controller类，处理页面传过来的参数，并调用Dao层方法同时将数据库中的取到的属性放在request中，返回页面视图

package com.besttone.controller;
    
    import javax.servlet.http.HttpServletRequest;
    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 com.besttone.dao.PageDao;
    import com.besttone.pojo.PageBean;
    
    @Controller
    public class PageController {
    
    	@Autowired
    	private PageDao pageDao;
    
    	@RequestMapping(value = "page/getPage")
    	public String getPage(HttpServletRequest request,
    			HttpServletResponse response) {
    		String nowPages = request.getParameter("nowPage");
    		System.out.println(nowPages);
    		int pageSize = 5;
    		int nowPage = 0;
    		
    		if (nowPages != null) {
    			nowPage = Integer.parseInt(nowPages);
    		}
    		if(nowPages == null){
    			nowPage=1;
    		}
    		
    		PageBean pageBean = pageDao.getNowPage(pageSize, nowPage);
    		request.setAttribute("pageBean", pageBean);
    		request.setAttribute("list", pageBean.getRowList());
    	//	request.setAttribute("nowPage", pageBean.getNowPage());
    	/*	Object object = request.getAttribute("nowPage");
    		int i = Integer.parseInt(object.toString());*/
    	//	request.setAttribute("pageCount", pageBean.getPageCount());
    		return "user";
    	}
    
    }

4、页面信息，主要关注分页操作的js代码和el表达式取到的页面

<%@page import="com.besttone.pojo.PageBean"%>
    <%@ page language="java" contentType="text/html; charset=UTF-8"
    	pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
    <%
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://"
    			+ request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <base href="<%=basePath%>">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>显示用户</title>
    <style type="text/css">
    table {
    	border-collapse: collapse;
    	width: 90%;
    	height: 90%;
    	text-align: center;
    }
    
    a {
    	text-decoration: none;
    	color: #000;
    }
    </style>
    <script type="text/javascript">
    	function upPage() {
    		var nowPage = '${pageBean.nowPage}';
    		if (nowPage < 2) {
    			nowPage == 1;
    		} else {
    			nowPage = nowPage - 1;
    		}
    		self.location.href = "page/getPage?nowPage=" + nowPage;
    	}
    
    	function downPage() {
    		var nowPage = '${pageBean.nowPage}';
    		var pageCount = '${pageBean.pageCount}';
    		if (nowPage >= pageCount) {
    			nowPage = pageCount;
    		} else {
    			nowPage = parseInt(nowPage) + 1;
    		}
    		self.location.href = "page/getPage?nowPage=" + nowPage;
    	}
    </script>
    
    </head>
    <body>
    	
    	<table border="1">
    		<tr>
    			<th>用户名</th>
    			<th>密码</th>
    			<th>操作</th>
    		</tr>
    		<c:forEach var="e" items="${list}">
    			<tr>
    				<td>${e.username}</td>
    				<td>${e.password}</td>
    				<td><a href="login/del?username=${e.username}"
    					onclick="return confirm('确定将此记录删除?')">删除</a> | <a><a
    						href="login/auth?userid=${e.userId}">授权</a></td>
    			</tr>
    		</c:forEach>
    	</table>
    	<span><a href="page/getPage?nowPage=1">首页</a></span>
    	<span><a href="javascript:upPage();">上一页</a></span> 
    	<span>第${pageBean.nowPage}页</span>/
    	<span>共${pageBean.pageCount}页</span> 
    	<span><a href="javascript:downPage();">下一页</a></span> ${msg}
    	<span><a href="page/getPage?nowPage=${pageBean.pageCount}">尾页</a></span>
    </body>
    </html>

总结：1、js中的upPage()和downPage()方法是实现上一页和下一页，如果没有这两个方法，则当点击上一页的时候页码小于1的时候会就会出错，当点击下一页的时候，如果页码大于总页码的时候则会一直查询数据库，但数据库没有这些数据则会返回null；2、如果有必要请自行封装Dao方法，则只需要每次调用Dao方法进行sql语句拼接就可以了！