SQL分页技术
1.主页面
1 <%@page import="com.zdsofe.work.Pagination"%> 2 <%@ page language="java" contentType="text/html; charset=UTF-8" 3 pageEncoding="UTF-8"%> 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 5 <html> 6 <head> 7 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> 8 <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> 9 <title>Insert title here</title> 10 <script type="text/javascript" src="../js/jquery-1.7.2.js"></script> 11 <script type="text/javascript"> 12 $(function(){ 13 14 $(":button").bind("click",function() 15 { 16 location.href="<%=request.getContextPath()%>/servlet"; 17 }); 18 }) 19 20 </script> 21 </head> 22 <body> 23 <button type="button">点我啊!</button> 24 25 <table border="1"> 26 <tr> 27 <td>序号</td> 28 <td>ID</td> 29 <td>姓名</td> 30 <td>密码</td> 31 <td>操作</td> 32 </tr> 33 <c:forEach items="${list}" var="s" varStatus="vs"> 34 <tr> 35 <td>${vs.count+(page.currentPage-1)*10}</td> 36 <td>${s.getId()}</td> 37 <td>${s.getUserName()}</td> 38 <td>${s.getMima()}</td> 39 <td>修改</td> 40 </tr> 41 </c:forEach> 42 <tr> 43 <td colspan="5"> 44 第${page.currentPage}页/共${page.totalPage}页 45 <c:if test="${page.currentPage>1}"> 46 <a href="<%=request.getContextPath()%>/servlet?currentP=${page.currentPage-1}">上一页</a></c:if> 47 <c:if test="${page.currentPage<page.totalPage}"> 48 <a href="<%=request.getContextPath()%>/servlet?currentP=${page.currentPage+1}">下一页</a></c:if> 49 </td> 50 </tr> 51 </table> 52 </body> 53 </html>
2.服务器
1 package com.zdsofe.work; 2 3 import java.io.IOException; 4 import java.util.List; 5 6 import javax.servlet.ServletException; 7 import javax.servlet.annotation.WebServlet; 8 import javax.servlet.http.HttpServlet; 9 import javax.servlet.http.HttpServletRequest; 10 import javax.servlet.http.HttpServletResponse; 11 import javax.servlet.http.HttpSession; 12 13 import org.apache.commons.lang.StringUtils; 14 15 /** 16 * Servlet implementation class servlet 17 */ 18 @WebServlet("/servlet") 19 public class servlet extends HttpServlet { 20 private static final long serialVersionUID = 1L; 21 private static final int PAGESIZE=10; 22 //初始化当前页 23 private int currentPage=1; 24 /** 25 * @see HttpServlet#HttpServlet() 26 */ 27 public servlet() { 28 super(); 29 // TODO Auto-generated constructor stub 30 } 31 32 /** 33 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse 34 * response) 35 */ 36 protected void doGet(HttpServletRequest request, 37 HttpServletResponse response) throws ServletException, IOException { 38 //定义分页符对象 39 Pagination page=new Pagination(); 40 //获取总记录数 41 int totalSize=StudentDao.getTotal(); 42 page.setTotalSize(totalSize); 43 page.setPageSize(PAGESIZE); 44 45 //传过来的页面为空时默认第一页 46 String currentP = request.getParameter("currentP"); 47 if(StringUtils.isNotBlank(currentP)) 48 { 49 currentPage = Integer.valueOf(currentP); 50 } 51 //设置当前页 52 page.setCurrentPage(currentPage); 53 // 查询分页的sql 54 List<Student> list = StudentDao.findInfoByPage(page); 55 //获取session对象 56 HttpSession session = request.getSession(); 57 session.setAttribute("list", list); 58 session.setAttribute("page", page); 59 request.getRequestDispatcher("/pages/table.jsp").forward(request, response); 60 } 61 62 /** 63 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 64 * response) 65 */ 66 protected void doPost(HttpServletRequest request, 67 HttpServletResponse response) throws ServletException, IOException { 68 69 } 70 71 }
3.实体类
1 package com.zdsofe.work; 2 3 public class Student { 4 private String id; 5 private String userName; 6 private String mima; 7 8 9 public Student(String id, String userName, String mima) { 10 11 this.id = id; 12 this.userName = userName; 13 this.mima = mima; 14 } 15 public String getId() { 16 return id; 17 } 18 public void setId(String id) { 19 this.id = id; 20 } 21 public String getUserName() { 22 return userName; 23 } 24 public void setUserName(String userName) { 25 this.userName = userName; 26 } 27 public String getMima() { 28 return mima; 29 } 30 public void setMima(String mima) { 31 this.mima = mima; 32 } 33 34 35 36 37 }
4.分页对象
1 package com.zdsofe.work; 2 3 public class Pagination { 4 5 //总页数 6 private int totalPage; 7 //当前页数 8 private int currentPage; 9 //总记录数 10 private int totalSize; 11 //每页显示的记录数 12 private int pageSize; 13 14 public int getTotalPage() { 15 totalPage=(int)Math.ceil(totalSize/(double)pageSize); 16 return totalPage; 17 } 18 public void setTotalPage(int totalPage) { 19 this.totalPage = totalPage; 20 } 21 public int getCurrentPage() { 22 return currentPage; 23 } 24 public void setCurrentPage(int currentPage) { 25 this.currentPage = currentPage; 26 } 27 public int getTotalSize() { 28 return totalSize; 29 } 30 public void setTotalSize(int totalSize) { 31 this.totalSize = totalSize; 32 } 33 public int getPageSize() { 34 return pageSize; 35 } 36 public void setPageSize(int pageSize) { 37 this.pageSize = pageSize; 38 } 39 40 }
5.查询和获取页数的方法类
1 package com.zdsofe.work; 2 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 import java.util.ArrayList; 7 import java.util.List; 8 9 public class StudentDao { 10 11 //分页查询用户列表 12 public static List<Student> findInfoByPage(Pagination page) 13 { 14 ResultSet rs=null; 15 Statement stam=null; 16 //获取当前页 17 int currentPage=page.getCurrentPage(); 18 //获取每页的显示的记录数 19 int pageSize= page.getPageSize(); 20 //分页的SQL语句 21 String sql = "select * from denglu limit "+(currentPage-1)*pageSize+","+pageSize; 22 //数据存放的集合 23 List<Student> list=new ArrayList<Student>(); 24 25 try { 26 //调用执行sql的类 27 stam= DBUtil.getConnection().createStatement(); 28 rs=stam.executeQuery(sql); 29 while(rs.next()) 30 { 31 String id=rs.getString("id"); 32 String userName=rs.getString("userName"); 33 String mima=rs.getString("mima"); 34 Student stu=new Student(id,userName,mima); 35 list.add(stu); 36 } 37 } catch (SQLException e) { 38 39 e.printStackTrace(); 40 }finally{ 41 try { 42 if(rs!=null&&stam!=null){ 43 44 rs.close(); 45 stam.close(); 46 } 47 48 } catch (SQLException e) { 49 e.printStackTrace(); 50 } 51 } 52 return list; 53 } 54 55 56 //获取用户的总记录数 57 public static int getTotal() 58 { 59 //总计数变量 60 int count=0; 61 //查询的sql语句 62 String sql="select count(1) as count from denglu"; 63 Statement stam=null; 64 ResultSet rs=null; 65 try { 66 //执行sql语句 67 stam=DBUtil.getConnection().createStatement(); 68 rs=stam.executeQuery(sql); 69 while(rs.next()) 70 { 71 count=rs.getInt("count"); 72 } 73 } catch (SQLException e) { 74 e.printStackTrace(); 75 }finally{ 76 try { 77 stam.close(); 78 rs.close(); 79 } catch (SQLException e) { 80 e.printStackTrace(); 81 } 82 } 83 return count; 84 } 85 }
6.连接数据库的包装类
1 package com.zdsofe.work; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6 7 public class DBUtil { 8 private static String DRIVER="com.mysql.jdbc.Driver"; 9 private static String URL="jdbc:mysql://localhost:3306/mysql"; 10 private static String user="root"; 11 private static String key="775297"; 12 public static Connection conn; 13 14 //加载驱动 15 static{ 16 try { 17 Class.forName(DRIVER); 18 } catch (ClassNotFoundException e) { 19 e.printStackTrace(); 20 } 21 } 22 //连接数据库 23 public static Connection getConnection(){ 24 try { 25 conn = DriverManager.getConnection(URL, user, key); 26 } catch (SQLException e) { 27 e.printStackTrace(); 28 } 29 return conn; 30 } 31 }
