js|oracle| Program | pagination
* Oracle-attached bean: file name: 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) {}
}
}
* Use Oracle's rownum for paging file name fy4.jsp
//*------------------------------------------------------------------------------------------------------------ ---------------------
<%@ page contenttype= "text/html;charset=gb2312"%>
<%@ page import= "java.sql.*"%>
<jsp:usebean id= "Dblink" scope= "page" class= "Conn_oracle.conn_oracle"/>
<%
Variable declaration
String Mysql=new string (); SQL statement
int introwcount=0; Total number of records
int intpagecount=0; Total number of pages
int intpagesize=5; Number of records displayed per page
int intpage; Page number to display
String Strpage=new string (); Used to receive when the page number parameter
int begin_no=0; Start RowNum record number
int end_no=0; RowNum record number of the end
Get the number to display
Strpage = request.getparameter ("page");
if (strpage==null) {//indicates that there is no page this parameter in QueryString, the first page data is now displayed
Intpage = 1;
}
else{//converts a string to an integral type
Intpage = Java.lang.Integer.parseInt (strpage);
if (intpage<1) intpage = 1;
}
Get the total number of rows of data records
Mysql= "SELECT COUNT (*) total_rows from scott.performance";
ResultSet rs=dblink.executequery (MySQL);
if (Rs.next ())
{
Introwcount=rs.getint ("Total_rows"); This can only be done with Getint ()
Out.print ("Total rows are:" +introwcount);
}
Rs.close ();
Calculate how many pages to divide in total
Intpagecount = (introwcount+intpagesize-1)/intpagesize;
Adjust the number of pages to be displayed
if (intpage>intpagecount) intpage = Intpagecount;
Out.print ("<br>total pages is:" +intpagecount);
%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<TITLE>JSP database operation Routines-data paging display-JDBC 2.0-oracle</title>
<body>
<table border= "1" cellspacing= "0" cellpadding= "0" >
<tr>
<th> School Number </th>
<th> name </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 to 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>
Page <%=intPage%> Total <%=intPageCount%> page
<a href= "fy4.jsp?page=1" > Home </a>
<%if (intpage<intpagecount) {%><a href= "fy4.jsp?page=<%=intpage+1%>" > next page </a><%}% >
<%if (intpage>1) {%><a href= "fy4.jsp?page=<%=intpage-1%>" > Prev </a><%}%>
<a href= "fy4.jsp?page=<%=intpagecount%>" > Last </a>
<%
To close a database connection
Dblink.closecon ();
%>
* Generally common paging method, but less efficient file name: Fy2.jsy
//*------------------------------------------------------------------------------------------------------------ ------------------------
<%@ page contenttype= "text/html;charset=gb2312"%>
<jsp:usebean id= "Dblink" scope= "page" class= "Conn_oracle.conn_oracle"/>
<%
Variable declaration
Java.sql.ResultSet rs; Result set Object
Java.lang.String SQL; SQL statement
int intpagesize; Number of records displayed on one page
int introwcount; Total Records
int intpagecount; Total pages
int intpage; Page number to display
Java.lang.String strpage;
int i;
Set the number of records to display on a page
Intpagesize = 20;
Get the number to display
Strpage = request.getparameter ("page");
if (strpage==null) {//indicates that there is no page this parameter in QueryString, the first page data is now displayed
Intpage = 1;
}
else{//converts a string to an integral type
Intpage = Java.lang.Integer.parseInt (strpage);
if (intpage<1) intpage = 1;
}
sql = "SELECT * from Scott.performance";
Execute the SQL statement and get the result set
rs = dblink.executequery (SQL);
Get Total Records
Rs.last ();
Introwcount = Rs.getrow ();
Count Total Pages
Intpagecount = (introwcount+intpagesize-1)/intpagesize;
Adjust the number of pages to be displayed
if (intpage>intpagecount) intpage = Intpagecount;
%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<TITLE>JSP database operation Routines-data paging display-JDBC 2.0-oracle</title>
<body>
<table border= "1" cellspacing= "0" cellpadding= "0" >
<tr>
<th> School Number </th>
<th> name </th>
</tr>
<%
if (intpagecount>0) {
Position the record pointer over the first record of the page you want to display
Rs.absolute ((intPage-1) * intpagesize + 1);
Display data
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>
Page <%=intPage%> Total <%=intPageCount%> page
<a href= "fy2.jsp?page=1" > Home </a>
<%if (intpage<intpagecount) {%><a href= "fy2.jsp?page=<%=intpage+1%>" > next page </a><%}% >
<%if (intpage>1) {%><a href= "fy2.jsp?page=<%=intpage-1%>" > Prev </a><%}%>
<a href= "fy2.jsp?page=<%=intpagecount%>" > Last </a>
</body>
<%
Close result set
Rs.close ();
To close a database connection
Dblink.closecon ();
%>