实现上图的分页功能,每页显示3条数据,每页显示3页供用户访问,点击左右双箭头时,可以跳转至上一个或者下一个大页,如点击右双箭头显示如下:
1 连接数据库的工具类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package com.latiny.db; 2 3 import java.io.*; 4 import java.sql.*; 5 import java.util.ArrayList; 6 import java.util.Properties; 7 8 /* 9 *操作数据库的工具类 10 */ 11 12 public class DBUtil { 13 14 //定义需要的变量 15 private static String driver =null; 16 private static String url =null; 17 private static String user=null; 18 private static String password=null; 19 20 private static Connection conn; 21 //使用PreparedStatment可以防止sql注入 22 private static PreparedStatement ps; 23 private static ResultSet rs; 24 private static CallableStatement cs; 25 26 //读配置文件 27 private static Properties pp=null; 28 private static InputStream fis=null; 29 30 //加载驱动,只需要执行一次 31 static 32 { 33 try 34 { 35 pp = new Properties(); 36 37 //当我们使用java web的时候,读取文件要使用类加载器 38 fis = DBUtil.class.getClassLoader().getResourceAsStream("dbinfo.properties"); 39 40 pp.load(fis); 41 driver = pp.getProperty("DRIVER"); 42 url = pp.getProperty("URL"); 43 user = pp.getProperty("USER"); 44 password = pp.getProperty("PASSWORD"); 45 46 // 1 加载驱动 47 Class.forName(driver); 48 49 } 50 catch(Exception e) 51 { 52 e.printStackTrace(); 53 } 54 finally 55 { 56 try 57 { 58 fis.close(); 59 } catch (IOException e) { 60 // TODO Auto-generated catch block 61 e.printStackTrace(); 62 } 63 fis = null; 64 } 65 } 66 67 /* 68 * 获取Connection连接 69 */ 70 public static Connection getConn() 71 { 72 try 73 { 74 // 2 获取数据库连接 75 conn = DriverManager.getConnection(url, user, password); 76 } 77 catch (SQLException e) 78 { 79 // TODO Auto-generated catch block 80 e.printStackTrace(); 81 } 82 83 return conn; 84 } 85 86 /* 87 * 直接返回rs结果,此方法不能关闭rs,因为后面调用它的类还会用到,如果关闭则不能正常使用 88 */ 89 public static ResultSet queryResult(String sql, String[] parameters) 90 { 91 try 92 { 93 conn = getConn(); 94 // 3 创建Statement对象 95 ps = conn.prepareStatement(sql); 96 // 4 给问号赋值,即给sql语句的条件参数赋值如果需要的话 97 if(parameters!=null) 98 { 99 for(int i=1; i<=parameters.length; i++) 100 { 101 ps.setString(i, parameters[i-1]); 102 } 103 } 104 105 // 5 执行sql获取返回结果 106 rs = ps.executeQuery(); 107 } 108 catch (SQLException e) 109 { 110 // TODO Auto-generated catch block 111 e.printStackTrace(); 112 } 113 114 return rs; 115 } 116 117 /* 118 * 将rs结果封装成ArrayList,然后可以关闭rs,节省数据库访问资源 119 */ 120 public static ArrayList queryResult2(String sql, String[] parameters) 121 { 122 ArrayList al = new ArrayList(); 123 124 try 125 { 126 //2 获取数据库连接 127 conn = getConn(); 128 //3 创建Statement对象 129 ps = conn.prepareStatement(sql); 130 131 //4 给问号赋值,即给sql语句的条件参数赋值如果需要的话 132 if(parameters!=null) 133 { 134 for(int i=1; i<=parameters.length; i++) 135 { 136 ps.setString(i, parameters[i-1]); 137 } 138 } 139 140 //5 执行sql语句获取返回结果 141 rs = ps.executeQuery(); 142 143 //获取rs的结构 144 ResultSetMetaData rsmd = rs.getMetaData(); 145 //获取查询语句的列数 146 int column = rsmd.getColumnCount(); 147 148 while(rs.next()) 149 { 150 //对象数组,存储一行数据 151 Object[] objs = new Object[column]; 152 for(int i=0; i<objs.length; i++) 153 { 154 objs[i] = rs.getObject(i+1); 155 } 156 al.add(objs); 157 } 158 159 } 160 catch (SQLException e) 161 { 162 // TODO Auto-generated catch block 163 e.printStackTrace(); 164 } 165 finally 166 { 167 //关闭资源 168 close(rs, ps, conn); 169 } 170 171 return al; 172 } 173 174 //调用存储过程,带输入输出参数的 175 public static CallableStatement callProcedure(String sql, String[] inputPara, Integer[] outputPara) 176 { 177 178 try 179 { 180 conn = getConn(); 181 cs = conn.prepareCall(sql); 182 for(int i=0; inputPara!=null && i<inputPara.length; i++) 183 { 184 cs.setObject(i+1, inputPara[i]); 185 } 186 187 //给output参数赋值 188 for(int j=0; outputPara!=null && j<outputPara.length; j++) 189 { 190 cs.registerOutParameter(inputPara.length+1+j, outputPara[j]); 191 } 192 193 cs.execute(); 194 195 } catch (SQLException e) { 196 // TODO Auto-generated catch block 197 e.printStackTrace(); 198 } 199 finally 200 { 201 close(rs, ps, conn); 202 } 203 204 return cs; 205 206 } 207 208 //update, insert, delete 209 public static Integer updateData(String sql, String[] parameters) 210 { 211 int result = 0; 212 try 213 { 214 conn = getConn(); 215 ps = conn.prepareStatement(sql); 216 if(parameters!=null) 217 { 218 for(int i=0; i<parameters.length; i++) 219 { 220 ps.setObject(i+1, parameters[i]); 221 } 222 } 223 224 //执行executeUpdate并且返回受影响的行数 225 result = ps.executeUpdate(); 226 227 } 228 catch(Exception e) 229 { 230 e.printStackTrace(); 231 } 232 finally 233 { 234 close(rs, ps, conn); 235 } 236 237 return result; 238 } 239 240 //关闭对应的数据库连接资源 241 public static void close(ResultSet rs1, PreparedStatement ps1, Connection conn1) 242 { 243 244 try 245 { 246 if(rs1!=null) 247 { 248 rs1.close(); 249 } 250 if(ps1!=null) 251 { 252 ps1.close(); 253 } 254 if(conn1!=null) 255 { 256 conn1.close(); 257 } 258 259 } catch (SQLException e) { 260 e.printStackTrace(); 261 } 262 263 } 264 }
2 从数据库获取需要的数据用于计算与显示
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package com.latiny.dao; 2 3 /* 4 * 从数据库获取页面需要的数据:1 记录总数; 2 获取每页需要显示的数据 5 */ 6 7 import java.sql.ResultSet; 8 import java.util.ArrayList; 9 import com.latiny.db.DBUtil; 10 import com.latiny.model.Users; 11 12 public class UserDao { 13 14 private ArrayList al; 15 16 public ArrayList<Users> getUserByPage(int startIndex, int pageSize) 17 { 18 ArrayList<Users> array = new ArrayList<Users>(); 19 //查询sql 20 String sql="select * from users order by user_id limit "+startIndex +","+pageSize; 21 ResultSet rs = DBUtil.queryResult(sql, null); 22 23 ArrayList al2 = DBUtil.queryResult2(sql, null); 24 25 //二次封装,将rs结果封装到array中 26 for(int i=0; i<al2.size(); i++) 27 { 28 Users user = new Users(); 29 Object[] objs = (Object[])al2.get(i); 30 31 //user.setUserId(Integer.parseInt(objs[0].toString())); 32 user.setUserId((Integer)objs[0]); 33 user.setUser_name((String)objs[1]); 34 user.setPasswd((String)objs[2]); 35 user.setEmail((String)objs[3]); 36 user.setGrade(Byte.parseByte(objs[4].toString())); 37 38 //将user放入到array中 39 array.add(user); 40 } 41 return array; 42 } 43 44 public int getTotalRows() 45 { 46 int rowCount=0; 47 48 String sql="select count(1) from users"; 49 al = DBUtil.queryResult2(sql, null); 50 51 //注意需要将游标下移,之后才能获取到查询结果 52 try 53 { 54 Object[] obj = (Object[])al.get(0); 55 rowCount = Integer.parseInt(obj[0].toString()); //获取数据库总的记录数 56 57 } catch (Exception e) { 58 // TODO Auto-generated catch block 59 e.printStackTrace(); 60 } 61 62 return rowCount; 63 } 64 }
3 通用分页类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package com.latiny.bean; 2 3 import java.util.List; 4 5 public class PageBean<T> { 6 7 //已知数据 8 private int currentPage; //当前页 9 private int pageSize; //每页显示的数据条数 10 private int totalRecord; //总的记录条数,查询数据库得到 11 private int pageNum; //当前页显示可访问的页数,如当前页面显示1,2,3,4,5页,则pageNum应设为5 12 13 //根据已知数据计算 14 private int totalPage; //小页总的页数 15 private int totalBigPage; //大页的总页数 16 17 //保存每页要显示的数据 18 private List<T> list; 19 20 //查询数据库的开始索引 21 private int startIndex; 22 23 //分页显示的页数,如当前页面显示1,2,3,4,5页,则start为1, end为5. 24 private int start; 25 private int end; 26 27 int currentBigPage = 1; //当前所在大页序号 28 int pageBigCount = 0; //共有多少大页数 29 30 public PageBean(int currentPage, int currentBigPage, int pageSize, int totalRecord, int pageNum) 31 { 32 this.currentPage = currentPage; 33 this.currentBigPage = currentBigPage; 34 this.pageSize = pageSize; 35 this.totalRecord = totalRecord; 36 37 //根据totalRecord 与 pageSize 计算总页数 38 totalPage = (this.totalRecord-1)/this.pageSize+1; 39 totalBigPage = (totalPage-1)/pageNum+1; 40 41 startIndex = (currentPage-1)*pageSize; 42 43 //根据小页数计算大页数(每页显示多少页可供用户选择跳转) 44 pageBigCount = (totalPage-1)/pageNum+1; 45 46 start = (currentBigPage-1)*pageNum+1; 47 end = currentBigPage*pageNum<totalPage?currentBigPage*pageNum:totalPage; 48 } 49 50 public int getCurrentPage() { 51 return currentPage; 52 } 53 54 public void setCurrentPage(int currentPage) { 55 this.currentPage = currentPage; 56 } 57 58 public int getPageBigCount() { 59 return pageBigCount; 60 } 61 62 public void setPageBigCount(int pageBigCount) { 63 this.pageBigCount = pageBigCount; 64 } 65 66 public int getPageNum() { 67 return pageNum; 68 } 69 70 public void setPageNum(int pageNum) { 71 this.pageNum = pageNum; 72 } 73 74 public int getPageSize() { 75 return pageSize; 76 } 77 78 public void setPageSize(int pageSize) { 79 this.pageSize = pageSize; 80 } 81 82 public int getTotalRecord() { 83 return totalRecord; 84 } 85 86 public void setTotalRecord(int totalRecord) { 87 this.totalRecord = totalRecord; 88 } 89 90 public int getTotalPage() { 91 return totalPage; 92 } 93 94 public void setTotalPage(int totalPage) { 95 this.totalPage = totalPage; 96 } 97 98 public List<T> getList() { 99 return list; 100 } 101 102 public void setList(List<T> list) { 103 this.list = list; 104 } 105 106 public int getStartIndex() { 107 return startIndex; 108 } 109 110 public void setStartIndex(int startIndex) { 111 this.startIndex = startIndex; 112 } 113 114 public int getStart() { 115 return start; 116 } 117 118 public void setStart(int start) { 119 this.start = start; 120 } 121 122 public void setEnd(int end) { 123 this.end = end; 124 } 125 126 public int getEnd() { 127 return end; 128 } 129 130 public int getTotalBigPage() { 131 return totalBigPage; 132 } 133 134 public void setTotalBigPage(int totalBigPage) { 135 this.totalBigPage = totalBigPage; 136 } 137 138 public int getCurrentBigPage() 139 { 140 return currentBigPage; 141 } 142 143 144 }
4 封装数据对象类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package com.latiny.model; 2 3 public class Users { 4 5 private int userId; 6 private String userName; 7 private String passwd; 8 private String email; 9 private byte grade; 10 11 public Users() 12 { 13 14 } 15 16 public Users(int userId, String userName, String passwd, String email, byte grade) 17 { 18 this.userId = userId; 19 this.userName = userName; 20 this.passwd = passwd; 21 this.email = email; 22 this.grade = grade; 23 } 24 25 public int getUserId() { 26 return userId; 27 } 28 public void setUserId(int userId) { 29 this.userId = userId; 30 } 31 32 public String getUser_name() { 33 return userName; 34 } 35 public void setUser_name(String user_name) { 36 this.userName = user_name; 37 } 38 public String getPasswd() { 39 return passwd; 40 } 41 public void setPasswd(String passwd) { 42 this.passwd = passwd; 43 } 44 public String getEmail() { 45 return email; 46 } 47 public void setEmail(String email) { 48 this.email = email; 49 } 50 public byte getGrade() { 51 return grade; 52 } 53 public void setGrade(byte grade) { 54 this.grade = grade; 55 } 56 57 }
5 Serivce层 结合dao 层与Page工具类
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package com.latiny.service; 2 3 import java.util.List; 4 5 import com.latiny.dao.UserDao; 6 import com.latiny.model.Page; 7 import com.latiny.model.Users; 8 9 public class UserService { 10 11 UserDao userDao = new UserDao(); 12 13 public Page pageUsers(int currentPage, int pageBigCurrent, int pageSize, int pageNum){ 14 15 int totalRows = userDao.getTotalRows(); 16 //根据页面传递的参数初始化page对象 17 Page<Users> page = new Page<Users>(currentPage, pageBigCurrent, pageSize, totalRows, pageNum); 18 //将要显示的数据赋值给Page 的list属性 19 List<Users> users = userDao.getUserByPage(page.getStartIndex(), pageSize); 20 page.setList(users); 21 22 return page; 23 } 24 }
6 Servlet 显示分页结果
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 package com.latiny.view; 2 3 import java.io.IOException; 4 import java.io.PrintWriter; 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.PreparedStatement; 8 import java.sql.ResultSet; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import javax.servlet.ServletException; 13 import javax.servlet.http.HttpServlet; 14 import javax.servlet.http.HttpServletRequest; 15 import javax.servlet.http.HttpServletResponse; 16 17 import com.latiny.model.Page; 18 import com.latiny.model.Users; 19 import com.latiny.service.UserService; 20 21 public class ManagerUser2 extends HttpServlet { 22 23 public void doGet(HttpServletRequest request, HttpServletResponse response) 24 throws ServletException, IOException { 25 26 response.setContentType("text/html;charset=utf-8"); 27 PrintWriter out = response.getWriter(); 28 29 out.println("<script type='text/javascript' languge='javascript'>"); 30 out.println("function gotoPageCurrent(){" + 31 "var pageCurrent=document.getElementById('pageCurrent'); " + 32 //"window.alert('pageCurrent='+pageCurrent.value);" + 33 "window.open('/Page/ManagerUser2?pageCurrent='+pageCurrent.value+'&pageBigCurrent='+parseInt(((pageCurrent.value-1)/3+1)),'_self');}" + 34 "function confirmOper()" + 35 "{return window.confirm('确认删除该用户?'); }"); 36 out.println("</script>"); 37 38 //当前页 39 int pageCurrent=1; 40 //当前页每页显示数据条数 41 int pageSize = 3; 42 //当前页显示多少页可供用户点击访问 43 int pageNum = 3; 44 //当前大页 45 int pageBigCurrent = 1; 46 47 //获取用户点击的页数(用户想要跳转到的页数),用户点击之后传递过来 48 String sPageCurrent = request.getParameter("pageCurrent"); 49 50 if(sPageCurrent!=null) 51 { 52 pageCurrent = Integer.parseInt(sPageCurrent); 53 } 54 55 //获取用户点击的大页数(下一个大页界面) 56 String sPageCurrent2 = request.getParameter("pageBigCurrent"); 57 if(sPageCurrent2!=null) 58 { 59 pageBigCurrent = Integer.parseInt(sPageCurrent2); 60 } 61 62 System.out.println(pageBigCurrent); 63 System.out.println(pageCurrent+" "+ pageBigCurrent+" "); 64 UserService useSer = new UserService(); 65 Page page = useSer.pageUsers(pageCurrent, pageBigCurrent, pageSize, pageNum); 66 67 //返回一个页面(html技术) 68 out.println("<html><br/>"); 69 out.println("<image src='images/logo1.jpg' /><hr/>"); 70 out.println("<head><br/>"+ 71 "<title>ManagerUser</title> <br/>"+ 72 "</head> <br/>"); 73 out.println("<body> <br/>"); 74 out.println("<h1>用户管理 </h1> <a href='/Page/OperUser?&type=goAddUser'>添加用户</a>"); 75 76 //定义一个table来显示数据 77 out.println("<table border=1 width=500px>"); 78 //表头 79 out.println("<tr><th>id</th><th>用户名</th><th>email</th><th>等级</th><th>删除</th><th>修改</th></tr>"); 80 81 //5 根据结果作处理 82 List<Users> array = page.getList(); 83 for(Users u: array) 84 { 85 out.println("<tr><td>"+u.getUserId()+"</td>" + 86 "<td>"+u.getUser_name()+"</td>" + 87 "<td>"+u.getEmail()+"</td>" + 88 "<td>"+u.getGrade()+"</td> " + 89 "<td><a onClick='return confirmOper();' href='/Page/OperUser?userId="+u.getUserId()+"&type=del"+" '>删除</a> </td>" + 90 "<td><a href='/Page/OperUser?type=gotoUpView&userId="+u.getUserId()+"'>修改</a> </td>" + 91 "</tr>"); 92 } 93 out.println("</table><br/>"); 94 95 //上一大页超链接 96 if(pageBigCurrent>1) 97 { 98 out.println("<a href='/Page/ManagerUser2?pageBigCurrent=" +(pageBigCurrent-1)+"&pageCurrent="+((pageBigCurrent-2)*pageNum+1)+"' style='text-decoration:none'> << </a>"+" "); 99 } 100 101 //显示分页选项 102 for(int i=page.getStart(); i<=page.getEnd();i++) 103 { 104 //此处需要传递两个参数到下一个分页页面,一个是小当前小页数,一个是当前大页数 105 out.println("<a href='/Page/ManagerUser2?pageCurrent="+i+"&pageBigCurrent="+pageBigCurrent+"'>"+i+"</a> "); 106 } 107 108 //下一大页超链接 109 if(pageBigCurrent<page.getTotalBigPage()) 110 { 111 //此处需要传递两个参数,一个是下一个大页数,一个是下一个大页数的开始小页数 112 out.println("<a href='/Page/ManagerUser2?pageBigCurrent=" +(pageBigCurrent+1)+"&pageCurrent="+((pageBigCurrent)*pageNum+1)+"' style='text-decoration:none'>>></a>"+" "); 113 } 114 115 //显示当前页/总页数 116 out.println("当前页"+pageCurrent+"/总页数"+page.getTotalPage()+"<br/>"); 117 out.println("跳转:<input type='text' id='pageCurrent' maxlength='6' name='pageCurrent'/><input type='button' value='跳' onClick='gotoPageCurrent()' /> <br/>"); 118 119 out.println("pageCurrent="+pageCurrent+" & "+"pageCurrent2="+pageBigCurrent); 120 out.println("<br/><br/>"); 121 out.println("<a href='/Page/MainFrame'>返回主页</a>"); 122 out.println("</body> <br/>"); 123 out.println("</html>"); 124 125 } 126 127 public void doPost(HttpServletRequest request, HttpServletResponse response) 128 throws ServletException, IOException { 129 130 this.doGet(request, response); 131 } 132 133 }