Easy to Learn Java: Programming Articles, Examples and Tips

Start with Java in a few days with Java Lessons or Lectures

Home

Code Examples

Java Tools

More Java Tools!

Java Forum

All Java Tips

Books

Submit News
Search the site here...
Search...
 
Search the JavaFAQ.nu
1000 Java Tips ebook

1000 Java Tips - Click here for the high resolution copy!1000 Java Tips - Click here for the high resolution copy!

Java Screensaver, take it here

Free "1000 Java Tips" eBook is here! It is huge collection of big and small Java programming articles and tips. Please take your copy here.

Take your copy of free "Java Technology Screensaver"!.

A Simple Database Viewer

JavaFAQ Home » Story by Dr. Kabutz Go to all tips in Story by Dr. Kabutz


Bookmark and Share

The Java Specialists' Newsletter [Issue 118]

Author: Dr. Heinz M. Kabutz

JDK version: JDK 1.3+

Category: Tips and Tricks

If you are reading this, and have not subscribed, please consider doing it now by going to our subscribe page [http://www.javaspecialists.co.za/archive/subscribe.jsp]. You can subscribe either via email or RSS.


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 Smile 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 [http://www.javaspecialists.co.za/pics/BantryBay.jpg] (originally called Botany Bay) on the eve of the 24th December 2003.

We are going to have some exciting specials for our Java courses on Crete in Greece [http://www.javaspecialists.co.za/crete] . These will be announced exclusively on the e-mail edition of this newsletter and will be on a first-come-first-served basis.

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 [http://jakarta.apache.org/commons/dbutils] mentioned two newsletters ago [http://www.javaspecialists.co.za/archive/newsletter.do?issue=116] . 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 [http://www.rubyonrails.com] .

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


Copyright 2000-2005 Maximum Solutions, South Africa

Reprint Rights. Copyright subsists in all the material included in this email, but you may freely share the entire email with anyone you feel may be interested, and you may reprint excerpts both online and offline provided that you acknowledge the source as follows: This material from The Java(tm) Specialists' Newsletter by Maximum Solutions (South Africa). Please contact Maximum Solutions [http://www.javaspecialists.co.za] for more information.

Java and Sun are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. Maximum Solutions is independent of Sun Microsystems, Inc.

 Printer Friendly Page  Printer Friendly Page
 Send to a Friend  Send to a Friend

.. Bookmark and Share

Search here again if you need more info!
Custom Search



Home Code Examples Java Forum All Java Tips Books Submit News, Code... Search... Offshore Software Tech Doodling

RSS feed Java FAQ RSS feed Java FAQ News     

    RSS feed Java Forums RSS feed Java Forums

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest 1999-2006 by Java FAQs Daily Tips.

Interactive software released under GNU GPL, Code Credits, Privacy Policy