Hacking Swing: A JDBC Table Model

Editor's note: Swing Hacks is not just about visual trickery, as this excerpt illustrates. The book's purpose is to enable developers to deliver more compelling desktop applications with Java, and this hack is an example of that, working not with the visuals of a JTable, but the model behind it. By leveraging the JDBC support provided by J2SE, you can map a database table into a Swing TableModel, which then lets you expose it as a JTable. Read on for the nitty-gritty.

Bring your database tables into Swing with a minimum of hassle.

If you've worked with databases, you've probably also worked with the tools they provide for quick table maintenance and queries: command-line tools that are well suited to brief hack-and-slash work, but hard to work with once you start dealing with any serious amount of data. It's hard enough to write the SQL command to return 10 or 20 columns in a query—it's even worse when the results word-wrap over the course of a dozen lines, and you can't tell where one result ends and another begins.

Wouldn't it be nice to be able to throw the contents of any database table into a Swing JTable? Give it a few JDBC strings, toss it in a JFrame, and pow!—instant GUI.

Building Connectivity

If you've worked with both JDBC and Swing, you'll grasp the concept in one sentence: use table metadata to build a Swing TableModel from the database table. If you haven't, here's the background you'll need: JDBC provides an abstract means of accessing databases. Java code to work with one database should work with another, the only difference is in the way that JDBC achieves a Connection to the database, which is usually a matter of providing Strings for:

A driver class, which provides implementations of the various java.sql interfaces.

A URL with which to connect to the database. This implies the use of sockets, though that's not necessarily the case. Some small embeddable databases can live in the same JVM as your application.

An optional username.

An optional password.

Once you have the Connection, you can begin to send commands (creation, deletion, and altering of tables) or queries to the database by creating Statements from the Connection. You can also use the Connection to get metadata about the database, like what kinds of features it supports, how long certain strings can be, etc. More importantly for this hack, it allows you to discover what tables are in the database, what columns they have, and what types of data are in those columns.

So, given just a Connection and the name of a table in the database, you can build a Java representation of its contents with two queries. The first query gets column metadata for the table and builds up arrays of the column names and their types. These can be mapped reasonably well to Java classes, at least for whatever types you intend to support. The second query gets all the data from the table. For each row, it gets each column's value. This is put into a two-dimensional array, which represents the entire contents of the table.

With these two queries done, you have everything you need to support the abstract methods of AbstractTableModel:

getRowCount() is the length of the contents array that you create.

getColumnCount() is 0 if you have no contents, or the length of the first item in the contents array (which is itself an array because contents is a two-dimensional array).

getValueAt() is the value at contents[row][col].

AbstractTableModel has utterly trivial implementations of getColumnClass() and getColumnName(), so the first always returns Object.class, the second returns "A", "B", "C", etc.; holding onto column metadata from the first query allows you to provide more useful implementations of these methods, too.