Jazz up ResultSets with a Swing JTable

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Teradata Employee

Jazz up ResultSets with a Swing JTable

HTML boasts a wide range of features to present data to end users. Add in the various frameworks that “sit on top” of HTML and the web represents a rich and powerful environment through which information may be delivered to end users.

While the web is a powerful environment, there are times when a windows based application might be better suited to the task at hand. This article presents a Java class library that makes this a lot easier.

This article is not intended to start a debate over the merits of web based applications over windows applications. Rather, it is intended to get the creative juices flowing and give you more choices in how you build your applications.

The class library

The class library consists of several classes. However to get started, you only need to know about two of them and even then, you only need to worry about the constructors. All of the relevant classes are found in the com.teradata.util.jtable package.

The following example shows how to run a query and create the table from a JDBC ResultSet.

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery (sqlText);

JDBCTableModel tableModel = new JDBCTableModel (rs);
JDBCSortingTable sortingTable = new JDBCSortingTable (tableModel);

All that remains is to incorporate the resulting JDBCSortingTable into your GUI. The “test program” section below covers this in more detail.

The library also has built in support for Large Objects.

The screen snapshot illustrates what the JDBCSortingTable looks like as part of the Test02 application. Null values are shown as (configurable) Orange cells. Large Objects are shown with the Binary or Character icons. The first few characters in a CLOB are also displayed in the result set table.

All of the classes belong to the com.teradata.jtable.util package.

User interaction

The user may sort the list by clicking on one a column header. Curiously, sorting is not provided in any of the underlying JTable classes, but is a feature of the JDBCSortingTable. Sorting is relatively quick and is performed via the sort method of the java.util.Collections class.

One feature the underlying JTable does provide is the ability for the user to rearrange the columns by dragging and dropping the column headers.

Placing the JDBCSortingTable into a JScrollPane will provide automatic scrolling of large table. Both Vertical and Horizontal scroll bars will be displayed automatically by the JScrollPane if the JDBCSortingTable is too big to fit into the available display area.

The Test programs

The attached test programs are extremely simple programs that illustrate how the JDBCSortingTable can be created and displayed in a JInternalFrame.

The first program (Test01.java) is IDE agnostic. As such most of its code relates to maintaining the GUI, not the JDBCSortingTable.

The second program (Test02.java) was built in NetBeans (v 6.5). In total it took about 10-15 minutes to create the second program as most of the code is generated by the NetBeans IDE. As a bonus it has a window that allows the user to enter a query entry (unlike Test01 where the query is hard coded).

Test01.java

The above example showing how to run a query and generate a JDBCSortingTable can be found in the executeQuery method. The connection details and query are hard coded in a series of attributes near the top of the class. Hard coding these keeps the example as simple as possible.

The rest of the executeQuery method establishes the database session and creates the various GUI items required to display the JDBCSortingTable.

In terms of creating the JDBCSortingTable there are only 2 relevant lines of code. These are:

  • The creation of the JDBCTableModel and
  • The creation of the JDBCSortingTable

All the rest of the code in the executeQuery method is still required even if I was displaying a standard Swing JTable. Note that in the second example, all of this “extra” code is reduced to a single line of code. This is because I simply reuse the Swing containers I created in the GUI, rather than creating them from scratch.

To run this example, simply save the files in appropriate directories (see below) compile and run. You will want to modify the connection details and hard coded query. These can all be easily found at the top of the class in a series of attribute (variable) declarations.

Test02.java

This example is a mini SQL Assistant (Queryman) and is shown above. In my opinion this is a far more interesting example. It still has hard coded connection details, but you can enter a query in the query window and run it. The Test02 example only supports queries that return a result set (e.g. select, help, explain etc) as we are talking about displaying results and action queries (e.g. insert, drop, create etc) don’t return much to display.

This example requires much less code entry (i.e. much less typing) because the IDE generates all the necessary code for all the GUI bits and pieces. The following steps show how I created this example in NetBeans, I expect a similar process could be used by users of Eclipse.

Firstly, I created a new Application Form (this includes a main method, a JFrame and a menu bar with a number of menu items). Then in the design window added the following:

  • A JDesktopPane
    • A JInternalFrame for the SQL text entry
      • A JScrollPane
        • A JTextArea – this is where the query text is entered (queryTextArea)
    • Another JInternalFrame for the result set display
      • A JScrollPane (resultSetScrollPane)

Additionally I cleaned up all the menu’s so that there was just an Execute menu item to which I associated the F5 function key (as an accelerator) and the exit menu item.

A bunch of String attributes at the top of the class contain the connection details. In addition to the connection details there is the connection attribute which maintains the connection to the database. This is different to the first test program as we want to keep the connection open while the program is running. Unlike Test01, we don’t want to have to establish a connection each time you run a query.

Next I added connect and disconnect methods. These are essentially copied and pasted from any other JDBC program you have in your library. The connect method is called from the JTable02 constructor. Thus the database connection is established when the program starts.

