Intro (Page 1)

Download Swing

Swing JavaDoc APIs

JDK 1.1.4 Documentation
JDK 1.1.4 Download

JFC Web Site

README

This release of Swing features a completely rewritten and streamlined JTable class that offers -- among many other things -- a mechanism for viewing and manipulating data stored in local or remote relational databases. When you create a table component from the JTable class, you can use JDBC -- the JDK's database-interface mechanism -- to connect your table to virtually any kind of relational database management system, including Oracle, Sybase, Informix, IBM DB2, and more.

Because JDBC is connectable with ODBC-based databases, you can also use JTable components to connect your applications to local or remote ODBC databases such as Microsoft Access and Microsoft SQL Server, .

And that's not all. As soon as you connect a JTable component to a database using JDBC, you can create other Swing components -- such as text fields, combo boxes, checkboxes -- and connect those components to databases, too.

Everything is Data-Aware in Swing

It's important to point out that in Swing, there's no need for a separate category of "data-aware" controls. That's because everything in Swing is data-aware. Because Swing has event notification built into every component, you don't have to do anything special to connect a table (or any other kind of Swing control) to a piece of data that may change. All you have to do is instantiate your control and then write (or copy) a small amount of code to provide it with database connectivity.

Once you've done that, you can use either Swing methods or standard SQL commands to view and manipulate the data stored in local and remote databases.

The secret behind Swing's database connectivity is the JDBC (Java database connectivity) mechanism, which is explained in detail in a new book from JavaSoft named JDBC Database Connectivity with Java. That volume, published by Addison-Wesley, was written Graham Hamilton, Rick Cattell, and Maydene Fisher. You can buy a copy at any bookstore with an adequately stocked computer-book section.

But you don't have to read JDBC Database Connectivity with Java to learn how to start connecting your Swing applications to relational databases. To show you how easy it is to combine Swing with JDBC, this release of Swing offers several example programs that show you how to view and manipulate data stored in databases using JDBC and Swing.

Most of this chapter is devoted to showing you how to compile and execute these example programs and how to use them to connect with databases and to work with database data. Once you have seen what these programs can do, we'll examine the code that makes them work and investigate all the database magic that makes it all happen.

The TableExample2 Program

The most important sample program that we'll be looking at is named TableExample2. It's a short but powerful sample application that shows you exactly how JTable's database connectivity works. With the TableExample2 program, you can easily connect to any popular variety of database -- such as Sybase, Oracle, Informix, DB2, Microsoft access, or MS SQL Server. Then you can retrieve, view, and manipulate data from tables stored in the database you have selected.

Because the TableExample2 application can access any major variety of relational database, the way you set up your system to use it depends upon what kind of database you want to access. In the sections that follow, we'll explain exactly how you can use the TableExample2 program to connect to data stored in three popular kinds of relational databases:

Sybase
Microsoft Access
Microsoft SQL Server

Once you see how Swing can access those three kinds of databases, you'll also know how to connect your Swing applications to any other major brand of database system.

Accessing a Remote Database with JDBC and Swing

Before you can connect to a remote RDBMS such as a Sybase, Oracle, Informix, or IBM DB2, you need to have access to a computer system that's configured to run the database that you're interested in. You'll also need an account that allows you to connect to your company's RDBMS; if you don't have one, you may have to obtain one from your friendly database administrator (DBA).

Once your RDBMS account is set up, you can compile the TableExample2 application provided by Wing by navigating to Swing's examples\Table directory and entering the command

   javac TableExample2.java

When you have compiled the TableExample2 program, you can run it by executing the command

   java TableExample2

When the TableExample2 program starts, it displays a login window like the one shown in the following screen shot:

Database URLs

When the TableExample2 app displays its login window, it expects you to enter whatever information is required to connect to the database you want to access, unless the correct information is already there.

There are two ways to change the information that appears in the TableExample2 application's login window. You can change it interactively, by simply typing new entries, or you can open the program's source code in a text editor and change the default values that are displayed in the window.

