Java程序辅导

CS381 - Lab Session 5 
 
 
 
A. Using JSP to retrieve data from a database 
 
1. Using “nedit” or “notepad” or your tool of choice, create a new JSP file, and 
add the required HTML and JSP elements (you could use an old JSP to get the 
required elements). 
2. At the top of the page include the necessary Java classes related to databases 
and SQL (i.e. <%@page import = "java.sql.*" %>). 
3. Within the same JSP page, follow the basic steps to connect and retrieve data 
from a database (use handouts for extra help): 
a. Load a driver which is compatible with the database (MySQL in this 
case) that is to be processed (“org.gjt.mm.mysql.Driver”.1). 
b. Establish a connection to the database (URL: 
“jdbc:mysql://mysql0.ee.surrey.ac.uk:3306/webtech”, 
username: “webtech”, password: “webtech”). 
c. Do not forget the try … catch statements. 
d. Associate an SQL statement with this connection (“select * from 
links;”). For the webtech database that you connect to, there is a table 
called links that contains several columns (id, title, category, url, 
description, creation_date) with data.  
e. Execute the SQL statement. 
f. The SQL statement that has been executed will produce a ResultSet 
object. Use this object to capture the results into an HTML table-
formatted string. 
g. Make sure that after the processing (associated with the database) is 
completed the database connection is then closed. 
4. Print the table-formatted string generated from the database as an HTML 
table. 
5. Save as dbRetrieve.jsp within the appropriate directory, (i.e. 
tomcat/webapps/webtech) 
6. Open the page in your browser (e.g. 
http://workstation01.ee.surrey.ac.uk:49999/webtech/dbRetrieve.jsp) 
 
 
 
                                               
1
 NOTE: MySQL database driver (mysql-connector-java-3.1.7-bin.jar) has to be downloaded from 
http://dev.mysql.com/downloads/connector/j/3.1.html. Unzip if necessary and place the JAR file in 
your WEB-INF/lib area (if the “lib” directory does not exist… create it). 
B. Moving the functionality into a JavaBean component 
 
1. Open the dbRetrieve.jsp and move all the functionality (all Java code that is 
used to connect to the database) into a new JavaBean. 
2. Create a function in the JavaBean that will return the data into a table-
formatted string as above (Exercise A). 
3. Place it into the WEB-INF/classes/webtech directory (remember to use the 
package webtech within the JavaBean). 
4. Save the JavaBean as dbBean.java and compile it. 
5. Include the JavaBean element into the dbRetrieve.jsp, and remember to call 
the function within the JavaBean that will return the data to be displayed on 
the page. 
6. Restart Tomcat. 
7. Open the page in your browser (e.g. 
http://workstation01.ee.surrey.ac.uk:49999/webtech/dbRetrieve.jsp). 
 
 
 
C. For extra challenge, try to modify the JavaBean (dbBean.java) so that each title of 
each record returned has an anchor tag associated with it, which links to its 
representative url retrieved from the database.