JTables + JDBC: Pt. 3 Connecting to your database through JDBC

This is part 3 in the series on JTables and JDBC

<< Pt. 2 Extending AbstractTableModel

Now that we have set up our empty TableModel, we’ll need to populate it with information from our database. In order to do that, we’ll first have to establish a connection to our database and query it for information.

First, we’ll need to find the appropriate JDBC drivers for your database. Since I am using a PostgreSQL db, I was able to obtain the driver here.  If you’re using something else then I’m sure a quick Google search will give you what you’re looking for.

In order to use the driver, you will need to add the jar to your CLASSPATH. If you are using Eclipse, then its a simple matter of adding the jar as an External Jar under your project’s Referenced Libraries. If you’re not, then you have a lot of other options, but I’ll let Google tell you what they are.

Connecting to the Database
Following the documentation for the PostgreSQL JDBC driver, we can connect to the our database as follows:

public void Connection connect() {
	String url = "jdbc:postgresql://localhost:5432/myDatabase";
	String user = "username";
	String pass = "password";

	try {
		Class.forName("org.postgresql.Driver");
		Connection db = DriverManager.getConnection(url, user, pass);

		return db;
	} catch (ClassNotFoundException e) {
		System.err.println("Could not find PostgreSQL driver");
	} catch (SQLException e) {
		System.err.println("Failed to establish connection with db");
	}
	return null;
}

If successful, the method will return a Connection object from which we can access and modify our database.

We can also disconnect from the database as follows:

public void disconnect(Connection conn) {
	try {
		conn.close();
	}
	catch (SQLException sqx) {
		System.err.println("Could not disconnect from db, trying again");
	}
}

It may seem like overkill, but this way we can reduce the number of try/catch blocks while also generating meaningful error statements at the same time.

Getting the Column Names of a Table

We can obtain the column names of a PostgreSQL table as a String[]by looking at a table’s metadata as follows:

public String[] getTableColumnNames(Connection conn, String tableName)
                                                          throws SQLException {
	Statement st = conn.createStatement();
	ResultSet rs = st.executeQuery("SELECT * FROM " + tableName + ";");

	ResultSetMetaData rsMeta = rs.getMetaData();

	String [] colNames = new String[rsMeta.getColumnCount()];

	for (int i = 0; i < rsMeta.getColumnCount(); i++) {
	    colNames[i] = rsMeta.getColumnName(i+1);
	}

	rs.close();
	st.close();
	return colNames;
}

By issuing a general query for all of the Columns in the target table, we can access each column’s name by accessing the ResultSet‘s metadata.

Determining the Class Type of each Column
For reasons that will be clear later, we will need to determine what type of data each column holds in the table. This can be done by using the following code:

public String[] getTableColumnClasses(Connection conn, String tableName)
                                                        throws SQLException {
	Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM " + tableName + ";");

	ResultSetMetaData rsMeta = rs.getMetaData();

	String [] colClasses = new String [rsMeta.getColumnCount()];

	for (int i = 0; i < rsMeta.getColumnCount(); i++) {
	    colClasses[i] = rsMeta.getColumnClassName(i + 1);
	}

	rs.close();
	st.close();
	return colClasses;
}

Getting at the Table’s Content
Getting at the actual content is a bit trickier.

First, because we cannot easily determine the number of rows in the table, we’ll be storing our information in an ArrayList at first, and then convert the ArrayList back into an Object[] array.

Second, we will also need to see what type of data (String, int, float, etc.) the cell holds, so we’ll be cross-referencing our cells with the Class array.

If you are thoroughly confused by now, I suggest you read a bit more from this O’Reilly article here. Most of the work done here is based off of that article.

Anyways, the code to grab the data can be seen here:

public static Object[][] getTableContent(Connection conn, String tableName)
                                                         throws SQLException {
	String [] colNames = DatabaseData.getTableColumnNames(conn, tableName);
	String [] colClasses = DatabaseData.getTableColumnClasses(conn,
                                                                 tableName);

	Statement st = conn.createStatement();
	ResultSet rs = st.executeQuery("SELECT * FROM " + tableName + ";");

	ArrayList< Object[] > rowList = new ArrayList< Object[] >();
	while (rs.next()) {
		ArrayList< Object> cellList = new ArrayList< Object >();
		for (int i = 0; i < colClasses.length; i++) {
			Object cellValue = null;

			if (colClasses[i].equals(String.class.getName())) {
				cellValue = rs.getString (colNames[i]);
			}
			else if (colClasses[i].equals(Integer.class.getName())) {
				cellValue = new Integer
                                                  (rs.getInt (colNames[i]));
			}
			else if (colClasses[i].equals(Double.class.getName())) {
				cellValue = new Double
                                                (rs.getDouble (colNames[i]));
			}
			else if (colClasses[i].equals(Date.class.getName())) {
				cellValue = rs.getDate (colNames[i]);
			}
			else if (colClasses[i].equals(Float.class.getName())) {
				cellValue = rs.getFloat(colNames[i]);
			}
			else {
				System.out.println("PSQL Read Warning: "
                                  + "Unknown type encountered, "
                                  + "attempting to read as String");
				cellValue = rs.getString(colNames[i]);
			}
			cellList.add (cellValue);
		}
		Object[] cells = cellList.toArray();
		rowList.add(cells);
	}

	rs.close();
	st.close();

	Object [][] content = new Object[rowList.size()] [];
	for (int i=0; i< content.length; i++) {
		content[i] = rowList.get(i);
	}

	return content;
}

As you can see, the sole purpose of determining the Class type of the columns is so that we know which getX() method on the data.

And that’s it, you can now pull data from your database! In part 4, we’ll be putting everything together to create a working JTable.

Next: Pt. 4 Putting it all Together >>