/*
 * @(#)DataAccessServlet.java  1.0.0 97/09/15
 *
 * Copyright (c) 1997 Sheldon Bradley Wosnick. All Rights Reserved.
 * AUTHOR EMAIL: swosnick@ca.ibm.com
 *
 * DISCLAIMER OF WARRANTIES:
 * The following [enclosed] code is sample code created by Sheldon Bradley
 * Wosnick. This sample code is not part of any standard product
 * and is provided to you solely for the purpose of assisting you in the
 * development of your applications.  The code is provided "AS IS",
 * without warranty of any kind.  Sheldon Bradley Wosnick shall not be
 * liable for any damages arising out of your use of the sample code, even
 * if he has been advised of the possibility of such damages.
 *
 * You may study, use, modify, and distribute this example for any purpose.
 *
 * CopyrightVersion 1.0.0
 */

import java.net.URL;
import java.util.*;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
 * This is a demonstration Java servlet which provides a wide range of
 * database access -- SQL query and update -- support via pure JDBC,
 * server-side, to illustrate how to begin construction of a multi-tier
 * (n-tier) distributed architecture that is possible using servlets to
 * communicate with backend (legacy) databases. It shows one of the many
 * potential areas for exploiting servlets, namely to keep the database
 * access on the backend with a better solution than CGI and hide all the
 * programming logic from the client. It is meant to whet the immagination
 * and to spur on further development in this area of server-side Java.
 *
 * <p>A feature of this servlet is that if the servlet throws an SQLException,
 * information about that exception is HTTP-streamed to the browser for
 * debugging purposes so the user can get meaningful feedback on the SQL-JDBC
 * methods that are being executed improperly. For Java exceptions other than
 * SQLException a Java console is required to accept calls to standard err.
 *
 * <p>All JDBC drivers are supported including the JDBC-ODBC Bridge which ships
 * with the JavaSoft JDK (1.1x). The client HTML form has been constructed to
 * allow for ODBC and DB2, but as new PURE JDBC drivers become available by
 * various database vendors, this page (or one functionally like it) can be
 * revised to include those new drivers.
 *
 * <p>PREREQUISITES:<UL>
 *
 * <LI>A working to advanced understanding of SQL query creation.
 *
 * <LI>Either the Servlet Runner which ships with the JavaSoft Java Servlet
 *     Development Kit (JSDK) or a Web server that supports servlets.
 *
 * <LI>An understanding of how to deploy and callup a servlet.
 * </UL>
 *
 * @version  1.0.0 05/15/97
 * @author Sheldon Bradley Wosnick
 */

public class DataAccessServlet extends HttpServlet {

    /**
     * The user ID.
     */
    private String m_strUID = "";

    /**
     * The password.
     */
    private String m_strPWD = "";

    /**
     * The URL with protocol and datasource name.
     */
    private String m_strURL = "";

    /**
     * The JDBC Driver.
     */
    private String m_strDriver = "";

    /**
     * The SQL string to use for query or update.
     */
    private String m_strSQLString = "";

    /**
     * The action type, either 'query' or 'update'.
     */
    private String m_strActionType = "";

    /**
     * The database name.
     */
    private String m_strDatabase = "";

    /**
     * The table name.
     */
    private String m_strTableName = "";

    /**
     * Properties to contain password and user ID.
     */
    private Properties m_properties = null;

    /**
     * The database connection.
     */
    private Connection m_connection = null;

    /**
     * Result set for a given query.
     */
    private ResultSet m_resultSet = null;

    /**
     * The Statement used for SQL query or update.
     */
    private Statement m_statement = null;

