【DRP】表格—分页查询（1）—表格加载数据
首次进入页面默认显示2条记录：
1 <%@ page import="java.util.*" %> 2 <%@ page import="java.text.*" %> 3 <%@ page import="com.bjpowernode.drp.sysmgr.domain.*" %> 4 <%@ page import="com.bjpowernode.drp.sysmgr.manager.*" %> 5 <%@ page import="com.bjpowernode.drp.util.*" %> 6 <% 7 int pageNo = 1; 8 int pageSize = 2; 9 String pageNoString = request.getParameter("pageNo"); 10 if (pageNoString != null) { 11 pageNo = Integer.parseInt(pageNoString); 12 } 13 PageModel pageModel = UserManager.getInstance().findUserList(pageNo, pageSize); 14 %>
调用后台分页查询方法：
1 /** 2 * 分页查询 3 * @param pageNo 第几页 4 * @param pageSize 每页多少条数据 5 * @return pageModel 6 */ 7 public PageModel findUserList(int pageNo, int pageSize) { 8 StringBuffer sbSql = new StringBuffer(); 9 sbSql.append("select user_id, user_name, password, contact_tel, email, create_date ") 10 .append("from ") 11 .append("( ") 12 .append("select rownum rn, user_id, user_name, password, contact_tel, email, create_date ") 13 .append("from ") 14 .append("( ") 15 .append("select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order by user_id ") 16 .append(") where rownum <= ? ") 17 .append(") where rn > ? "); 18 Connection conn = null; 19 //JDBC数据库连接API之一 20 PreparedStatement pstmt = null; 21 ResultSet rs = null; 22 //封装的分页实体 23 PageModel pageModel = null; 24 try { 25 conn = DbUtil.getConnection(); 26 pstmt = conn.prepareStatement(sbSql.toString()); 27 pstmt.setInt(1, pageNo * pageSize); 28 pstmt.setInt(2, (pageNo - 1) * pageSize); 29 rs = pstmt.executeQuery(); 30 List userList = new ArrayList(); 31 while (rs.next()) { 32 User user = new User(); 33 user.setUserId(rs.getString("user_id")); 34 user.setUserName(rs.getString("user_name")); 35 user.setPassword(rs.getString("password")); 36 user.setContactTel(rs.getString("contact_tel")); 37 user.setEmail(rs.getString("email")); 38 user.setCreateDate(rs.getTimestamp("create_date")); 39 userList.add(user); 40 } 41 pageModel = new PageModel(); 42 pageModel.setList(userList); 43 pageModel.setTotalRecords(getTotalRecords(conn)); 44 pageModel.setPageSize(pageSize); 45 pageModel.setPageNo(pageNo); 46 }catch(SQLException e) { 47 e.printStackTrace(); 48 }finally { 49 DbUtil.close(rs); 50 DbUtil.close(pstmt); 51 DbUtil.close(conn); 52 } 53 return pageModel; 54 }
前台显示：
1 <% 2 List userList = pageModel.getList(); 3 for (Iterator iter=userList.iterator(); iter.hasNext();) 4 5 { 6 User user = (User)iter.next(); 7 %> 8 <tr> 9 <td class="rd8"> 10 <input type="checkbox" name="selectFlag" class="checkbox1" 11 value="<%=user.getUserId() %>"> 12 </td> 13 <td class="rd8"> 14 <%=user.getUserId() %> 15 </td> 16 <td class="rd8"> 17 <%=user.getUserName() %> 18 </td> 19 <td class="rd8"> 20 <%=user.getContactTel()%> 21 </td> 22 <td class="rd8"> 23 <%=user.getEmail() %> 24 </td> 25 <td class="rd8"> 26 <%=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateDate()) %> 27 </td> 28 </tr> 29 <% 30 } 31 %>
关于 PreparedStatement 对象。
PageModel 实体
1 import java.util.List; 2 3 /** 4 * 封装分页信息 5 * @author Administrator 6 * 7 */ 8 public class PageModel { 9 10 //结果集 11 private List list; 12 13 //查询记录数 14 private int totalRecords; 15 16 //每页多少条数据 17 private int pageSize; 18 19 //第几页 20 private int pageNo; 21 22 /** 23 * 总页数 24 * @return 25 */ 26 public int getTotalPages() { 27 return (totalRecords + pageSize - 1) / pageSize; 28 } 29 30 /** 31 * 取得首页 32 * @return 33 */ 34 public int getTopPageNo() { 35 return 1; 36 } 37 38 /** 39 * 上一页 40 * @return 41 */ 42 public int getPreviousPageNo() { 43 if (pageNo <= 1) { 44 return 1; 45 } 46 return pageNo - 1; 47 } 48 49 /** 50 * 下一页 51 * @return 52 */ 53 public int getNextPageNo() { 54 if (pageNo >= getBottomPageNo()) { 55 return getBottomPageNo(); 56 } 57 return pageNo + 1; 58 } 59 60 /** 61 * 取得尾页 62 * @return 63 */ 64 public int getBottomPageNo() { 65 return getTotalPages(); 66 } 67 68 public List getList() { 69 return list; 70 } 71 72 public void setList(List list) { 73 this.list = list; 74 } 75 76 public int getTotalRecords() { 77 return totalRecords; 78 } 79 80 public void setTotalRecords(int totalRecords) { 81 this.totalRecords = totalRecords; 82 } 83 84 public int getPageSize() { 85 return pageSize; 86 } 87 88 public void setPageSize(int pageSize) { 89 this.pageSize = pageSize; 90 } 91 92 public int getPageNo() { 93 return pageNo; 94 } 95 96 public void setPageNo(int pageNo) { 97 this.pageNo = pageNo; 98 } 99 }
编程小技巧：
1、遍历的应用：
List userList = pageModel.getList(); for (Iterator iter=userList.iterator(); iter.hasNext();) {
}
2、日期格式化：
1 <%=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateDate()) %>
3、三目运算
表格中的联系电话个email数据库中是null，如果数据库为null显示空。
//这里用到了三目运算 public String getContactTel() { return contactTel == null ? "" : contactTel; } //这里用到了三目运算 public String getEmail() { return email == null ? "" : email; }
总结：不断提高自己的编程效率！
