 |
|
|
|
|
|
|
 |
||||||
 |
||||||
 |
||||||
 |
||||||
 |
||||||
 |
||||||
 |
||||||
 |
||||||
|
||||
The
Archive
JTable
Archived March 1998The JTable Class Is DB-AwareBy Philip Milne and Mark Andrews
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 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:
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 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 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:
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:
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:
runnit 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:
Populating a table with data What is a database table without data? It's an empty shell. So now do this:
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 = 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:
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 TableModelapi 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() { 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 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) { 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. |
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 
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.
