jsp 分页_jsp+servlet+jdbc分页查询

直接上结构图(如果你在写的时候碰见一些没见过的方法名，请参考我的工具类 也就是7 DButil)

edu.ahau.bean

这是Page实体类，这里面封装了我们将在servlet里面需要用的参数

public class Page {
//	当前页  currentPage
	private int currentPage ;
//	页面大小 pageSize
	private int pageSize ;

//	总数据 totalCount
	private int totalCount;
//	总页数   totalPage
	private int totalPage ;
	
//	当前页的数据集合  students
	
	private List<Student> students;

	public Page() {
	}

	public Page(int currentPage, int pageSize, int totalCount, int totalPage, List<Student> students) {
		this.currentPage = currentPage;
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		this.totalPage = totalPage;
		this.students = students;
	}

	public int getCurrentPage() {
		return currentPage;
	}

	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}

	public int getPageSize() {
		return pageSize;
	}
	/*
	 * 总页数 = 数据总数%页面大小==0? 数据总数/页面大小:数据总数/页面大小+1 ;
	 * 
	 * 当我们调换用了 数据总数的set() 和 页面大小的set()以后，自动计算出 总页数
	 * 务必注意顺序：先set 数据总数   再set 页面大小
	 */
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
		//自动计算出 总页数
	
//		总页数 = 数据总数%页面大小==0? 数据总数/页面大小:数据总数/页面大小+1 ;
		this.totalPage =this.totalCount%this.pageSize==0?this.totalCount/this.pageSize:totalCount/this.pageSize+1; 
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}

	public int getTotalPage() {
		return totalPage;
	}

	//给总页数赋值
//	public void setTotalPage(int totalPage) {
//		this.totalPage = totalPage;
//	}

	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}

	@Override
	public String toString() {
		return "Page [currentPage=" + currentPage + ", pageSize=" + pageSize + ", totalCount=" + totalCount
				+ ", totalPage=" + totalPage + ", students=" + students + "]";
	}
}

下面的是Student实体类

public class Student {
private String stuName;
private int age;
private int id;
public String getStuName() {
	return stuName;
}
public void setStuName(String stuName) {
	this.stuName = stuName;
}
public Student() {
	super();
}
public Student(String stuName, int age ,int id) {
	super();
	this.id = id;
	this.stuName = stuName;
	this.age = age;
}
@Override
public String toString() {
	return "Student [stuName=" + stuName + ", age=" + age + ", id=" + id + "]";
}
public int getAge() {
	return age;
}
public void setAge(int age) {
	this.age = age;
}
public int getId() {
	return id;
}
public void setId(int id) {
	this.id = id;
}

}

对持久层我无话可说 都是可以看的懂的东西，唯一的就是我们在写sql的时候需要用的between and查询

edu.ahau.dao

public interface IStudenetDAO {	
	List<Student> queryStudentsByPage(int currentPage, int pageSize);
	int getTotalCount();//查询总数
}

edu.ahau.dao.impl

public class StudentDaoImpl implements IStudenetDAO{
@Override
public List<Student> queryStudentsByPage(int currentPage, int pageSize) {
	String sql = " select * from student where id between ? and ?";
			    
			
			 
	Object[] params = {(currentPage-1)*pageSize+1,currentPage*pageSize}; 
	
	List<Student> students = new ArrayList<>();
	
	ResultSet rs = DBUtil.executeQuery(sql, params) ;
	
	try {
		while(rs.next()) {
			Student student = new Student(rs.getString("sname"),rs.getInt("sage"), rs.getInt("id")) ;
			students.add(student) ;
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}catch (Exception e) {
		e.printStackTrace();
	}finally {
		DBUtil.closeAll(rs, null, null);
	}
	return students;
}

@Override
public int getTotalCount() {
	// TODO Auto-generated method stub
	String sql = "select count(1) from student";
	return DBUtil.getTotalCount(sql);
}
}

edu.ahau.service

public interface IStudentService {
	List<Student> queryStudentsByPage(int currentPage ,int pageSize);
	 public int getTotalCount();
}

edu.ahau.service.impl

public class StudentServiceImpl implements IStudentService {
	StudentDaoImpl studentDao = new StudentDaoImpl();

