jsp上利用ajax将列表分页的实现

分页的原理是用了一条sql：

select * from 你的表名 limit ?,? 

举个栗子。。。这里limit里面如果填8和3 ， 这样“SELECT id,goods_name FROM t_goods LIMIT 8,3”查出来的结果是从数据库中第9条开始的，每页显示三条。就是这样了。。。要想从1开始查就要写limit 0,3 。

原理就是基于这条sql语句，那要怎样在jsp上实现呢：

1.先写个分页工具类

public class PageUtils {
	private Integer currentPage;  //当前页码
	private Integer prePage;		//上一页页码
	private Integer nextPage;		//下一页页码
	private Integer lastPage;	//最后一页页码
	private Integer pageSize;	   //每页显示多少条数据
	private Integer counter;		//你的数据库里数据总数
	
	//由当前页码、pagesize、counter算出其他的页码
	public PageUtils(String currentPage, Integer pageSize, Integer counter) {
		this.pageSize = pageSize;
		this.counter = counter;
		if(null==currentPage) {
			currentPage="1";     //默认当前页码为1
		}
		this.currentPage=Integer.parseInt(currentPage);   //由于current是由string类型传进来的要给它转成int类型才能运算
		lastPage=this.counter/this.pageSize;   //计算最后一页
		if(this.counter%this.pageSize !=0) {
			lastPage+=1;
		}
		nextPage=this.currentPage==lastPage?this.currentPage:this.currentPage+1;  //当前页是否为最后一页，是下一页=当前页，否下一页=当前页+1
		prePage=this.currentPage==1?this.currentPage:this.currentPage-1;    //当前页是否为首页
	}
	public Integer getCurrentPage() {
		return currentPage;
	}
	public PageUtils(Integer currentPage, Integer prePage, Integer nextPage, Integer lastPage, Integer pageSize) {
		super();
		this.currentPage = currentPage;
		this.prePage = prePage;
		this.nextPage = nextPage;
		this.lastPage = lastPage;
		this.pageSize = pageSize;
	}
	public void setCurrentPage(Integer currentPage) {
		this.currentPage = currentPage;
	}
	public Integer getPrePage() {
		return prePage;
	}
	public void setPrePage(Integer prePage) {
		this.prePage = prePage;
	}
	public Integer getNextPage() {
		return nextPage;
	}
	public void setNextPage(Integer nextPage) {
		this.nextPage = nextPage;
	}
	public Integer getLastPage() {
		return lastPage;
	}
	public void setLastPage(Integer lastPage) {
		this.lastPage = lastPage;
	}
	public Integer getPageSize() {
		return pageSize;
	}
	public void setPageSize(Integer pageSize) {
		this.pageSize = pageSize;
	}
	public Integer getCounter() {
		return counter;
	}
	public void setCounter(Integer counter) {
		this.counter = counter;
	}	
}

 

2.写个get分页查询方法

	public List<Goods> getPageList(Integer i, Integer j) {
		QueryRunner runner = new QueryRunner();
		List<Goods> list =null;
		Connection connection = JdbcUtils.getConnection();
		try {
			list=runner.query(connection, "select g.id,g.goods_name,g.goods_desc,g.price,g.cate_id,g.brand_id,c.cat_name,b.brand_name from t_goods g,t_brand b,t_category c where g.brand_id=b.id and g.cate_id=c.id limit ?,?", new BeanListHandler<>(Goods.class), i,j);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.close(null, null, connection);
		}
//		System.out.println(list);
		return list;
	}

3.写个get数据库总量查询方法

	public Integer getCounter() {
		Integer counter=null;
		Connection connection =JdbcUtils.getConnection();
		PreparedStatement pst=null;
		ResultSet rs = null;
		try {
			pst=connection.prepareStatement("select count(id) from t_goods");
			rs=pst.executeQuery();
			while(rs.next()) {
				counter=rs.getInt(1);
			}
//			System.out.println(counter);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.close(null, null, connection);
		}
		return counter;
	}

4.写servlet里面的方法：

private PageUtils pageUtils=null;
	final Integer PAGESIZE = 4;

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	request.setCharacterEncoding("UTF-8");
	response.setCharacterEncoding("UTF-8");
	
	String method = request.getParameter("method");
	if("getPage".equals(method)) {
		getPage(request, response);
	}else if ("changePage".equals(method)) {
		changePage(request, response);
	}
	else {
		getGooList(request, response);
	}
}

