// 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 facDirJavaDb {
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("Directory Search");
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("Directory Listing
");
}
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, execute it, and get a ResultSet
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT faculty.* FROM faculty ORDER BY last DESC, first DESC");
// Step 4: process result of the query
while (rs.next()) {
String lastName = rs.getString("last");
String firstName = rs.getString("first");
System.out.println("Name: " + firstName + " " + lastName);
System.out.println("
");
System.out.println("- Title: " + rs.getString("title"));
System.out.println("
- E-mail: " + rs.getString("email"));
System.out.println("
- Telephone: " + rs.getString("phone"));
System.out.println("
- Office: " + rs.getString("office"));
System.out.println("
");
}
} 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
} // facDirJavaDb