package com.oreilly.jdbc.examples; import javax.servlet.*; import javax.servlet.http.*; import java.io.IOException; import java.sql.Connection; import java.sql.Date; import java.sql.Driver; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DateFormat; import java.util.Locale; import java.util.Properties; import java.util.Random; public class GuestBookServlet extends HttpServlet { private Properties connectionProperties = new Properties(); private Driver driver = null; private String driverName = null; private String jdbcURL = null; private Random random = new Random(); /** * Provides the servlet with the chance to get runtime configuration * values and initialize itself. For a database servlet, you want * to grab the driver name, URL, and any connection information. * For this example, I assume a driver that requires a user name * and password. For an example of more database independent * configuration, see Chapter 4. * @param cfg the servlet configuration information * @throws javax.servlet.ServletException could not load the specified * JDBC driver */ public void init(ServletConfig cfg) throws ServletException { super.init(cfg); driverName = cfg.getInitParameter("gb.driver"); jdbcURL = cfg.getInitParameter("gb.jdbcURL"); connectionProperties.put("user", cfg.getInitParameter("gb.user")); connectionProperties.put("password", cfg.getInitParameter("gb.pw")); try { driver = (Driver)Class.forName(driverName).newInstance(); } catch( Exception e ) { throw new ServletException("Unable to load driver: " + e.getMessage()); } } /** * Performs the HTTP GET. This is where we print out a form and * a random sample of the comments. * @param req the servlet request information * @param res the servlet response information * @throws javax.servlet.ServletException an error occurred talking to * the database * @throws java.io.IOException a socket error occurred */ public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { ServletOutputStream out = res.getOutputStream(); int id = -1; res.setContentType("text/html"); printPageHeader(out); printCommentForm(out); printComments(out); printPageFooter(out); } public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { ServletOutputStream out = res.getOutputStream(); Date date = new Date((new java.util.Date()).getTime()); String name, email, 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("email"); if( tmp == null || tmp.length != 1 ) { email = null; } else { email = 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( email.length() < 3 ) { out.println("You must specify a valid email address!"); printCommentForm(out); printPageFooter(out); return; } if( email.indexOf("@") < 1 ) { out.println("You must specify a valid email address!"); 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 = DriverManager.getConnection(jdbcURL, connectionProperties); // remove the "setAutoCommit(false)" line for mSQL or MySQL conn.setAutoCommit(false); stmt = conn.createStatement(); // generate a new comment ID // more on ID generation in Chapter 4 result = stmt.executeQuery("SELECT next_id " + "FROM sys_gen " + "WHERE id = 'comment_id'"); if( !result.next() ) { throw new ServletException("Failed to generate id."); } id = result.getInt(1) + 1; stmt.close(); // closing the statement closes the result stmt = conn.createStatement(); stmt.executeUpdate("UPDATE sys_gen SET next_id = " + id + " WHERE id = 'comment_id'"); stmt.close(); stmt = conn.createStatement(); comment = fixComment(comment); stmt.executeUpdate("INSERT into comments " + "(comment_id, email, name, comment, " + "cmt_date) "+ "VALUES (" + id +", '" + email + "', '" + name + "', '" + comment + "', '" + date.getTime() + "')"); conn.commit(); stmt.close(); } catch( SQLException 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); } } public String getServletInfo() { return "Guest Book Servlet\nFrom MySQL and mSQL"; } private void printCommentForm(ServletOutputStream out) throws IOException { out.println("
"); out.println("
"); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println("
Name:
Email:
Comments:
"); out.println("
"); out.println("
"); out.println("
"); } private void printComments(ServletOutputStream out) throws IOException { Connection conn = null; try { DateFormat fmt = DateFormat.getDateInstance(DateFormat.FULL, Locale.getDefault()); ResultSet results; Statement stmt; int rows, count; conn = DriverManager.getConnection(jdbcURL, connectionProperties); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); results = stmt.executeQuery("SELECT name, email, cmt_date, " + "comment, comment_id " + "FROM Comments " + "ORDER BY cmt_date"); out.println("
"); results.last(); rows = results.getRow(); // pick a random row rows = random.nextInt()%rows; if( rows < 4 ) { // if the random row is less than 4, print the first 4 rows results.afterLast(); } else { // otherwise go to the specified row, print the prior 5 rows results.absolute(rows); } count = 0; // print up to 5 rows going backwards from the randomly // selected row while( results.previous() && (count < 5) ) { String name, email, cmt; Date date; count++; name = results.getString(1); if( results.wasNull() ) { name = "Unknown User"; } email = results.getString(2); if( results.wasNull() ) { email = "user@host"; } date = results.getDate(3); if( results.wasNull() ) { date = new Date((new java.util.Date()).getTime()); } cmt = results.getString(4); if( results.wasNull() ) { cmt = "No comment."; } out.println("
" + name + " (" + email + ") on " + fmt.format(date)); cmt = noHTML(cmt); out.println("
" + cmt + "
"); } out.println("
"); } catch( SQLException e ) { out.println("A database error occurred: " + e.getMessage()); } finally { if( conn != null ) { try { conn.close(); } catch( SQLException e ) { } } } } private void printPageHeader(ServletOutputStream out) throws IOException { out.println(""); out.println(""); out.println("Guest Book"); out.println(""); out.println(""); out.println("

Guest Book

"); } private void printPageFooter(ServletOutputStream out) throws IOException { out.println(""); out.println(""); out.flush(); } private String noHTML(String cmt) { if( cmt.indexOf("<") != -1 || cmt.indexOf(">") != -1 ) { String tmp = ""; for(int i=0; i' ) { tmp = tmp + ">"; } else { tmp = tmp + c; } } cmt = tmp; } return cmt; } private String fixComment(String comment) { if( comment.indexOf("'") != -1 ) { String tmp = ""; for(int i=0; i