oreilly.com: Making SQL Queries with JDBC and Displaying Results with Swing

Published on O'Reilly (http://www.oreilly.com/)
http://java.oreilly.com/news/javaex_1000.html
See this if you're having trouble printing code examples

Making SQL Queries with JDBC and Displaying Results with Swing

by David Flanagan
10/19/2000

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.

Figure 1. Query results displayed in a Swing JTable component

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.

The JDBC Driver

The JDBC API is vendor-neutral. You can use it to communicate with any database server, as long as someone has created a conforming JDBC driver for that server. For instance, one commonly used database is MySql; there is an open-source JDBC driver for that database available from www.worldserver.com/mm.mysql/.

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.

Connecting to the Database

Once you've set up your system by obtaining and installing your JDBC driver, you're ready to begin writing JDBC programs. The first step in any JDBC program is to establish a connection to the database. In order to do this, you need several pieces of information:

The name of the JDBC driver class for your database. The JDBC driver JAR or Zip file you just installed contains a bunch of Java classes; only one of these is the Driver itself--the rest are all part of the driver implementation. If you're using MySql, the name of the driver is org.gjt.mm.mysql.Driver.
The address of the database to connect to. This is specified using a URL-like syntax. The details of the syntax depend on the driver you're using. A MySql database might be named: jdbc:mysql://dbserver.domain.com/employees, for example.
A username and password for access to the database.

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

Executing a SQL Query

Establishing the database connection is the most confusing part of using JDBC. Once you've done that, things are easy. Take a look now at the getResultSetTableModel() method of Example 1. This method shows how we use the Connection object to create a Statement object, and how we then use the Statement object to execute a SQL query and obtain a ResultSet object. As the name of the method implies, the ResultSet object is used to create a ResultSetTableModel object, which is returned.

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.

Working with the ResultSet

The ResultSet object contains the results of the SQL query we made with the Statement object. Working with a ResultSet is easy. We use getObject() to retrieve the value of a numbered column. You can also retrieve values by column name, and you can use a variety of other methods (such as getDate() and getInt() to get column values by their specific type). Use next() to move from row to row of the result set. next() returns false when there are no more rows. In this example, we specified that we wanted a scrollable result set (this is a JDBC 2.0 feature) so we're also able to use first(), last(), absolute(), and relative() to jump around to any numbered row of the ResultSet.

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

Putting it all Together

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 jdbc:mysql://db.mydomain.com/mydbname david SecretPassword1234

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

Download the source code.

David Flanagan is a computer programmer who spends most of his time writing about Java. His other books with O'Reilly & Associates include the best-selling Java in a Nutshell, Java Foundation Classes in a Nutshell, Java Enterprise in a Nutshell, Java Power Reference, JavaScript: The Definitive Guide, and JavaScript Pocket Reference. David has a degree in computer science and engineering from the Massachusetts Institute of Technology. He lives with his partner, Christie, in the U.S. Pacific Northwest between the cities of Seattle, Washington and Vancouver, British Columbia.

O'Reilly & Associates recently released Java Examples in a Nutshell, 2nd Edition.

Sample Chapter 19, XML, is available free online.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.