Saving persistent objects to files would be a third choice for most programmers. The first choice would be saving objects to an object-oriented database. The format of data in an object oriented database is indistinguishable from objects in memory, so there's no special code that needs to be written. A standard called ODMG-93 for interfacing C++ programs to object-oriented databases is emerging. Unfortunately, object-oriented databases are catching on slowly, and the ODMG standard seems to be languishing. So what's the second choice? The second choice is to save objects to a relational database.
Relational Database Concepts
A relational database is a collection of tables (also called relations). The rows of a table are called records or tuples. The columns are called attributes or fields.
For example, a database might contain two tables called TEACHES:
Basic database operations are:
ODBC
Each database an MFC program interacts with is represented by an instance of the CDatabase class, which is responsible for communicating with the database using SQL and an ODBC driver. An instance of CRecordset represents a set of records from the database. It can be initialized using a string representing an SQL statement.
Additional member functions let the program move from one record to another, edit the records, add new records to the set, then update the database. Instances of the CRecordView class provide a dialog-style view of individual records plus buttons for naviagating forward and backward through the record set.
A Basic Database Browser Application
Register the Database with Windows
A Browser without Add/Delete Record
Generating the Application
Use App Wizard to create an SDI application called Employee. Select "Database View without File Support".
In the Database Options dialog select the Department Store data source. Select the Employees table from the database tables dialog.
Accept all other options except printing support. The following classes are generated:
Add four edit controls labeled ID, NAME, RATE, and DEPARTMENT to the dialog associated with the view. Using Class Wizard, associate these controls with the corresponding variables in *m_pSet, the CRecordSet pointer in the view class. (Note: The pull down lists in the add variable dialog already list these variables.)
Build the Application
Now you can iterate through the Employee table using the application's menu or toolbar.
Adding and Deleting Records
Add add and delete record commands to the Record menu
Add add and delete record buttons to the tool bar
Add Handlers for the add and delete messages
Add a protected boolean variable called m_adding to the view class. The view constructor should initialize this to false.
void CEmployeeView::OnRecordDelete()
{
m_pSet->Delete();
m_pSet->MoveNext();
if (m_pSet->IsEOF()) m_pSet->MoveLast();
if (m_pSet->IsBOF()) m_pSet->SetFieldNull(NULL);
UpdateData(FALSE);
}
BOOL CEmployeeView::OnMove(UINT nIDMoveCommand)
{
if (m_adding)
{
m_adding = FALSE;
UpdateData(TRUE);
if (m_pSet->CanUpdate()) m_pSet->Update();
m_pSet->Requery();
UpdateData(FALSE);
CEdit* pCtrl = (CEdit*)GetDlgItem(IDC_ID);
pCtrl->SetReadOnly(TRUE);
return TRUE;
}
else
return CRecordView::OnMove(nIDMoveCommand);
}
Java Database Connectivity (JDBC) is a package consisting of eight interfaces:
The central class in JDBC is java.sql.DriverManager. It maintains a list of all available drivers. A driver knows how to create a connection to a particular database. Drivers can be a little hard to find. JDBC drivers for most well known databases are available for a price. If a database has an ODBC driver, then one can use a JDBC-ODBC bridge, which is provided free on Windows NT, but must be purchased for Sun platforms. Consult the javasoft web site for a list of drivers and bridges.
An application loads the driver manager class, then asks it for a database connection using the URL of the database. The driver manager searchs its list of drivers, and, if it finds a matching driver, asks the driver to create the connection:
We can collect these objects in a Database class encapsulating a connection and a statement. The constructor loads a driver manager, uses the driver manager to find a driver, uses the driver to establish a connection, then uses the connection to create a statement:
class Database {
private Connection myConn;
private Statement myStmt;
public Database(String dm, String url, String user, String
pswd) {
try {
Class.forName(dm); // loads a DriverManager class
myConn = DriverManager.getConnection(url, user, pswd);
myStmt = myConn.getStatement();
} //try
catch(SQLException sqlexp) {
Tools.error("connection failed: " + sqlexp);
}
catch (ClassNotFoundException cnfe) {
Tools.error("Failed to load driver; " + cnfe);
}
}
// etc.
} // Database
The principle method provided by the Database class is execute(). Given a standard SQL statement, represented as a String, it returns a result represented as a formatted string. The execute method uses private predicates to determine if its input is a select, update, or insert statement. In case it is an update or insert method, the treatement is the same: myStmt.executeUpdate(sql) is called and the number of rows affected is returned. In case the statement is a select statement, myStmt.executeQuery(sql) is called. A result set is returned. This is converted into a formatted string and returned:
private Connection myConn;
private Statement myStmt;
public Database(String dm, String url, String user, String
pswd) {
...
}
public String execute(String sql) {
try {
if (isSelect(sql)) {
ResultSet rs = myStmt.executeQuery(sql);
return formatResultSet(rs);
}
else if (isUpdate(sql) || isInsert(sql)) {
int rs = myStmt.executeUpdate(sql);
return "# of rows affected = " + rs;
}
else
Tools.error("unrecognized SQL: " + sql);
} // try
catch (SQLException sqle) {
Tools.error("Dbase access failure; " + sqle);
} // catch
catch(IOException ioe) {
Tools.error("Request read failure; " + ioe);
} // catch
} // execute
// etc.
private boolean isSelect(String stmt) {
return (stmt.substring(0, 6).equalsIgnoreCase("SELECT"));
}
private boolean isUpdate(String stmt) {
return stmt.substring(0, 6).equalsIgnoreCase("UPDATE");
}
private boolean isInsert(String stmt) {
return stmt.substring(0, 6).equals("INSERT");
}
} // Database
A result set represents a list of rows extracted from a table. We can iterate through these rows using the result set's next() method. Each row is a list of columns. Each column is a String.
A result set has an associated metadata object that tells us the number of colums and the name and type of each column. The Database class supplies a formatResultSet() method can be used to convert the result set into a table string suitable for stream input or tokenizing. The format of a table string is given by the EBNF:
private Connection myConn;
private Statement myStmt;
public Database(String dm, String url, String user, String
pswd) {
...
}
public String execute(String sql) { ... }
private String formatResultSet(ResultSet rs)
throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
int cc = rsmd.getColumnCount(); // = # of columns
String result = " table: ";
// iterate through each row
while(rs.next()) {
result = result + " row: ";
// iterate through each column
for(int i = 1; i <= cc; i++) {
String colName = " name: " + rsmd.getColumnName(i);
String colType = " type: " + rsmd.getColumnType(i);
String colVal = " value: " + rs.getString(i);
result = result + colName + colType + colVal;
} // for
} // while
return result;
} // formatResultSet
// etc.
} // Database
Assume an address book is represented by a table called People, which is in a database with URL: jdbc:odbc:ODBC_Addresses. Assume the fully qualified name of the driver manager class provided by Java is sun.jdbc.odbc.JdbcOdbcDriver. (This class manages a single JDBC-ODBC bridge.) Each row in the table represents one address book entry and consists of seven columns named:
public static void main(String[] args) {
String sql =
"SELECT FNAME, LNAME, ADDRESS, CITY, STATE, ZIP, PHONE
" +
"FROM People " +
"WHERE PHONE = " +
"'" + args[0] + "'\n";
Database db =
new Database("sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:ODBC_Addresses",
"smith", // owner
"foobar"); // password
Tools.cout.println(db.execute(sql));
}