// gb3, based on example from http://www.oreilly.com/catalog/msql/servlet.txt
// Very loosely based, as it turns out.  I couldn't find adequate documentation to make that work.
// this is the generic (non-Jetty standalone) servlet version.

/* Here's how to build the database:
mysqladmin create GUESTBOOK
mysql <<DONE
use GUESTBOOK;
create table comments (comment_id int primary key not null auto_increment,
                       name varchar(40) not null, date varchar(30), comment varchar(255));
quit
DONE

Here's how to set up the web.xml entry in Tomcat:

    <servlet>
        <servlet-name>gb3</servlet-name>
        <servlet-class>gb3</servlet-class>
        <init-param>
            <param-name>mysql_account</param-name>
            <param-value>********8</param-value>   - provide your own
        </init-param>
        <init-param>
            <param-name>mysql_password</param-name>
            <param-value>**********</param-value> - provide your own
        </init-param>
        </init-param>
        <init-param>
            <param-name>mysql_schema</param-name>
            <param-value>GUESTBOOK</param-value>
        </init-param>
        <init-param>
            <param-name>URI</param-name>
            <param-value>rowland/servlet/gb3</param-value>
        </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>gb3</servlet-name>
        <url-pattern>/servlet/gb3/*</url-pattern>
    </servlet-mapping>

*/ 

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.sql.*;
import java.text.DateFormat;
import java.util.Properties;
import java.util.Random;

public class gb3 extends HttpServlet {
    static final String driverName          = "com.mysql.jdbc.Driver";
    static final String jdbcURL1            = "jdbc:mysql://127.0.0.1/";
    private Driver     driver               = null;
    private Random     random               = new Random();

    protected String databaseName;
    protected String userName;
    protected String URI;
    protected String userPassword;

    protected void openDriver() throws Exception {
	if (driver!= null)  return;  // already done
	println("Instantiating "+ driverName);
	driver = (Driver)Class.forName(driverName).newInstance();
	if (driver== null)  throw new SQLException("newInstance of "+ driverName+ "returned NULL!");
    }

    protected Connection openConnection()  throws Exception {
	// return null if failed to connect
	openDriver();
	println("  Connecting "+ jdbcURL1+ databaseName);
	Connection conn;
	conn= DriverManager.getConnection(jdbcURL1+ databaseName, userName, userPassword);
	//[Not for Msql or MySql]conn.setAutoCommit(false);
	println("  ...connected");
	return conn;
    }

    public void init(ServletConfig cfg) throws ServletException {
	// Called the first time a servlet is invoked
	super.init(cfg);
	databaseName= cfg.getInitParameter("mysql_schema");
	userName= cfg.getInitParameter("mysql_account");
	userPassword= cfg.getInitParameter("mysql_password");
	URI= cfg.getInitParameter("URI");
	println("init() called "+ databaseName+ " "+ userName);
	println("init() called "+ databaseName+ " "+ userName);
	try { openDriver(); }
        catch( Exception e ) {
            throw new ServletException("EXC init(): " + e.getMessage());
        }
    }

    protected String curDateString() {
	java.util.Date date= new java.util.Date();
	return DateFormat.getDateTimeInstance().format(date);
    }

    protected static void println(String s) {
	java.util.Date d= new java.util.Date();
	System.out.println(d.getTime()%10000+ ": "+ s);
    }

