Until now, you have used a single database file for all users. This may work for a small guest book, but it is not realistic for a larger application.
In this exercise, you will replace your FileDatabase with a JDBCDatabase using the standard Java Database Connectivity API. You will not be using any of the Symantec-specific database classes that are included in Visual Café.
Java Database Connectivity, JDBC, is a part of the Java 1.1 standard (the java.sql package). You can read more about it in the following places:
The first time you use the database server, you must create a new database. You can do
this by starting the Sybase SQLAnywhere SQL Central and selecting the
Database Utilities/Create database option. Name the database z:\javadb.db
and press the Next (or Finish ) button until the dialog window disappears.
You now have a database, but it does not contain any tables. You will need to create at least one table that will contain the information you collect about your guests. To do that, connect to the database (View /Connect ):
DBA (you are the DataBase Administrator).
sql
javadb
z:\javadb.db
You can add a table by doing javadb /javadb /Tables /Add Table . Give the table a name and press OK. Using Guests /Columns /Add Column , you can add new columns in the table. Read about different datatypes in the JDBC documentation.
Your table columns should always have types that are ``natural'' for the kind of information you want to represent - for example, you should make the ``wants to be notified'' column a boolean column, not a one-character column containing ``Y'' or ``N''.
Now you should write the JDBCDatabase class. Like the FileDatabase class, the new class should implement the UserDatabase interface.
Make sure that your database class does not hard-code the database URL, user name, or password. You should either give them as arguments to the constructor or create a Connection outside the constructor.
You should also create a JDBCDatabaseServer analogous to your FileDatabaseServer.
In the JDBC: Getting started document, there is some sample code showing how you can query a database.
The following code is a modified version of that sample code: Since you already know the names of the columns in your database, there is no need to find out the names of the columns using the ResultSetMetaData class.
Note that in your implementation, you may have to add more error handling code. You will also have to find out how to perform queries that update the database (since you will need to be able to remove and modify user information).
import java.net.URL;
import java.sql.*;
class SimpleSelect
{
// This is my own class; you will have to decide how to handle
// your errors...
protected ErrorHandler err = new ErrorHandler(System.err);
// We are using the Symantec JDBC driver. (JDBC itself is
// defined in the java.sql package, but anyone can write
// JDBC drivers for different databases.)
public final static String dbDriver = "symantec.itools.db.jdbc.Driver";
public final static String tableName = "People";
// This is the URL of our database. We assume that the
// database is running on the same computer as this program,
// on port 8889.
public final static String dbURL =
"jdbc:dbaw://localhost:8889/SYBASE_SQLANY/javadb/javadb";
public static void main (String args[]) {
// Try to load the database driver. We do this by getting the
// Class object corresponding to the driver. This causes the
// class to be loaded into the Java virtual machine. Its
// static initializer methods are executed; one of them
// registers the driver with the JDBC DriverManager -- so if
// we succeed with this call, the DriverManager now knows that
// the DBAnywhere driver is available.
try {
Class.forName(dbDriver);
} catch (ClassNotFoundException e) {
// You are allowed to fail and exit here, since this can
// happen only when you *start* your threaded server. You
// are not allowed to terminate the program later, since
// the server should stay up even if there are temporary
// database problems.
err.fatal("Database driver not found", e);
return;
}
try {
// Let all drivers log their messages to System.out, so
// that we can see what is happening...
DriverManager.setLogStream(System.out);
// Try to connect to the database. As you may remember, DBA
// is the DataBase Administrator, and sql is the password we
// used when we created the database in SQL Central.
Connection con = DriverManager.getConnection(dbURL, "DBA", "sql");
// If we were unable to connect, an exception would have
// been thrown. So, if we get here, we are successfully
// connected to the database.
// Check for, and display any warnings generated by the
// connect.
checkForWarning (con.getWarnings());
// Get the DatabaseMetaData object and display some
// information about the connection
DatabaseMetaData dma = con.getMetaData();
System.out.println("\nConnected to " + dma.getURL());
System.out.println("Driver " + dma.getDriverName());
System.out.println("Version " + dma.getDriverVersion());
System.out.println();
// Create a Statement object so we can submit SQL
// statements to the driver.
Statement stmt = con.createStatement();
// Submit a query, creating a ResultSet object
ResultSet rs = stmt.executeQuery("SELECT Name, Address FROM " + tableName);
// Display all columns and rows from the result set
dispResultSet (rs);
// Close the result set
rs.close();
// Close the statement
stmt.close();
// Close the connection
con.close();
} catch (SQLException ex) {
// A SQLException was generated. Catch it and display the
// error information. Note that there could be multiple
// error objects chained together.
System.out.println ("\n*** SQLException caught ***\n");
while (ex != null) {
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("Message: " + ex.getMessage());
System.out.println("Vendor: " + ex.getErrorCode());
System.out.println();
ex = ex.getNextException();
}
}
}
//-------------------------------------------------------------------
// checkForWarning
// Checks for and displays warnings. Returns true if a warning
// existed
//-------------------------------------------------------------------
private static boolean checkForWarning (SQLWarning warn)
throws SQLException
{
boolean rc = false;
// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together
if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println("SQLState: " + warn.getSQLState());
System.out.println("Message: " + warn.getMessage());
System.out.println("Vendor: " + warn.getErrorCode());
System.out.println();
warn = warn.getNextWarning();
}
}
return rc;
}
//-------------------------------------------------------------------
// dispResultSet
// Displays all columns and rows in the given result set
//-------------------------------------------------------------------
private static void dispResultSet(ResultSet rs)
throws SQLException
{
// Display data, fetching until end of the result set
boolean more = rs.next();
while (more) {
// Loop through each column, getting the
// column data and displaying
System.out.println("Name: " + rs.getString("Name"));
System.out.println("Address: " + rs.getString("WWW"));
System.out.println();
// Fetch the next result set row
more = rs.next();
}
}
}
In this exercise, you have added two (or possibly more) new classes to the system: The new JDBCDatabase database and the JDBCDatabaseServer. Since your previous classes were written to be easily extended by plugging in new databases, you should not have had to change any of your old code: You should still use exactly the same code for the applet, threaded server, and so on.
Now, you can test your new system. Try adding some guests, modifying their attributes, and so on. While you are doing this, you may want to look at the current contents of the database using SQL Central.