The Java Specialists' Newsletter
Issue 1182005-12-19 Category: Tips and Tricks Java version: JDK 1.3+

Welcome to the 118th edition of The Java(tm) Specialists' Newsletter. My last newsletter evoked envious comments from the northern hemisphere, where you are already shivering in your boots. It was cold today in Cape Town (22 degrees C), so we could only go for a short swim in our pool :) You see, we are also suffering down here...

Since my childhood, the weather in Cape Town has been unpredictable until late December. Often lousy until Christmas Eve, with Christmas Day bringing beautiful beach weather. Whilst the 25th is celebrated with relatives, tradition dictates that everybody heads for the beaches on the 26th. Visitors, don't come in December. Come in March. Don't believe me? Have a look at Saunder's Rocks & Bantry Bay (originally called Botany Bay) on the eve of the 24th December 2003.

Upcoming Java Specialist Master Courses: - please click here to sign up.

As from May 2010, we are also offering this course on the island of Crete. We only accept 6 students per class in Crete, due to the size of our conference room. Please book early to avoid disappointment!

San Jose CA, Mar 16-19 2010, $3500
Ottawa, Canada, Mar 22-25 2010, $3500
Oslo, Norway, Apr 13-16 2010, Kr 24500
Montreal, Canada, Apr 20-23 2010, $3500
Toronto, Canada, May 17-20 2010, $3500
Chania, Crete, May 25-28, Jun 29-Jul 2 or Aug 24-27 2010, €2500

In-house courses if these dates or locations do not suit you - click here for more information.

A Simple Database Viewer

A while ago I was showing some Java programmers how to read meta-data from the database. I then quickly put together an application that reads all the tables and displays the contents. The initial version had a background fetching thread, progress bar, etc. This version is as simple as I could make it. It now weighs in at under 100 lines of Java code, including a couple of comments.

The first class we need is a ListModel that will contain all the table names. As is typical of database vendors, there is no standard way of knowing whether a table is a system table or a user table. Here we just show all of the tables. Finding all the tables is easy - we get the database meta-data and call the method getTables().

import javax.swing.*;
import java.sql.*;
import java.util.*;

public class TableNameListModel extends AbstractListModel {
  private final List listData = new ArrayList();
  public TableNameListModel(Connection con) throws SQLException {
    ResultSet rs = con.getMetaData().getTables(null,null,null,null);
    // you might need a filter here if your database mixes system
    // tables with user tables, e.g. Microsoft SQL Server
    while (rs.next()) {
      listData.add(rs.getString("TABLE_NAME"));
    }
    rs.close();
  }
  public int getSize() { return listData.size(); }
  public Object getElementAt(int i) { return listData.get(i); }
}
  

Next we write the table model for one database table. We use the Jakarta Commons DbUtils mentioned two newsletters ago. I perhaps should have mentioned in that newsletter that Spring offers a similar construct for executing database queries. In addition, if you want to present an object view over a database, Hibernate or JDO should be considered. Or if you need a web front-end nursing a database, look at Ruby on Rails.

One problem here is that the table names may be reserved keywords in some databases and there are different ways of escaping them. In Microsoft SQL Server we escape with [] and in MySQL with ``. We cannot use prepared statements since the query "SELECT * FROM ?" does not parse.

import org.apache.commons.dbutils.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
import java.util.Vector;

public class DatabaseTableModel extends DefaultTableModel {
  private final QueryRunner queryRunner = new QueryRunner();
  public DatabaseTableModel(Connection con, Object tableName)
      throws SQLException {
    // might need to delimit table names
    String sql = "SELECT * FROM " + tableName;
    queryRunner.query(con, sql, new ResultSetHandler() {
      public Object handle(ResultSet rs) throws SQLException {
        // extract the column names
        int numColumns = rs.getMetaData().getColumnCount();
        Vector column = new Vector();
        for (int i = 1; i <= numColumns; i++) {
          column.add(rs.getMetaData().getColumnName(i));
        }
        // extract the data
        Vector data = new Vector();
        while (rs.next()) {
          Vector row = new Vector();
          for (int i = 1; i <= numColumns; i++) {
            row.add(rs.getString(i));
          }
          data.add(row);
        }
        setDataVector(data, column);
        return null;
      }
    });
  }
}
  

Lastly we bind everything together with the view and controller. This view does not allow me to update the data in the database. It uses straight result sets to view the information. An alternative would be to use RowSets.

You run this program with command line arguments: driver, url, username and password. For example: java DatabaseShower com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/test root password.

import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;

public class DatabaseShower extends JFrame {
  private final JList names;
  private final JTable data = new JTable();
  public DatabaseShower(final Connection con, String title)
      throws SQLException {
    super(title);
    names = new JList(new TableNameListModel(con));
    names.addListSelectionListener(new ListSelectionListener() {
      public void valueChanged(ListSelectionEvent e) {
        if (!e.getValueIsAdjusting()) {
          Object tableName = names.getSelectedValue();
          if (tableName != null) {
            try {
              data.setModel(new DatabaseTableModel(con, tableName));
            } catch (SQLException ex) {
              ex.printStackTrace();
              data.setModel(new DefaultTableModel());
            }
          }
        }
      }
    });
    getContentPane().add(new JSplitPane(JSplitPane.HORIZONTAL_SPLIT,
        new JScrollPane(names), new JScrollPane(data)));
  }
  public static void main(String[] args) throws Exception {
    if (args.length != 4) {
      System.err.println("Usage: java DatabaseShower " +
          "driver url user password");
      System.exit(1);
    }
    Class.forName(args[0]);
    Connection con = DriverManager.getConnection(args[1], args[2],
        args[3]);
    String title = "Database Shower  ->  " + args[1];
    DatabaseShower frame = new DatabaseShower(con, title);
    frame.setSize(1024, 768);
    frame.setDefaultCloseOperation(EXIT_ON_CLOSE);
    frame.setLocationRelativeTo(null);
    frame.setVisible(true);
  }
}

The magic lines of code: TableNameListModel 18, DatabaseTableModel 34 and DatabaseShower 47. Total 99 lines.

Try it out. It is not perfect, and it is not meant to be. It provides a basic view into any database using Java JDBC. It demonstrates an MVC approach with Swing. We can use the same model with a different view, such as Java Server Pages. Paging and progress bars are left as an exercise to the reader.

Kind regards

Heinz

Tips and Tricks Articles Related Java Course