Finally, I added some event handlers (again via the IDE, so that it generates all of the standard code). My event handlers are:

  • exitMenuActionPerformed – invoked when the exit menu item is selected,
  • formWindowClosing – invoked when the window is closed (by clicking the X in the title bar or the choosing close from the system menu) and
  • runQueryMenuItemActionPerformed – invoked when the “Execute” menu item is selected or F5 is hit.

All of the rest of the code in Test02.java is generated by the IDE and is hidden from view!

The runQueryMenuItemActionPerformed method looks very much like the sample code shown above. Apart from the try / catch logic, there is only one additional code line:

resultSetScrollPane.setViewportView(sortingTable);

Once the JDBCSortingTable is created, the setViewPortView method associates the table with the JScrollPane and it is displayed in all its glory!

Downloading the source

The attachment contains both Test programs and the JDBCSortingTable package. It also contains NetBeans 6.5 project files for those of you who use NetBeans. For users of other IDE’s (and the Notepad recalcitrants), the source files are in a standard Java package directory structure. The two Large Object icons are also included.

To Do

There are a number of things that can be added to the JDBCSortingTable and its associated classes. Following are a couple of items that I’ve deliberately left out to allow you to tinker with the code to better understand how it works. In addition, you may find using a TableModel of benefit for some other aspects of your project (e.g. a history window). Here are some To Do’s:

if (descending) {
row = getRowCount () – row; /* Beware, this might not be 100% correct!
* think about a table with 2 rows and we are
* asked for rows 0 and 1! */
}
  • Add sort descending (Difficulty: Easy). To do this, add a boolean that tracks whether we are sorting in ascending or descending order. If the user consecutively clicks on the same column, the flag is toggled (don’t resort the list). Then in the getValueAt(int row, int col) method reverse the interpretation of the row number. That is use something like the following:
  • Add a context sensitive popup menu (Difficulty: Medium). The JDBCSortingTable supports both Binary and Character Large Objects. A really cool feature would be to display images and play sounds directly from your simple SQL Assistant tool. One way to do this is to add a context sensitive popup menu similar to the one below. To achieve this, you will need to add a MouseListener to your JDBCSortingTable. I suggest reviewing the Sun Tutorial about popup Menus to get your popup menu going. You can determine the row and column clicked on from the MouseEvent. This can then be used to extract the value from the table and determine if it is a lob and the kind (Character or Binary). To display an image, create an ImageIcon from the BLOB file JDBCTableLob.getFile (). The resulting ImageIcon can then be placed into a Label in a popup window. This will work for the common image types that ImageIcon supports (e.g. GIF, PNG &JPEG). A similar process can be used to play sounds. Use Google to search for “Play sounds in Java”. The javax.sound.sampled package supports a number of sampled audio file formats such as “AIFF”, “WAV” and “AU”.
  • Note: If you intend to work with Large Objects, refer to JDBCTableModel.cleanup ().

     
  • Add row numbers (Difficulty: Medium to hard). One way to do this is to set up another JTable to contain the row numbers. Use a table model to generate the row numbers – not a Table loaded with numbers from 1 to the number of rows in your result set. You will also need to tell your JScrollPane about the row header table. Refer to JScrollPane.setRowHeaderView (Component view).
  • Modify the display to alternately highlight rows (Difficulty: Medium). Modify the JDBCTableCellRenderer’s getTableCellRendererComponent method so that it changes the background colour depending upon whether or not the requested row number is even or odd. If you get really excited, you could even create a rainbow display by using different colours that cycle in larger numbers (i.e. instead of switching between just 2 colours for even/odd rows use 4 colours that cycle over 4 or 7 rows).

Conclusion

The JDBCSortingTable is a useful facility to have. I’ve used versions of it in a couple of projects with great success.

I hope that if you find that you are building a Swing application and need to display result sets in a tabular form that the above will be of use to you as well.

Below is an image showing a collage of what the Test application might look like with row numbers and various colour schemes. I call the colour schemes "Obnoxious", "Cycling Rainbow", "Tan Bark" and "Green Fanfold".

Tags (4)
3 REPLIES
kw
Enthusiast

Re: Jazz up ResultSets with a Swing JTable

Hi,

Where can the Test programs and the JDBCSortingTable package be downlaoded ?

Thanks,
Karl
Teradata Employee

Re: Jazz up ResultSets with a Swing JTable

The sample code is in the attachment near the top right of the article (JTableModelExample.zip).
I just tried downloading it and all the source, images etc seem to be in the zip file.
N/A

Re: Jazz up ResultSets with a Swing JTable

Hello reviving an old post....

I am trying to implement my beginner knowledge for the TO DO list you mentioned:

Let me know if im on the right track for your "Green Fanfold" but in light gray?

if ((row % 2) == 0){

            retValue.setBackground(Color.white);

        } else {

            retValue.setBackground(Color.LIGHT_GRAY);