import java.sql.*;
import javax.swing.table.*;
/**
* This class encapsulates a JDBC database connection and, given a SQL query
* as a string, returns a ResultSetTableModel object suitable for display
* in a JTable Swing component
**/
public class ResultSetTableModelFactory {
Connection connection; // Holds the connection to the database
/** The constructor method uses the arguments to create db Connection */
public ResultSetTableModelFactory(String driverClassName, String dbname,
String username, String password)
throws ClassNotFoundException, SQLException
{
// Look up the JDBC driver by class name. When the class loads, it
// automatically registers itself with the DriverManager used in
// the next step.
Class driver = Class.forName(driverClassName);
// Now use that driver to connect to the database
connection = DriverManager.getConnection(dbname, username, password);
}
/**
* This method takes a SQL query, passes it to the database, obtains the
* results as a ResultSet, and returns a ResultSetTableModel object that
* holds the results in a form that the Swing JTable component can use.
**/
public ResultSetTableModel getResultSetTableModel(String query)
throws SQLException
{
// If we've called close(), then we can't call this method
if (connection == null)
throw new IllegalStateException("Connection already closed.");
// Create a Statement object that will be used to excecute the query.
// The arguments specify that the returned ResultSet will be
// scrollable, read-only, and insensitive to changes in the db.
Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// Run the query, creating a ResultSet
ResultSet r = statement.executeQuery(query);
// Create and return a TableModel for the ResultSet
return new ResultSetTableModel(r);
}
/**
* Call this method when done with the factory to close the DB connection
**/
public void close() {
try { connection.close(); } // Try to close the connection
catch (Exception e) {} // Do nothing on error. At least we tried.
connection = null;
}
/** Automatically close the connection when we're garbage collected */
protected void finalize() { close(); }
}
import java.sql.*;
import javax.swing.table.*;
import javax.swing.event.*;
/**
* This class takes a JDBC ResultSet object and implements the TableModel
* interface in terms of it so that a Swing JTable component can display the
* contents of the ResultSet. Note that it requires a scrollable JDBC 2.0
* ResultSet. Also note that it provides read-only access to the results
**/
public class ResultSetTableModel implements TableModel {
ResultSet results; // The ResultSet to interpret
ResultSetMetaData metadata; // Additional information about the results
int numcols, numrows; // How many rows and columns in the table
/**
* This constructor creates a TableModel from a ResultSet. It is package
* private because it is only intended to be used by
* ResultSetTableModelFactory, which is what you should use to obtain a
* ResultSetTableModel
**/
ResultSetTableModel(ResultSet results) throws SQLException {
this.results = results; // Save the results
metadata = results.getMetaData(); // Get metadata on them
numcols = metadata.getColumnCount(); // How many columns?
results.last(); // Move to last row
numrows = results.getRow(); // How many rows?
}
/**
* Call this when done with the table model. It closes the ResultSet and
* the Statement object used to create it.
**/
public void close() {
try { results.getStatement().close(); }
catch(SQLException e) {};
}
/** Automatically close when we're garbage collected */
protected void finalize() { close(); }
// These two TableModel methods return the size of the table
public int getColumnCount() { return numcols; }
public int getRowCount() { return numrows; }
// This TableModel method returns columns names from the ResultSetMetaData
public String getColumnName(int column) {
try {
return metadata.getColumnLabel(column+1);
} catch (SQLException e) { return e.toString(); }
}
// This TableModel method specifies the data type for each column.
// We could map SQL types to Java types, but for this example, we'll just
// convert all the returned data to strings.
public Class getColumnClass(int column) { return String.class; }
/**
* This is the key method of TableModel: it returns the value at each cell
* of the table. We use strings in this case. If anything goes wrong, we
* return the exception as a string, so it will be displayed in the table.
* Note that SQL row and column numbers start at 1, but TableModel column
* numbers start at 0.
**/
public Object getValueAt(int row, int column) {
try {
results.absolute(row+1); // Go to the specified row
Object o = results.getObject(column+1); // Get value of the column
if (o == null) return null;
else return o.toString(); // Convert it to a string
} catch (SQLException e) { return e.toString(); }
}
// Our table isn't editable
public boolean isCellEditable(int row, int column) { return false; }
// Since its not editable, we don't need to implement these methods
public void setValueAt(Object value, int row, int column) {}
public void addTableModelListener(TableModelListener l) {}
public void removeTableModelListener(TableModelListener l) {}
}
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.SQLException;
/**
* This class creates a Swing GUI that allows the user to enter a SQL query.
* It then obtains a ResultSetTableModel for the query and uses it to display
* the results of the query in a scrolling JTable component.
**/
public class QueryFrame extends JFrame {
ResultSetTableModelFactory factory; // A factory to obtain our table data
JTextField query; // A field to enter a query in
JTable table; // The table for displaying data
JLabel msgline; // For displaying messages
/**
* This constructor method creates a simple GUI and hooks up an event
* listener that updates the table when the user enters a new query.
**/
public QueryFrame(ResultSetTableModelFactory f) {
super("QueryFrame"); // Set window title
// Arrange to quit the program when the user closes the window
addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent e) { System.exit(0); }
});
// Remember the factory object that was passed to us
this.factory = f;
// Create the Swing components we'll be using
query = new JTextField(); // Lets the user enter a query
table = new JTable(); // Displays the table
msgline = new JLabel(); // Displays messages
// Place the components within this window
Container contentPane = getContentPane();
contentPane.add(query, BorderLayout.NORTH);
contentPane.add(new JScrollPane(table), BorderLayout.CENTER);
contentPane.add(msgline, BorderLayout.SOUTH);
// Now hook up the JTextField so that when the user types a query
// and hits ENTER, the query results get displayed in the JTable
query.addActionListener(new ActionListener() {
// This method is invoked when the user hits ENTER in the field
public void actionPerformed(ActionEvent e) {
// Get the user's query and pass to displayQueryResults()
displayQueryResults(query.getText());
}
});
}
/**
* This method uses the supplied SQL query string, and the
* ResultSetTableModelFactory object to create a TableModel that holds
* the results of the database query. It passes that TableModel to the
* JTable component for display.
**/
public void displayQueryResults(final String q) {
// It may take a while to get the results, so give the user some
// immediate feedback that their query was accepted.
msgline.setText("Contacting database...");
// In order to allow the feedback message to be displayed, we don't
// run the query directly, but instead place it on the event queue
// to be run after all pending events and redisplays are done.
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
// This is the crux of it all. Use the factory object
// to obtain a TableModel object for the query results
// and display that model in the JTable component.
table.setModel(factory.getResultSetTableModel(q));
// We're done, so clear the feedback message
msgline.setText(" ");
}
catch (SQLException ex) {
// If something goes wrong, clear the message line
msgline.setText(" ");
// Then display the error in a dialog box
JOptionPane.showMessageDialog(QueryFrame.this,
new String[] { // Display a 2-line message
ex.getClass().getName() + ": ",
ex.getMessage()
});
}
}
});
}
/**
* This simple main method tests the class. It expects four command-line
* arguments: the driver classname, the database URL, the username, and
* the password
**/
public static void main(String args[]) throws Exception {
// Create the factory object that holds the database connection using
// the data specified on the command line
ResultSetTableModelFactory factory =
new ResultSetTableModelFactory(args[0], args[1], args[2], args[3]);
// Create a QueryFrame component that uses the factory object.
QueryFrame qf = new QueryFrame(factory);
// Set the size of the QueryFrame, then pop it up
qf.setSize(500, 600);
qf.setVisible(true);
}
}
Back to article.