JDBC MySQL Guestbook

/* GuestJDBC.java * * By Jeff Schmitt * January, 1998 * June, 1998 * * This CGI program uses GWE JDBC drivers to access a MySQL database * named schmitt on triton.towson.edu * */ // You need to import the java.sql package to use JDBC import java.sql.*; /* Java 1.1 */ import CGIParameters; import java.util.*; import java.io.*; public class GuestJDBC { CGIParameters params; // get a value from the Form and environment data public String getKey(String key) { try { if (params.containsKey(key)) { return params.get(key); } else { return ""; } } catch (NoSuchFieldException e) { System.out.println("No such parameter -> "+e.getMessage()); return ""; } } public GuestJDBC(String args[]) throws SQLException, ClassNotFoundException { System.out.println("Content-type: text/html\n\n"); System.out.println("JDBC MySQL Guestbook"); System.out.println("

JDBC Guestbook

"); // Load the GWE Mysql JDBC driver Class.forName ("gwe.sql.gweMysqlDriver"); // access the CGI parameters try { params = new CGIParameters(true); } catch (Exception e) { System.out.println("Error in CGIParameters: "+e.getMessage()); System.exit(1); } System.out.println("

Hello "+getKey("REMOTE_HOST")+"

"); System.out.println("Guestbook MySQL JDBC

"); // Read the MySQL userid and password from the datafile // .mysql-init in your home directory String account="**"; String password="**"; try { BufferedReader in = new BufferedReader(new InputStreamReader( new FileInputStream("/usr/faculty/schmitt/.mysql-init"))); account=in.readLine(); password=in.readLine(); } catch (Exception e) { System.out.println("GuestJDBC: cannot read .mysql-init"); System.exit(1); } // Connect to the database // if the connection parameters are wrong you will get the // error message "No suitable driver" -- check the documentation // for the jdbc driver you are using. // For the GWE mysql implementation of JDBC, it // looks for connection URL's in the form of // jdbc:mysql://[host_addr]:[port]/[db_name] // jdbc:mysql:db_name Connection conn = DriverManager.getConnection ("jdbc:mysql://:3306/schmitt", account, password); // Create a Statement Statement stmt = conn.createStatement (); ResultSet rset=null; // Insert the guestbook data into table guestbook3 try { rset = stmt.executeQuery("insert into guestbook3 values ('"+ getKey("NAME")+"', '"+getKey("AFF1")+"', '"+ getKey("AFF2")+"', '"+getKey("EMAIL")+"')"); } catch (SQLException e) { System.out.println("GuestJDBC: Error in insert "+e+"
"); } // Select all columns from the table guestbook3 try { rset = stmt.executeQuery ("select * from guestbook3"); } catch (SQLException e) { System.out.println("GuestJDBC: Error in select "+e+"
"); } // Iterate through the result and print the Guestbook System.out.println(""); while (rset.next ()) { System.out.println(""); for (int i=1;i<=4;i++) { System.out.println("
"+rset.getString (i)); } } System.out.println("
"); System.out.println(""); } public static void main(String args[]) { try { GuestJDBC smain = new GuestJDBC(args); } catch (Exception e) { System.out.println("GuestJDBC: Error in program"); System.exit(1); } } }