Home | | Internet & World Wide Web HOW TO PROGRAM | | Internet Programming | | Web Programming | Multitier Applications: Using JDBC from a Servlet

Chapter: Internet & World Wide Web HOW TO PROGRAM - Servlets: Bonus for Java™ Developers

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail

Multitier Applications: Using JDBC from a Servlet

Servlets can communicate with databases via JDBC (Java Database Connectivity), which provides a uniform way for a Java program to connect with a variety of databases in a general manner without having to deal with the specifics of those database systems.

Multi-tier Applications: Using JDBC from a Servlet


Servlets can communicate with databases via JDBC (Java Database Connectivity), which provides a uniform way for a Java program to connect with a variety of databases in a general manner without having to deal with the specifics of those database systems.


Many of today’s applications are three-tier distributed applications, consisting of a user interface, business logic and database access. The user interface in such an appli-cation is often created using HTML, XHTML (as shown in this chapter) or Dynamic HTML. In some cases, Java applets are also used for this tier. HTML and XHTML are the preferred mechanisms for representing the user interface in systems where portability is a concern. Because HTML is supported by all browsers, designing the user interface to be accessed through a Web browser guarantees portability across all platforms that have browsers. Using the networking provided automatically 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 com-puters that are connected to a network.

In multi-tier architectures, Web servers often represent the middle tier. They provide the business logic that manipulates data from databases and that communicates with client Web browsers. Servlets, through JDBC, can interact with popular database sys-tems. Developers do not need to be familiar with the specifics of each database system. Rather, developers use SQL-based queries and the JDBC driver handles the specifics of interacting with each database system.


