【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     }

View Code

前台显示：

 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 对象。

JDBC使用PreparedStatement的好处

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 }

View Code

编程小技巧：

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;
    }

总结：不断提高自己的编程效率！

秒客网

【DRP】表格—分页查询（1）—表格加载数据

相关文章