Let’s begin with a simple example that shows you how to read data from a relational table. The CD-ROM includes a sample Microsoft Access database named Airline.mdb.
Even if you don’t have Access installed on your system, you can access the database so long as you have the proper ODBC driver installed. The driver is included with Microsoft Office, Microsoft Visual C++, and other products. Therefore, if you run Microsoft
Windows, it’s probably already installed on your Windows 9x/NT system. If not, you may be able to download it from Microsoft’s Web site.
The database contains three simple tables: Passenger, Flight, and Seat. These tables hold a subset of the data needed by the airline reservation system we use as an example throughout this book.
Copy the database from the CD-ROM to your system. In order to access the database via ODBC, you must configure a data source name (DSN). Here’s how to do so:
1. Open the Windows Control Panel and double-click the ODBC applet. This launches the ODBC Data Source Administrator. Click the User DSN tab (see Figure 12.1).
Figure 12.1: The ODBC Data Source Administrator lets you create DSNs.
2. Click the Add button to launch the Create New Data Source dialog box shown in Figure 12.2.
Figure 12.2: The Create New Data Source dialog box lets you identify the data source of your new DSN.
3. Select Microsoft Access Driver and click Finish. This launches the ODBC Microsoft Access 97 Setup dialog box. Type Airline as the data source name and then type a suitable description, as shown in Figure 12.3.
Figure 12.3: The ODBC Microsoft Access 97 Setup dialog box lets you associate a DSN with a database.
4. Click the Select button and then use the Select Database dialog box to select the file that contains your database. Click OK to return to the ODBC Microsoft Access 97 Setup dialog box. Click OK to return to the ODBC Data Source Administrator. Finally, click OK to exit. Your database is now ready for access via ODBC.
Using appletviewer, run the PassengerList applet by using the PassengerList.html file.
Type a SQL query in the Query field and press Enter. The applet displays the query
results in its Result field, as you can see in Figure 12.4.
Figure 12.4: The PassengerList applet lets you execute queries and view results.
Opening a Database Connection
Let’s see how the PassengerList applet works. Listing 12.1 shows its source code. Apart from the TextField and TextArea that comprise the applet’s user interface, the PassengerList class defines four fields, including three constants and a field that references a Connection.
LISTING 12.1 PassengerList.java —READING A DATABASE
import java.applet.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class PassengerList extends Applet {
static final String DB = "jdbc:odbc:airline";
static final String USER = "";
static final String PASSWORD = "";
TextField theQuery = new TextField();
TextArea theResult = new TextArea(20, 64);
Connection theConnection;
public void init() {
setLayout(new BorderLayout());
Panel p1 = new Panel();
add(p1, BorderLayout.NORTH);
add(theResult, BorderLayout.CENTER);
p1.setLayout(new GridLayout(0, 1));
p1.add(new Label("Query:"));
p1.add(theQuery);
p1.add(new Label("Result:"));
theResult.setEditable(false);
Font f = new Font("Monospaced", Font.PLAIN, 12);
theQuery.setFont(f);
theResult.setFont(f);
theQuery.addActionListener(new QueryHandler());
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
theConnection =
DriverManager.getConnection(DB, USER, PASSWORD);
}
catch (ClassNotFoundException ex1) { fatalError(ex1); } catch (SQLException ex2) { fatalError(ex2); }
}
public void fatalError(Exception ex) {
ex.printStackTrace();
}
// Inner class omitted }
The constants specify which database the program accesses and the user ID and password used to log on. The name of the database has the form of a special URL. The first component of the URL is the protocol name, jdbc . Every JDBC driver defines the structure of the remaining components of the URL. The JDBC-ODBC bridge uses the format jdbc:odbc:dsn , where dsn is the data source name that identifies the database.
The init method creates a Connection object and binds it to the field
theConnection . As you’ll see, the Connection object encapsulates the database.
JDBC programs send several types of messages to the Connection object.
Let’s explore the init method. It begins with the usual sort of code that establishes the applet’s user interface. The last part of the method includes a try-catch that opens a connection to the database. The static method Class.forName loads the named class into the JVM (if the class has not already been loaded). The init method uses the Class.forName method to load the JDBC-ODBC driver, the full class name of which is sun.jdbc.odbc.JdbcOdbcDriver . The init method then uses the static
DriverManager.getConnection method to open a connection to the database, which it stores in the field theConnection . The getConnection method checks the URL that specifies the database and selects an appropriate driver from among those loaded.
Several errors can occur while attempting to open the database. For example, the
JDBCODBC driver may not be present or the database may be inaccessible. Two catch statements deal with these possibilities by invoking a utility method that prints a stack trace that identifies the error. Because you launch appletviewer from a command window, it displays stack traces there.
Executing a SQL Statement
The inner class QueryHandler contains the statements that create and execute the SQL query and that display the results. Its actionPerformed method creates and generates the query, which is encapsulated within a Statement object obtained by sending the createStatement message to the Connection object obtained from DriverManager.getConnection in the init method. The Statement object understands several important messages, including the executeQuery message that sends a query to the database. The query is a String argument of the executeQuery message, which returns the results encapsulated within a ResultSet object.
The actionPerformed method uses the displayResult utility method to display the contents of the ResultSet. It then closes the Statement. This action frees
resources that are no longer needed. Notice that the database connection remains open:
By pressing Enter in the Query field, you can repeatedly invoke the actionPerformed method. See Listing 12.2.
LISTING 12.2 PassengerList.java —INNER CLASS
class QueryHandler implements ActionListener {
public void actionPerformed(ActionEvent evt) {
try {
String query = theQuery.getText();
Statement stmt = theConnection.createStatement();
ResultSet rs = stmt.executeQuery(query);
displayResult(rs);
stmt.close();
}
catch (SQLException ex) { fatalError(ex); } }
public void displayResult(ResultSet rs) {
try {
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
theResult.setText("");
while (rs.next()) {
theResult.append("\n");
for (int i = 1; i <= cols; i++) {
String text = rs.getString(i);
if (text == null) text = "";
theResult.append(text + ":");
} }
rs.close();
}
catch (SQLException ex) { fatalError(ex); } }
}
Displaying Query Results
The displayResult method obtains a ResultSetMetaData object by sending the getMetaData message to the ResultSet object. It uses the ResultSetMetaData object to determine the structure of the ResultSet that contains the query result. The ResultSetMetaData class supports many useful methods, which you’ll explore in the next section, “Learning About Result Sets: MetaData.” The displayResult method uses only one of these: getColumnCount , which returns a count of the number of columns (fields) in the ResultSet.
To access a row of the ResultSet, you send the ResultSet the next message. The ResultSet returns true if it found a row or false if you’ve accessed all the rows.
The while loop processes each row of the ResultSet. It uses the column count to control a for loop that iterates over the columns of the ResultSet. Most ODBC
database drivers require you to access columns sequentially. The for loop uses the getString message to obtain the value of each database column as a String. A family of get messages lets you get column values as other data types (see Table 12.1). However, most database data types can be conveniently converted to String, so it’s often expeditious to use getString, whatever the actual data type of the column.
TABLE 12.1 DATA ACCESS METHODS OF ResultSet
Method Meaning
getDouble(int col) Gets the value of a column as a double
getFloat(int col) Gets the value of a column as a float
getInt(int col) Gets the value of a column as an int
getLong(int col) Gets the value of a column as a long
getObject(int col) Gets the value of a column as an Object
getShort(int col) Gets the value of a column as a short
getString(int col) Gets the value of a column as a String
getTime(int col) Gets the value of a column as a Time
getTimeStamp(int col) Gets the value of a column as a TimeStamp
getUnicodeStream(int
col) Gets the value of a column as an InputStream of
Unicode characters
wasNull() Returns true if the last column accessed had the SQL value null
The displayResult method appends the value of each column to the contents of the TextArea. After processing all the rows, it closes the ResultSet. Notice that the method uses a try-catch to handle SQLException errors, which are thrown when the SQL interpreter finds an error or another problem occurs.