private void getGooList(HttpServletRequest request, HttpServletResponse response) throws IOException {
	response.setContentType("application/json;charset=UTF-8");
	List<Goods> goolist = null;
	String currentPage = null;
	pageUtils=new PageUtils(currentPage,PAGESIZE , gooService.getCounter());
	goolist = gooService.getPageList((pageUtils.getCurrentPage()-1)*PAGESIZE,PAGESIZE);
	String string = JSON.toJSONString(goolist);
//	System.out.println(string);
	response.getWriter().write(string);
}

private void getPage(HttpServletRequest request,HttpServletResponse response) throws IOException {
	response.setContentType("application/json;charset=UTF-8");
	String string = JSON.toJSONString(pageUtils);
// 	System.out.println(string);
	response.getWriter().write(string);
}

private void changePage(HttpServletRequest request,HttpServletResponse response) throws IOException {
	response.setContentType("application/json;charset=UTF-8");
	List<Goods> goolist =null;
	List<Object> list = new ArrayList<>();
	String page=request.getParameter("page");
//	System.out.println(page);
	pageUtils=new PageUtils(page, PAGESIZE, gooService.getCounter());
	goolist=gooService.getPageList((pageUtils.getCurrentPage()-1)*PAGESIZE, PAGESIZE);
	list.add(pageUtils);
	list.add(goolist);
	String string = JSON.toJSONString(list);
//	System.out.println(string);
	response.getWriter().write(string);
}

5.写jsp页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>列表</title>
</head>
<script type="text/javascript" src="../js/jquery-1.8.2.min.js"></script>

<body>
		<tr>
			<td colspan="5">
				<button onclick="page(this)" class="frstn">首页</button>
				<button onclick="page(this)" class="prvtn">上一页</button>
				<span id="page"></span>
				<button onclick="page(this)" class="nxttn">下一页</button>
				<button onclick="page(this)" class="lsttn">尾页</button>
				<a href="#" onclick="add()">新增</a>
			</td>
		</tr>
	</table>

<script type="text/javascript">
	$(function(){
		$.ajax({
			url:"../ajaxServlet",
			data:"",
			dataType:"json",
			type:"post",
			success:function(obj){
				//alert("fun");
				for(var i=0;i<obj.length;i++){
					$("table tr:first").after(
						"<tr id='list'><td>"+obj[i].goods_name+"</td><td>"+obj[i].price+"</td><td>"+obj[i].cat_name+"</td><td>"+obj[i].brand_name+"</td><td><a href='#' onclick='update("+obj[i].id+")'>修改</a> <a href='#' onclick='deleted("+obj[i].id+")'>删除</a></td></tr>"		
					)
				}
			}
				
		});
	})
	$(function() {
		$.ajax({
			url:"../ajaxServlet?method=getPage",
			data:"",
			dataType:"json",
			type:"post",
			success:function(obj){
//				alert("dfa");
				$("#page").text(obj.currentPage+"/"+obj.lastPage);
			 	$(".frstn")[0].id=1;
			 	$(".prvtn")[0].id=obj.prePage;
			 	$(".nxttn")[0].id=obj.nextPage;
			 	$(".lsttn")[0].id=obj.lastPage;
			}
		});
	})	
	function page(btn){
// 		alert(btn.id);
		var page=btn.id;
		$.ajax({
			url:"../ajaxServlet?method=changePage&page="+page,
			type:"post",
			data:{},
			dataType:"json",
			success:function(obj){
				  //alert(obj[1].length);
				$("#page").text(obj[0].currentPage+"/"+obj[0].lastPage);
				$(".frstn")[0].id=1;
				$(".prvtn")[0].id=obj[0].prePage;
				$(".nxttn")[0].id=obj[0].nextPage;
				$(".lsttn")[0].id=obj[0].lastPage;
				for(var i=0;i<obj[0].pageSize;i++){
					$("#list").remove();
				}
				for(var i=0;i<obj[1].length;i++){
					$("table tr:first").after(
						"<tr id='list'><td>"+obj[1][i].goods_name+"</td><td>"+obj[1][i].price+"</td><td>"+obj[1][i].cat_name+"</td><td>"+obj[1][i].brand_name+"</td><td><a href='#' onclick='update("+obj[1][i].id+")'>修改</a> <a href='#' onclick='deleted("+obj[1][i].id+")'>删除</a></td></tr>"		
					)
				}
			}
		});
	}
</script>
</body>
</html>

 

那个。。我写的这个完整练习就在这里放着。。。

https://download.csdn.net/download/tritoy/10493931

还有度盘，有时候会挂掉。。

https://pan.baidu.com/s/1Y1xiBTFBDlqyCv-aMCGSgA

mima：xwj3