In this example you will have the opportunity to dynamically query the database

Part 1

// Fig. 23.27: ResultSetTableModel.java from Java how to program
// A TableModel that supplies ResultSet data to a JTable.

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

// ResultSet rows and columns are counted from 1 and JTable rows and columns are counted from 0.
// When processing ResultSet rows or columns for use in a JTable, it is necessary to add 1
// to the row or column number to manipulate the appropriate ResultSet column
// (i.e., JTable column 0 is ResultSet column 1 and JTable row 0 is ResultSet row 1).

public class ResultSetTableModel extends AbstractTableModel {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private ResultSetMetaData metaData;
private int numberOfRows;

// keep track of database connection status
private boolean connectedToDatabase = false;

// initialize resultSet and obtain its meta data object determine number of rows
public ResultSetTableModel( String driver, String url,String query ) throws SQLException, ClassNotFoundException
{

Class.forName( driver );

connection = DriverManager.getConnection( url );

statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY );

// update database connection status
connectedToDatabase = true;

// set query and execute it
setQuery( query );
}
//=============================================================================
// get class that represents column type
public Class getColumnClass( int column ) throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

// determine Java class of column
try {
String className = metaData.getColumnClassName( column + 1 );

return Class.forName( className );
}

// catch SQLExceptions and ClassNotFoundExceptions
catch ( Exception exception ) {exception.printStackTrace(); }

// if problems occur above, assume type Object
return Object.class;
}
//============================================================================

// get name of a particular column in ResultSet
public String getColumnName( int column ) throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

// determine column name
try {
return metaData.getColumnName( column + 1 );
}

// catch SQLExceptions and print error message
catch ( SQLException sqlException ) {sqlException.printStackTrace();}

// if problems, return empty string for column name
return "";
}
//=============================================================================
// get number of columns in ResultSet
public int getColumnCount() throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

// determine number of columns
try {
return metaData.getColumnCount();
}

// catch SQLExceptions and print error message
catch ( SQLException sqlException ) {sqlException.printStackTrace();}

// if problems occur above, return 0 for number of columns
return 0;
}
//=============================================================================

// return number of rows in ResultSet
public int getRowCount() throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

return numberOfRows;
}
//=============================================================================

// obtain value in particular row and column
public Object getValueAt( int row, int column ) throws IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

// obtain a value at specified ResultSet row and column
try {
resultSet.absolute( row + 1 );

return resultSet.getObject( column + 1 );
}

// catch SQLExceptions and print error message
catch ( SQLException sqlException ) { sqlException.printStackTrace();}

// if problems, return empty string object
return "";
}
//=============================================================================

// set new database query string
public void setQuery( String query ) throws SQLException, IllegalStateException
{
// ensure database connection is available
if ( !connectedToDatabase )
throw new IllegalStateException( "Not Connected to Database" );

// specify query and execute it
resultSet = statement.executeQuery( query );

// obtain meta data for ResultSet
metaData = resultSet.getMetaData();

// determine number of rows in ResultSet
resultSet.last(); // move to last row
numberOfRows = resultSet.getRow(); // get row number

// notify JTable that model has changed
// inherited from class AbstractTableModel, to notify any JTable using this ResultSetTableModel object as its model
// that the structure of the model has changed (i.e., the underlying ResultSet contains new data or new columns).
// This causes the JTable to repopulate its rows and columns with the new ResultSet data, refresh
fireTableStructureChanged();
}
//=============================================================================

// close Statement and Connection
public void disconnectFromDatabase()
{
// close Statement and Connection
try {
statement.close();
connection.close();
}

// catch SQLExceptions and print error message
catch ( SQLException sqlException ) { sqlException.printStackTrace(); }

// update database connection status
finally { connectedToDatabase = false;}
}
}

Part 2

// Fig. 23.30: DisplayQueryResults.java from Java how to program
// Display the contents of the Authors table in the Books database.

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;

