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">
    
</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.同时我也希望其他人能提供给我些分层的意见。

补充：Jsp教程,Java技巧及代码