Manipulating Databases with JDBC

In this section, we present two examples. The first example introduces how to connect to a database and query the database. The second example demonstrates how to display the result of the query in a JTable.

25.8.1. Connecting to and Querying a Database

The example of Fig. 25.25 performs a simple query on the books database that retrieves the entire authors table and displays the data. The program illustrates connecting to the database, querying the database and processing the result. The following discussion presents the key JDBC aspects of the program. [Note: Section 25.5 demonstrates how to start the MySQL server, how to prepare the MySQL database and how to create the books database. The steps in Section 25.5 must be performed before executing the program of Fig. 25.25.]

Figure 25.25. Displaying the authors table from the books database.

(This item is displayed on pages 1208 - 1209 in the print version)

 1 // Fig. 25.25: DisplayAuthors.java
 2 // Displaying the contents of the authors table.
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 import java.sql.DriverManager;
 6 import java.sql.ResultSet;
 7 import java.sql.ResultSetMetaData;
 8 import java.sql.SQLException;
 9
10 public class DisplayAuthors
11 {
12 // JDBC driver name and database URL 
13 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
14 static final String DATABASE_URL = "jdbc:mysql://localhost/books";
15
16 // launch the application
17 public static void main( String args[] )
18 {
19 Connection connection = null; // manages connection
20 Statement statement = null; // query statement
21
22 // connect to database books and query database
23 try
24 {
25 Class.forName( JDBC_DRIVER ); // load database driver class
26
27 // establish connection to database 
28 connection = 
29  DriverManager.getConnection( DATABASE_URL, "jhtp6", "jhtp6" );
30
31 // create Statement for querying database
32 statement = connection.createStatement();
33
34 // query database 
35 ResultSet resultSet = statement.executeQuery( 
36  "SELECT authorID, firstName, lastName FROM authors" );
37
38 // process query results
39 ResultSetMetaData metaData = resultSet.getMetaData();
40 int numberOfColumns = metaData.getColumnCount(); 
41 System.out.println( "Authors Table of Books Database:" );
42
43 for ( int i = 1; i <= numberOfColumns; i++ )
44 System.out.printf( "%-8s	", metaData.getColumnName( i ) );
45 System.out.println();
46
47 while ( resultSet.next() )
48 {
49 for ( int i = 1; i <= numberOfColumns; i++ )
50 System.out.printf( "%-8s	", resultSet.getObject( i ) );
51 System.out.println();
52 } // end while
53 } // end try
54 catch ( SQLException sqlException )
55 {
56 sqlException.printStackTrace();
57 System.exit( 1 );
58 } // end catch
59 catch ( ClassNotFoundException classNotFound )
60 {
61 classNotFound.printStackTrace();
62 System.exit( 1 );
63 } // end catch
64 finally // ensure statement and connection are closed properly
65 { 
66  try 
67  { 
68  statement.close(); 
69  connection.close(); 
70  } // end try 
71  catch ( Exception exception ) 
72  { 
73  exception.printStackTrace(); 
74  System.exit( 1 ); 
75  } // end catch 
76 } // end finally 
77 } // end main
78 } // end class DisplayAuthors

 

Authors Table of Books Database:
authorID firstName lastName
1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Sean Santry

 

Lines 38 import the JDBC interfaces and classes from package java.sql used in this program. Line 13 declares a String constant that contains theMySQL JDBC driver's class name. The program will use this value to load the proper driver into memory. Line 14 declares a string constant for the database URL. This identifies the name of the database to connect to, as well as information about the protocol used by the JDBC driver (discussed shortly). Method main (lines 1776) connects to the books database, queries the database, displays the result of the query and closes the database connection.

The program must load the database driver before connecting to the database. Line 25 uses static method forName of class Class to load the class for the database driver. This line throws a checked exception of type java.lang.ClassNotFoundException if the class loader cannot locate the driver class. To avoid this exception, you need to include the mysql-connector-java-3.0.14-production-bin.jar (in the C:mysql-connector-java-3.0.14-production directory) in your program's classpath when you execute the program, as in:

