Use JSP scriptlets to create the database Connection and Statement objects for the Road Sign Review application that you started developing in Tutorial 30. The roadsigns database has the table signs, which contains three columns—id, name and sign. The id field is a unique number to identify a road sign. The name field is a string that specifies the name of the road sign. The sign field is a string that specifies an image file name of the road sign, such as "sign01.png".
a) Opening roadSigns.jsp. Open the roadSigns.jsp file that you created.
b) Importing classes from java.sql for use in the JSP. Copy lines 4–5 and insert them in line 4 of roadSigns.jsp.
c) Starting a JSP scriptlet. In the roadSigns.jsp source code, after the h1 HTML ele- ment, start a JSP scriptlet. In the scriptlet, start a try block.
d) Adding a Connection to the database. In the try block, specify the database loca- tion with value "C:\\Examples\\Tutorial31\\Exercises\\Databases", load the database driver class and connect to the roadsigns database (jdbc:db2j:road- signs). [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.]
) Creating a Statement and executing a query that retrieves road sign information from the database. 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 road signs from the database.
f) Ending the scriptlet. In the roadSigns.jsp source code, end the scriptlet started in
Step c so that literal HTML markup can be placed in the response to the client.
g) Adding a second JSP scriptlet. In the roadSigns.jsp source code, after the img HTML element that will display the last road sign image retrieved from the database, add a second JSP scriptlet that will close the database connection, end the try block and display the SQLException error message if an exception occurs.
h) Saving the file. Save your modified file roadsigns.jsp.
i) Copying the roadSigns.jsp file to the Tomcat webapps directory. Copy your updated roadSigns.jsp file and paste it into the C:\Program Files\ Apache Group\Tomcat 4.1\webapps\RoadSign directory.
j) Copying the database JAR files to the Road Signs Web application. The informa- tion tier for the Road Signs 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\RoadSign\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.
k) Starting Tomcat. Select Start > Programs > Apache Tomcat 4.1 > Start Tomcat to start the Tomcat server.
l) Testing the application. Open a Web browser and enter the URLs http://
localhost:8080/RoadSign/roadSigns.jsp to test the application.
m)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
Road Signs
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
29 // load Cloudscape driver
Class.forName( "com.ibm.db2j.jdbc.DB2jDriver" );
30
31
32
33 // connect to database
Connection connection = DriverManager.getConnection( "jdbc:db2j:roadsigns" );
34
35
36 // obtain road sign information
if ( connection != null )
37 {
38
39 // create statement
Statement statement = connection.createStatement();
40
41
42 // execute query to get road sign information
ResultSet results = statement.executeQuery(
43 "SELECT * FROM signs" );
44
45 %> <%-- end scriptlet to insert HTML --%>
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
<% // continue scriptlet
connection.close(); // close database connection
} // end if
} // end try
// catch SQLException
catch( SQLException exception )
{
out.println( "Exception: " + exception + " occurred." );
}
%> <%-- end scriptlet --%>
108
109
125
126
```
```
89
90
91
92 <% // continue scriptlet
93
94 connection.close(); // close database connection
95
96 } // end if
97
98 } // end try
99
100 // catch SQLException
101 catch( SQLException exception )
102 {
103 out.println( "Exception: " + exception + " occurred." );
104 }
105
106 %> <%-- end scriptlet --%>
107
108
109
125