The preceding picture illustrates what the TableExample2 program displays by default in its login window. As the illustration shows, the program assumes that a user who is named "guest" and has the password "trustworthy" is attempting to connect with a Sybase database named pubs2. The program also assumes that the URL of the database being accessed is

   jdbc:sybase://dbtest:1455/pubs2

and that the name of the database driver that is being used is

   connect.sybase.SybaseDriver

As you can see, the format of a database URL is different from the format of the kind of URL that is conventionally used to navigate to pages in Web browsers. A database URL, unlike a browser URL, has fields that are separated by colons rather than slash marks. Also, those fields sometimes contain items that you don't find in Web-page URLs. For example, the URL used in this example contains the number

-- which happens to be the port number that's used to access a database at Sun that's named

   pubs2

You can see that the name "pubs2" appears in the URL used in the example.

Database Drivers

Along with requesting the name of a URL, the TableExample's login window also asks for the name of a database driver. Refer back to the illustration of the program's login window, and you'll see that the name of the driver that's accessed by default is

  connect.sybase.SybaseDriver

Obviously, the default driver used in this example is a Sybase driver. When you want to connect to a database using JDBC, you must provide JDBC with the exact name of the driver you want to use because the JDBC mechanism uses different kinds of drivers to connect with different kinds of databases. Some drivers, including ODBC/JDBC bridge drivers (which can access ODBC-based drivers such as Microsoft Access and MS SQL Server), are included with JDK and are immediately available as soon as JDK is installed. JDBC drivers for other kinds of databases are available from RDBMS vendors.

Along with providing JDBC with the name of a driver, you must also tell JDBC where the driver resides. At Sun, the driver that's used to connect to the database that the TableExample2 program uses resides at a location named connect. That's why the TableExample2 program uses the entry

connect.sybase.SybaseDriver

to connect to its Sybase driver.

Connecting to a Remote Database

When you type the appropriate entries in the login window's text fields and click the Connect button, the TableExample2 program's login window goes away and is replaced by a window that looks like this:

This window is a bare-bones SQL editor. To use it, just type any valid SQL query in the top window and click the Fetch button. The TableExample2 program then creates a JTable component in the lower window and populates the table with whatever data you have requested in your SQL query.

For example, if you connect to a database that has a table named COFFEES and execute the query

   SELECT * FROM COFFEES

-- TableExample2 responds by retrieving all the data that's stored in the COFFEES table and displaying it in a JTable component in the lower window. The result looks something like this:

NOTE: While the TableExample2 program's SQL editor window is open, you can reopen the login window and enter different configuration information without closing the SQL editor. To do that, just click the Configuration button.

Using MS Access with Swing

If you are a Windows NT or Windows 95 user and have Microsoft Access software, you can access Microsoft Access tables from Swing components using an ODBC/JDBC bridge driver that comes with the JDK. This section explains how to set up MS Access on a Windows NT computer so you can access MS Access databases using JTable components and other Swing controls. In the following exercise, you'll set up some MS Access data that you can access using Swing components.

Configuring Your System for MS Access and Swing

To set up Microsoft Access and configure it to work with the TableExample2 application, follow these steps:

If MS Access isn't installed on your computer, install it in accordance with instructions provided by Microsoft.
From the Windows NT desktop, open the examples directory that the Swing installer created in your Swing 0.5 directory when you downloaded and installed Swing. Then open the DBDemos and CreateCoffees subdirectories.
From the Windows NT Control Panel, double-click the System icon. When the System Properties dialog opens, tab to the Environment page and create a user variable named JDBCHOME. Set JDBCHOME to point to the DBDemos subdirectory inside your examples directory -- for example, if Swing is installed in a directory named Swing-0.5 on your C drive, set JDBCHOME to

