// File: SurveyServlet.java
// Example that either creates or accesses database to store survey results
// Note usage of Exceptions to determine whether to create or access table
// Prepared by Dr. Spiegel from example found at URL:
// http://www.alnaja7.org/Programmer/393/servlet/Multi-tier_Applications_Using_JDBC_from_a_Servlet.htm
// and updated to create database if not found
import java.io.*;
import java.text.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class SurveyServlet extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
private Connection connection;
private PreparedStatement updateVotes, totalVotes, results;
/*
private static final String HOST = "csit.kutztown.edu";
private static final String DATABASE_NAME = "orcl";
private static final int DATABASE_PORT = 1521;
*/
private static final String USERNAME = "spiegel";
private static final String PASSWORD = "legeips";
private static final String petNames[]={"","dog","cat","bird","snake","none"};
private static boolean createdTable;
public void init(ServletConfig config) throws ServletException {
try {
try { // Get a connection
connection = new OracleConnection().
// getConnection(HOST,DATABASE_NAME,DATABASE_PORT,USERNAME,PASSWORD);
getConnection(USERNAME,PASSWORD);
}
catch (Exception e) {
e.printStackTrace();
}
// 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");
}
catch (Exception exception) {
exception.printStackTrace();
throw new UnavailableException(exception.getMessage());
}
createdTable=false;
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType( "text/html" );
PrintWriter out = response.getWriter();
DecimalFormat twoDigits = new DecimalFormat( "0.00" );
// start XHTML document
out.println( "" );
out.println( "" );
out.println("" );
// head section of document
out.println( "" );
// 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( "Thank you!" );
out.println( "" );
out.println( "" );
out.println( "Thank you for participating." );
out.println( "
Results:
" );
// 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( "" );
out.close();
}
// if database exception occurs, return error page
catch ( SQLException sqlException ) {
if (!createdTable && sqlException.getErrorCode()==942) {
// Table does not exist. Must create it!
createdTable=true;
try {
Statement statement=connection.createStatement();
String createString=
"CREATE TABLE surveyresults (id INT, surveyoption VARCHAR(14),votes INT)";
statement.executeUpdate(createString);
for (int idx=1;idx<=5;idx++)
statement.executeUpdate("INSERT INTO surveyresults VALUES("+idx+
",'"+petNames[idx]+"',0)");
doPost(request,response);
}
catch (SQLException s) {
s.printStackTrace();
out.println( "Error" );
out.println( "" );
out.println( "Database error occurred. Error Code:"+
sqlException.getErrorCode());
out.println( " Try again later.