ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Hacking Swing: A JDBC Table Model
Pages: 1, 2, 3

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.

Pages: 1, 2, 3

Next Pagearrow