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
|
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
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.
|
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.
|
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. |
|
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. |
|
|
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
|