01
开发环境
Myeclipse 10.5
Mysql 5.5
Tomcat 7.0
JDK 1.7
Chrome浏览器
02
数据库和表结构：
下面是表中的测试数据，需要说明的是saddress这一列，本来是当做地址的，现在有其他的需求，就当成了角色使用，但是并不影响分页。
03
Javaweb代码
Java代码是以分层开发的思想来实现的，其中有实体类：Student，Dao类和接口：BaseDaoNew,IStudentDao，Dao层实现类StudentDaoImpl：Service接口和实现类IStudentService，StudentService以及最后的工具类PageUtils
代码如下
实体类：Student
package org.entity; /** * * @author 24519 * 学生的实体类 * */ public class Student { private int sid; private String sname; private String sphone; private String spass; private String saddress; private int sage; //封装 public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getSphone() { return sphone; } public void setSphone(String sphone) { this.sphone = sphone; } public String getSpass() { return spass; } public void setSpass(String spass) { this.spass = spass; } public String getSaddress() { return saddress; } public void setSaddress(String saddress) { this.saddress = saddress; } public int getSage() { return sage; } public void setSage(int sage) { this.sage = sage; } }
BaseDaoNew：
package org.dao; import java.sql.*; import java.util.List; import com.sun.org.glassfish.external.statistics.annotations.Reset; /** * * @author 24519 * 连接数据库的工作类 * */ public class BaseDaoNew { private Connection conn = null; private PreparedStatement pre; private ResultSet rs; //连接数据库 public Connection getConn(){ try{ //加载驱动 Class.forName("com.mysql.jdbc.Driver"); //数据库连接字符串 String url = "jdbc:mysql://localhost:3306/schooldb?user=root&password=root"; //连接数据库 conn = DriverManager.getConnection(url); }catch(Exception ex){ ex.printStackTrace(); } return conn; } //增删改 public int ExecuteUpdate(String sql,List params) throws SQLException{ int rel = 0; conn = getConn(); pre = conn.prepareStatement(sql); if(params!=null){ for(int i = 0;i<params.size();i++){ pre.setObject(i+1, params.get(i)); } } rel = pre.executeUpdate(); return rel; } //查询 public ResultSet ExecuteQuerty(String sql,List params) throws SQLException{ conn = getConn(); pre = conn.prepareStatement(sql); if(params!=null){ for(int i = 0;i<params.size();i++){ pre.setObject(i+1, params.get(i)); } } return pre.executeQuery(); } //关闭连接 public void closeConn(Connection conn, PreparedStatement pre,ResultSet rs){ try { if(rs!=null){ rs.close(); } if(pre!=null){ pre.close(); } if(conn!=null){ conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
IStudentDao：
package org.dao; import java.util.List; import org.entity.Student; //学生信息的接口 public interface IStudentDao { //增加学生信息 public int addStudent(Student stu); //修改学生信息 public int updateStudent(Student stu); //删除学生信息 public int delStudent(int sid); //根据编号查询学生信息 public Student findStudentById(int sid); //查询全部学生信息 public List<Student> findStudentAll(); //登录 public Student login(String name,String pass); //总记录数 public int findAllStudentCount(); //分页查询 public List<Student> findStudentByPage(int currIndex,int pageSize); }
StudentDaoImpl：
package org.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.dao.BaseDao; import org.dao.BaseDaoNew; import org.dao.IStudentDao; import org.entity.Student; import com.sun.xml.internal.ws.Closeable; /** * * @author 24519 * 学生信息的实现类 * */ public class StudentDaoImpl implements IStudentDao { BaseDao base = new BaseDao(); private Connection conn = base.getConn(); PreparedStatement pre = null; ResultSet rs = null; @Override public int addStudent(Student stu) { int rel = 0; String sql = "insert into Student values(?,?,?,?,?,?);"; try { List<Object> params = new ArrayList<Object>(); params.add(stu.getSid()); params.add(stu.getSname()); params.add(stu.getSphone()); params.add(stu.getSpass()); params.add(stu.getSaddress()); params.add(stu.getSage()); rel = base.ExecuteUpdate(sql, params); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return rel; } @Override public int updateStudent(Student stu) { String sql = "update student set sname = ?," + "sphone = ?,spass=?,saddress=?,sage=? where sid = ?"; int rel = 0; try { List<Object> params = new ArrayList<Object>(); params.add(stu.getSname()); params.add(stu.getSphone()); params.add(stu.getSpass()); params.add(stu.getSaddress()); params.add(stu.getSage()); params.add(stu.getSid()); rel = base.ExecuteUpdate(sql, params); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return rel; } @Override public int delStudent(int sid) { String sql = "delete from Student where sid = ?"; int rel = 0; try { List<Object> params = new ArrayList<Object>(); params.add(sid); rel = base.ExecuteUpdate(sql, params); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return rel; } @Override public Student findStudentById(int sid) { Student student = new Student(); String sql = "select * from student where sid = ?"; try { List<Object> params = new ArrayList<Object>(); params.add(sid); rs = base.ExecuteQuery(sql, params); while(rs.next()){ student.setSid(rs.getInt(1)); student.setSname(rs.getString(2)); student.setSphone(rs.getString(3)); student.setSpass(rs.getString(4)); student.setSaddress(rs.getString("saddress")); student.setSage(rs.getInt(6)); } } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return student; } @Override public List<Student> findStudentAll() { List<Student> stus = new ArrayList<Student>(); String sql = "select * from Student"; try { rs = base.ExecuteQuery(sql, null); while(rs.next()){ Student student = new Student(); student.setSid(rs.getInt(1)); student.setSname(rs.getString(2)); student.setSphone(rs.getString(3)); student.setSpass(rs.getString(4)); student.setSaddress(rs.getString(5)); student.setSage(rs.getInt(6)); stus.add(student);//将信息放入集合中 } } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return stus; } //登陆 @Override public Student login(String name, String pass) { Student student = new Student(); String sql = "select * from student where sname = ? and spass = ?;"; try { List<Object> params = new ArrayList<Object>(); params.add(name); params.add(pass); rs = base.ExecuteQuery(sql, params); while(rs.next()){ student.setSid(rs.getInt(1)); student.setSname(rs.getString(2)); student.setSphone(rs.getString(3)); student.setSpass(rs.getString(4)); student.setSaddress(rs.getString(5)); student.setSage(rs.getInt(6)); } } catch (SQLException e) { e.printStackTrace(); } return student; } //查询总记录数 @Override public int findAllStudentCount() { String sql = "select count(*) from student"; int count = 0; try { rs = base.ExecuteQuery(sql, null); rs.next(); count = rs.getInt(1); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return count; } //分页查询 @Override public List<Student> findStudentByPage(int currIndex, int pageSize) { String sql = "select * from student limit ? ,?"; int one = (currIndex-1)*pageSize; List<Object> params = new ArrayList<Object>(); params.add(one); params.add(pageSize); List<Student> sList = new ArrayList<Student>(); try { rs = base.ExecuteQuery(sql, params); while(rs.next()){ Student stu = new Student(); stu.setSid(rs.getInt(1)); stu.setSname(rs.getString(2)); stu.setSphone(rs.getString(3)); stu.setSpass(rs.getString(4)); stu.setSaddress(rs.getString(5)); stu.setSage(rs.getInt(6)); sList.add(stu); } } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return sList; } }
IStudentService：
package org.service; import java.util.List; import org.entity.Student; public interface IStudentService { //增加学生信息 public int addStudent(Student stu); //修改学生信息 public int updateStudent(Student stu); //删除学生信息 public int delStudent(int sid); //根据编号查询学生信息 public Student findStudentById(int sid); //查询全部学生信息 public List<Student> findStudentAll(); //登录 public Student login(String name,String pass); //计算总记录数 public int findAllStudentCount(); //分页查询 public List<Student> findStudentByPage(int currIndex,int pageSize); }
StudentService：
package org.service.impl; import java.util.List; import org.dao.IStudentDao; import org.dao.impl.StudentDaoImpl; import org.entity.Student; import org.service.IStudentService; public class StudentServiceImpl implements IStudentService { //创建Dao层的对象 private IStudentDao sDao = new StudentDaoImpl(); @Override public int addStudent(Student stu) { return sDao.addStudent(stu); } @Override public int updateStudent(Student stu) { return sDao.updateStudent(stu); } @Override public int delStudent(int sid) { return sDao.delStudent(sid); } @Override public Student findStudentById(int sid) { return sDao.findStudentById(sid); } @Override public List<Student> findStudentAll() { return sDao.findStudentAll(); } @Override public Student login(String name, String pass) { return sDao.login(name, pass); } @Override public int findAllStudentCount() { return sDao.findAllStudentCount(); } //分页查询 @Override public List<Student> findStudentByPage(int currIndex, int pageSize) { return sDao.findStudentByPage(currIndex, pageSize); } }
PageUtils:
package org.utils; import java.util.List; import org.entity.Student; /** * * @author 24519 * 分页的工具类 * */ public class PageUtils { //页大小（每页显示多少条记录） private int pageSize; //当前页 private int currIndex; //总记录数 private int totalCount; //总页数 private int totalPage; //每页显示的数据 List<Student> sList; public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getCurrIndex() { return currIndex; } //设置当前页 public void setCurrIndex(int currIndex) { //判断当前页是否小于0，如果是，则默认第一页 if(currIndex<=0){ this.currIndex = 1; }else{ this.currIndex = currIndex; } } public int getTotalCount() { return totalCount; } //计算总页数 public void setTotalCount(int totalCount) { this.totalCount = totalCount; int pages = totalCount%pageSize==0 ?totalCount/pageSize :totalCount/pageSize+1; totalPage = pages; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public List<Student> getsList() { return sList; } public void setsList(List<Student> sList) { this.sList = sList; } }
代码解析:我们可以看到，在IStudentDao中，除了有增删改和登录的接口外，还有两个接口，一个是查询总记录数，另一个为分页查询数据.
//总记录数 public int findAllStudentCount(); //分页查询 public List<Student> findStudentByPage(int currIndex,int pageSize);
总记录数的作用就是对总页数进行计算，公式为，总页数=总记录数%页大小==0？总记录数/页大小:总记录数/页大小+1；
分页查询的数据，由于是多条，即返回List<Student>集合，每页显示5条数据，那我们就查询5条，即参数pageSize的值为5。currIndex的作用是用来记录当前页。
实现类对于这两个接口的关键代码如下：
//查询总记录数 @Override public int findAllStudentCount() { String sql = "select count(*) from student"; int count = 0; try { rs = base.ExecuteQuery(sql, null); rs.next(); count = rs.getInt(1); } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return count; } //分页查询 @Override public List<Student> findStudentByPage(int currIndex, int pageSize) { String sql = "select * from student limit ? ,?"; int one = (currIndex-1)*pageSize; List<Object> params = new ArrayList<Object>(); params.add(one); params.add(pageSize); List<Student> sList = new ArrayList<Student>(); try { rs = base.ExecuteQuery(sql, params); while(rs.next()){ Student stu = new Student(); stu.setSid(rs.getInt(1)); stu.setSname(rs.getString(2)); stu.setSphone(rs.getString(3)); stu.setSpass(rs.getString(4)); stu.setSaddress(rs.getString(5)); stu.setSage(rs.getInt(6)); sList.add(stu); } } catch (SQLException e) { e.printStackTrace(); }finally{ base.closeConn(conn, pre, rs); } return sList; }
PageUtils类的我们需要注意，在设置当前页currIndex的值时，要对齐进行判断，因为当前页永远永远的不可能小于0 或者大于总页数，设置的关键代码如下：
//设置当前页 public void setCurrIndex(int currIndex) { //判断当前页是否小于0，如果是，则默认第一页 if(currIndex<=0){ this.currIndex = 1; }else{ this.currIndex = currIndex; } }
设置总记录数的关键代码如下：顺便对总页数进行计算
//计算总页数 public void setTotalCount(int totalCount) { this.totalCount = totalCount; int pages = totalCount%pageSize==0 ?totalCount/pageSize :totalCount/pageSize+1; totalPage = pages; }
接下来就可以在表现层进行分页，我们在进分页查询的页面之前，先进doPage.jsp对数据进行处理，将所有需要用到的分页数据全部封装至PageUtils类中，完整实现代码如下：各个关键步骤均有注释
<%@page import="org.entity.Student"%> <%@page import="org.service.impl.StudentServiceImpl"%> <%@page import="org.service.IStudentService"%> <%@page import="org.utils.PageUtils"%> <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <% Student stu = (Student) request.getAttribute("student"); //判断是否是管理员 if (stu.getSaddress().equals("管理员")) { //获取当前页， //如果是空，默认是1 String curr = request.getParameter("currIndex"); if (curr == null) { curr = "1"; } Integer currIndex = Integer.parseInt(curr); PageUtils p = new PageUtils(); p.setPageSize(5); //计算总记录数 IStudentService s = new StudentServiceImpl(); //从数据库中查询总记录数 p.setTotalCount(s.findAllStudentCount()); //如果当前页大于等于总页数，那当前页就是总页数 if (currIndex >= p.getTotalPage()) { currIndex = p.getTotalPage(); } else if (currIndex <= 0) { //如果当前页小于等于0，则当前页等于1 currIndex = 1; } p.setCurrIndex(currIndex); //从数据库中查询每页显示的数据放在集合中 List<Student> sList = s.findStudentByPage(currIndex, 5); p.setsList(sList); //将p对象放在request作用域中，在请求的页面中获取 request.setAttribute("p", p); //跳转到分页的页面 request.getRequestDispatcher("findStudentPage.jsp").forward( request, response); } else { //普通员工 //跳转到查询自己信息的页面 request.setAttribute("stu", stu); request.getRequestDispatcher("shouInfo.jsp").forward(request, response); } %>
注意看这一行代码，当我们将一切处理完之后，通过这行代码转发至分页显示信息的页面，
request.getRequestDispatcher("findStudentPage.jsp").forward(request, response);
下面就是分页显示数据的页面了：
<%@page import="org.utils.PageUtils"%> <%@page import="org.entity.Student"%> <%@page import="org.service.impl.StudentServiceImpl"%> <%@page import="org.service.IStudentService"%> <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'FindStudentPage.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body> <% //获取所有的数据 PageUtils p = (PageUtils)request.getAttribute("p"); //从工具类中获取每页显示的数据 List<Student> sList =p.getsList() ; %> <table border="1"> <tr> <td>编号</td> <td>姓名</td> <td>电话</td> <td>密码</td> <td>地址</td> <td>年龄</td> </tr> <% for(Student stu :sList){ %> <tr> <td><%=stu.getSid() %></td> <td><%=stu.getSname() %></td> <td><%=stu.getSphone() %></td> <td><%=stu.getSpass() %></td> <td><%=stu.getSaddress()%></td> <td><%=stu.getSage() %></td> </tr> <% } %> </table> <a href="doPage.jsp?currIndex=1">首页</a> <a href="doPage.jsp?currIndex=<%=p.getCurrIndex()-1%>">上一页</a> <%=p.getCurrIndex()%> / <%=p.getTotalPage()%> <a href="doPage.jsp?currIndex=<%=p.getCurrIndex()+1%>">下一页</a> <a href="doPage.jsp?currIndex=<%=p.getTotalPage()%>">末页</a> <br/> <form action="doPage.jsp" method="post"> 至 <input type="number" max="<%=p.getTotalPage() %>" min="1" value="<%=p.getCurrIndex() %>" style="display:inline-block; width:50px" name="currIndex"/> 页 <input type="submit" value="跳转"> </form> </body> </html>
代码解析：
先从request中获取doPage.jsp中放入的数据，然后以表格的形式展示出来。
04
翻页解析
首页，很简单，当前页码必定是1，所以直接写currIndex=1即可。
上一页，假如当前也是2，那么上一页就是当前也减去1，所以上一页就是currIndex-1。
下一页，正好和上一页相反，即currIndex+1。
尾页，假如一共有5页，那么尾页就是5，即currIndex=5。
当前页和总页数由于我们在doPage都放在了PageUtils中，所以直接从PageUtils中获取即可。
注意我们无论是点击上一页还是下一页，或者首页尾页，均是跳转至doPage.jsp中操作，在doPage.jsp中，直接获取currIndex的值，然后在调用Service里面的方法进行分页查询。
运行结果如下：