[View full width]

java -classpath c:mysql-connector-java-3.0.14-productionmysql-connector-java-3.0 .14-production-bin.jar;. DisplayAuthors

In the above command, notice the period (.) before DisplayAuthors. If this period is missing, the JVM will not find the DisplayAuthors class file. You may also copy the mysql-connector-java-3.0.14-production-bin.jar file to the JRE's libext directory, e.g., C:Program FilesJavajdk1.5.0jrelibext. After doing so, you could run the application simply using the command java DisplayAuthors.

JDBC supports four categories of drivers: JDBC-to-ODBC bridge driver (Type 1), Native-API, partly Java driver (Type 2), Pure Java client to server driver (Type 3) and Pure Java driver (Type 4). A description of each driver type is shown in Fig. 25.26. The MySQL driver com.mysql.jdbc.Driver is a Type-4 driver.

Figure 25.26. JDBC driver types.

Type	Description
1	The JDBC-to-ODBC bridge driver connects Java programs to Microsoft ODBC (Open Database Connectivity) data sources. The Java 2 Software Development Kit from Sun Microsystems, Inc. includes the JDBC-to-ODBC Bridge driver (sun.jdbc.odbc.JdbcOdbcDriver). This driver typically requires the ODBC driver on the client computer and normally requires configuration of ODBC data sources. The Bridge driver was introduced primarily for development purposes, before other types of drivers were available, and should not be used for production applications.
2	Native-API, partly Java drivers enable JDBC programs to use database-specific APIs (normally written in C or C++) that allow client programs to access databases via the Java Native Interface (JNI). JNI is a bridge between a JVM and code written and compiled in a platform-specific language such as C or C++. Such code is known as native code. JNI enables Java applications to interact with native code. A Type 2 driver translates JDBC into database-specific calls. Type 2 drivers were introduced for reasons similar to the Type 1 ODBC bridge driver.
3	Pure Java client to server drivers take JDBC requests and translate them into a network protocol that is not database specific. These requests are sent to a server, which translates the database requests into a database-specific protocol.
4	Pure Java drivers implement database-specific network protocols, so that Java programs can connect directly to a database.

Software Engineering Observation 25.4

Most major database vendors provide their own JDBC database drivers, and many third-party vendors provide JDBC drivers as well. For more information on JDBC drivers, visit the Sun Microsystems JDBC Web site, servlet.java.sun.com/products/jdbc/drivers.

Software Engineering Observation 25.5

On the Microsoft Windows platform, most databases support access via Open Database Connectivity (ODBC). ODBC is a technology developed by Microsoft to allow generic access to disparate database systems on the Windows platform (and some UNIX platforms). The JDBC-to-ODBC Bridge allows any Java program to access any ODBC data source. The driver is class JdbcOdbcDriver in package sun.jdbc.odbc.

Lines 2829 of Fig. 25.25 creates a Connection object (package java.sql) referenced by connection. An object that implements interface Connection manages the connection between the Java program and the database. Connection objects enable programs to create SQL statements that access databases. The program initializes Connection with the result of a call to static method getConnection of class DriverManager (package java.sql), which attempts to connect to the database specified by its URL. Method getConnection takes three argumentsa String that specifies the database URL, a String that specifies the username and a String that specifies the password. The username and password are set in Section 25.6. If you used different username and password, you need to replace the username (second argument) and password (third argument) passed to method getConnection in line 29. The URL locates the database (possibly on a network or in the local file system of the computer). The URL jdbc:mysql://localhost/books specifies the protocol for communication (jdbc), the subprotocol for communication (mysql) and the location of the database (//localhost/books, where localhost is the name of the MySQL server host and books is the database name). The subprotocol mysql indicates that the program uses a MySQL-specific subprotocol to connect to the MySQL database. If the DriverManager cannot connect to the database, method getConnection tHRows a SQLException (package java.sql). Figure 25.27 lists the JDBC driver names and database URL formats of several popular RDBMSs.

