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.
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.
|
Related Reading Swing Hacks |
|
Example 3-12 shows how the JDBCTableModel is implemented.
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
import java.util.*;
/** an immutable table model built from getting
metadata about a table in a jdbc database
*/
public class JDBCTableModel extends AbstractTableModel {
Object[][] contents;
String[] columnNames;
Class[] columnClasses;
public JDBCTableModel (Connection conn,
String tableName)
throws SQLException {
super();
getTableContents (conn, tableName);
}
protected void getTableContents (Connection conn,
String tableName)
throws SQLException {
// get metadata: what columns exist and what
// types (classes) are they?
DatabaseMetaData meta = conn.getMetaData();
System.out.println ("got meta = " + meta);
ResultSet results =
meta.getColumns (null, null, tableName, null) ;
System.out.println ("got column results");
ArrayList colNamesList = new ArrayList();
ArrayList colClassesList = new ArrayList();
while (results.next()) {
colNamesList.add (results.getString ("COLUMN_NAME"));
System.out.println ("name: " +
results.getString ("COLUMN_NAME"));
int dbType = results.getInt ("DATA_TYPE");
switch (dbType) {
case Types.INTEGER:
colClassesList.add (Integer.class); break;
case Types.FLOAT:
colClassesList.add (Float.class); break;
case Types.DOUBLE:
case Types.REAL:
colClassesList.add (Double.class); break;
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
colClassesList.add (java.sql.Date.class); break;
default:
colClassesList.add (String.class); break;
};
System.out.println ("type: " +
results.getInt ("DATA_TYPE"));
}
columnNames = new String [colNamesList.size()];
colNamesList.toArray (columnNames);
columnClasses = new Class [colClassesList.size()];
colClassesList.toArray (columnClasses);
// get all data from table and put into
// contents array
Statement statement =
conn.createStatement ();
results = statement.executeQuery ("SELECT * FROM " +
tableName);
ArrayList rowList = new ArrayList();
while (results.next()) {
ArrayList cellList = new ArrayList();
for (int i = 0; i<columnClasses.length; i++) {
Object cellValue = null;
if (columnClasses[i] == String.class)
cellValue = results.getString (columnNames[i]);
else if (columnClasses[i] == Integer.class)
cellValue = new Integer (
results.getInt (columnNames[i]));
else if (columnClasses[i] == Float.class)
cellValue = new Float (
results.getInt (columnNames[i]));
else if (columnClasses[i] == Double.class)
cellValue = new Double (
results.getDouble (columnNames[i]));
else if (columnClasses[i] == java.sql.Date.class)
cellValue = results.getDate (columnNames[i]);
else
System.out.println ("Can't assign " +
columnNames[i]);
cellList.add (cellValue);
}// for
Object[] cells = cellList.toArray();
rowList.add (cells);
} // while
// finally create contents two-dim array
contents = new Object[rowList.size()] [];
for (int i=0; i<contents.length; i++)
contents[i] = (Object []) rowList.get (i);
System.out.println ("Created model with " +
contents.length + " rows");
// close stuff
results.close();
statement.close();
}
// AbstractTableModel methods
public int getRowCount() {
return contents.length;
}
public int getColumnCount() {
if (contents.length == 0)
return 0;
else
return contents[0].length;
}
public Object getValueAt (int row, int column) {
return contents [row][column];
}
// overrides methods for which AbstractTableModel
// has trivial implementations
public Class getColumnClass (int col) {
return columnClasses [col];
}
public String getColumnName (int col) {
return columnNames [col];
}
}
The constructor dumps off its real work to getTableContents(), which is responsible for the two queries just described. It gets a DatabaseMetaData object from the Connection, from which you can then get the column data with a getColumns()
call. The arguments to this method are the catalog, schema pattern,
table name pattern, and column name pattern; this implementation
ignores catalogs and schema, although you might need to have callers
specify them if you have a complex database. getColumns() returns a ResultSet, which you iterate over just like you would with the results of a regular JDBC query.
Getting the column name is easy: just call getString("COLUMN_NAME"). The type is a little more interesting, as the getInt("DATA_TYPE") call will return an int, which represents one of the constants of the java.sql.Types class. In this example, I've simply mapped Strings and the basic number types to appropriate Java classes. TIMESTAMP is SQL's concept of a point in time (a DATE and a TIME), so it gets to be a Java Date. Knowing these types will make it easier to call the right getXXX() method when retrieving the actual table data.
The second query is a simple SELECT * FROM tableName. With no WHERE restriction on the query, this will create a ResultSet with every row in the table. I shouldn't have to mention that if tableName is a table with millions of records, your resulting TableModel is not going to fit into memory. You knew that, right?
Again, you need to iterate over a ResultSet. Each time that results.next()
returns true, meaning there's another result, you pull out every column
you know about from the earlier metadata query. This means calling a getXXX() method and passing in the column name, where you know which getXXX()
to use from your earlier investigation of the type of each column. You
can go ahead and put numeric data into its proper wrapper class (Integer, Double, etc.) because that works well with the class-based rendering system of JTables. A caller might decide to use a TableCellRenderer that applies a Format class to all Doubles
in the table to display them only to a certain number of decimal
points, or to render Dates with relative terms like "Today" and "25
hours ago." Strongly typing the data in your model will help with that.
With the queries done, you just convert the ArrayLists to real arrays (which offer quick lookups for the get methods). The implementations of the AbstractTableModel methods mentioned previously, as well as the improved implementations of getColumnClass() and getColumnName(), are trivial uses of the columnNames, columnClasses, and contents arrays built up by this method.
|
Before you say "I can't run this hack, I don't have a database," relax! The open source world has you covered. And no, it's not some big thing like JBoss. HSQLDB, more commonly known by its old name, Hypersonic, is a JDBC relational database engine written in Java. It is really small and can be run as a standalone server or within your JVM. If you are database-less, grab HSQLDB from http://hsqldb.sourceforge.net/.
Whatever your database, you'll need a driver classname, URL, username, and password to make a connection to the database. If you have your own database, I trust you already know this. If you just downloaded HSQLDB one paragraph ago, then you'll be using the following information:
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:file:testdb
User: sa
Password: (none)
This assumes you'll be running Hypersonic as part of your application, meaning you'll need to extend your classpath to pick up the hsqldb.jar file. Also note that this will create some testdb files in your current directory that you can clean up when done. You can also provide a full path to some other directory; see HSQLDB's docs for more info.
The test runner expects to pick up the connection strings as properties named jdbctable.driver, jdbctable.url, jdbctable.user, and jdbctable.pass. To make things easier, there are two ways to pass these in: either as system properties (usually specified with -D arguments to the java command), or in a file called jdbctable.properties. The book code has a sample of the latter with HSQLDB values as defaults.
To test the JDBCTableModel, the TestJDBCTable creates an entirely new table in the database. The model gets the Connection and the name of this table and loads the data from the database. Then the test class simply creates a new JTable from the model and puts it in a JFrame. Example 3-13 shows the source for this demo.
import javax.swing.*;
import javax.swing.table.*;
import java.sql.*;
import java.util.*;
import java.io.*;
public class TestJDBCTable {
public static void main (String[] args) {
try {
/*
driver, url, user, and pass can be passed in as
system properties "jdbctable.driver",
"jdbctable.url", "jdbctable.user", and
"jdbctable.pass", or specified in a file
called "jdbctable.properties" in current
directory
*/
Properties testProps = new Properties();
String ddriver = System.getProperty ("jdbctable.driver");
String durl = System.getProperty ("jdbctable.url");
String duser = System.getProperty ("jdbctable.user");
String dpass = System.getProperty ("jdbctable.pass");
if (ddriver != null)
testProps.setProperty ("jdbctable.driver", ddriver);
if (durl != null)
testProps.setProperty ("jdbctable.url", durl);
if (duser != null)
testProps.setProperty ("jdbctable.user", duser);
if (dpass != null)
testProps.setProperty ("jdbctable.pass", dpass);
try {
testProps.load (new FileInputStream (
new File ("jdbctable.properties")));
} catch (Exception e) {} // ignore FNF, etc.
System.out.println ("Test Properties:");
testProps.list (System.out);
// now get a connection
// note care to replace nulls with empty strings
Class.forName(testProps.getProperty
("jdbctable.driver")).newInstance();
String url = testProps.getProperty ("jdbctable.url");
url = ((url == null) ? "" : url);
String user = testProps.getProperty ("jdbctable.user");
user = ((user == null) ? "" : user);
String pass = testProps.getProperty ("jdbctable.pass");
pass = ((pass == null) ? "" : pass);
Connection conn =
DriverManager.getConnection (url, user, pass);
// create db table to use
String tableName = createSampleTable(conn);
// get a model for this db table and add to a JTable
TableModel mod =
new JDBCTableModel (conn, tableName);
JTable jtable = new JTable (mod);
JScrollPane scroller =
new JScrollPane (jtable,
ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
ScrollPaneConstants.HORIZONTAL_SCROLLBAR_AS_NEEDED);
JFrame frame = new JFrame ("JDBCTableModel demo");
frame.getContentPane().add (scroller);
frame.pack();
frame.setVisible (true);
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static String createSampleTable (Connection conn)
throws SQLException {
Statement statement = conn.createStatement();
// drop table if it exists
try {
statement.execute ("DROP TABLE EMPLOYEES");
} catch (SQLException sqle) {
sqle.printStackTrace(); // if table !exists
}
statement.execute ("CREATE TABLE EMPLOYEES " +
"(Name CHAR(20), Title CHAR(30), Salary INT)");
statement.execute ("INSERT INTO EMPLOYEES VALUES " +
"('Jill', 'CEO', 200000 )");
statement.execute ("INSERT INTO EMPLOYEES VALUES " +
"('Bob', 'VP', 195000 )");
statement.execute ("INSERT INTO EMPLOYEES VALUES " +
"('Omar', 'VP', 190000 )");
statement.execute ("INSERT INTO EMPLOYEES VALUES " +
"('Amy', 'Software Engineer', 50000 )");
statement.execute ("INSERT INTO EMPLOYEES VALUES " +
"('Greg', 'Software Engineer', 45000 )");
statement.close();
return "EMPLOYEES";
}
}
The createSampleTable() method is something you could
rewrite to insert your own types and values easily. In fact, because it
returns the name of the table you've created, you could create many
different tables in your database and test out how the model handles
them. Or, use a loop to create lots of rows and see how long it takes
to load them.
At any rate, when run, the TestJDBCTable produces a JFrame with the database table's contents, as seen in Figure 3-9.
Figure 3-9. JTable populated from a database
Joshua Marinacci is the author of "The Java Sketchbook" column for java.net, covering topics in Java client-side and web development.
Chris Adamson is the editor for ONJava and java.net, and is an Atlanta-based consultant, specializing in Java, Mac OS X, and media development.
View catalog information for Swing Hacks
Return to ONJava.com.
Copyright © 2005 O'Reilly Media, Inc.