	@Override
	public List<Student> queryStudentsByPage(int currentPage, int pageSize) {
		// TODO Auto-generated method stub
		return studentDao.queryStudentsByPage(currentPage, pageSize);
	}

	@Override
	public int getTotalCount() {
		// TODO Auto-generated method stub
		return studentDao.getTotalCount();
	}

}

edu.ahau.servlet

从page类中的setPageSize()里面我们可以知道在servlet里面获取数据的时候一定要将我们得到的数据总是放在页面大小的前面(即页面数据量)

/**
 * Servlet implementation class QueryStudentByPage
 */
@WebServlet("/QueryStudentByPage")
public class QueryStudentByPage extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public QueryStudentByPage() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=utf-8");
		Page page = new Page();
		IStudentService studentService = new StudentServiceImpl();
		
		String cPage = request.getParameter("currentPage");
		System.out.println(cPage);
		int pageSize = 3;
		//默认得到第一页
		if (cPage==null||cPage=="0") {
			cPage = "1";
		}
		int currentPage =Integer.parseInt(cPage);
		
		page.setCurrentPage(currentPage);
		int count = studentService.getTotalCount();//数据总数
		page.setTotalCount(count);
		
		page.setPageSize(pageSize);

		
		List<Student> students = studentService.queryStudentsByPage(currentPage, pageSize);
		page.setStudents(students);
		request.setAttribute("p", page);
		System.out.println(page);
		System.out.println(page.getCurrentPage());
		request.getRequestDispatcher("index.jsp").forward(request, response);
		
     	
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

edu.ahau.util

public class DBUtil {
	static String url = "jdbc:mysql://localhost:3306/page?useUnicode=true&useJDBCCompliantTimezo"
			+ "neShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
	static String user = "root";
	static String password = "123";
	static PreparedStatement pstmt;
	static Connection connection;
	static ResultSet rs;
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		 Class.forName("com.mysql.cj.jdbc.Driver") ;
		 return  DriverManager.getConnection( url,user,password ) ;
	}
	public static void closeAll(ResultSet rs,Statement stmt,Connection connection)
	{
		try {
			if(rs!=null)rs.close();
			if(pstmt!=null)pstmt.close();
			if(connection!=null)connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		
		
	}
	//查询总数
	public static int getTotalCount(String sql) {
		int count = -1;
		try {
			
			pstmt = createPreParedStatement(sql, null);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			closeAll(rs, pstmt, connection);
		}
		return count;
	}
	public static PreparedStatement createPreParedStatement(String sql,Object[] params) throws ClassNotFoundException, SQLException {
		  pstmt = getConnection() .prepareStatement( sql) ;
		  if(params!=null ) {
			  for(int i=0;i<params.length;i++) {
				  pstmt.setObject(i+1, params[i]);
			  }
		  }
		  return pstmt;
	}
	public static ResultSet executeQuery( String sql ,Object[] params) {
		Student student = null;
	
		List<Student> students = new ArrayList<>();
		try {
			
			
			
			pstmt = createPreParedStatement(sql,params);
			 rs =  pstmt.executeQuery() ;
			  return rs ;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null ; 
		} catch (SQLException e) {
			e.printStackTrace();
			return null ; 
		}catch (Exception e) {
			e.printStackTrace();
			return null ; 
		}
	
}
}

下面的是jsp代码，仅供参考的页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@page import="edu.ahau.bean.*"%>
<%@page import="java.util.List"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

	<table border="1px">
		<tr>
			<th>学号</th>
			<th>姓名</th>
			<th>年龄</th>
			
		</tr>

		<%
			//获取request域中的数据
		Page p = (Page)request.getAttribute("p");

		for (Student student : p.getStudents()) {
		%>
		<tr>
			<td><%=student.getId() %> </a>
			</td>


			<td><%=student.getStuName() %> </td>
			<td><%=student.getAge() %> </td>
			
			</td>

		</tr>
		<%
			}
		%>
	</table>
	<a href="QueryStudentByPage?currentPage=1">首页</a>
	<a href="QueryStudentByPage?currentPage=<%=p.getCurrentPage()-1%>">上一页</a>
	<a href="QueryStudentByPage?currentPage=<%=p.getCurrentPage()+1%>">下一页</a>

</body>
</html>