C:\Swing-0.5\examples\DBDemos
Close the System Properties dialog box by clicking OK.
From the Windows NT control panel, double-click the ODBC32 icon. When the "ODBC Data Source Administrator" dialog box opens, click the Add Button. Then, when the "Create New Data Source" dialog opens, select the item labeled "Microsoft Access Driver (*.mdb)" and click the Finish button. Windows then opens a dialog box named "ODBC Microsoft Access 97 Setup."
When the "ODBC Microsoft Access 97 Setup" dialog appears, click the Select button. Windows then opens another dialog titled "Select Database."
In the "Select Database" dialog box, navigate to the examples\DBDemos\JavaDB subdirectory inside Swing's examples directory.
Notice that the entry "CAFEJAVA.MDB" appears inside the Select Database dialog's "Database Name" list box. text field. Select the "CAFEJAVA.MDB" entry and confirm that it has been copied into the Database name text field.
Close the "Select Database," "ODBC Microsoft Access 97 Setup," and "ODBC Data Source Administrator" dialog boxes by clicking their OK buttons.

You have now connected your computer's ODBC mechanism to an Access database named CafeJava.mdb, which resides in your examples\DBDemos\JavaDB directory. Also, you have made the CafeJava.mdb database an ODBC/JDBC data source named CafeJava.

Creating a Database Table

Now that you have created an MS Access data source, you are ready to create a database table using JDBC. To do that, follow these steps:

Open an MS-DOS console window.
Navigate to the CreateCoffees subdirectory inside your examples\DBDemos directory.
Compile the CreateCoffees.java source file inside that directory by executing the command

javac CreateCoffees.java
Run the CreateCoffees application by executing the runnit command:

    runnit

NOTE: Although the CreateCoffees program isn't difficult to understand, and although you'll have an idea of how it works by the time you finish this Web page, it's beyond the scope of this document to examine the CreateCoffees application in any detail. But the program is explained very clearly in JDBC Database Connectivity with Java, from which it is taken, and it's worth studying closely if you really want to understand JDBC. The same is true of the other "helper" programs that are used to create and populate the MS Access tables named COFFEES and SUPPLIERS in the other examples presented in this section.

Verifying that a Table Is Created

In the preceding exercise, you used JDBC to create an Access table named COFFEES. To verify that this has happened, follow these steps:

