jsp+oracle分頁實現程式碼

今天做了個基於jsp+oracle分頁的實現，對於初學者來說這是好的（看了後絕對可以自己實現，動手試試把），但是對於有基礎的只是溫故下sql語句（沒涉及到很好的分層），好了，我們開始把它實現把：

1.首先建立一個web項目。（如圖）

2.匯入oracle驅動包到lib目錄下，開編寫資料庫連接類DBMamager。

代碼如下

複製代碼

package com.page.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBManager {         private static Connection connection = null;         static     {         try {             Class.forName("oracle.jdbc.driver.OracleDriver");             connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","******","******");//自己oracle資料庫的帳號密碼        　　　　　} catch (ClassNotFoundException e) {             // TODO Auto-generated catch block             e.printStackTrace();         } catch (SQLException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }     }         protected static Connection getConnection()     {         return connection;     }         public int update(String sql)     {         //boolean flag = false;         int row = 0;         Connection connection = DBManager.getConnection();         PreparedStatement statement = null;         try         {             statement = connection.prepareStatement(sql);             row= statement.executeUpdate();         //    System.out.println(sql);         }         catch (SQLException e)         {             e.printStackTrace();         }         return row;     }         public ResultSet find(String sql)     {         Connection connection = getConnection();         ResultSet result = null;                 PreparedStatement statement = null;         try         {             System.out.println(sql);             statement = connection.prepareStatement(sql);             result = statement.executeQuery();                     } catch (SQLException e)         {             e.printStackTrace();         }                 return result;     } }

3.具體實現分頁的代碼如下（先看代碼後面有注釋別太心急慢慢看）

代碼如下

複製代碼

<%@page import="com.sun.crypto.provider.RSACipher"%> <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ page import="com.page.util.*"%> <%@ page import="java.sql.*"  %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html>   <head>     <base href="<%=basePath%>">         <title>使用者資訊列表</title>     <meta http-equiv="pragma" content="no-cache">     <meta http-equiv="cache-control" content="no-cache">     <meta http-equiv="expires" content="0">        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">     <meta http-equiv="description" content="This is my page">     <!--     <link rel="stylesheet" type="text/css" href="styles.css">     -->   </head>     <body>     <table align="center" width="1000px" style="margin:100px" border="1" cellspacing="0" cellpadding="0" >         <tr align="center" bgcolor="#3270E5" height="30px">             <th>編號</th>             <th>使用者帳號</th>             <th>使用者姓名</th>             <th>使用者密碼</th>             <th>使用者資訊</th>         </tr>         <%             int i;             int page_size=3; //分頁單位             int all_pages; //總頁數             int pages; //接受的頁碼變數             int cur_page=1; //當前頁             int start_page; //本頁記錄開始             int count_row; //總記錄數             int end_page;//本頁記錄結束             String sql_row="select count(id) as count_row from page";             DBManager dbManager=new DBManager();             ResultSet count_rs=dbManager.find(sql_row);             count_rs.next();             count_row=count_rs.getInt("count_row");             all_pages=(int)Math.ceil((count_row+page_size-1)/page_size);//計算總頁數                         //判斷參數pages是否為空白             if(request.getParameter("pages")==null){                 pages=1;             }else{                 pages= new Integer(request.getParameter("pages")).intValue();             }             //判斷當前頁             if(pages > all_pages || pages == 0){                 cur_page = 1;             } else {                 cur_page = pages;             }             start_page=(cur_page-1)*page_size; //本頁開始的記錄編號數（資料庫中的第幾條資料）             end_page=start_page+page_size;//本頁顯示的最後一條編號數             String sql="select * from(select rownum rn,p.* from(select * from page )p where rownum<= '"+end_page+"')where rn>'"+start_page+"'";                         ResultSet rsSet=dbManager.find(sql);             int t_row=1;             String color="#FFFFFF";             while(rsSet.next()){                        if(t_row%2==0){            //讓表格更加好看雙數行數時顯示不同顏色                     color="#EDF5FC";                 }else{                     color="#FFFFFF";                 }         %>         <tr bgcolor=<%=color %>>             <td><%=rsSet.getString(1) %></td>             <td><%=rsSet.getString(2) %></td>             <td><%=rsSet.getString(3) %></td>             <td><%=rsSet.getString(4) %></td>             <td><%=rsSet.getString(5) %></td>         </tr>         <%             t_row++;             }         %>         <tr>             <td colspan="5" align="right">             <%if(cur_page>1){%>//不在第一頁時顯示上一頁             <a href="index.jsp?pages=<%=cur_page-1%>">上一頁</a>             <%             }             if(cur_page<all_pages){//不在最後一行時顯示下一頁             %>             <a href="index.jsp?pages=<%=cur_page+1%>">下一頁</a>             <a href="index.jsp?pages=<%=all_pages%>">末頁</a>//顯示最後一頁             <%             }             %>             <% for (i=1;i<=all_pages;i++) {%>// 迴圈顯示每一頁，本頁時不顯示超連結（沒有底線）                 <% if (i != pages) {%>                     <a href="index.jsp?pages=<%= i %>"><%= i %></a>                 <% } else{%>                 <%=i %>                 <%} %>             <%}%>             共<%=all_pages %>頁&nbsp;             </td>         </tr>        </table>   </body> </html>

4.好了分頁已經完成了，部署好tomcat運行網站吧！（如圖）

第二頁：

第三頁：

 

第四頁：

 注意：

總頁數的求取是：all_pages=(int)Math.ceil((count_row+page_size-1)/page_size);//計算總頁數

代碼如下

複製代碼

sql語句是：String sql="select * from(select rownum rn,p.* from(select * from page )p where rownum<= '"+end_page+"')where rn>'"+start_page+"'";   例如：select *     from     (      select rownum rn,p.*      from         (select *          from page order by id          )p where rownum<= 4     )where rn>3;//要用偽列！！ 最後附上我的sql代碼： create table page (     id varchar2(6) not null,     username varchar2(20) not null,     password varchar2(20) not null,     info varchar2(200) default '大家好，很高興認識你們！',     constraints pk_id primary key(id) ); select * from page; delete page; drop table page; insert into page (id,username,password) values('000001','黃凱','111111'); insert into page (id,username,password,info) values('000002','肖旺','222222','我是JJ,林俊傑！'); insert into page (id,username,password) values('000003','申俊傑','qqqq'); insert into page (id,username,password,info) values('000004','楊小宇','444444','我班長！'); insert into page (id,username,password) values('000005','許世群','xxxxxx'); insert into page (id,username,password,info) values('000006','王東寶','666666','我寶爺！'); insert into page (id,username,password,info) values('000007','admin','admin','我管理員！'); insert into page (id,username,password,info) values('000008','劉鵬','666666','我愛遊戲！'); insert into page (id,username,password,info) values('000009','劉永軍','liu666','我少夜哈哈！'); update page set info='我是少爺哈哈！！' where id='000009'; select rownum,p.* from page p where rownum between 1 and 4; select count(id) as a from page; select count(id) as count_row from page; select *     from     (      select rownum rn,p.*      from         (select *          from page order by id          )p where rownum<= 4     )where rn>3; select *     from     (      select rownum rn,p.*      from         (select *          from page )p where rownum<= 6     )where rn>3

1.在這裡我們的任務完成了，在如果有什麼問題可以聯絡我QQ：541817557（一起交流）。

2.同時我也希望其他人能提供給我些分層的意見。