//*連接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;
//取得待顯示頁碼
strpage = request.getparameter("page");
if(strpage==null){//表明在querystring中沒有page這一個參數，此時顯示第一頁數據
intpage = 1;
}
else{//將字符串轉換成整型
intpage = java.lang.integer.parseint(strpage);
if(intpage<1) intpage = 1;
}
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>
<table border="1" cellspacing="0" cellpadding="0">
<tr>
<th>學號</th>
<th>姓名</th>
</tr>
<%
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();
%>
新聞熱點
疑難解答
