Use JSP scriptlets to create the database Connection and Statement objects for the US State Facts application. The statefacts database has the table states, which contains seven columns—id, name, flag, capital, flower, tree and bird. The id field is a unique number to identify a state. The name field is a string that specifies the state name. The flag field is a string that specifies an image file name of the state’s flag, such as "flag1.png". The capital field is a string that indicates the state capital. The flower field is a string that indicates the state flower. The tree field is a string that indicates the state tree. The bird field is a string that indicates the state bird.

a) Opening states.jsp. Open the states.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 states.jsp.
c) Adding a JSP scriptlet. Inside the select HTML element of the states.jsp source file, add a JSP scriptlet that contains a try block and a catch block that catches a SQLException.
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 statefacts database (jdbc:db2j:statefacts). [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 object and executing the query to retrieve state names in 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 state 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, display the SQLException error message using the println method of the implicit JSP object out.
h) Saving the file. Save your modified states.jsp file.
i) Opening stateFacts.jsp. Open the stateFacts.jsp that you created in
Exercise 30.12.
j) Importing classes from java.sql for use in the JSP. Copy lines 4–5 and insert them in line 4 of stateFacts.jsp.
k) Starting a JSP scriptlet. In the stateFacts.jsp source code, after the h1 HTML
element, start a JSP scriptlet. In the scriptlet, start a try block.
l) Adding a Connection to the database. In the scriptlet of the stateFacts.jsp, spec- ify the database location with value "C:\\Examples\\Tutorial31\\Exer- cises\\Databases", load the database driver class and connect to the statefacts database. [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.]
m)Creating a Statement and executing the query to retrieve state information in 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 the infor-
mation for the state name selected by the user.
n) Ending the scriptlet. End the scriptlet 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 stateFacts.jsp source code, after the para- graph HTML element that will display the bird name 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.
p) Saving the file. Save your modified file stateFacts.jsp.
q) Copying the states.jsp and stateFacts.jsp files to the Tomcat webapps direc- tory. Copy your updated states.jsp and stateFacts.jsp files and paste them into the C:\Program Files\Apache Group\Tomcat 4.1\webapps\StateFacts directory.
r) Copying the database JAR files to the State Facts Web application. The informa- tion tier for the State Facts 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\StateFacts\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/StateFacts/states.jsp and http://localhost:8080/State- Facts/stateFacts.jsp to test your 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

States

18
19
20

21
22

Select a state from the list and click the
23 button to view facts about that state

24
25
26
27
28 <%-- begin JSP scriptlet to connect to database --%>
29 <%
30 // setup database connection
31 try
32 {
33 // specify database location
34 System.setProperty( "db2j.system.home",
35 "C:\\Examples\\Tutorial31\\" +
36 "Exercises\\Databases" );
37
38 // load Cloudscape driver
39 Class.forName( "com.ibm.db2j.jdbc.DB2jDriver" );
40
41 // connect to database
42 Connection connection =
43 DriverManager.getConnection(
44 "jdbc:db2j:statefacts" );
45
46 // obtain list of state names
47 if ( connection != null )
48 {
49 Statement statement =
50 connection.createStatement();
51
52 ResultSet results = statement.executeQuery(
53 "SELECT name FROM states" );
54
55 // close database connection
56 connection.close();
57
58 } // end if
59
60 } // end try
61
62 // catch SQLException
63 catch( SQLException exception )
64
65 out.println(
66 "Exception: " + exception + " occurred." );
67
68
69 %> <%-- end scriptlet --%>
70
71
72
73
74

75
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

State Name

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 // connect to database
32 Connection connection = DriverManager.getConnection(
33 "jdbc:db2j:statefacts" );
34
35 // obtain state information
36 if ( connection != null )
37 {
38 // create statement
39 Statement statement = connection.createStatement();
40
41 // execute query to get state information
42 ResultSet results = statement.executeQuery(
43 "SELECT * FROM states WHERE name = '" +
44 // execute query to get state information
45
46 %> <%-- end scriptlet to insert HTML --%>
47
48
49
50
51
52

Capital:

53
54
55

Flower:

56
57
58

Tree:

59
60
61

Bird:

62
63 <% // continue scriptlet
64
65 connection.close(); // close database connection
66
67 } // end if
68
69 } // end try
70
71 // catch SQLException
72 catch( SQLException exception )
73 {
74 out.println( "Exception: " + exception + " occurred." );
75 }
76
77 %> <%-- end scriptlet --%>
78
79
80

State List

81
82
83
```