Multitier Applications: Using JDBC from a Servlet

Multitier Applications Using JDBC from a Servlet

Servlets can communicate with databases via JDBC (Chapter 25). Many of today's applications are three-tier distributed applications, consisting of a user interface, business logic and a database. The user interface in such an application is often created using HTML or XHTML (as shown in this chapter). HTML and XHTML are preferred in systems where portability is a concern. Using the networking provided by the browser, the user interface can communicate with the middle-tier business logic. The middle tier can then access the database to manipulate the data. The three tiers can reside on separate computers that are connected to a network.

In multitier architectures, Web servers often are used in the middle tier. Server-side components, such as servlets, execute in an application server alongside the Web server. These components provide the business logic that manipulates data from databases and communicates with client Web browsers. Servlets, through JDBC, can interact with popular database systems. Developers use SQL for queries, and JDBC drivers handle the specifics of interacting with each database system.

The SurveyServlet in Fig. 26.21 and the Survey.html document in Fig. 26.22 implement portions of a three-tier distributed application. The middle tier is Survey-Servlet, which handles requests from the client browser and provides access to the third tiera MySQL database accessed via JDBC. The servlet in this example allows users to vote for their favorite animals. When the servlet receives a post request from the Web browser, the servlet uses JDBC to update the total number of votes for that animal in the database and returns a dynamically generated XHTML document containing the survey results to the client.

Figure 26.21. Multitier Web-based survey using XHTML, servlets and JDBC.

(This item is displayed on pages 1266 - 1268 in the print version)

""Thank you!Error

Database error occurred. " ); 129 out.println( "Try again later.

 1 // Fig. 26.21: SurveyServlet.java
 2 // A Web-based survey that uses JDBC from a servlet.
 3 package com.deitel.jhtp6.servlets;
 4
 5 import java.io.PrintWriter;
 6 import java.io.IOException;
 7 import java.sql.Connection; 
 8 import java.sql.DriverManager;
 9 import java.sql.Statement; 
