jsp+oracle 的两种分页实现程序

2024-09-05 00:19:21

字体：大中小

来源：转载

供稿：网友

中国最大的web开发资源网站及技术社区，

//*连接oracle的bean: 文件名为：conn_oracle.java
//*--------------------------------------------------------------------------------------------------------------

package conn_oracle;
import java.sql.*;
import java.util.*;
import java.io.printstream;
public class conn_oracle
{

    string servername="localhost";
    string sconnstr="jdbc:oracle:thin:@localhost:1521:oemrep";
    string login_name="scott";
    string pwd="tiger";
    statement stmt=null;
    connection conn=null;
    resultset rs=null;
    int afint;

    public conn_oracle()
    {

        try
        {
         class.forname("oracle.jdbc.driver.oracledriver");
        }
        catch(classnotfoundexception classnotfoundexception)
        {
            system.err.println(classnotfoundexception.getmessage());
        }
    }

public resultset executequery(string sql){
  try{
   conn = drivermanager.getconnection(sconnstr, login_name, pwd);
            stmt=conn.createstatement(resultset.type_scroll_sensitive,resultset.concur_read_only);
   rs=stmt.executequery(sql);
  }catch(sqlexception e){
   system.out.println("can't executequery");
  }
  return rs;
}

public int executeupdate(string sql) throws sqlexception{
   try
       {
        conn = drivermanager.getconnection(sconnstr, login_name, pwd);
  stmt=conn.createstatement() ;
  afint=stmt.executeupdate(sql);
    }catch(sqlexception sqlexception)
          {
            system.err.println(sqlexception.getmessage());
              }

return afint;
}

public void closecon()
{
   try{
       if(rs!=null)
{
       rs.close();
}
       if(stmt!=null)
{
       stmt.close();
}
       if(conn!=null)
{
          conn.close();
}
    }catch(exception e){}
}
}

//* 使用oracle 的rownum 进行分页文件名为 fy4.jsp
//*---------------------------------------------------------------------------------------------------------------------------------
<%@ page contenttype="text/html;charset=gb2312" %>
<%@ page import="java.sql.*" %>
<jsp:usebean id="dblink" scope="page" class="conn_oracle.conn_oracle"/>

<%
//变量声明
string mysql=new string(); //sql语句
int introwcount=0; //总的记录数
int intpagecount=0; //总的页数
int intpagesize=5; //每页显示的记录数
int intpage; //待显示页码
string strpage=new string(); //用来接收当页码参数
int begin_no=0; //开始的rownum记录号
int end_no=0; //结束的rownum记录号

//取得待显示页码
strpage = request.getparameter("page");
if(strpage==null){//表明在querystring中没有page这一个参数，此时显示第一页数据
intpage = 1;
}
else{//将字符串转换成整型
intpage = java.lang.integer.parseint(strpage);
if(intpage<1) intpage = 1;
}

//得到总的数据记录行数
mysql="select count(*) total_rows from scott.performance";
resultset rs=dblink.executequery(mysql);
if(rs.next())
{
introwcount=rs.getint("total_rows"); //这里只能用getint()
//out.print("total rows is:"+introwcount);
}
rs.close();

//计算总共要分多少页
intpagecount = (introwcount+intpagesize-1) / intpagesize;
//调整待显示的页码
if(intpage>intpagecount) intpage = intpagecount;
//out.print("<br>total pages is:"+intpagecount);

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>jsp数据库操作例程 - 数据分页显示 - jdbc 2.0 - oracle</title>
</head>
<body>
<table border="1" cellspacing="0" cellpadding="0">
<tr>
<th>学号</th>
<th>姓名</th>
</tr>

<%
    begin_no=(intpage-1) * intpagesize + 1;
    end_no = intpage * intpagesize;
//out.print("<br>begin:"+begin_no+"<br>end:"+end_no);
mysql="select * from (select rownum row_id ,s_id,s_name from (select s_id,s_name from performance order by s_id desc)) where row_id between " +begin_no+ " and " +end_no;
    rs = dblink.executequery(mysql);
while(rs.next())
{
   %>
   <tr>
       <td><%=rs.getstring("s_id")%></td>
       <td><%=rs.getstring("s_name")%></td>
      </tr>
   <%
      }
   rs.close();
   %>
</table>

第<%=intpage%>页共<%=intpagecount%>页
<a href="fy4.jsp?page=1">首页</a>
<%if(intpage<intpagecount){%><a href="fy4.jsp?page=<%=intpage+1%>">下一页</a><%}%>
<%if(intpage>1){%><a href="fy4.jsp?page=<%=intpage-1%>">上一页</a><%}%>
<a href="fy4.jsp?page=<%=intpagecount%>">尾页</a>

<%
//关闭数据库连接
dblink.closecon();
%>

//* 一般通用的分页方法，不过效率较低文件名为：fy2.jsy
//*------------------------------------------------------------------------------------------------------------------------------------
<%@ page contenttype="text/html;charset=gb2312" %>
<jsp:usebean id="dblink" scope="page" class="conn_oracle.conn_oracle"/>

<%
//变量声明

java.sql.resultset rs; //结果集对象
java.lang.string sql; //sql语句

int intpagesize; //一页显示的记录数
int introwcount; //记录总数
int intpagecount; //总页数
int intpage; //待显示页码
java.lang.string strpage;

int i;

//设置一页显示的记录数
intpagesize = 20;

sql = "select * from scott.performance";

//执行sql语句并获取结果集
rs = dblink.executequery(sql);

//获取记录总数
rs.last();
introwcount = rs.getrow();

//记算总页数
intpagecount = (introwcount+intpagesize-1) / intpagesize;

//调整待显示的页码
if(intpage>intpagecount) intpage = intpagecount;
%>

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>jsp数据库操作例程 - 数据分页显示 - jdbc 2.0 - oracle</title>
</head>

<body>

<%
if(intpagecount>0){
//将记录指针定位到待显示页的第一条记录上
rs.absolute((intpage-1) * intpagesize + 1);

//显示数据
i = 0;
while(i<intpagesize && !rs.isafterlast()){
%>
<tr>
<td><%=rs.getstring("s_id")%></td>
<td><%=rs.getstring("s_name")%></td>
</tr>
<%
rs.next();
i++;
}
}
%>

</table>

第<%=intpage%>页共<%=intpagecount%>页
<a href="fy2.jsp?page=1">首页</a>
<%if(intpage<intpagecount){%><a href="fy2.jsp?page=<%=intpage+1%>">下一页</a><%}%>
<%if(intpage>1){%><a href="fy2.jsp?page=<%=intpage-1%>">上一页</a><%}%>
<a href="fy2.jsp?page=<%=intpagecount%>">尾页</a>

</body>
</html>

<%
//关闭结果集
rs.close();
//关闭数据库连接
dblink.closecon();
%>

上一篇：JSP中读文件和写文件的例子

下一篇：解决JSP中rs.getString （）的换行问题及当一行中文过长时撑开表格的问题