JSP实现分页显示

 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 }

 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
 articles = new ArrayList
();
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 
63   
64     
65
66     
67     
68     
69     
70     
71     
72     
75   
76
77   
78     共<%=totalPage %>页 第<%=pageNo %>页 首页 “>上一页 “>下一页 “>末页
79

80
81           <%for(Iterator
 it = articles.iterator(); it.hasNext(); ) {
82                     Article a = it.next(); %>
83              
84
85
86
87
88
89
90
91
92
93
                                        <%=a.getATitle() %>                  
                                        <%=a.getACont() %>                  
                                        <%=a.getAAuthor() %>                  
              

94         <%} %>
95
96   

97