10 import java.sql.ResultSet; 
11 import java.sql.SQLException; 
12 import javax.servlet.ServletConfig;
13 import javax.servlet.ServletException;
14 import javax.servlet.UnavailableException;
15 import javax.servlet.http.HttpServlet;
16 import javax.servlet.http.HttpServletRequest;
17 import javax.servlet.http.HttpServletResponse;
18 
19 public class SurveyServlet extends HttpServlet
20 {
21 private Connection connection;
22 private Statement statement; 
23 
24 // set up database connection and create SQL statement
25 public void init( ServletConfig config ) throws ServletException
26 {
27 // attempt database connection and create Statements
28 try
29 {
30 Class.forName( config.getInitParameter( "databaseDriver" ) );
31 connection = DriverManager.getConnection( 
32  config.getInitParameter( "databaseName" ) ); 
33  config.getInitParameter( "username" ), 
34  config.getInitParameter( "password" ) ); 
35 
36 // create Statement to query database
37 statement = connection.createStatement();
38 } // end try
39 // for any exception throw an UnavailableException to
40 // indicate that the servlet is not currently available
41 catch ( Exception exception )
42 {
43 exception.printStackTrace();
44 throw new UnavailableException(exception.getMessage());
45 } // end catch
46 } // end method init
47 
48 // process survey response
49 protected void doPost( HttpServletRequest request,
50 HttpServletResponse response )
51 throws ServletException, IOException
52 {
53 // set up response to client
54 response.setContentType( "text/html" );
55 PrintWriter out = response.getWriter();
56 
57 // start XHTML document
58 out.println( "" );
59 
60 out.printf( "%s%s%s" , " ,
61 " "-//W3C//DTD XHTML 1.0 Strict//EN"",
62 " "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
" );
63 
64 out.println(
65 "

); 66 67 // head section of document 68 out.println( "

) ; 69 70 // read current survey response 71 int value = 72 Integer.parseInt( request.getParameter( "animal" ) ); 73 String sql; 74 75 // attempt to process a vote and display current results 76 try 77 { 78 // update total for current survey response 79 sql = "UPDATE surveyresults SET votes = votes + 1 " + 80 "WHERE id = " + value; 81 statement.executeUpdate( sql ); 82 83 // get total of all survey responses 84 sql = "SELECT sum( votes ) FROM surveyresults" ; 85 ResultSet totalRS = statement.executeQuery( sql ); 86 totalRS.next(); // position to first record 87 int total = totalRS.getInt( 1 ); 88 89 // get results 90 sql = "SELECT surveyoption, votes, id FROM surveyresults " + 91 "ORDER BY id"; 92 ResultSet resultsRS = statement.executeQuery( sql ); 93 out.println( "

" ) ; 94 out.println( "" ); 95 96 out.println( "

); 97 out.println( "

Thank you for participating." ); 98 out.println( "
Results:

" );
99 
100 // process results
101 int votes;
102 
103 while ( resultsRS.next() )
104 {
105 out.print( resultsRS.getString( 1 ) );
106 out.print( ": " );
107 votes = resultsRS.getInt( 2 );
108 out.printf( "%.2f", ( double ) votes / total * 100 );
109 out.print( "% responses: " );
110 out.println( votes );
111 } // end while
112 
113 resultsRS.close();
114 
115 out.print( "Total responses: " );
116 out.print( total );
117 
118 // end XHTML document
119 out.println( "

" ); 120 out.close(); 121 } // end try 122 // if database exception occurs, return error page 123 catch ( SQLException sqlException ) 124 { 125 sqlException.printStackTrace(); 126 out.println( "

" ); 127 out.println( "" ); 128 out.println( "

" ); 130 out.close(); 131 } // end catch 132 } // end method doPost 133 134 // close SQL statements and database when servlet terminates 135 public void destroy() 136 { 137 // attempt to close statements and database connection 138 try 139 { 140 statement.close(); 141 connection.close(); 142 } // end try 143 // handle database exceptions by returning error to client 144 catch ( SQLException sqlException ) 145 { 146 sqlException.printStackTrace(); 147 } // end catch 148 } // end method destroy 149 } // end class SurveyServlet

Figure 26.22. Survey.html document that allows users to submit survey responses to SurveyServlet.

(This item is displayed on pages 1270 - 1271 in the print version)

"http://www.w3.org/1999/xhtml"> 8 9

 1  "1.0" ?>
 2  "-//W3C//DTD XHTML 1.0 Strict//EN"
 3 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
 4
 5 
 6
 7 

Survey 10 11 12

13 "post" action = "/jhtp6/animalsurvey"> 14

What is your favorite pet?

15

16 "radio" name = "animal" 17 value = "1" />Dog
18 "radio" name = "animal" 19 value = "2" />Cat
20 "radio" name = "animal" 21 value = "3" />Bird
22 "radio" name = "animal" 23 value = "4" />Snake
24 "radio" name = "animal" 25 value = "5" checked = "checked" /> None 26

27

"submit" value = "Submit" />

28 29 30

Lines 21 and 22 begin by declaring a Connection to manage the database connection and a Statement for updating the vote count for an animal, totaling all the votes and obtaining the complete survey results.

Servlets are initialized by method init, which we override in SurveyServlet (lines 2546). Method init is called exactly once in a servlet's life cycle, before any client requests are accepted. The method takes ServletConfig argument and throws a Servlet-Exception. The argument provides the servlet with information about its initialization parameters (i.e., parameters not associated with a request, but passed to the servlet for initializing the servlet). These parameters are specified in the web.xml deployment descriptor file as part of a servlet element. Each parameter appears in an init-param element of the following form:

 
 parameter name
 parameter value 
 

 

Servlets can obtain initialization parameter values by invoking ServletConfig method getInitParameter, which receives a string representing the param-name of the parameter and returns the param-value as a string.

In this example, the servlet's init method (lines 2546) performs the connection to the MySQL database. Line 30 loads the driver (com.mysql.jdbc.Driver, which is specified in the initialization parameter "databaseDriver"). Lines 3134 attempt to open a connection to the animalsurvey database. The database name, username and password are specified in the initialization parameters "databaseName", "username" and "password", respectively. The database contains one table (surveyresults) that consists of three fieldsa unique integer to identify each record (id), a string representing the survey option (surveyoption) and an integer representing the number of votes for a survey option (votes). [Note: The examples folder for this chapter contains the SQL script animalsurvey.sql with which you can create the animalsurvey database for this example. For information on executing the SQL script, please refer to Chapter 25.]

When a user submits a survey response, method doPost (lines 49132) handles the request. Lines 7172 obtain the survey response, then lines 76121 attempt to process it. Lines 7980 specify an update statement to increment the votes value for the record with the specified ID and update the database. Lines 8587 execute the query specified in line 84 to retrieve the total number of votes received using SQL's built-in sum function to total all the votes in the surveyresults table. Then lines 92120 execute the query specified in lines 9091 to obtain the data in the table and process the ResultSet to create the survey summary for the client. When the servlet container terminates the servlet, method destroy (lines 135148) closes the Statement, then closes the database connection. Figure 26.22 shows survey.html, which invokes SurveyServlet through alias animalsurvey when the user submits the form.

We use our jhtp6 context root to demonstrate the servlet of Fig. 26.21. Place Survey.html in the servlets directory created previously. Place SurveyServlet.class (with the complete package structure) in the classes subdirectory of WEB-INF in the jhtp6 context root. Then, edit the web.xml deployment descriptor in the WEB-INF directory to include the information specified in Fig. 26.23. This program cannot execute in Tomcat unless the Web application has access to the JAR file that contains the MySQL database driver and its supporting classes. This JAR file (mysql-connector-java-3.0.14-production-bin.jar) can be found in your MySQL Connector's installation directory. Place a copy of this file in the WEB-INF subdirectory lib to make its contents available to the Web application. Please refer to Chapter 25 for more information on how to configure MySQL.

Figure 26.23. Deployment descriptor information for servlet SurveyServlet.

(This item is displayed on page 1272 in the print version)

Descriptor element	Value
servlet element
servlet-name	animalsurvey
description	Connecting to a database from a servlet.
servlet-class	com.deitel.jhtp6.servlets.SurveyServlet
init-param
param-name	databaseDriver
param-value	com.mysql.jdbc.Driver
init-param
param-name	databaseName
param-value	jdbc:mysql://localhost/animalsurvey
init-param
param-name	username
param-value	jhtp6
init-param
param-name	password
param-value	jhtp6
servlet-mapping element
servlet-name	animalsurvey
url-pattern	/animalsurvey

 

After copying these files, type the following URL in your Web browser:

 http://localhost:8080/jhtp6/servlets/Survey.html

 

Select an animal and press the Submit button to invoke the servlet. [ Note: The MySQL database server should be running when the servlet is invoked.]