mysql通过limit实现分页功能

注：通过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>
	<!-- <span>数据库显示页面</span> -->
	<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语句拼接就可以了！