Figure 25.27. Popular JDBC driver names and database URL.

RDBMS	JDBC driver name	Database URL format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname: port Number: databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2: hostname: portnumber/ databaseName
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds: hostname: portnumber/ databaseName

Software Engineering Observation 25.6

Most database management systems require the user to log in before accessing the database contents. DriverManager method getConnection is overloaded with versions that enable the program to supply the user name and password to gain access.

Line 32 invokes Connection method createStatement to obtain an object that implements interface Statement (package java.sql). The program uses the Statement object to submit SQL to the database.

Lines 3536 use the Statement object's executeQuery method to submit a query that selects all the author information from table authors. This method returns an object that implements interface ResultSet and contains the result of the query. The ResultSet methods enable the program to manipulate the query result.

Lines 3952 process the ResultSet. Line 39 obtains the metadata for the ResultSet as a ResultSetMetaData (package java.sql) object. The metadata describes the ResultSet's contents. Programs can use metadata programmatically to obtain information about the ResultSet's column names and types. Line 40 uses ResultSetMetaData method getColumnCount to retrieve the number of columns in the ResultSet. Lines 4243 display the column names.

Software Engineering Observation 25.7

Metadata enables programs to process ResultSet contents dynamically when detailed information about the ResultSet is not known in advance.

Lines 4752 display the data in each ResultSet row. Before processing the ResultSet, the program positions the ResultSet cursor to the first row in the ResultSet with method next (line 47). The cursor points to the current row. Method next returns boolean value true if it is able to position to the next row; otherwise the method returns false.

Common Programming Error 25.8

Initially, a ResultSet cursor is positioned before the first row. Attempting to access a ResultSet's contents before positioning the ResultSet cursor to the first row with method next causes a SQLException.

If there are rows in the ResultSet, line 50 extracts the contents of one column in the current row. When processing a ResultSet, it is possible to extract each column of the ResultSet as a specific Java type. In fact, ResultSetMetaData method getColumnType returns a constant integer from class Types (package java.sql) indicating the type of a specified column. Programs can use these values in a switch statement to invoke ResultSet methods that return the column values as appropriate Java types. If the type of a column is Types.INT, ResultSet method getInt returns the column value as an int. ResultSet get methods typically receive as an argument either a column number (as an int) or a column name (as a String) indicating which column's value to obtain. Visit

java.sun.com/j2se/5.0/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html

for detailed mappings of SQL data types to Java types and to determine the appropriate ResultSet method to call for each SQL data type.

Performance Tip 25.1

If a query specifies the exact columns to select from the database, the ResultSet contains the columns in the specified order. In this case, using the column number to obtain the column's value is more efficient than using the column name. The column number provides direct access to the specified column. Using the column name requires a linear search of the column names to locate the appropriate column.

For simplicity, this example treats each value as an Object. The program retrieves each column value with ResultSet method getObject (line 50) and prints the String representation of the Object. Note that, unlike array indices, which start at 0, ResultSet column numbers start at 1. The finally block (lines 6476) closes the Statement (line 68) and the database Connection (line 69).

Common Programming Error 25.9

Specifying column number 0 when obtaining values from a ResultSet causes a SQLException.

Common Programming Error 25.10

Attempting to manipulate a ResultSet after closing the Statement that created the ResultSet causes a SQLException. The program discards the ResultSet when the corresponding Statement is closed.

Software Engineering Observation 25.8

Each Statement object can open only one ResultSet object at a time. When a Statement returns a new ResultSet, the Statement closes the prior ResultSet. To use multiple ResultSets in parallel, separate Statement objects must return the ResultSets.

 

25.8.2. Querying the books Database

The next example (Fig. 25.28 and Fig. 25.31) allows the user to enter any query into the program. The example displays the result of a query in a JTable, using a TableModel object to provide the ResultSet data to the JTable. Class ResultSetTableModel (Fig. 25.28) performs the connection to the database and maintains the ResultSet. Class DisplayQueryResults (Fig. 25.31) creates the GUI and specifies an instance of class ResultSetTableModel to provide data for the JTable.

