js|odbc| Page One, prepare before running
Recommended a MS SQLServer7 database DNS, named: test_db
There is a table in the Database: Guestbook field: Name (varchar), email (varchar), body (text)
The database user is null for the SA password and can be modified by itself.
Second, the Code
<%@ page contenttype= "text/html;charset=gb2312"%>
<%
Variable declaration
Java.sql.Connection Sqlcon; Database Connection objects
Java.sql.Statement sqlstmt; SQL Statement Object
Java.sql.ResultSet Sqlrst; Result set Object
Java.lang.String Strcon; Database connection string
Java.lang.String strSQL; 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,j,k; Set the number of records to display on a page
Intpagesize = 5; Get the number to display
Strpage = request.getparameter ("page");
if (strpage==null) {
Indicates that there is no page parameter in QueryString, and the first page of data is displayed at this time
Intpage = 1;
} else{
Converts a string to an integral type
Intpage = Java.lang.Integer.parseInt (strpage);
if (intpage<1) intpage = 1; }
Load JDBC-ODBC Driver
Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");
To set the database connection string
Strcon = "Jdbc:odbc:Test_DB";
Connecting to a database
Sqlcon = Java.sql.DriverManager.getConnection (Strcon, "sa", "");
To create an SQL statement object
sqlstmt = Sqlcon.createstatement ();
Get Total Records
strSQL = "SELECT count (*) from Guestbook";
Sqlrst = Sqlstmt.executequery (strSQL);
Execute the SQL statement and get the result set
Sqlrst.next (); When the recordset is just opened, the pointer is before the first record
Introwcount = Sqlrst.getint (1);
Sqlrst.close (); Close result set
Count Total Pages
Intpagecount = (introwcount+intpagesize-1)/intpagesize;
Adjusts the page number if (intpage>intpagecount) intpage = Intpagecount to be displayed;
Set GET Data SQL statement
strSQL = "Select Name,email,body from Guestbook";
Execute the SQL statement and get the result set
Sqlrst = Sqlstmt.executequery (strSQL);
Position the record pointer over the first record of the page you want to display
i = (intPage-1) * intpagesize;
for (j=0;j<i;j++) Sqlrst.next (); %>
<TITLE>JSP database operation Routines-data paging display-jdbc-odbc</title>
<body>
<P ALIGN=CENTER>JDBC-ODBC Message Board </p>
<table border= "1" cellspacing= "0" cellpadding= "0" width=600 align=center>
<%
Display data
i = 0;
while (I<intpagesize && sqlrst.next ()) {%>
<tr>
<td> Name: <%=sqlrst.getstring (1)%></td>
<td> Mail: <%=sqlrst.getstring (2)%></td>
</tr>
<tr>
<TD colspan=2><%=sqlrst.getstring (3)%></td>
</tr>
<% i++; }%>
<tr>
<TD colspan=2 align=center>
Page <%=intPage%> Total <%=intPageCount%> page
<%if (Intpage<intpagecount) {%>
<a href= "mssql.jsp?page=<%=intpage+1%>" > next page </a><%
}
%>
<%if (intpage>1) {%>
<a href= "mssql.jsp?page=<%=intpage-1%>" > previous page </a><%
}
%>
</td>
</tr>
</table> </body>
<%
Close result set
Sqlrst.close ();
Close an SQL statement object
Sqlstmt.close ();
Close Database
Sqlcon.close ();
%>
Third, how to run?
Save the code as a file test.jsp
Orion Application Server:
Copy to Orion's Default-web-app directory, through:
http://localhost:port/test.jsp
Access test
For Resin,tomcat,jws and so on, all can be run through.
