Archived  March 1998

The JTable Class Is DB-Aware 

By Philip Milne and Mark Andrews

The Swing component set is database-ready --  it features a powerful, streamlined, easy-to-use mechanism for viewing  and manipulating data stored in local or remote relational databases.

When  you create a table component from Swing's JTable^api 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.

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.

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

This article covers the following major topics:

• The JDBC mechanism: Describes JDBC, the  secret behind Swing's database connectivity.
   
• The TableExample2 Program: Demonstrating  database connectivity in a Swing program.
   
• Examining TableExample2: Taking a closer  look at the TableExample2 program.
   
• Retrieving database data: Connecting  to a database and fetching data.
   
• An even shorter database program:  Presenting a database-connectivity program that requires just one page  of code.
   
• Summing up: A few words in closing.

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 titled JDBC Database Connectivity with Java. That volume,  published by Addison-Wesley, was written by 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 sample programs  that show you how to view and manipulate data stored in databases using  JDBC and Swing.

Most of this article 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 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 -- Sybase, Oracle,  Informix, DB2, Microsoft access, or MS SQL Server, to name just a few.  Then you can retrieve, view, and manipulate data from tables stored in  the database you have selected.

Because the TableExample2 application can access practically any kind  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 accesses these three varieties 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 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 Swing 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 TableExample2 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 who 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 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

   1455

-- which happens to be the port number 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  the JDK and are immediately available once the 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 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:

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:

1. If MS Access isn't installed on your computer, install it in accordance  with instructions provided by Microsoft.
    
2. 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.
    
3. From the Windows NT Control Panel, double-click the System icon. When  the System Properties dialog box opens, tab to the Environment page  and create a user variable named JDBCHOME.
    
4. Click the System Properties dialog box's Set button. That action sets  the JDBCHOME directory 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
    
5. Close the System Properties dialog box by clicking OK.
    
6. 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."
    
7. When the "ODBC Microsoft Access 97 Setup" dialog appears,  click the Select button. Windows then opens another dialog titled "Select  Database."
    
8. In the "Select Database" dialog box, navigate to the examples\DBDemos\JavaDB subdirectory inside Swing's examples directory.
    
9. Notice that the entry "CAFEJAVA.MDB" appears inside the  Select Database dialog's "Database Name" text field. Select  the "CAFEJAVA.MDB" entry and confirm that it has been copied  into the Database name text field.
    
10. 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:

1. Open an MS-DOS console window.
    
2. Navigate to the CreateCoffees subdirectory inside your examples\DBDemos directory.
    
3. Compile the CreateCoffees.java source file inside that directory  by executing the command
   
   javac CreateCoffees.java
   
    
4. Run the CreateCoffees application by executing the runnit command:

    runnit

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 the Creation of a Table

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

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

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 set up by the CreateCoffees  program, open the program's source code in your favorite editor and  change its login-name and password entries.

Populating a table with data

What is a database table without data? It's an empty shell. So now do  this:

1. From your MS-DOS console window, navigate to the examples\DBDemos\InsertCoffees directory.
    
2. Compile the InsertCoffees.java source file by executing the  command
   
   javac InsertCoffees.java
    
3. Execute the InsertCoffees program by entering the command
   
   runnit
    
4. When you run the InsertCoffees program, it populates the COFFEES table  with data. To verify that this has happened, 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.
    
5. From MS Access, close the CafeJava.mdb database (if you haven't already  done so).

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 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 for 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. The procedures for  configuring your system to work with SQL Server are almost identical to  those for establishing 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:

1. Make sure that it your system is connected to a SQL Server system  (or that it is a SQL Server system).
    
2. Configure your system's ODBC32 mechanism for a SQL Server database  using the same procedures outlined earlier in this document in the section  headed "Using MS Access with Swing."
    
3. Create a database table (or multiple tables) using either the SQL  Enterprise Manager or the sample applications provided with Swing. (To  use the sample 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.)
    
4. 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.

The code that implements the examples presented on this Web page is so  straightforward that it may surprise you. Why? Because Swing makes use  of two "black boxes," implemented as separate 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 adapter, 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 Swing's examples\TableExample2 director, a JDBC Adapter.java file is provided along with the other  source files and is simply compiled along with the 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^api interface provided by Swing to transfer your data to a table, or to retrieve  it from a JTable object.

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 as 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(
       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();
   }
}

Of course the JDBCAdapter.java and TableExample2.java files  perform various other tasks. They contain methods that (1) set up the  program's GUI interface, (2) pass information back and forth among Swing  components, and (3) 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 its contents  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.

There are many ways to for an application to use data from a relational  database. Often the database is used to provide object persistence so  that objects in a business model can be loaded and saved as rows in a  database table. This mechanism typically requires some "middleware"  which handles all of the details that are needed to bridge the gap between  a class hierarchy in a client application and the relational schema of  a relational database.

The short examples provided in this section are not in competition with  these middleware products. Instead they show a simpler scenario in which  raw database data is mapped directly from the format provided by the JDBC  driver (a ResultSet) into the tabular format required by the JTable (a  TableModel).

The reason that the TableModel interface is used by the JTable is that  we would not want to tie ourselves to a strategy that maps raw data from  a JDBC driver when there are many advantages to inflating a full object  model. And anyway, it shouldn't matter whether the data is a ResultSet  from a JDBC driver, a list of objects in an object model, some specialized  data structure for storing tables, or even a pseudo-table like the TableSorter  object -- these are all tables and the JTable should not need to know  the details of how the data is stored.

And this is just the way things are in Swing: All a data set needs to  do to be displayed in a JTable is to implement the handful of methods  in the TableModel interface. These define how many rows the table has,  how many columns it has, what the values are in each of the cells, and  so on. Once these attributes are defined, we can simply hand the object  to the JTable knowing that it will to be able to display the data, edit  it, and redraw it correctly when it changes.

	[Download Swing] [Swing API Docs] [README Files] [Download JDK] [JDK Docs] [The Java Tutorial]
This page was updated at 4 p.m. PST on Tuesday, March 16, 1999
For information, call: (800) 786-7638 Outside the U.S. and Canada, dial your country's AT&T Direct Access Number first.		Copyright © 1995-99 Sun Microsystems, Inc. All Rights Reserved. Legal Terms. Privacy Policy.