jsp+oracle分頁實現程式碼

最後更新：2017-01-13 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

今天做了個基於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">
    
</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 %>頁 
            </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.同時我也希望其他人能提供給我些分層的意見。

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

jsp+oracle分頁實現程式碼

聯繫我們

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support