Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: neutral (herse.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=swbell.net;
  h=Received:X-YMail-OSG:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:Subject:Content-Type:Content-Transfer-Encoding;
  b=RQhRB67qxFHe8oNQ48EATBnbkoLT/G7JRFb8OY0WE1xCAJ3PvDY7QroJ7D6FaNgPR8jJ+Bv0KlQ9APWzmIdznIqty2YFMj0jyeDGfWlZ/7iBeY7evLElmVLHFlXiOWKr/ohKynw9EXiN7k3YVQZuvJv0B2Y7I5xyKbXU7QiAPOY=
  ;
Message-ID: <45BD1A42.5030903@swbell.net>
Date: Sun, 28 Jan 2007 15:48:50 -0600
From: "Mr. Steve Burrus" <burrus1@swbell.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
MIME-Version: 1.0
To: users@tomcat.apache.org
Subject: Need Help w. Database Servlet.
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

hi all. I find myself in need of help/assistance with a longtime problem 
of mine and that is how do you get a servlet working with a backend 
database server?? Here is the basic SurveyServlet.java servlet and 
please notice that the database server in question is IBM's Cloudscape . 
after the servlet I have the html file which the servlet is supposed to 
work with. thanx in advance for anyone's help.


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 =
            connection.prepareStatement(
               "UPDATE surveyresults SET votes = votes + 1 " +
               "WHERE id = ?"
            );

         // PreparedStatement to sum the votes
         totalVotes =
            connection.prepareStatement(
               "SELECT sum( votes ) FROM surveyresults"
            );

         // PreparedStatement to obtain surveyoption table's data
         results =
            connection.prepareStatement(
               "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 ) {
         exception.printStackTrace();
         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\">" );

      out.println(
         "<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 );
         updateVotes.executeUpdate();

         // get total of all survey responses
         ResultSet totalRS = totalVotes.executeQuery();
         totalRS.next();
         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 );
         }

         resultsRS.close();
         
         out.print( "Total responses: " );
         out.print( total );
         
         // end XHTML document
         out.println( "</pre></body></html>" );         
         out.close();
      }

      // if database exception occurs, return error page
      catch ( SQLException sqlException ) {
         sqlException.printStackTrace();
         out.println( "<title>Error</title>" );
         out.println( "</head>" );  
         out.println( "<body><p>Database error occurred. " );
         out.println( "Try again later.</p></body></html>" );
         out.close();
      }

   }  // end of doPost method

   // close SQL statements and database when servlet terminates
   public void destroy()
   {
      // attempt to close statements and database connection
      try {
         updateVotes.close();
         totalVotes.close();
         results.close();
         connection.close();
      }

      // handle database exceptions by returning error to client
      catch( SQLException sqlException ) {
         sqlException.printStackTrace();
      }
   }  // end of destroy method
}

 the html file :

<?xml version = "1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<!--  Survey.html -->

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

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

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

   <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>

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

</form>
</body>
</html>


---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org