ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Swing Hacks

Hacking Swing: A JDBC Table Model

by Joshua Marinacci and Chris Adamson

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:

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:

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.

Swing Hacks

Related Reading

Swing Hacks
Tips and Tools for Killer GUIs
By Joshua Marinacci, Chris Adamson

Example 3-12 shows how the JDBCTableModel is implemented.

Example 3-12. Populating a Swing TableModel from a database connection


	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.

Testing Things Out

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:

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.

Example 3-13. Testing the JDBC-based table


		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
Figure 3-9. JTable populated from a database

Joshua Marinacci is a blogger and co-author of "Swing Hacks" and "Building Mobile Apps with Java" for O'Reilly.

Chris Adamson is an author, editor, and developer specializing in iPhone and Mac.


View catalog information for Swing Hacks

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.