JSP实现分页显示

今天实现了一个小小的功能：将数据库里面记录实现分页查询显示出来，代码如下

DB.java

 1 package com.sunjob.db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import java.sql.Statement;
 9 import java.util.ArrayList;
10 import java.util.List;
11 
12 import com.sunjob.pojo.Article;
13 
14 public class DB {
15     
16     public static Connection getConn(){
17         Connection conn = null;
18         try {
19             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
20             conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=MyDB", "sa" , "sasa");
21         } catch (ClassNotFoundException e) {
22             e.printStackTrace();
23         } catch (SQLException e) {
24             e.printStackTrace();
25         }
26         return conn;
27     }
28     
29     
30     public static Statement createStmt(Connection conn) {
31         Statement stmt = null;
32         try {
33             stmt = conn.createStatement();
34         } catch (SQLException e) {
35             e.printStackTrace();
36         }
37         return stmt;
38     }
39     public static ResultSet executeQuery(Statement stmt, String sql) {
40         ResultSet rs = null;
41         try {
42             rs = stmt.executeQuery(sql);
43         } catch (SQLException e) {
44             e.printStackTrace();
45         }
46         return rs;
47     }
48     public static void close(Connection conn) {
49         if(conn != null) {
50             try {
51                 conn.close();
52             } catch (SQLException e) {
53                 e.printStackTrace();
54             }
55             conn = null;
56         }
57     }
58     
59     public static void close(Statement stmt) {
60         if(stmt != null) {
61             try {
62                 stmt.close();
63             } catch (SQLException e) {
64                 e.printStackTrace();
65             }
66             stmt = null;
67         }
68     }
69     
70     public static void close(ResultSet rs) {
71         if(rs != null) {
72             try {
73                 rs.close();
74             } catch (SQLException e) {
75                 e.printStackTrace();
76             }
77             rs = null;
78         }
79     }
80     
81 }

Article.java

 1 package com.sunjob.db;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import java.sql.Statement;
 9 import java.util.ArrayList;
10 import java.util.List;
11 
12 import com.sunjob.pojo.Article;
13 
14 public class DB {
15     
16     public static Connection getConn(){
17         Connection conn = null;
18         try {
19             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
20             conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=MyDB", "sa" , "sasa");
21         } catch (ClassNotFoundException e) {
22             e.printStackTrace();
23         } catch (SQLException e) {
24             e.printStackTrace();
25         }
26         return conn;
27     }
28     
29     
30     public static Statement createStmt(Connection conn) {
31         Statement stmt = null;
32         try {
33             stmt = conn.createStatement();
34         } catch (SQLException e) {
35             e.printStackTrace();
36         }
37         return stmt;
38     }
39     public static ResultSet executeQuery(Statement stmt, String sql) {
40         ResultSet rs = null;
41         try {
42             rs = stmt.executeQuery(sql);
43         } catch (SQLException e) {
44             e.printStackTrace();
45         }
46         return rs;
47     }
48     public static void close(Connection conn) {
49         if(conn != null) {
50             try {
51                 conn.close();
52             } catch (SQLException e) {
53                 e.printStackTrace();
54             }
55             conn = null;
56         }
57     }
58     
59     public static void close(Statement stmt) {
60         if(stmt != null) {
61             try {
62                 stmt.close();
63             } catch (SQLException e) {
64                 e.printStackTrace();
65             }
66             stmt = null;
67         }
68     }
69     
70     public static void close(ResultSet rs) {
71         if(rs != null) {
72             try {
73                 rs.close();
74             } catch (SQLException e) {
75                 e.printStackTrace();
76             }
77             rs = null;
78         }
79     }
80     
81 }

显示页面：index.jsp

 1 <%@ page language="java" import="java.util.*" pageEncoding="GB18030"%>
 2 <%@page import="java.sql.Connection"%>
 3 <%@page import="com.sunjob.db.DB"%>
 4 <%@page import="java.sql.Statement"%>
 5 <%@page import="java.sql.ResultSet"%>
 6 <%@page import="com.sunjob.pojo.Article"%>
 7 <%
 8 String path = request.getContextPath();
 9 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
10 %>
11 <%
12     final int pageSize = 4;//每页显示的数量
13     int pageNo = 1;  //显示的页数
14     String pageNoStr = request.getParameter("pageNo");
15     if(pageNoStr!=null && !pageNoStr.trim().equals("")){
16         try{
17             pageNo = Integer.parseInt(pageNoStr);
18         }catch(NumberFormatException e){
19             pageNo = 1;
20         }
21     }
22     
23     if(pageNo<=0){
24         pageNo = 1;
25     }
26     
27     int totalPage = 0; //总页数
28     
29     List<Article> articles = new ArrayList<Article>();
30     Connection conn = DB.getConn();
31     Statement stmtCount = DB.createStmt(conn);
32     ResultSet rsCount = DB.executeQuery(stmtCount,"select count(*) from article");
33     rsCount.next();
34     int totalArticle = rsCount.getInt(1); //取得总的文章数
35     
36     totalPage = (totalArticle + pageSize - 1)/pageSize; //计算总页数
37     
38     if(pageNo > totalPage) pageNo = totalPage;
39     
40     int startPos = (pageNo-1) * pageSize; //每页开始的帖子
41     String sql = "select top "+pageSize+" * from article where a_id not in(select top "+startPos+" a_id from article)";
42     System.out.println(sql);
43     
44     Statement stmt = DB.createStmt(conn);
45     ResultSet rs = DB.executeQuery(stmt, sql);
46     while(rs.next()) {
47         Article a = new Article();
48         a.setAId(rs.getInt("a_id"));
49         a.setATitle(rs.getString("a_title"));
50         a.setACont(rs.getString("a_cont"));
51         a.setAAuthor(rs.getString("a_author"));
52         articles.add(a);
53     }
54     DB.close(rsCount);
55     DB.close(stmtCount);
56     DB.close(rs);
57     DB.close(stmt);
58     DB.close(conn);
59     
60  %>
61 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
62 <html>
63   <head>
64     <base href="<%=basePath%>">
65     
66     <title>分页显示</title>
67     <meta http-equiv="pragma" content="no-cache">
68     <meta http-equiv="cache-control" content="no-cache">
69     <meta http-equiv="expires" content="0">    
70     <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
71     <meta http-equiv="description" content="This is my page">
72     <!--
73     <link rel="stylesheet" type="text/css" href="styles.css">
74     -->
75   </head>
76           
77   <body>
78     共<%=totalPage %>页 第<%=pageNo %>页 <a href="index.jsp?pageNo=1">首页</a> <a href="index.jsp?pageNo=<%=pageNo-1 %>">上一页</a> <a href="index.jsp?pageNo=<%=pageNo+1 %>">下一页</a> <a href="index.jsp?pageNo=<%=totalPage %>">末页</a>
79       <br>
80       
81           <%for(Iterator<Article> it = articles.iterator(); it.hasNext(); ) {           
82                     Article a = it.next(); %>
83              <table border="1">
84                  <tr>
85                      <td><%=a.getATitle() %></td>
86                  </tr>
87                  <tr>
88                      <td><%=a.getACont() %></td>
89                  </tr>
90                  <tr>
91                      <td><%=a.getAAuthor() %></td>
92                  </tr>
93              </table>
94         <%} %>
95       
96   </body>
97 </html>

posted @ 2012-07-23 15:03 残剑_ 阅读(7527) 评论(3) 编辑收藏举报

刷新页面返回顶部

残剑

JSP实现分页显示

公告