Figure 25.28. ResultSetTableModel enables a JTable to display the contents of a ResultSet.

(This item is displayed on pages 1214 - 1217 in the print version)

 1 // Fig. 25.28: ResultSetTableModel.java
 2 // A TableModel that supplies ResultSet data to a JTable.
 3 import java.sql.Connection;
 4 import java.sql.Statement;
 5 import java.sql.DriverManager;
 6 import java.sql.ResultSet;
 7 import java.sql.ResultSetMetaData;
 8 import java.sql.SQLException;
 9 import javax.swing.table.AbstractTableModel;
10
11 // ResultSet rows and columns are counted from 1 and JTable
12 // rows and columns are counted from 0. When processing
13 // ResultSet rows or columns for use in a JTable, it is
14 // necessary to add 1 to the row or column number to manipulate
15 // the appropriate ResultSet column (i.e., JTable column 0 is
16 // ResultSet column 1 and JTable row 0 is ResultSet row 1).
17 public class ResultSetTableModel extends AbstractTableModel
18 {
19 private Connection connection;
20 private Statement statement;
21 private ResultSet resultSet;
22 private ResultSetMetaData metaData;
23 private int numberOfRows;
24 
25 // keep track of database connection status 
26 private boolean connectedToDatabase = false;
27 
28 // constructor initializes resultSet and obtains its meta data object;
29 // determines number of rows
30 public ResultSetTableModel( String driver, String url,
31 String username, String password, String query )
32 throws SQLException, ClassNotFoundException
33 {
34 // load database driver class
35 Class.forName( driver );
36 
37 // connect to database
38 connection = DriverManager.getConnection( url, username, password );
39 
40 // create Statement to query database 
41 statement = connection.createStatement( 
42  ResultSet.TYPE_SCROLL_INSENSITIVE, 
43  ResultSet.CONCUR_READ_ONLY ); 
44 
45 // update database connection status
46 connectedToDatabase = true; 
47 
48 // set query and execute it
49 setQuery( query );
50 } // end constructor ResultSetTableModel
51 
52 // get class that represents column type
53 public Class getColumnClass( int column ) throws IllegalStateException
54 {
55 // ensure database connection is available 
56 if ( !connectedToDatabase ) 
57  throw new IllegalStateException( "Not Connected to Database" );
58 
59 // determine Java class of column
60 try
61 {
62 String className = metaData.getColumnClassName( column + 1 );
63 
64 // return Class object that represents className
65 return Class.forName( className ); 
66 } // end try
67 catch ( Exception exception )
68 {
69 exception.printStackTrace();
70 } // end catch
71 
72 return Object. class; // if problems occur above, assume type Object
73 } // end method getColumnClass
74 
75 // get number of columns in ResultSet
76 public int getColumnCount() throws IllegalStateException
77 {
78 // ensure database connection is available
79 if ( !connectedToDatabase )
80 throw new IllegalStateException( "Not Connected to Database" );
81 
82 // determine number of columns
83 try
84 {
85 return metaData.getColumnCount();
86 } // end try
87 catch ( SQLException sqlException )
88 {
89 sqlException.printStackTrace();
90 } // end catch
91 
92 return 0; // if problems occur above, return 0 for number of columns
93 } // end method getColumnCount
94 
95 // get name of a particular column in ResultSet
96 public String getColumnName( int column ) throws IllegalStateException
97 {
98 // ensure database connection is available
99 if ( !connectedToDatabase )
100 throw new IllegalStateException( "Not Connected to Database" );
101 
102 // determine column name
103 try
104 {
105 return metaData.getColumnName( column + 1 );
106 } // end try
107 catch ( SQLException sqlException )
108 {
109 sqlException.printStackTrace();
110 } // end catch
111 
112 return ""; // if problems, return empty string for column name
113 } // end method getColumnName
114 
115 // return number of rows in ResultSet
116 public int getRowCount() throws IllegalStateException
117 {
118 // ensure database connection is available
119 if ( !connectedToDatabase )
120 throw new IllegalStateException( "Not Connected to Database" );
121 
122 return numberOfRows;
123 } // end method getRowCount
124 
125 // obtain value in particular row and column
126 public Object getValueAt( int row, int column )
127 throws IllegalStateException
128 {
129 // ensure database connection is available
130 if ( !connectedToDatabase )
131 throw new IllegalStateException( "Not Connected to Database" );
132 
133 // obtain a value at specified ResultSet row and column
134 try
135 {
136 resultSet.absolute( row + 1 );
137 return resultSet.getObject( column + 1 );
138 } // end try
139 catch ( SQLException sqlException )
140 {
141 sqlException.printStackTrace();
142 } // end catch
143 
144 return ""; // if problems, return empty string object
145 } // end method getValueAt
146 
147 // set new database query string
148 public void setQuery( String query )
149 throws SQLException, IllegalStateException
150 {
151 // ensure database connection is available
152 if ( !connectedToDatabase )
153 throw new IllegalStateException( "Not Connected to Database" );
154 
155 // specify query and execute it
156 resultSet = statement.executeQuery(query);
157 
158 // obtain meta data for ResultSet
159 metaData = resultSet.getMetaData();
160 
161 // determine number of rows in ResultSet
162 resultSet.last(); // move to last row
163 numberOfRows = resultSet.getRow(); // get row number
164 
165 // notify JTable that model has changed
166 fireTableStructureChanged(); 
167 } // end method setQuery
168 
169 // close Statement and Connection 
170 public void disconnectFromDatabase() 
171 { 
172  if (!connectedToDatabase) 
173   return; 
174 
175  // close Statement and Connection 
176  try 
177  { 
178  statement.close(); 
179  connection.close(); 
180  } // end try 
181  catch ( SQLException sqlException ) 
182  { 
183  sqlException.printStackTrace(); 
184  } // end catch 
185  finally // update database connection status
186  { 
187  connectedToDatabase = false; 
188  } // end finally 
189 } // end method disconnectFromDatabase 
190 } // end class ResultSetTableModel