public class DisplayQueryResults extends JFrame {

static final String JDBC_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
static final String DATABASE_URL = "jdbc:derby:books";

// default query retrieves all data from authors table
static final String DEFAULT_QUERY = "SELECT * FROM authors";

private ResultSetTableModel tableModel;                                                // 1 - a
private JTextArea queryArea;
//=================================================================
public DisplayQueryResults()
{
super( "Displaying Query Results" );

// create ResultSetTableModel and display database table
try {

System.setProperty( "derby.system.home", "C:/jdbc/derby/examples" );

// create TableModel for results of query SELECT * FROM authors
tableModel = new ResultSetTableModel( JDBC_DRIVER, DATABASE_URL,DEFAULT_QUERY );        // 1- b

// set up JTextArea in which user types queries
queryArea = new JTextArea( DEFAULT_QUERY, 3, 100 );
queryArea.setWrapStyleWord( true );
queryArea.setLineWrap( true );

JScrollPane scrollPane = new JScrollPane( queryArea,
ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED,
ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER );

JButton submitButton = new JButton( "Submit Query" );

// create Box to manage placement of queryArea and submitButton in GUI
Box box = Box.createHorizontalBox();
box.add( scrollPane );
box.add( submitButton );

// create JTable delegate for tableModel , registers the JTable as a listener for TableModelEvents generated by the ResultSetTableModel
JTable resultTable = new JTable( tableModel );      //   2

// place GUI components on content pane
Container c = getContentPane();
c.add( box, BorderLayout.NORTH );
c.add( new JScrollPane( resultTable ), BorderLayout.CENTER );

//-----------------------------------------------------------------------

submitButton.addActionListener(
new ActionListener() {

// pass query to table model
public void actionPerformed( ActionEvent event )
{
// perform a new query
try { tableModel.setQuery( queryArea.getText() ); }

// catch SQLExceptions when performing a new query
catch ( SQLException sqlException )
{
JOptionPane.showMessageDialog( null,sqlException.getMessage(), "Database error",JOptionPane.ERROR_MESSAGE );

// try to recover from invalid user query by executing default query
try {
tableModel.setQuery( DEFAULT_QUERY );
queryArea.setText( DEFAULT_QUERY );
}

// catch SQLException when performing default query
catch ( SQLException sqlException2 ) {
JOptionPane.showMessageDialog( null, sqlException2.getMessage(),"Database error",JOptionPane.ERROR_MESSAGE );

// ensure database connection is closed
tableModel.disconnectFromDatabase();

System.exit( 1 ); // terminate application

} // end inner catch
} // end outer catch
} // end actionPerformed
} // end ActionListener inner class
); // end call to addActionListener

// set window size and display window
setSize( 500, 250 );
setVisible( true );
} // end try

// catch ClassNotFoundException thrown by ResultSetTableModel if database driver not found
catch ( ClassNotFoundException classNotFound ) {
JOptionPane.showMessageDialog( null,"Cloudscape driver not found", "Driver not found",JOptionPane.ERROR_MESSAGE );
System.exit( 1 ); // terminate application
}

// catch SQLException thrown by ResultSetTableModel if problems occur while setting up database connection and querying database
catch ( SQLException sqlException ) {
JOptionPane.showMessageDialog( null, sqlException.getMessage(),"Database error", JOptionPane.ERROR_MESSAGE );

// ensure database connection is closed
tableModel.disconnectFromDatabase();

System.exit( 1 ); // terminate application
}

// dispose of window when user quits application (this overrides the default of HIDE_ON_CLOSE)
setDefaultCloseOperation( DISPOSE_ON_CLOSE );

//----------------------------------------------------------------

// ensure database connection is closed when user quits application
addWindowListener(
new WindowAdapter() {
// disconnect from database and exit when window has closed
public void windowClosed( WindowEvent event )
{
tableModel.disconnectFromDatabase();
System.exit( 0 );
}
}
);
} // end DisplayQueryResults constructor
//===============================================================
// execute application
public static void main( String args[] )
{
new DisplayQueryResults();
}
}