Use JSP scriptlets to create the database Connection and Statement objects for the Phone Book application. The phonebook database has the table phoneNumbers, which contains three columns—id, name and phoneNumber. The id field is a unique number to identify a person. The name field is a string that specifies the person’s name. The phoneNumber field is a string that indicates the person’s phone number.
a) Opening phoneBook.jsp. Open the phoneBook.jsp file that you created.
b) Importing classes from java.sql for use in the JSP. Copy lines 4–5 of and insert them in line 4 of phoneBook.jsp.
c) Adding a JSP scriptlet. Inside the select HTML element in the phoneBook.jsp source code add a JSP scriptlet that contains a try block and a catch block that catches SQLExceptions thrown from the try block.
d) Adding a Connection to the database. In the try block declared in the previous step, specify the database location with value "C:\\Examples\\Tutorial31\\Exer- cises\\Databases", load the database driver class and connect to the phonebook database (jdbc:db2j:phonebook). [Note: If you copied and pasted the Tutorial31 directory on the CD to a directory other than C:\Examples, then replace C:\\Examples with the directory to where you pasted Tutorial31.]
e) Creating a Statement and executing a query that retrieves names from the database. In the try block, after the database connection is created successfully, create a Statement object that will be used to execute an SQL query. Execute a query that gets all the names from the database.
f) Closing the database connection. After executing the query, invoke the close
method of the Connection object to disconnect from the database.
g) Displaying the SQLException error message. Inside the catch block of the phone- Book.jsp, display the SQLException error message using the println method of the implicit JSP object out.
h) Saving the file. Save your modified file phoneBook.jsp file.
i) Opening phoneNumber.jsp. Open the phoneNumber.jsp file that you created in
Exercise 30.11.
j) Importing classes from java.sql for use in the JSP. Copy lines 4–5 of Fig. 31.22 and insert them in line 4 of phoneNumber.jsp.
k) Starting a JSP scriptlet. In the phoneNumber.jsp source code, after the h1 HTML
element, start a JSP scriptlet. Inside the scriptlet, start a try block.
l) Adding a Connection to the database. In the try block, specify the database location with value "C:\\Examples\\Tutorial31\\Exercises\\Databases", load the database driver class and connect to the phonebook database. [Note: If you copied and pasted the Tutorial31 directory on the CD to a directory other than C:\Exam- ples, then replace C:\\Examples with the directory to where you pasted
Tutorial31.]
m)Creating a Statement object and executing a query that retrieves information from the database. After the database connection is created successfully, create a State- ment object that will be used to execute an SQL query. Execute a query that gets the information of the person whose name the user selected.
n) Ending the scriptlet. Before the p (paragraph) HTML element containing “num- bers,” end the scriptlet you started in Step k so that literal HTML markup can be placed in the response to the client.
o) Adding a second JSP scriptlet. In the phoneNumber.jsp source code, after the para- graph HTML element containing the hyperlink to phoneBook.jsp, add a second JSP scriptlet that will close the database connection, end the try block and display an error message if an exception occurs.
p) Saving the file. Save your modified phoneNumber.jsp file.
q) Copying the phoneBook.jsp and phoneNumber.jsp files to the Tomcat webapps directory. Copy your updated phoneBook.jsp and phoneNumber.jsp files and paste them into the C:\Program Files\Apache Group\Tomcat 4.1\webapps\PhoneBook directory.
r) Copying the database JAR files to the Phone Book Web application. The informa- tion tier for the Phone Book Web application uses the Cloudscape database. You need to copy the db2j.jar and license.jar files to the C:\Program Files\ Apache Group\Tomcat 4.1\webapps\PhoneBook\WEB-INF\lib directory. The db2j.jar and license.jar files are located in the C:\Cloudscape_5.1\lib direc- tory. In Tutorial 26, if you installed Cloudscape in a directory other than C:\Cloudscape_5.1, replace C:\Cloudscape_5.1 with your Cloudscape installa- tion.
s) Starting Tomcat. Select Start > Programs > Apache Tomcat 4.1 > Start Tomcat to start the Tomcat server.
t) Testing the application. Open a Web browser and enter the URLs http:// localhost:8080/PhoneBook/phoneBook.jsp and http://localhost:8080/ PhoneBook/phoneNumber.jsp to test the application.
u) Stopping Tomcat. Select Start > Programs > Apache Tomcat 4.1 > Stop Tomcat to stop the Tomcat server.
```
1
2
3
4 <%-- import java.sql.* for database classes --%>
5 <%@ page import = "java.sql.*" %>
6
7
8
9
10
11
12
13
14
15
16
17
Phone Book Web Application
18
19
20
76
77
78
```
```
1
2
3
4 <%-- import java.sql.* for database classes --%>
5 <%@ page import = "java.sql.*" %>
6
7
8
9
10
11
12
13
14
15
16
17
Phone Number:
18
19 <%-- begin JSP scriptlet to connect to a database --%>
20 <%
21 // setup database connection
22 try
23 {
24 // specify database location
25 System.setProperty( "db2j.system.home",
26 "C:\\Examples\\Tutorial31\\Exercises\\Databases"
27
28 // load Cloudscape driver
29 Class.forName( "com.ibm.db2j.jdbc.DB2jDriver" );
30
31 // obtain connection to database
32 Connection connection = DriverManager.getConnection(
33 "jdbc:db2j:phonebook" );
34
35
36 // obtain information from database
if ( connection != null )
37 {
38 // create statement
39 Statement statement = connection.createStatement();
40
41 // execute query to get all information
42 ResultSet results = statement.executeQuery(
43 "SELECT * FROM phoneNumbers WHERE name = '" +
44 request.getParameter( "personName" ) + "'" );
45
46 %> <%-- end scriptlet to insert HTML --%>
47
48
numbers
49
50
51
52
53 <% // continue scriptlet
54
55 connection.close(); // close database connection
56
57 } // end if
58
59 } // end try
60
61 //
62 catch( SQLException exception )
63 {
64 out.println( "Exception: " + exception + " occurred." );
65 }
66
67 %> <%-- end scriptlet --%>
68
69
70
```
You might also like to view...
Case-Based Critical Thinking QuestionsCase 4-1You have heard about pseudo-class selectors. But because you do not know what they are or how they can be helpful to you, you consult an online tutorial for more information.Which of the following do you learn is the selector for when a user holds down the mouse button to click a link?
A. a:down B. a:active C. a:mousedown D. a:hover
Which of the following is NOT an example of a computer crime?
A. theft of time and service B. entering incorrect data C. pirating software D. online service fraud
KPI is short for:
A. Kelly Pickler Institution B. Kritical Profits Inc. C. Key Performance Indicators D. Key Planning Indicators
Item 1 in the figure above points to the ____________________.
Fill in the blank(s) with the appropriate word(s).