    /**
     * The output stream used for HTTP output to create dynamic HTML page.
     */
    private ServletOutputStream m_outstream = null;

/**
 * Checks for and displays SQL warnings. Returns true if a warning existed.
 * @param anSQLwarning SQLWarning
 * @return boolean indicating existence of an SQL warning.
 * @exception SQLException
 */
private static boolean checkForWarning(SQLWarning anSQLwarning)
    throws SQLException {

    boolean bReturnValue = false;

    if (anSQLwarning != null) {
        System.err.println("\n *** SQL Warning ***\n");
        bReturnValue = true;
        while (anSQLwarning != null) {
            System.err.println("SQLState: " +
                anSQLwarning.getSQLState());
            System.err.println("Message:  " +
                anSQLwarning.getMessage());
            System.err.println("Vendor:   " +
                anSQLwarning.getErrorCode());
            System.err.println("");
            anSQLwarning = anSQLwarning.getNextWarning();
        }
    }
    return bReturnValue;
}
/**
 * Perform the physical connection to the database.
 * @param aURL String, aDriver String, aProperties Properties, aResponse ServletResponse
 */
private void connectToDatabase(String aURL, String aDriver, Properties aProperties, ServletResponse aResponse) {

    try {
        trace("Trying the JDBC driver.");
        Class.forName(aDriver);

        // The following line will enable driver logging for debugging.
        // It is not required, and may be commented out if desired.
        // Note: this will impact performance!
        //trace("Setting the Log Stream.");
        //DriverManager.setLogStream(System.out);

        trace("Getting a database connection.");
//        m_connection = DriverManager.getConnection(aURL, aProperties);
        m_connection = DriverManager.getConnection(aURL, aProperties.get("UID").toString(),
                                                         aProperties.get("PWD").toString());
        trace("Checking for connection warnings.");
        checkForWarning(m_connection.getWarnings());
        trace("Connected to database.");
    }
    catch(SQLException anSQLException) {
        trace("An SQL Exception was thrown:\n" + anSQLException);
        processSQLException(anSQLException, aResponse);
    }
    catch (Exception anException) {
        // some other kind of exception occurred so dump the call stack
        anException.printStackTrace();
    }
}
/**
 * Destroys the servlet upon server shutdown.
 */
public void destroy() {
    trace("Destroying the servlet.");
    try {
        m_connection.commit();
        m_resultSet.close();
        m_resultSet = null;
        m_statement.close();
        m_statement = null;
        m_connection.close();
        m_connection = null;
    }
    catch(SQLException anSQLException) {

        System.err.println("\n*** SQLException caught ***\n");

        while (anSQLException != null) {
            System.err.println("SQLState: " +
                anSQLException.getSQLState());
            System.err.println("Message:  " + anSQLException.getMessage());
            System.err.println("Vendor:   " + anSQLException.getErrorCode());
            anSQLException = anSQLException.getNextException();
            System.err.println("");
        }
    }
}
/**
 * Executes a user-defined SQL query on a table.
 * @param anSQLQuery String, aResponse ServletResponse
 */
private void executeTheQuery(String anSQLQuery, ServletResponse aResponse) {

    try {
        trace("Executing a query.");
        m_statement = m_connection.createStatement();
        m_resultSet = m_statement.executeQuery(anSQLQuery);
    }
    catch (SQLException anSQLException) {
        trace("An SQL Exception was thrown:\n" + anSQLException);
        processSQLException(anSQLException, aResponse);
    }
}
/**
 * Executes a User-defined SQL update command on a table.
 * @param anUpdateCommand String, aResponse ServletResponse
 */
private void executeTheUpdate(String anUpdateCommand, ServletResponse aResponse) {

    try {
        trace("Executing an update.");
        m_statement = m_connection.createStatement();
        m_statement.executeUpdate(anUpdateCommand);
    }
    catch (SQLException anSQLException) {
        trace("An SQL Exception was thrown:\n" + anSQLException);
        processSQLException(anSQLException, aResponse);
    }
}
/**
 * Returns information about this servlet.
 * @return String about the servlet.
 */
public String getServletInfo() {
    return "a data access servlet by Sheldon Bradley Wosnick which demonstrates server-side JDBC programming with servlets";
}
/**
 * One-time initialization of the servlet where expensive, resource-intensive
 * activities should take place, such as file and network IO, and setting up
 * connection to a database. Hence, the database connection could made here,
 * but is made in service() for simplicity of this sample.
 * @param servletConfig ServletConfig
 */
public void init(ServletConfig servletConfig) {

    trace("Calling overridden init() method now.");

    try{
        super.init(servletConfig);
    }
    catch (ServletException aServletException) {
        System.err.println("ServletException: " + aServletException.getMessage());
    }
}
/**
 * Prepares http output to create new html page with results of query
 * after a user-entered SQL query or update command.
 * @param aResponse HttpServletResponse, anActionDescription String
 * @exeception SQLException
 */
private void outResultSet(ServletResponse aResponse, String anActionDescription)
    throws SQLException {

    try {
        trace("Getting an output stream for the HTML response page.");
        m_outstream = aResponse.getOutputStream();
        aResponse.setContentType("text/html");
        m_outstream.println("<html>");
        m_outstream.println("<head><title>A Data Access Servlet using Pure JDBC</title></head>");
//        m_outstream.println("<body BGCOLOR=#000000 TEXT=#C0C0C0>");
//        m_outstream.println("<BASEFONT FACE=\"Verdana, Arial, Helvetica\">");
//        m_outstream.println("<FONT FACE=\"Arial, Helvetica\" SIZE=2>");

            try {
                trace("Preparing some connection information.");
                ResultSetMetaData resultSetMetaData = m_resultSet.getMetaData();
                DatabaseMetaData databaseMetaData = m_connection.getMetaData();

                int iNumCols = resultSetMetaData.getColumnCount();

                // extra connection information provided debugging purposes
                m_outstream.println("JDBC DATABASE CONNECTION DETAILS:");
                m_outstream.println("<BR>");

                m_outstream.println("<BR>Current date and time: " + new java.util.Date().toString());

                m_outstream.println("<BR>Connection URL: " + databaseMetaData.getURL());

                m_outstream.println("<BR>Driver: " +
                    databaseMetaData.getDriverName());

                m_outstream.println("<BR>Version: " +
                    databaseMetaData.getDriverVersion());

                m_outstream.println("<BR>User Name: " +
                    databaseMetaData.getUserName());

                m_outstream.println("<BR>Driver Major Version: " +
                    databaseMetaData.getDriverMajorVersion());

                m_outstream.println("<BR>Driver Minor Version: " +
                    databaseMetaData.getDriverMinorVersion());

                m_outstream.println("<BR>Database Product Name: " +
                    databaseMetaData.getDatabaseProductName());

                m_outstream.println("<BR>Database Product Version: " +
                    databaseMetaData.getDatabaseProductVersion());

                m_outstream.println("<BR>SQL query or update command processed by servlet: " + m_strSQLString);
                m_outstream.println("<BR><BR>");

                trace("Preparing an HTML table.");
                m_outstream.println("<TABLE>");
                m_outstream.println("<TABLE BORDER=1>");

                String strCaption = "TABLE: " + m_strTableName +  " ***** ACTION TAKEN: " + anActionDescription;

                m_outstream.println("<CAPTION>" + strCaption + "</CAPTION>");

                int i;
                // create column names
                trace("Creating the column names");

                for (i = 1; i <= iNumCols; i++) {
                    m_outstream.println("<TH>" + resultSetMetaData.getColumnLabel(i));
                }

                boolean more = m_resultSet.next();

                // fill cells in table with values extracted from result set
                trace("Filling in the table elements.");
                while (more) {
                    m_outstream.println("<TR>");
                    for (i = 1; i <= iNumCols; i++) {
                             	if (m_strTableName.toUpperCase().equals("TESTTT")&& (i == 1 || i == 3)) {
	                             	m_outstream.println("<TD>" + new String(m_resultSet.getBytes(i),"ibm819"));
			} else {
                             		m_outstream.println("<TD>" + m_resultSet.getString(i));
			}
                    }
                    more = m_resultSet.next();
                }

                m_outstream.println("</FONT>");
                m_outstream.println("</TABLE>");

                trace("Finished preparing the HTML table.");
            }
            catch (SQLException anSQLException) {
                trace("An SQL Exception was thrown:\n" + anSQLException);
                processSQLException(anSQLException, aResponse);
            }

            m_outstream.println("</body></html>");
            trace("Finished with the output stream.");
            m_outstream.flush();
            m_outstream.close();
            trace("Flushed and closed the output stream.");
    }
    catch(IOException anIOException) {
        System.err.println ("\n*** IOException caught ***\n");
        System.err.println("IOException: " + anIOException.toString());
    }
}
/**
 * Process the SQL exception by extracting relevant information from it.
 * Display to the browser.
 * @param anSQLException SQLException, aResponse ServletResponse
 */
private void processSQLException(SQLException anSQLException, ServletResponse aResponse) {

    try {
        trace("Processing an SQL Exception");
        m_outstream = aResponse.getOutputStream();
        aResponse.setContentType("text/html");
        m_outstream.println("<html>");
        m_outstream.println("<head><title>Servlet threw an SQLException</title></head>");
        m_outstream.println("<body BGCOLOR=#000000 TEXT=#C0C0C0>");

        m_outstream.println("<P>*** An SQLException was caught ***</P>");
        //while (true) {;//(anSQLException != null) {
        while(anSQLException != null) {
            m_outstream.println("<P>" + "SQLState: " +
                anSQLException.getSQLState() + "</P>");
            m_outstream.println("<P>Message:  " + anSQLException.getMessage() + "</P>");
            m_outstream.println("<P>Vendor:   " + anSQLException.getErrorCode() + "</P>");
            anSQLException = anSQLException.getNextException();
            m_outstream.flush();
            m_outstream.close();
        }
    }
    catch(IOException anIOException) {
        System.err.println ("\n*** IOException caught ***\n");
        System.err.println("IOException: " + anIOException.toString());
    }
}
/**
 * Required method for a servlet, overridden to perform specialized JDBC work.
 * @param aRequest ServletRequest, aResponse ServletResponse
 * @exception ServletException, IOException
 */
public void service(ServletRequest aRequest, ServletResponse aResponse)
    throws ServletException, IOException {

    trace("Calling the service() method now.");

    m_properties = new Properties();
    
    trace("Getting request parameters.");
    m_strUID = aRequest.getParameter("UID");
    m_strPWD = aRequest.getParameter("password");
    m_strDriver = aRequest.getParameter("driver");
    m_strTableName = aRequest.getParameter("table");
//    m_strSQLString = new String(aRequest.getParameter("SQL").getBytes(),"ibm819");
    m_strSQLString = aRequest.getParameter("SQL");
    m_strDatabase = aRequest.getParameter("database");
    if (m_strDriver.substring(0,16).equals("org.gjt.mm.mysql") ||
        m_strDriver.substring(0,22).equals("com.imaginary.sql.msql") ||
        m_strDriver.substring(0,20).equals("COM.ibm.db2.jdbc.net")) {
        m_strURL = "jdbc:"+ aRequest.getParameter("URL") + "/" + m_strDatabase;
    } else {
        m_strURL = "jdbc:"+ aRequest.getParameter("URL") + ":" + m_strDatabase;
    }
    m_strActionType = aRequest.getParameter("action");
    m_properties.put("UID", m_strUID);
    m_properties.put("PWD", m_strPWD);

    trace("User ID: " + m_strUID);
    trace("Driver: " + m_strDriver);
    trace("URL: " + m_strURL);
    trace("Table Name = " + m_strTableName);
    trace("SQL String = " + m_strSQLString);
    trace("Action Type = " + m_strActionType);

     // only attempt a connection if not already successfully connected for
    // this particular client
    if(m_connection == null)
        connectToDatabase(m_strURL, m_strDriver, m_properties, aResponse);

    // Decide if the user intends to query the database or to actually update,
    // which translates eventually into the difference between executeQuery() and
    // executeUpdate()
    if (m_strActionType.equals("query")) {

        executeTheQuery(m_strSQLString, aResponse);
        try {
            outResultSet(aResponse, "User-defined Query");
        }
        catch (SQLException anSQLException) {
            trace("An SQL Exception was thrown.");
            processSQLException(anSQLException, aResponse);
        }
    }
    else if (m_strActionType.equals("update")) {
        // if it is an update requested, create a result table both before and after just
        // to illustrate that the updates were committed.
        try {
            // requery the current table
            trace("Querying the database for table " + m_strTableName + " before any updates.");
            m_statement = m_connection.createStatement();
            m_resultSet = m_statement.executeQuery("select * from " + m_strTableName);
            outResultSet(aResponse, "Query Before the Update");
        }
        catch (SQLException anSQLException) {
            trace("An SQL Exception was thrown:\n" + anSQLException);
            processSQLException(anSQLException, aResponse);
        }

        executeTheUpdate(m_strSQLString, aResponse);

        try {
            // requery the current table
            trace("Requerying the database for table " + m_strTableName + " after any updates.");
            m_statement = m_connection.createStatement();
            m_resultSet = m_statement.executeQuery("select * from " + m_strTableName);
            outResultSet(aResponse, "Query after the Update");
        }
        catch (SQLException anSQLException) {
            trace("An SQL Exception was thrown:\n" + anSQLException);
            processSQLException(anSQLException, aResponse);
        }
    }
    else
        System.err.println("ERROR >> For processing purposes, you must explicitly pass 'update' or 'query'");

    // close connections and free resources
    try {
        m_connection.commit();
        m_resultSet.close();
        m_resultSet = null;
        m_statement.close();
        m_statement = null;
        m_connection.close();
        m_connection = null;
    }
    catch(SQLException anSQLException) {
        trace("An SQL Exception was thrown:\n" + anSQLException);
        processSQLException(anSQLException, aResponse);
    }
}
/**
 * This method is used to output trace messages to standard output.
 * @param anTraceMessage String
 */
public void trace(String aTraceMessage) {
    System.out.println("DEBUG TRACE:  " + aTraceMessage);
}
}