Class ResultSetTableModel (Fig. 25.28) extends class AbstractTableModel (package javax.swing.table), which implements interface TableModel. Class ResultSetTableModel overrides TableModel methods getColumnClass, getColumnCount, getColumnName, getrowCount and getValueAt. The default implementations of TableModel methods isCellEditable and setValueAt (provided by AbstractTableModel) are not overridden, because this example does not support editing the JTable cells. The default implementations of TableModel methods addTableModelListener and removeTableModelListener (provided by AbstractTableModel) are not overridden, because the implementations of these methods in AbstractTableModel properly add and remove event listeners.

The ResultSetTableModel constructor (lines 3050) accepts five String argumentsthe driver class name, the URL of the database, the username, the password and the default query to perform. The constructor throws any exceptions that occur in its body back to the application that created the ResultSetTableModel object, so that the application can determine how to handle the exception (e.g., report an error and terminate the application). Line 35 loads the database driver. Line 38 establishes a connection to the database. Lines 4143 invoke Connection method createStatement to create a Statement object. This example uses a version of method createStatement that takes two argumentsthe result set type and the result set concurrency. The result set type (Fig. 25.29) specifies whether the ResultSet's cursor is able to scroll in both directions or forward only and whether the ResultSet is sensitive to changes. ResultSets that are sensitive to changes reflect those changes immediately after they are made with methods of interface ResultSet. If a ResultSet is insensitive to changes, the query that produced the ResultSet must be executed again to reflect any changes made. The result set concurrency (Fig. 25.30) specifies whether the ResultSet can be updated with ResultSet's update methods. This example uses a ResultSet that is scrollable, insensitive to changes and read only. Line 49 invokes ResultSetTableModel method setQuery (lines 148167) to perform the default query.

Portability Tip 25.5