    public void doGet(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {
	println("doGet called...");
        ServletOutputStream out = res.getOutputStream();
        int id = -1;

        res.setContentType("text/html");
        printPageHeader(out);
        printCommentForm(out);
        printComments(out);
        printPageFooter(out);
	println("...doGet done");
    }

    public void doPost(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {
	println("doPost called");
        ServletOutputStream out = res.getOutputStream();
        String date = curDateString();
        String name, comment;
        Connection conn = null;
        Exception err = null;
        int id = -1;
        String[] tmp;

        // get the form values
        tmp = req.getParameterValues("name");
        if( tmp == null || tmp.length != 1 ) {
            name = null;
        }
        else {
            name = tmp[0];
        }
        tmp = req.getParameterValues("comments");
        if( tmp == null || tmp.length != 1 ) {
            comment = null;
        }
        else {
            comment = tmp[0];
        }
        res.setContentType("text/html");
        printPageHeader(out);
        // validate values
        if( name.length() < 1 ) {
            out.println("You must specify a valid name!");
            printCommentForm(out);
            printPageFooter(out);
            return;
        }
        if( comment.length() < 1 ) {
            out.println("You left no comments!");
            printCommentForm(out);
            printPageFooter(out);
            return;
        }
        try {
            ResultSet result;
            Statement stmt;
	    
	    conn= openConnection();
            stmt = conn.createStatement();
            comment = fixComment(comment);
            stmt.executeUpdate("INSERT into comments (comment_id, name, comment, date) "+
                               "VALUES (0, '" + name + "', '" +
                               comment + "', '" + date + "')");
            //[Not for Msql or MySql]conn.commit();
            stmt.close();
        }
        catch( Exception e ) {
            e.printStackTrace();
            err = e;
        }
        finally {
            if( conn != null ) {
                try { conn.close(); }
                catch( Exception e ) { }
            }
        }
        if( err != null ) {
            out.println("An error occurred on save: " + err.getMessage());
        }
        else {
            printCommentForm(out);
            printComments(out);
        }
	println("...doPost done");
    }

    public String getServletInfo() {
        return "Guest Book Servlet\nFrom MySQL and mSQL";
    }

    private void printCommentForm(ServletOutputStream out)
        throws IOException {
	println("  printCommentForm()");
        out.println("<CENTER>");
        out.println("<FORM ACTION=\"/"+ URI+ "\" METHOD=\"POST\">");
        out.println("<TABLE>");
        out.println("<TR ALIGN=\"LEFT\">");
        out.println("<TD>Name:</TD>");
        out.println("<TD><INPUT TYPE=\"TEXT\" NAME=\"name\" SIZE=30></TD>");
        out.println("<TD><INPUT TYPE=\"SUBMIT\" VALUE=\"Save\"></TD>");
        out.println("</TR>");
        out.println("<TR ALIGN=\"LEFT\">");
        out.println("</TR>");
        out.println("<TR ALIGN=\"LEFT\">");
        out.println("<TD>Comments:</TD>");
        out.println("</TR>");
        out.println("<TR ALIGN=\"CENTER\">");
        out.println("<TD COLSPAN=3>");
        out.println("<TEXTAREA NAME=\"comments\" COLS=40 ROWS=7>");
        out.println("</TEXTAREA></TD>");
        out.println("</TR>");
        out.println("</TABLE>");
        out.println("</FORM>");
        out.println("</CENTER>");
    }

    private void printComments(ServletOutputStream out)
        throws IOException {
        Connection conn = null;
	
	println("  printComments()");
        try {
            ResultSet results;
            Statement stmt;
            int rows, count;
	    
            conn = openConnection();
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            results = stmt.executeQuery("SELECT name, date, " +
                                        "comment, comment_id " +
                                        "FROM comments " +
                                        "ORDER BY comment_id");
	    println("  ...SELECT FROM comments");
            out.println("<DL>");
            results.last();
            count = 0;
            // print up to 5 rows going backwards
            while (count < 5) {
                String name, cmt, date;

                name = results.getString(1);
                if( results.wasNull() ) name = "Unknown User";
                date = results.getString(2);
                if( results.wasNull() ) date = curDateString();
                cmt = results.getString(3);
                if( results.wasNull() )  cmt= "No comment";
                out.println("<DT><B>" + name + "</B>  on " + date);
                cmt = noHTML(cmt);
                out.println("<DD> <PRE>" + cmt + "</PRE>");
		if (!results.previous())  break;  // no more.
                count++;
            }
            out.println("</DL>");
        }
        catch( Exception e ) {
            out.println("Error: " + e.getMessage());
	    println("EXC printComments(): " + e.getMessage());
	    e.printStackTrace();
        }
        finally {
            if( conn != null ) {
                try { conn.close(); }
                catch( SQLException e ) { }
            }
        }
    }
    
    private void printPageHeader(ServletOutputStream out)
        throws IOException {
        out.println("<HTML>");
        out.println("<HEAD>");
        out.println("<TITLE>Guest Book</TITLE>");
        out.println("</HEAD>");
        out.println("<BODY>");
        out.println("<CENTER><H1><B>Guest Book</B></FONT></CENTER>");
    }

    private void printPageFooter(ServletOutputStream out)
        throws IOException {
        out.println("</BODY>");
        out.println("</HTML>");
        out.flush();
    }

    private String noHTML(String cmt) {
	int ilt= cmt.indexOf('<');
	int igt= cmt.indexOf('>');
	if (ilt< 0 && igt< 0)
	    return cmt;
	
	String tmp = "";
	
	for (int i=0; i<cmt.length(); i++) {
	    char c = cmt.charAt(i);
	    if( c == '<' ) tmp = tmp + "&lt;";
	    else if( c == '>' )   tmp = tmp + "&gt;";
	    else  tmp = tmp + c;
	}
        return tmp;
    }

    private String fixComment(String comment) {
        if( comment.indexOf('\'') != -1 ) {
            String tmp = "";
	    
            for(int i=0; i<comment.length(); i++) {
                char c = comment.charAt(i);
                if( c == '\'' )  tmp = tmp + "\\'";
                else  tmp = tmp + c;
            }
            comment = tmp;
        }
        return comment;
    }


    //----------------------------------------------------------------------------------------

    static void realityCheck() {
	try {
	    gb3 temp= new gb3();
	    temp.openDriver();
	    Connection conn= temp.openConnection();
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery("SELECT name " +
                                       "FROM comments WHERE comment_id = 0");
	} catch (Exception E) {
	    System.err.println("EXC realityCheck: "+ E.getMessage());
	    E.printStackTrace();
	    System.exit(-1);
	}
    }
}