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 （）的換行問題及當一行中文過長時撐開表格的問題