JSP database operation routine (JDBC-ODBC)

To achieveJSP database operation instanceCreates a DNS for the MS SQLServer7 database named Test_DB.

There is a table in the database: guestbook field: name (varchar), email (varchar), body (text)

The sa password is blank for database users and can be modified by themselves.

Code

 
 

  
  
<% @ PageContentType="Text/html; charset = gb2312"%> 
  
  
<%
  
  
// Variable Declaration
  
  
Java. SQL. Connection sqlCon; // database Connection object
  
  
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; // The total number of records.
  
  
Int intPageCount; // the total number of pages.
  
  
Int intPage; // the page number to be displayed.
  
  
Java. lang. String strPage;
  
  
Int I, j, k; // set the number of records displayed on one page
  
  
IntPageSize=5; // Obtain the page number to be displayed
  
  
StrPage=Request. GetParameter ("page ");
  
  
If (StrPage= Null ){
  
  
// Indicates that the QueryString parameter does not contain the page parameter. The first page of data is displayed.
  
  
IntPage=1;
  
  
} Else {
  
  
// Converts a string to an integer.
  
  
IntPage=Java. Lang. Integer. parseInt (strPage );
  
  
If (intPage< 1)IntPage=1;}
  
  
// Load the JDBC-ODBC driver
  
  
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
  
  
// Set the database connection string
  
  
StrCon="Jdbc: odbc: Test_DB";
  
  
// Connect to the database
  
  
SqlCon=Java. SQL. DriverManager. getConnection (strCon, "sa ","");
  
  
// Create an SQL statement object
  
  
SqlStmt=SqlCon. CreateStatement ();
  
  
// Obtain the total number of records
  
  
StrSQL="Select count (*) from guestbook";
  
  
SqlRst=SqlStmt. ExecuteQuery (strSQL );
  
  
// Execute the SQL statement and obtain the result set
  
  
SqlRst. next (); // when the record set is opened, the pointer is located before the first record.
  
  
IntRowCount=SqlRst. GetInt (1 );
  
  
SqlRst. close (); // close the result set.
  
  
// Calculate the total number of pages
  
  
IntPageCount= (IntRowCount + intPageSize-1)/intPageSize;
  
  
// Adjust the page number to be displayed if (intPage>IntPageCount)IntPage=IntPageCount;
  
  
// Set the SQL statement for getting data
  
  
StrSQL="Select name, email, body from guestbook";
  
  
// Execute the SQL statement and obtain the result set
  
  
SqlRst=SqlStmt. ExecuteQuery (strSQL );
  
  
// Locate the record pointer to the first record on the page to be displayed.
  
  
I= (IntPage-1) * intPageSize;
  
  
For (J=0; J< I; J ++) sqlRst. next (); %> 
  
  
< Html> 
  
  
< Head> 
  
  
< Title>JSP database operation routine-data paging display-JDBC-ODBC</Title> 
  
  
</Head> 
  
  
< 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>Email:<% = SqlRst. getString (2) %></Td> 
  
  
</Tr> 
  
  
< Tr> 
  
  
< Td Colspan=2><% = SqlRst. getString (3) %></Td> 
  
  
</Tr> 
  
  
<% I ++;} %> 
  
  
< Tr> 
  
  
< Td Colspan=2 Align=Center> 
  
  
The<% = IntPage %>Total pages<% = IntPageCount %>Page
  
  
<% If (intPage< IntPageCount) {%> 
  
  
< A Href="Mssql. jsp? Page = <% = intPage + 1%>">Next Page</><%
  
  
}
  
  
%> 
  
  
<% If (intPage>1) {%> 
  
  
< A Href="Mssql. jsp? Page = <% = intPage-1 %>">Previous Page</><%
  
  
}
  
  
%> 
  
  
</Td> 
  
  
</Tr> 
  
  
</Table> </Body> 
  
  
</Html> 
  
  
<%
  
  
// Close the result set
  
  
SqlRst. close ();
  
  
// Close the SQL statement object
  
  
SqlStmt. close ();
  
  
// Close the database
  
  
SqlCon. close ();
  
  
%> 
 
 

How to run JSP database operations?

Save the code as a file test. jsp

Under Orion Application Server:

Copy to the default-web-app directory of orion, and run the following command:

 
 

  
  
http://localhost:port/test.jsp 
 
 

Access Test

You can run Resin, Tomcat, JWS, and so on. This completes the JSP database operation.

1. Basic preparation for learning JSP: JDK installation and configuration
2. Java Servlet Introduction: Perfect Combination with JSP
3. Integrate JSP and PHP in Apache
4. Java Servlets (JSP) Development Environment
5. Develop jsp http Server