The SurveyServlet of Fig. 30.28 and the Survey.html document of Fig. 30.29 demonstrate a three-tier distributed application that displays the user interface in a browser using XHTML. The middle tier is a Java servlet that handles requests from the client browser and provides access to the third tier—a Cloudscape database accessed via JDBC. The servlet in this example is a survey servlet that allows users to vote for their favorite animal. When the servlet receives a post request from the Survey.html doc-ument, the servlet updates 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.

    // Fig. 9.27: SurveyServlet.java

    // A Web-based survey that uses JDBC from a servlet.

    package com.deitel.advjhtp1.servlets;


    import java.io.*;

    import java.text.*;

    import java.sql.*;

    import javax.servlet.*;

    import javax.servlet.http.*;


      public class SurveyServlet extends HttpServlet {

      private Connection connection;

      private PreparedStatement updateVotes, totalVotes, results;


      // set up database connection and prepare SQL statements

      public void init( ServletConfig config )

      throws ServletException


      // attempt database connection and create PreparedStatements

try {

Class.forName( "COM.cloudscape.core.RmiJdbcDriver" );

connection = DriverManager.getConnection(

"jdbc:rmi:jdbc:cloudscape:animalsurvey" );


// PreparedStatement to add one to vote total for a

// specific animal

updateVotes =


"UPDATE surveyresults SET votes = votes + 1 " +

"WHERE id = ?"



// PreparedStatement to sum the votes

totalVotes =


"SELECT sum( votes ) FROM surveyresults"



// PreparedStatement to obtain surveyoption table's data

results =


"SELECT surveyoption, votes, id " +

"FROM surveyresults ORDER BY id"


// for any exception throw an UnavailableException to

      // indicate that the servlet is not currently available




      catch ( Exception exception ) {


      throw new UnavailableException(exception.getMessage());


          }  // end of init method

      // process survey response

      protected void doPost( HttpServletRequest request,

      HttpServletResponse response )

      throws ServletException, IOException


      // set up response to client

      response.setContentType( "text/html" );

      PrintWriter out = response.getWriter();

      DecimalFormat twoDigits = new DecimalFormat( "0.00" );


      // start XHTML document

         out.println( "<?xml version = \"1.0\"?>" );

         out.println( "<!DOCTYPE html PUBLIC \"-//W3C//DTD " +

         "XHTML 1.0 Strict//EN\" \"http://www.w3.org" +

         "/TR/xhtml1/DTD/xhtml1-strict.dtd\">" );



         "<html xmlns = \"http://www.w3.org/1999/xhtml\">" );


                    // head section of document

                    out.println( "<head>" );


                    // read current survey response

                    int value =


         Integer.parseInt( request.getParameter( "animal" ) );


                    // attempt to process a vote and display current results

                    try {

         // update total for current survey response

         updateVotes.setInt( 1, value );



         // get total of all survey responses

         ResultSet totalRS = totalVotes.executeQuery();


         int total = totalRS.getInt( 1 );


         // get results

         ResultSet resultsRS = results.executeQuery();

         out.println( "<title>Thank you!</title>" );

         out.println( "</head>" );

                    out.println( "<body>" );

                    out.println( "<p>Thank you for participating." );

                    out.println( "<br />Results:</p><pre>" );


                    // process results

                    int votes;


         while ( resultsRS.next() ) {

         out.print( resultsRS.getString( 1 ) );

         out.print( ": " );

         votes = resultsRS.getInt( 2 );

         out.print( twoDigits.format(

         ( double ) votes / total * 100 ) );

         out.print( "%  responses: " );

       out.println( votes );




       out.print( "Total responses: " );

       out.print( total );


       // end XHTML document

       out.println( "</pre></body></html>" );




       // if database exception occurs, return error page

       catch ( SQLException sqlException ) {


       out.println( "<title>Error</title>" );

       out.println( "</head>" );

       out.println( "<body><p>Database error occurred. " );

       out.println( "Try again later.</p></body></html>" );



          }  // end of doPost method

       // close SQL statements and database when servlet terminates

       public void destroy()


       // attempt to close statements and database connection

       try {






       // handle database exceptions by returning error to client

       catch( SQLException sqlException ) {



       }  // end of destroy method



 Fig. 30.28  Multi-tier Web-based survey using XHTML, servlets and JDBC



Lines 12 and 13 begin by declaring a Connection reference to manage the database connection and three PreparedStatement references. The PreparedStatements will be used to update the vote count for an animal, to total all the votes and to obtain the complete survey results.


Servlets are initialized by overriding method init (lines 16–54). Method init is called exactly once in a servlet’s lifetime, before any client requests are accepted. Method init takes a ServletConfig argument and throws a ServletException. The argu-ment provides the servlet with information about its initialization parameters (i.e., parameters not associated with a request, but passed to the servlet for initializing servlet variables). 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:




<param-name>parameter name goes here</param-name> <param-value>parameter value goes here</param-value>



Servlets can obtain initialization parameter values by invoking ServletConfig method getInitParameter, which receives a string representing the name of the parameter.


In this example, the servlet’s init method (lines 16–54) performs the connection to the Cloudscape database. Line 21 loads the driver (COM.cloudscape.core.Rmi-JdbcDriver). Lines 22–23 attempt to open a connection to the animalsurvey database. The database contains one table (surveyresults) that consists of three fields—a unique integer to identify each record called id, a string representing the survey option called sur-veyoption and an integer representing the number of votes for a survey option called votes. See Section 30.8.1 for instructions on creating the animalsurvey database, exe-cuting the Cloudscape server and configuring this example to execute in Tomcat.

Lines 27–44 create PreparedStatement objects called updateVotestotalVotes and results. The updateVotes statement adds one to the votes value for the record with the specified ID. The totalVotes statement uses SQL’s built-in sum capability to total all the votes in the surveyresults table. The results state-ment returns all the data in the surveyresults table.


When a user submits a survey response, method doPost (lines 57–137) handles the request. Lines 80–81 obtain the survey response, then the try block (lines 84–125) attempts to process the response. Lines 87–88 set the first parameter of Prepared-Statement updateVotes to the survey response and update the database. Lines 91– 93 execute PreparedStatement totalVotes to retrieve the total number of votes received. Then, lines 96–123 execute PreparedStatement results and process the ResultSet to create the survey summary for the client. When the servlet container ter-minates the servlet, method destroy (lines 140–154) closes each PreparedState-ment, then closes the database connection. Figure 30.29 shoes survey.html, which invokes SurveyServlet with the alias animalsurvey when the user submits the form.


    <?xml version = "1.0"?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"



    <!-- Survey.html -->

      <html xmlns = "http://www.w3.org/1999/xhtml">






        <form method = "post" action = "/advjhtp1/animalsurvey">


15      <p>What is your favorite pet?</p>




        <input type = "radio" name = "animal"

        value = "1" />Dog<br />

        <input type = "radio" name = "animal"

        value = "2" />Cat<br />

        <input type = "radio" name = "animal"

        value = "3" />Bird<br />

        <input type = "radio" name = "animal"

        value = "4" />Snake<br />

        <input type = "radio" name = "animal"

        value = "5" checked = "checked" />None


          <p><input type = "submit" value = "Submit" /></p>





Fig. 30.29  Survey.html document that allows users to submit survey responses to SurveyServlet

Configuring animalsurvey Database and



The example in Fig. 30.28 and Fig. 30.29 cannot execute until we create the Cloudscape database animalsurvey and configure the advjhtp1 Web application to recognize SurveyServlet. Before proceeding, ensure that you have installed and configured Cloudscape on your system. If you have not, download Cloudscape from the Web site www.cloudscape.com. Follow the provided instructions to install Cloudscape. Cloud-scape executes on many platforms including Windows, Solaris, Linux, Macintosh and oth-ers. For a complete list of platforms on which Cloudscape 3.6 has been tested, visit

cloudweb1.cloudscape.com/support/servepage.jsp? page=fyi_cert36vms.html


The Cloudscape server must be executing to create and manipulate databases in Cloud-scape. To execute the server, begin by opening a command window (i.e., an MS-DOS prompt, Command Prompt or UNIX/Linux shell). Change directories to the Cloudscape installation directory (Cloudscape_3.6, by default). The installation directory contains a frameworks subdirectory. Cloudscape comes with two frameworks in which it can execute—embedded and RmiJdbc. The embedded framework enables Cloudscape to execute as part of a Java application. The RmiJdbc framework enables Cloudscape to exe-cute as a standalone database server. We use the standalone database server in this book. Each framework directory has a bin subdirectory containing batch files (Windows) and shell scripts (Linux/UNIX) to set environment variables and execute Cloudscape. Change directories to the bin directory in the RmiJdbc framework. Execute the batch file or shell script starting with the name setServerCloudscapeCP to set the environment vari-ables required by the server. Then, execute batch file or shell script starting with the name startCS to launch the Cloudscape database server.


The examples for this chapter include a SQL script (animalsurvey.sql) that cre-ates the database and its tables. Cloudscape provides an interactive command-line tool called ij that can execute this script. We provide a Windows batch file called createDatabase.bat that you can use to start ij and execute the SQL scripts. Both animalsurvey.sql and createDatabase.bat are included in the examples directory for this chapter on the CD that accompanies this book. To create database ani-malsurvey, ensure that the Cloudscape server is executing. Next, open a new command prompt and change directories to the RmiJdbc framework’s bin directory in the Cloud-scape installation directory. Then, execute the batch file setClientCloud-scapeCP.bat. In that command prompt, change to the directory containing our examples for this chapter and type


createDatabase animalsurvey.sql


to execute the SQL script. After completing this task, the animalsurvey database is ready for use in SurveyServlet. Next, we configure the advjhtp1 Web application.


We use our advjhtp1 context root to demonstrate the servlet of Fig. 30.28. Place Survey.html in the servlets directory created previously. Place Survey-Servlet.class in the classes subdirectory of WEB-INF in the advjhtp1 context root. Then, edit the web.xml deployment descriptor in the WEB-INF directory to include the information specified in Fig. 30.30. Also, this program cannot execute in Tomcat unless the Web application is aware of the JAR files cloudscape.jar and RmiJdbc.jar that contain the Cloudscape database driver and its supporting classes. A copy of these files should be placed in the advjhtp1 context root’s WEB-INF subdirectory called lib. The file cloudscape.jar is located in the Cloudscape installation’s lib subdirectory. The file RmiJdbc.jar is located in the RmiJdbc framework’s classes subdirectory. After copying these files, restart Tomcat and type the following URL in your Web browser:



When the Web page appears, select a survey response and press the Submit button in the Web page to invoke the servlet.

HttpUtils Class


Class HttpUtils provides three static utility methods to simplify servlet program-ming. These methods are discussed in Fig. 30.31.

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail

Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.