Some JDBC drivers do not support scrollable ResultSets. In such cases, the driver typically returns a ResultSet in which the cursor can move only forward. For more information, see your database driver documentation.

Portability Tip 25.6

Some JDBC drivers do not support updatable ResultSets. In such cases, the driver typically returns a read-only ResultSet. For more information, see your database driver documentation.

Common Programming Error 25.11

Attempting to update a ResultSet when the database driver does not support updatable ResultSets causes SQLExceptions.

Figure 25.29. ResultSet constants for specifying ResultSet type.

ResultSet static type constant	Description
TYPE_FORWARD_ONLY
	Specifies that a ResultSet's cursor can move only in the forward direction (i.e., from the first row to the last row in the ResultSet).
TYPE_SCROLL_INSENSITIVE
	Specifies that a ResultSet's cursor can scroll in either direction and that the changes made to the ResultSet during ResultSet processing are not reflected in the ResultSet unless the program queries the database again.
TYPE_SCROLL_SENSITIVE
	Specifies that a ResultSet's cursor can scroll in either direction and that the changes made to the ResultSet during ResultSet processing are reflected immediately in the ResultSet.

Figure 25.30. ResultSet constants for specifying result properties.

ResultSet static concurrency constant	Description
CONCUR_READ_ONLY	Specifies that a ResultSet cannot be updated (i.e., changes to the ResultSet contents cannot be reflected in the database with ResultSet's update methods).
CONCUR_UPDATABLE	Specifies that a ResultSet can be updated (i.e., changes to the ResultSet contents can be reflected in the database with ResultSet's update methods).

Common Programming Error 25.12

Attempting to move the cursor backwards through a ResultSet when the database driver does not support backwards scrolling causes a SQLException.

Method getColumnClass (lines 5373) returns a Class object that represents the superclass of all objects in a particular column. The JTable uses this information to configure the default cell renderer and cell editor for that column in the JTable. Line 62 uses ResultSetMetaData method getColumnClassName to obtain the fully qualified class name for the specified column. Line 65 loads the class and returns the corresponding Class object. If an exception occurs, the catch in lines 6770 prints a stack trace and line 72 returns Object.classthe Class instance that represents class Objectas the default type. [Note: Line 62 uses the argument column + 1. Like arrays, JTable row and column numbers are counted from 0. However, ResultSet row and column numbers are counted from 1. Thus, when processing ResultSet rows or columns for use in a JTable, it is necessary to add 1 to the row or column number to manipulate the appropriate ResultSet row or column.]

Method getColumnCount (lines 7693) returns the number of columns in the model's underlying ResultSet. Line 85 uses ResultSetMetaData method getColumnCount to obtain the number of columns in the ResultSet. If an exception occurs, the catch in lines 8790 prints a stack trace and line 92 returns 0 as the default number of columns.

Method getColumnName (lines 96113) returns the name of the column in the model's underlying ResultSet. Line 105 uses ResultSetMetaData method getColumnName to obtain the column name from the ResultSet. If an exception occurs, the catch in lines 107110 prints a stack trace and line 112 returns the empty string as the default column name.

Method getrowCount (lines 116123) returns the number of rows in the model's underlying ResultSet. When method setQuery (lines 148167) performs a query, it stores the number of rows in variable numberOfRows.

Method getValueAt (lines 126145) returns the Object in a particular row and column of the model's underlying ResultSet. Line 136 uses ResultSet method absolute to position the ResultSet cursor at a specific row. Line 137 uses ResultSet method getObject to obtain the Object in a specific column of the current row. If an exception occurs, the catch in lines 139142 prints a stack trace and line 144 returns an empty string as the default value.

Method setQuery (lines 148167) executes the query it receives as an argument to obtain a new ResultSet (line 156). Line 159 gets the ResultSetMetaData for the new ResultSet. Line 162 uses ResultSet method last to position the ResultSet cursor at the last row in the ResultSet. Line 163 uses ResultSet method getrow to obtain the row number for the current row in the ResultSet. Line 166 invokes method fireTableStructureChanged (inherited from class AbstractTableModel) to notify any JTable using this ResultSetTableModel object as its model that the structure of the model has changed. This causes the JTable to repopulate its rows and columns with the new ResultSet data. Method setQuery tHRows any exceptions that occur in its body back to the application that invoked setQuery.

Method disconnectFromDatabase (lines 170189) implements an appropriate termination method for class ResultSetTableModel. A class designer should provide a public method that clients of the class must invoke explicitly to free resources that an object has used. In this case, method disconnectFromDatabase closes the database statement and connection (lines 178179), which are considered limited resources. Clients of the ResultSetTableModel class should always invoke this method when the instance of this class is no longer needed. Before releasing resources, line 172 verifies whether the connection is already terminated. If so, the method simply returns. In addition, note that each other method in the class throws an IllegalStateException if the boolean field connectedToDatabase is false. Method disconnectFromDatabase sets connectedToDatabase to false (line 184) to ensure that clients do not use an instance of ResultSetTableModel after that instance has already been terminated. IllegalStateException is an exception from the Java libraries that is appropriate for indicating this error condition.

The DisplayQueryResults (Fig. 25.31) constructor (lines 34140) creates a ResultSetTableModel object and the GUI for the application. Lines 2225 and 28 declare the database driver class name, database URL, username, password and default query that are passed to the ResultSetTableModel constructor to make the initial connection to the database and perform the default query. Line 64 creates the JTable object and passes a ResultSetTableModel object to the JTable constructor, which then registers the JTable as a listener for TableModelEvents generated by the ResultSetTableModel. Lines 71110 register an event handler for the submitButton that the user clicks to submit a query to the database. When the user clicks the button, method actionPerformed (lines 76108) invokes ResultSetTableModel method setQuery to execute the new query. If the user's query fails (e.g., because of a syntax error in the user's input), lines 9394 execute the default query. If the default query also fails, there could be a more serious error, so line 103 ensures that the database connection is closed and line 105 exits the program. The screen captures in Fig. 25.31 show the results of two queries. The first screen capture shows the default query that retrieves all the data from table authors of database books. The second screen capture shows a query that selects each author's first name and last name from the authors table and combines that information with the title and edition number from the titles table. Try entering your own queries in the text area and clicking the Submit Query button to execute the query.

Figure 25.31. DisplayQueryResults for querying database books.

(This item is displayed on pages 1220 - 1223 in the print version)

 1 // Fig. 25.31: DisplayQueryResults.java
 2 // Display the contents of the Authors table in the
 3 // Books database.
 4 import java.awt.BorderLayout;
 5 import java.awt.event.ActionListener;
 6 import java.awt.event.ActionEvent;
 7 import java.awt.event.WindowAdapter;
 8 import java.awt.event.WindowEvent;
 9 import java.sql.SQLException;
10 import javax.swing.JFrame;
11 import javax.swing.JTextArea;
12 import javax.swing.JScrollPane;
13 import javax.swing.ScrollPaneConstants;
14 import javax.swing.JTable;
15 import javax.swing.JOptionPane;
16 import javax.swing.JButton;
17 import javax.swing.Box;
18 
19 public class DisplayQueryResults extends JFrame
20 {
21 // JDBC driver and database URL
22 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
23 static final String DATABASE_URL = "jdbc:mysql://localhost/books";
24 static final String USERNAME= "jhtp6";
25 static final String PASSWORD= "jhtp6";
26 
27 // default query selects all rows from authors table
28 static final String DEFAULT_QUERY = "SELECT * FROM authors";
29 
30 private ResultSetTableModel tableModel;
31 private JTextArea queryArea;
32 
33 // create ResultSetTableModel and GUI
34 public DisplayQueryResults()
35 {
36 super ( "Displaying Query Results" );
37 
38 // create ResultSetTableModel and display database table
39 try
40 {
41 // create TableModel for results of query SELECT * FROM authors 
42 tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL,
43  USERNAME, PASSWORD, DEFAULT_QUERY ); 
44 
45 // set up JTextArea in which user types queries
46 queryArea = new JTextArea( DEFAULT_QUERY, 3, 100 );
47 queryArea.setWrapStyleWord( true );
48 queryArea.setLineWrap( true );
49 
50 JScrollPane scrollPane = new JScrollPane(queryArea,
51 ScrollPaneConstants. VERTICAL_SCROLLBAR_AS_NEEDED,
52 ScrollPaneConstants. HORIZONTAL_SCROLLBAR_NEVER );
53 
54 // set up JButton for submitting queries
55 JButton submitButton = new JButton( "Submit Query" );
56 
57 // create Box to manage placement of queryArea and
58 // submitButton in GUI
59 Box box = Box.createHorizontalBox();
60 box.add( scrollPane );
61 box.add( submitButton );
62 
63 // create JTable delegate for tableModel 
64 JTable resultTable = new JTable( tableModel );
65 
66 // place GUI components on content pane
67 add( box, BorderLayout. NORTH );
68 add( new JScrollPane( resultTable ), BorderLayout. CENTER );
69 
70 // create event listener for submitButton
71 submitButton.addActionListener(
72 
73 new ActionListener()
74 {
75 // pass query to table model
76 public void actionPerformed( ActionEvent event )
77 {
78 // perform a new query
79 try
80 {
81 tableModel.setQuery(queryArea.getText() );
82 } // end try
83 catch ( SQLException sqlException )
84 {
85 JOptionPane.showMessageDialog( null,
86 sqlException.getMessage(), "Database error",
87 JOptionPane.ERROR_MESSAGE );
88 
89 // try to recover from invalid user query
90 // by executing default query
91 try
92 {
93 tableModel.setQuery( DEFAULT_QUERY );
94 queryArea.setText( DEFAULT_QUERY );
95 } // end try
96 catch ( SQLException sqlException2 )
97 {
98 JOptionPane.showMessageDialog( null,
99 sqlException2.getMessage(), "Database error",
100 JOptionPane.ERROR_MESSAGE );
101 
102 // ensure database connection is closed
103 tableModel.disconnectFromDatabase(); 
104 
105 System.exit( 1 ); // terminate application
106 } // end inner catch
107 } // end outer catch
108 } // end actionPerformed
109 } // end ActionListener inner class
110 ); // end call to addActionListener
111 
112 setSize( 500, 250 ); // set window size
113 setVisible( true ); // display window
114 } // end try
115 catch ( ClassNotFoundException classNotFound )
116 {
117 JOptionPane.showMessageDialog( null,
118 "MySQL driver not found", "Driver not found",
119 JOptionPane.ERROR_MESSAGE );
120 
121 System.exit( 1 ); // terminate application
122 } // end catch
123 catch ( SQLException sqlException )
124 {
125 JOptionPane.showMessageDialog( null, sqlException.getMessage(),
126 "Database error", JOptionPane.ERROR_MESSAGE );
127 
128 // ensure database connection is closed
129 tableModel.disconnectFromDatabase(); 
130 
131 System.exit( 1 ); // terminate application
132 } // end catch
133 
134 // dispose of window when user quits application (this overrides
135 // the default of HIDE_ON_CLOSE)
136 setDefaultCloseOperation( DISPOSE_ON_CLOSE );
137 
138 // ensure database connection is closed when user quits application
139 addWindowListener(
140 
141 new WindowAdapter()
142 {
143 // disconnect from database and exit when window has closed
144 public void windowClosed( WindowEvent event ) 
145 { 
146  tableModel.disconnectFromDatabase(); 
147  System.exit( 0 ); 
148 } // end method windowClosed
149 } // end WindowAdapter inner class
150 ); // end call to addWindowListener
151 } // end DisplayQueryResults constructor
152 
153 // execute application
154 public static void main(String args[])
155 {
156 new DisplayQueryResults();
157 } // end main
158 } // end class DisplayQueryResults