|Sign In/My Account | View Cart|
The java.sql package defines the JDBC API for sending SQL queries and update statements to a database, and for obtaining query results. This article uses examples that did not fit in the second edition of my book, Java Examples in a Nutshell, to demonstrate how to use JDBC to connect to a database and obtain a java.sql.Connection object, how to execute a SQL query using a java.sql.Statement object, and how to work with the query results returned in a java.sql.ResultSet object.
The javax.swing package contains the Swing graphical user interface components. One of the most powerful of these components is JTable, which as its name suggests, displays tables. JTable can display any type of tabular data as long as it implements the javax.swing.table.TableModel interface. The examples in this article include a TableModel implementation that interprets a JDBC ResultSet object, so that the queried data can be displayed by a JTable, as shown in Figure 1.
This article follows the style of Java Examples in a Nutshell: the examples are well commented and speak for themselves. The code is intended for intermediate-level Java programmers, and I don't try to explain every nuance in this limited space. Study the code carefully, and have a Java API reference at hand, so you can look up the details of the classes and methods we use. You may be interested in the books Java Enterprise in a Nutshell for details about JDBC, and Java Foundation Classes in a Nutshell for details about Swing.
Note that the examples require Java 1.2 or later. You can download the source code to the examples.
Before beginning a JDBC program, you'll have to decide what database (or databases) you'll be using, and obtain appropriate JDBC drivers. Each driver is usually packaged in a JAR or Zip file, which you must install somewhere on your system and include in your CLASSPATH environment variable.
Some programs will hardcode this information. Others will read it from some external source, such as a java.util.Properties file, or will ask the user to enter it using a GUI dialog box. In the examples here, we'll simply require the user to provide this information on the command line when running the Java program.
The first step in connecting to the database is to register the JDBC driver class with the java.sql.DriverManager. You can do this explicitly yourself by calling DriverManager.registerDriver(), but by convention, JDBC drivers register themselves when the driver class is first loaded. Thus, all you have to do is force your driver class to be loaded, which can be done by calling the Class.forName() method. This technique makes it very easy to write JDBC programs that do not hardcode the driver name, and can work with database servers from any vendor.
Once you have registered the driver with the DriverManager, you simply pass the URL of your database, along with the username and password to the DriverManager.getConnection() method, which (if everything goes well) returns a Connection object representing the database connection.
Take a look now at Example 1. This class, ResultSetTableModelFactory encapsulates a database connection. The constructor method performs the connection setup steps we've just described.
Example 1. ResultSetTableModelFactory.java
If you're doing database updates instead of queries, you'll use executeUpdate(), instead of executeQuery(). If you'll be executing many similar SQL statements, you'll probably want to use PreparedStatement instead of Statement. If you're working with stored procedures, you'll want to use CallableStatement.
Example 2 shows the code for ResultSetTableModel, a class that takes a ResultSet and implements the TableModel interface to adapt the ResultSet for display by a JTable. Read Example 2 over carefully, paying particular attention to the constructor and to the getColumnName() and getValueAt() methods. These methods show how you can work with the ResultSet object, and also introduce the ResultSetMetaData class that provides additional information about the results.
Example 2. ResultSetTableModel.java
Let's review: Our ResultSetTableModelFactory class encapsulates a connection to a database. It uses that Connection to process SQL queries and return ResultSetTableModel objects. These objects encapsulate query results in a way that allows them to be easily "plugged in" to a Swing JTable component for display. Example 3 shows the QueryFrame class, a simple Swing program that uses ResultSetTableModel with a JTable component. This is the program that was used to produce Figure 1.
QueryFrame creates a new window that contains a JTextField object into which the user types a query, and a JTable object that displays the query results. The main() method is where the program starts: it reads the JDBC driver class name, the database URL, and the username and password from the command line, and uses these arguments to create a ResultSetTableModelFactory object. It passes this factory object to the QueryFrame constructor, and then pops up the QueryFrame() window it has created. Run the program with a command line like the following, which specifies the arguments necessary to connect to a hypothetical MySql database. Note that this is a single long command line that has been wrapped on to multiple lines for display:
java QueryFrame org.gjt.mm.mysql.Driver
The QueryFrame() constructor is a good example of how to create a simple Swing GUI: it creates components, places them in the window, and connects them with an event listener that is triggered when the user enters a query and hits the ENTER key. displayQueryResults() is the other interesting method: it takes a query string, and uses the factory object to obtain a ResultSetTableModel for the query. It then simply passes this object to the setModel() method of the JTable component. In case there is a syntax error in the query or something else goes wrong, this method displays a dialog box to notify the user of the problem. Other points of note in Example 3 are the use of anonymous inner classes and of the EventQueue.invokeLater() method.
Example 3. QueryFrame.java
O'Reilly & Associates recently released Java Examples in a Nutshell, 2nd Edition.