
import java.net.URL;
import java.util.*;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class MySQLServlet extends HttpServlet {
    /**
     * 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;

private void connectToDatabase(String aURL, String aDriver, String uid,
                               String pass, ServletResponse aResponse) {

    try {
        Class.forName(aDriver);

        m_connection = DriverManager.getConnection(aURL, uid,pass);
    }
    catch(SQLException anSQLException) {
        processSQLException(anSQLException, aResponse, "CON");
    }
    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() {
    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");
    }
}
private void executeTheQuery(String anSQLQuery, ServletResponse aResponse) {

    try {
        m_statement = m_connection.createStatement();
        m_resultSet = m_statement.executeQuery(anSQLQuery);
    }
    catch (SQLException anSQLException) {
        processSQLException(anSQLException, aResponse, "QUERY");
    }
}
public void init(ServletConfig servletConfig) {


    try{
        super.init(servletConfig);
    }
    catch (ServletException aServletException) {
        System.err.println("ServletException: " + aServletException.getMessage());
    }
}
private void outResultSet(ServletResponse aResponse, String anActionDescription)
    throws SQLException {

    try {
        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>");
          try {
                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_outstream.println("<BR><BR>");

                m_outstream.println("<TABLE>");
                m_outstream.println("<TABLE BORDER=1>");

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

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

                int i;
                // create 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
                byte[] tmp = new byte[10];
                String aStr;
                while (more) {
                    m_outstream.println("<TR>");
                    for (i = 1; i <= iNumCols; i++) {
                        if (i==1) {
                            tmp = m_resultSet.getBytes(i);
                            aStr = new String(tmp,"ibm819");
               	            m_outstream.println("<TD>" + aStr);
                        } else {
               	            m_outstream.println("<TD>" + m_resultSet.getString(i));
                        }                        
                    }
                    more = m_resultSet.next();
                }
                m_outstream.println("</FONT>");
                m_outstream.println("</TABLE>");

            }
            catch (SQLException anSQLException) {
                processSQLException(anSQLException, aResponse, "OUT");
            }

            m_outstream.println("</body></html>");
            m_outstream.flush();
            m_outstream.close();
    }
    catch(IOException anIOException) {
        System.err.println ("\n*** IOException caught ***\n");
        System.err.println("IOException: " + anIOException.toString());
    }
}
private void processSQLException(SQLException anSQLException,
                                 ServletResponse aResponse, String str) {

    try {
        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:" + str + " ***</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());
    }
}
public void service(ServletRequest aRequest, ServletResponse aResponse)
    throws ServletException, IOException {

    String aDriver="org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://202.120.182.10:3306/test";
    if(m_connection == null) {
        connectToDatabase(url, aDriver,"dehua", "yangdehua", aResponse);
        executeTheQuery("SELECT * FROM test", aResponse);
        try {
            outResultSet(aResponse, "User-defined Query");
        }
        catch (SQLException anSQLException) {
            processSQLException(anSQLException, aResponse,"AAA");
        }
    }
    // 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) {
        processSQLException(anSQLException, aResponse,"SERV");
    }
}
}