Launch MS Access.
Open the CafeJava.mdb database from the File menu.
From MS Access, close the CafeJava.mdb database (if you haven't done so already).

NOTE: When you run the CreateCoffees program, it creates a table that requires the user to log in using the login name "Admin" and the password "duke1." So you must type in those same words whenever you want to gain access to the data source that the program creates. If you want to change the rules for gaining access to the data source that the CreateCoffees program sets up, you can do that by opening the program's source code in your favorite editor and changing its login-name and password entries.

Populating a Table with Data

What is a database table without data? It's like a fish without a bicycle. So now do this:

From your MS-DOS console window, navigate to the examples\DBDemos\InsertCoffees directory.
Compile the InsertCoffees.java source file by executing the command

javac InsertCoffees.java
Execute the InsertCoffees program by entering the command

runnit
When you run the InsertCoffees program, it populates the COFFEES table with data. To verify that this is true, use MS Access to inspect the COFFEE table. If everything has worked correctly, you will see that JDBC has magically stuffed your COFFEE table with data.
From MS Access, close the CafeJava.mdb database (if you haven't done so already).

Creating and Populating the SUPPLIERS Table

Following the same sequences of steps shown above, create and populate the SUPPLIERS database table that's provided in the JavaDB directory.

Using TableExample2 with MS Access

When you have performed all the preceding exercises, you can access both the COFFEES table and the SUPPLIERS table using the TableExample2 program. To display the information you have stored in the COFFEES table, run TableExample2 and log in using the information shown in the following screen shot:

When you have supplied all the necessary information, click the Connect button. When TableExample2 opens its SQL editor, type in the query shown in the following illustration and click the Fetch button. TableExample2 then displays the data you have placed in the COFFEES table:

Editing Data Stored in a Table

You can use the TableExample's SQL editor program to edit data stored in a table. For example, to change the "TOTAL" column in the COFFEE table's "Colombia" record from 0 to 7, simply execute this SQL query:

   UPDATE COFFEES set TOTAL = TOTAL + 7 where 
      COF_NAME = 'Colombian'

Then click the Fetch button, and you'll see that the value stored in the TOTAL column of the record you have specified has changed from 0 to 7.

Where to Go for More Information

If you're an experienced database developer or administrator, you undoubtedly understand how all the exercises in this section work. If you'd like to do more research, you can find a wealth of material in JDBC Database Connectivity with Java, which was the source of all the exercises presented in this section.

Using MS SQL Server with Swing

Many Windows NT users have access to Microsoft SQL Server (pronounced "Sequel Server") databases. Because MS SQL server is an ODBC-compatible database, you can connect a Windows 95 or Windows NT PC to MS SQL Server using the JDBC/ODBC driver that's supplied with JDK. And the procedures for configuring your system to work with SQL Server are almost identical to those that are needed to established a Microsoft Access connection.

Once you connect to a SQL Server database, you can create tables using the sample JDBC programs referred to in the previous exercises, and you can access and modify them using the TableExample2 program. (You can also create and drop tables using the TableExample2 application; all you have to do is enter the appropriate SQL commands.)

To set your Windows 95 or Windows NT system to work with MS SQL Server, follow these steps:

Make sure that it your system is connected to a SQL Server system (or that it is a SQL Server system)
Configure your system's ODBC32 mechanism for a SQL Server database using the same procedures that were outlined earlier in this document in the section headed "Using MS Access with Swing."
Create a database table (or multiple tables) using either the SQL Enterprise Manager or the example applications provided with Swing. (To use the example programs provided with Swing, you'll have to change the names of the URLs and database drivers accessed in the code, as well as the user names and password names that are hard-coded in; see the "Using MS Access with Swing" section for more details.)
Use the TableExample2 program to view and manipulate the data stored in your SQL Server table, using exactly the same techniques described in the preceding sections of this document.

How the TableExample2 Program Works

The code that implements the examples presented on this Web page is so straightforward that it may surprise you. The reason is that Swing makes use of two "black boxes," implemented as interfaces, that isolate JTable object from the collection of database data. These interfaces also work in the opposite direction, isolating the mechanisms that collect database data from the Swing components that have the job of displaying database data.

These abstraction mechanisms -- called a Table Model (on the Swing end) and a ResultSet (on the database end) -- are represented as rectangles in the following diagram:

The JDBC Adapter

The examples presented in this document also make use of another mechanism, called a JDBC adapter, that isolates the code you write not only from Swing components and data-collection operations, but also from the ResultSet and Table-Model interfaces provided by JDBC and Swing.

The JDBC adaptor, represented as a circle in the middle of the preceding diagram, is not part of the JDBC and is not an official part of Swing. In the examples presented in this section, the JDBC adaptor is just a Java source file that's written like any other Java source file and is compiled along with the code. (In the examples\TableExample2 directory provided with Swing, a JDBC Adapter.java file is provided along with the other source files and is simply compiled along with all other source files to create the TableExample2 application

In your applications, you can use the JDBC adapter that's provided with the examples presented in this chapter -- either "as is" or modified to suit your own needs -- or, if you prefer, you can write your own code for handling database connections, data I/O, and table models.

Result Sets

In JDBC, a ResultSet is an object that stores information from a database in a specific format. Once a collection of data is place in a ResultSet, an application that uses JDBC can retrieve data from the ResultSet fields in which the data is stored.

To retrieve data from a data source, you call the JDBC method getMetaData(). This is how getMetaData() is called in the TableExample2 program:

  metaData = resultSet.getMetaData();

The TableModel Interface

Once you have retrieved data from a database using the getMetaData() method, you can use the TableModel interface provided by Swing to transfer your data to a table, or to retrieve it from a JTable object. For more information about how the TableModel interface is used in Swing, see the specification titled "The JTable API" and the TableModel JavaDoc API.

Connecting to a Database and Retrieving Data

The result-set mechanism that isolates data-collection mechanisms from Swing components and developer applications is built into JDBC. The Table Model mechanism that appears opposite the ResultSet mechanism in the preceding diagram is an interface that's implemented by the JTable class in Swing.

Either way, the JDBC adapter that you provide in your applications will always have the same general kinds of tasks to perform. In any application, the most important job of a JDBC adapter is to open a database and retrieve data from a database table so it can be used by other parts of the application. A JDBC adapter usually performs that function by calling JDBC's getMetaData() method.

Because a JDBC adapter is usually expected to open a database, it has to be provided in some way with the parameters that JDBC needs to open databases -- that is, the name of the database's URL, the name of the driver that is to be used to access the database, and information about the user, such the user's login name and password.

Connecting to a Database

The TableExample2 program collects all that data using a GUI interface that is defined and created in a source file named TableExample2.java. When the data has been retrieved, a method named connect() is called to pass the data to the program's JDBC adapter:

    public void connect() {
       dataBase = new JDBCAdapter(
       serverField.getText(),
       driverField.getText(),
       userNameField.getText(),
       passwordField.getText());
       sorter.setModel(dataBase);  //optional
   }

When the JDBC adapter is called using these parameters, it prints the line "Opening db connection" in your application's terminal window and then attempts to open the database you have specified by calling a JDBC method named DriverManager.getConnection():

 
    public JDBCAdapter(String url, String driverName,
                       String user, String passwd) {
        try {
            Class.forName(driverName);
            System.out.println("Opening db connection");

            connection = DriverManager.getConnection
               (url, user, passwd);
            statement = connection.createStatement();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }    
     }

Executing SQL Queries

Once a connection with a database is established, the JDBC adapter is ready to start accessing data by executing queries. When the user of the TableExample2 program uses the SQL editor to execute a query, the GUI interface that is set up in the TableExample2.java source file retrieves the user's input from a text field. The user executes the query by clicking the SQL editor's Fetch button. Then the JDBC adapter executes a method named executeQuery() to execute the query, retrieve its results, and pass it on to a JTable object via the TableModel interface:

    public void executeQuery(String query) { 
        try {
            resultSet = statement.executeQuery(query);

            metaData = resultSet.getMetaData();

            int numberOfColumns =  
                metaData.getColumnCount(); 
            columnNames = new String[numberOfColumns]; 
            // Get the column names and cache them. 
            // Then we can close the connection. 
            for(int column = 0; column 
              < numberOfColumns; column++) { 
                columnNames[column] = 
                     metaData.getColumnLabel(column+1);
            }

            // Get all rows.
            rows = new Vector();
            while (resultSet.next()) {
                Vector newRow = new Vector();
                for (int i = 1; i <= 
                   getColumnCount(); i++) {
	            newRow.addElement(resultSet.getObject(i));
                }
                rows.addElement(newRow);
            }
            // close();
            // Need to copy the metaData;
            // bug in jdbc:odbc driver. 
            fireTableChanged(
               new TableModelEvent(this, -1, -1));
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }    
    }

TableExample1: An Even Simpler DB Program

The JDBCAdapter.java and TableExample2.java files perform various other tasks, of course. They contain methods that set up the program's GUI interface, pass information back and forth among Swing components, and close databases when user sessions are completed.

Because it takes a considerable amount of code to create and manage GUI interface devices, we could significantly shorten the TableExample2 program by just stripping out all its GUI controls and turning it into a command-line program. And to provide you with a dramatic demonstration of how short and simple a data-aware Swing program can be when its GUI interface is stripped away, we've done just that. We have provided another sample database program named TableExample1 (in case you've been wondering, that's why the longer example that you worked with earlier in this chapter is named TableExample2). The TableExample1 program simply prompts the user for a command-line entry, opens a database table, and displays the contents of that in a JTable component.

For user input, the TableExample1 program depends solely on a command line. When you execute program, it prompts you for a set of the usual parameters: the name of a database URL, the name of a database driver, a user ID, and a password. When you fill in the blanks and hit Return, the program opens the table you have specified and displays its contents inside a JTable component.

The most interesting feature of the TableExample1 application is that the entire program is complete in one file, and consists of exactly one page of code.

Swinging Duke says check it out. It'll show you how easy it is to implement database connectivity in Swing.