// Establish a connection to a mSQL database using JDBC.
// This program is adapted from a tutorial by mysql.com:
// http://dev.mysql.com/doc/refman/5.0/en/cj-using-statements.html
// Applied to a database of math/cs faculty
// by Henry M. Walker
import java.sql.*;
class facDirJavaDbReset {
public static void printWebPageHeader ()
throws Exception {
/* prints an html page header */
System.out.println("Content-type: text/html");
System.out.println();
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("Reset Directory");
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("Reseting Directory
");
}
public static void printWebPageFooter ()
throws Exception {
/* prints an html page footer */
System.out.println("");
System.out.println("");
}
public static void main (String[] args)
{
Statement stmt = null;
ResultSet rs = null;
try {
// Step 0: Print Web header
printWebPageHeader ();
// Step 1: Load the JDBC driver.
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println ("database registered!!
");
// Step 2: Establish the connection to the database.
String url = "jdbc:mysql://localhost/csc223?user=***&password=***";
Connection conn = DriverManager.getConnection(url);
System.out.println ("connection made!!!!!!
");
// Step 3: Set up a database query
stmt = conn.createStatement();
// Step 4: Remove all faculty records
System.out.println ("removing faculty records, if any
");
// do not expect a result from a delete
stmt.execute("DELETE from faculty WHERE facultyID <= 15");
// Step 5: Insert original faculty records
System.out.println ("adding original faculty records
");
String query = "INSERT INTO faculty VALUES";
query += "( 1, \"Arnold\", \"Adelberg\",";
query += " \"Myra Steele Professor of Mathematics\",";
query += " \"adelbe@math.grin.edu\", 4201,\"Science 2405\"),";
query += "( 2, \"Nathaniel\", \"Borenstein\",";
query += " \"Noyce Visiting Professor\",";
query += " \"borenste@cs.grinnell.edu\", 3017, \"Science 1131\"),";
query += "( 3, \"Marc\", \"Chamberland\",";
query += " \"Assistant Professor of Mathematics\",";
query += " \"chamberl@math.grin.edu\", 4207, \"Science 2410\"),";
query += "( 4, \"Pamela\", \"Ferguson\",";
query += " \"Professor of Mathematics\",";
query += " \"fergusoa@ac.grin.edu\", 3017, \"Science 1131\"),";
query += "( 5, \"Eugene\", \"Herman\",";
query += " \"Samuel R. and Marie-Louise Rosenthal Professor of Natural Science and Mathematics\",";
query += " \"herman@math.grin.edu\", 4202, \"Science 2403\"),";
query += "( 6, \"Chris\", \"Hill\",";
query += " \"Assistant Professor of Mathematics\",";
query += " \"hillc@math.grin.edu\", 4556, \"Science 2401\"),";
query += "( 7, \"Charles\", \"Jepsen\",";
query += " \"Professor of Mathematics\",";
query += " \"jepsen@math.grin.edu\", 4203, \"Science 2407\"),";
query += "( 8, \"Katherine\", \"McClelland\",";
query += " \"Director of the Mathematics Laboratory and Lecturer\",";
query += " \"mcclella@math.grin.edu\", 3060, \"Science 2012A\"),";
query += "( 9, \"Karen\", \"McRitchie\",";
query += " \"Local Area Network Manager\",";
query += " \"mcritchi@math.grin.ed\", 4531, \"Science 2148\"),";
query += "(10, \"Emily\", \"Moore\",";
query += " \"Associate Professor of Mathematics and Computer Science\",";
query += " \"mooree@math.grin.edu\", 4205, \"Science 2416\"),";
query += "(11, \"Thomas\", \"Moore\",";
query += " \"Associate Professor of Mathematics\",";
query += " \"mooret@ac.grin.edu\", 4206, \"Science 2414\"),";
query += "(12, \"Samuel\", \"Rebelsky\",";
query += " \"Assistant Professor of Computer Science\",";
query += " \"rebelsky@math.grin.edu\", 4410, \"Science 2427\"),";
query += "(13, \"John\", \"Stone\",";
query += " \"Lecturer in Computer Science\",";
query += " \"stone@math.grin.edu\", 3181, \"Science 2418\"),";
query += "(14, \"Henry\", \"Walker\",";
query += " \"Professor of Mathematics and Computer Science\",";
query += " \"walker@cs.grinnell.edu\", 4208, \"Science 2420\"),";
query += "(15, \"Royce\", \"Wolf\",";
query += " \"Associate Professor of Mathematics\",";
query += " \"wolf@math.grin.edu\", 4209, \"Science 2412\");";
// do not expect a result returned from an insert either
stmt.execute(query);
System.out.println ("original faculty records restored!
");
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
System.err.println(e.getCause());
} finally {
// Step 5: release resources in reverse-order of their creation,
// assuming they are no-longer needed
try {
System.out.println ("release resources!!
");
} catch (Exception e) {
System.err.println("Exception during cleanup");
}
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
// ignore
rs = null;
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
// ignore
stmt = null;
}
}
// Step 6: finish up Web page
try {
System.out.println ("concluding Web page!");
printWebPageFooter ();
} catch (Exception e) {
System.err.println("Exception during printing of footer");
}
}
} // main
} // facDirJavaDbReset