Database Application With GUI
AAHC

Click on A to make all fonts on the page smaller.

Click on A to make all fonts on the page larger.

Click on HC to toggle high contrast mode. When you move your mouse over some bold words in high contrast mode, related words are automatically highlighted. Text is shown in black and white.


Refining the Application

Our last example demonstrated the general concepts used to implement JDBC, and some of the fundamental elements you encounter when writing database applications:

In this lesson, we'll enable the phonebook application to:

  1. create the database table using SQL that was written into the application initially to populate a table.
  2. provide the user with a graphical user interface that:
    • has a graphical display.
    • allows users to search the table for desired entries.
    • allows users to edit the database table (add and delete).
Improving the Appearance

Graphical user interfaces make an application more visually appealing, but they come at a programming cost. You must program every aspect you want to allow the user to experience. But even though you'll write lots of code, the end result may not look particularly impressive to the untrained eye. Such is the thankless life of the Java programmer. Your reward will be found in the satisfaction of knowing you've written amazing, clean code.

Copying an Existing Class

Create a new java4_Lesson13 project. If you're given the option to Open Associated Perspective, click No. Right-click your new project and select New | Package. For the Name, enter greenDB (this code was provided courtesy of David Green).

Copy the PasswordDialog class from the previous Project (java4_Lesson11) and paste it into the java4_Lesson13 project, greenDB package.

Open the new copy of PasswordDialog in the editor to verify that it contains package greenDB.

Save and run it. We aren't running it from another application, we're just checking the Login dialog itself.

Creating New Classes

The first new class for this application will connect it to the database. Although the JDBC provides more advanced features, you'll notice that the basic elements used when working with database are consistent. Most JDBC code that "talks" to a database looks similar.

As you type this class, you may notice some minor changes. For example, some variable and method names have been changed.

The inspectForTable() method has been made more general; it passes the name of the table you are looking for rather than having it hard-coded. This is always a better choice for reusable code.

In the java4_Lesson13 project, create a DatabaseManager class as shown. (Because it is in a different package, it can have the same name as the class used in previous lessons):

Type DatabaseManager as shown in blue:

CODE TO TYPE: DatabaseManager
package greenDB;
                
import java.sql.*;
                
public class DatabaseManager {
    private Connection conn;  
    private Statement stmt;
    private ResultSet rset;
                    
    public DatabaseManager (String username, String password) {  // the constructor for the database manager
        // Connect to database and execute the SQL commands for creating and initializing the Listings table.
        try {
            Class.forName ("com.mysql.jdbc.Driver");  // Load the MySQL JDBC driver
        }
        catch (ClassNotFoundException e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            e.printStackTrace();
            return;
        }
                    
        try {                   
            // Connect to the database.
            // Give the whole URL as a parameter rather than using a variable
            conn = DriverManager.getConnection("jdbc:mysql://sql.useractive.com:3306/" + username, username, password);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);   // Create a Statement
            // Execute the creation and initialization of table query 
            DatabaseMetaData aboutDB = conn.getMetaData(); 
            String [] tableType = {"TABLE"};
            ResultSet rs = aboutDB.getTables(null, null, "Listings",  tableType);   
            if (!inspectForTable (rs, "Listings")) {     // Find out if the table is already there
                // there is no table--make it from the initialization listing
                String [] SQL = initListingsTable();     // code for this method is below
                for (int i=0; i < SQL.length; i++) 
                {
                    stmt.execute(SQL[i]);
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
                    
    private String [] initListingsTable() {
        // Executable SQL commands for creating Listings table 
        // inserting initial names and phone numbers.
        String[]  SQL = {
            "create table Listings (" +
            "LAST_NAME  varchar (16)," +
            "FIRST_NAME varchar (16)," +
            "AREA_CODE  varchar(3)," +
            "PREFIX     varchar(3)," +
            "SUFFIX     varchar(4))",
            "insert into Listings values ('ANDERSON', 'JOHN',  '314', '825', '1695')",
            "insert into Listings values ('CABLES',   'WALLY', '212', '434', '9685')",
            "insert into Listings values ('FRY',      'EDGAR', '415', '542', '5885')",
            "insert into Listings values ('MARTIN',   'EDGAR', '665', '662', '9001')",
            "insert into Listings values ('TUCKER',   'JOHN',  '707', '696', '8541')",
        };
        return SQL;
    }
                    
    private boolean inspectForTable (ResultSet rs, String tableName)  throws SQLException {  // exception will be caught when method is used
        int i;
        ResultSetMetaData rsmd = rs.getMetaData ();  // Get the ResultSetMetaData to use for the column headings
        int numCols = rsmd.getColumnCount ();        // Get the number of columns in the result set
                    
        boolean more = rs.next ();
        while (more) {                               // Get each row, fetching until end of the result set
            for (i=1; i<=numCols; i++) { 
                if (rsmd.getColumnLabel(i) == "TABLE_NAME")   // Loop through each row, getting the column data looking for Tables
                    if  (rs.getString(i).equals(tableName))   // If the column is the TABLE_NAME, is it the one we are looking for?
                    {
                        System.out.println("Found one that equals " + rs.getString(i));
                        return true;
                    }
            }
            System.out.println("");
            more = rs.next ();              // Fetch the next result set row
        }
        return false;
    }
                    
    public void doGetQuery(String query) {  // rather than the "getEntries" of the previous example
        try {
            rset = stmt.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
                    
    public void doInsertQuery(String query) {   // rather than the hard-coded "addEntry" of the previous example
        try {  
            stmt.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
                    
    public ResultSet getResultSet() {  // a new method that will let the GUI get the resultSet to manipulate it
        return rset;
    } 
                    
    public void close(boolean remove){  // closes all open connections                                             
        try {
            if (remove) 
                stmt.execute ("drop table Listings;");                                 
            stmt.close();
            conn.close();
        }
        catch ( SQLException e ) {
            System.out.println ("\n*** SQLException caught ***\n");
            e.printStackTrace();
        }
    }
}

Save it (there's nothing to run yet).

We need a class to instantiate and start this application. We'll create the necessary class, but it won't be ready for consumption until we make the GUI. Please be patient--we need all the ingredients before we can cook!

In the java4_Lesson13 project, create SimplePhoneBook as shown:

Type SimplePhoneBook as shown in blue:

CODE TO TYPE: SimplePhoneBook
package greenDB;
                
import javax.swing.JFrame;
                
public class SimplePhoneBook {
    public static void main(String args[]) {   // Instantiate the phone book frame window and display it.
        PhoneBookFrame pbFrame = new PhoneBookFrame();
        pbFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        pbFrame.setVisible(true);
    }
}  // End SimplePhoneBook class

Even though there are errors, save it. The errors are there because we haven't defined the GUI and its components yet.

Creating the View

Now we need to create the JFrame for our application.

Click here to see what the PhoneBookFrame will look like, so you can compare the code and the GUI as you write the code.

In the java4_Lesson13 project, create a PhoneBookFrame class as shown:

Type PhoneBookFrame as shown in blue:

CODE TO TYPE: PhoneBookFrame
package greenDB;

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

class PhoneBookFrame extends JFrame {
    /** The initial user interface width, in pixels */
    private static final int WIDTH  = 577;
    /** The initial user interface height, in pixels */
    private static final int HEIGHT = 466;
    /** Provides methods for displaying a SQL result set in a JTable */
    // Commented out for now so the program can run without it.
    // private ListingsTableModel tblModel;
    /** Used to display the SQL result set in a cell format */
    private JTable table;
    /** A scrollable view for the SQL result set */
    private JScrollPane scrollPane;
    /** A text field for entering the phone listing's last name */
    private JTextField lNameField    = new JTextField(10);
    /** A text field for entering the phone listing's first name */
    private JTextField fNameField    = new JTextField(10);
    /** A text field for entering the phone listing's area code. The value in parentheses  
    is the number of columns (NOT necessarily characters) to allow for the field. */
    private JTextField areaCodeField = new JTextField(2);
    /** A text field for entering the phone listing's prefix */
    private JTextField prefixField   = new JTextField(2);
    /** A text field for entering the phone listing's extension */
    private JTextField suffixField   = new JTextField(3);
    /** Database Operations */
    private DatabaseManager myDB;
    
    public PhoneBookFrame() {
        String [] info = PasswordDialog.login(this);  // static login so can call from class
        // create and initialize the listings table
        myDB = new DatabaseManager(info[0], info[1]);
        // should have access so make GUI   
        JButton getButton = new JButton("Get");  // get the listing
        JButton add       = new JButton("+");    // add a listing
        JButton rem       = new JButton("-");    // remove a listing
        JLabel  space     = new JLabel(" ");
        // set the window size and title
        setTitle("Simple Phone Book");
        setSize(WIDTH, HEIGHT);
        // if user presses Enter, get button pressed
        getRootPane().setDefaultButton(getButton);
        // create the panel for looking up listing
        JPanel south = new JPanel();
        south.setLayout(new FlowLayout(FlowLayout.LEFT));
        
        south.add(new JLabel("Last:"));
        south.add(lNameField);
        south.add(new JLabel(" First:"));
        south.add(fNameField);
        south.add(new JLabel("  Phone:  ("));
        south.add(areaCodeField);
        south.add(new JLabel(") "));
        south.add(prefixField);
        south.add(new JLabel("-"));
        south.add(suffixField);
        south.add(new JLabel("   "));
        south.add(getButton);
        // create the panel for adding and deleting listings
        JPanel  east           = new JPanel();
        GridBagLayout gb       = new GridBagLayout();
        GridBagConstraints gbc = new GridBagConstraints();
        east.setLayout(gb);
        add.setFont(new Font("SansSerif", Font.BOLD, 12));
        rem.setFont(new Font("SansSerif", Font.BOLD, 12));
        
        gbc.fill = GridBagConstraints.BOTH;
        gbc.gridwidth = GridBagConstraints.REMAINDER;
        gb.setConstraints(add, gbc);
        gb.setConstraints(space, gbc);
        gb.setConstraints(rem, gbc);
        east.setLayout(gb);
        east.add(add);
        east.add(space);
        east.add(rem);

        // add the panels
        Container contentPane = getContentPane();
        contentPane.add(south, BorderLayout.SOUTH);
        contentPane.add(east, BorderLayout.EAST);
        // Add listeners
        // When the application closes, drop the Listings table and close the connection to MySQL
        addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent wEvent) {      
        	    myDB.close(false);  // We will want to save our additions to the PhoneBook, so don't drop table
            }
        });

        // when the UI first displays, do an empty lookup so the center panel doesn't look funny
        getButton.doClick();
        lNameField.requestFocus();  // set focus to last name field (most common lookup)
    }

    public DatabaseManager getDBManager() {
	    return myDB;
    }
}  // End PhoneBookFrame class

Save and run it (from SimplePhoneBook).

Whoops! We haven't added the Driver to this package yet--do it now:

Terminate the current running process from the console. Right-click the java4_Lesson13 Project and select Build Path | Add External Archives to open the file browser so you can get the driver. choose Build Path | Add External Archives, which opens the file browser for you to get the driver. Again, in the file dialog, start to type the path C:\jdbc\mysql-connector-java-5.1.5-bin.jar. The auto-complete feature should allow you to press Tab to fill in the file name mysql-connector-java-5.1.5-bin.jar. Then, click Open.

Run the SimplePhoneBook class.

We've made a nice little user interface here, but it may not seem that impressive yet because we have not added listeners. So far, the only component on the PhoneBookFrame that listens at all is the Window. At least we can close it.

Close the Window by clicking the X in the upper right corner of the application window.

Creating Controllers for the View

We have quite a few components here. Let's arrange them so we can see the table Listing we created through the DatabaseManager.

Edit PhoneBookFrame as shown in blue:

CODE TO EDIT: PhoneBookFrame
package greenDB;

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

class PhoneBookFrame extends JFrame {
    /** The initial user interface width, in pixels */
    private static final int WIDTH  = 577;
    /** The initial user interface height, in pixels */
    private static final int HEIGHT = 466;
    /** Provides methods for displaying a SQL result set in a JTable */
    // Commented out for now so the program can run without it.
    // private ListingsTableModel tblModel;
    /** Used to display the SQL result set in a cell format */
    private JTable table;
    /** A scrollable view for the SQL result set */
    private JScrollPane scrollPane;
    /** A text field for entering the phone listing's last name */
    private JTextField lNameField    = new JTextField(10);
    /** A text field for entering the phone listing's first name */
    private JTextField fNameField    = new JTextField(10);
    /** A text field for entering the phone listing's area code */
    private JTextField areaCodeField = new JTextField(2);
    /** A text field for entering the phone listing's prefix */
    private JTextField prefixField   = new JTextField(2);
    /** A text field for entering the phone listing's extension */
    private JTextField suffixField   = new JTextField(3);
    /** Database Operations */
    private DatabaseManager myDB;

    public PhoneBookFrame() {
        String [] info = PasswordDialog.login(this);  // static login so can call from class
        // create and initialize the listings table
        myDB = new DatabaseManager(info[0], info[1]);
        // Should have access so make GUI   
        JButton getButton = new JButton("Get");  // get the listing
        JButton add       = new JButton("+");    // add a listing
        JButton rem       = new JButton("-");    // remove a listing
        JLabel  space     = new JLabel(" ");
        // set the window size and title
        setTitle("Simple Phone Book");
        setSize(WIDTH, HEIGHT);
        // if user presses enter, get button pressed
        getRootPane().setDefaultButton(getButton);
        // create the panel for looking up listing
        JPanel south = new JPanel();
        south.setLayout(new FlowLayout(FlowLayout.LEFT));
        
        south.add(new JLabel("Last:"));
        south.add(lNameField);
        south.add(new JLabel(" First:"));
        south.add(fNameField);
        south.add(new JLabel("  Phone:  ("));
        south.add(areaCodeField);
        south.add(new JLabel(") "));
        south.add(prefixField);
        south.add(new JLabel("-"));
        south.add(suffixField);
        south.add(new JLabel("   "));
        south.add(getButton);
        
        // create the panel for adding and deleting listings
        JPanel  east           = new JPanel();
        GridBagLayout gb       = new GridBagLayout();
        GridBagConstraints gbc = new GridBagConstraints();
        east.setLayout(gb);
        add.setFont(new Font("SansSerif", Font.BOLD, 12));
        rem.setFont(new Font("SansSerif", Font.BOLD, 12));
        
        gbc.fill = GridBagConstraints.BOTH;
        gbc.gridwidth = GridBagConstraints.REMAINDER;
        gb.setConstraints(add, gbc);
        gb.setConstraints(space, gbc);
        gb.setConstraints(rem, gbc);
        east.setLayout(gb);
        east.add(add);
        east.add(space);
        east.add(rem);
        
        // add the panels
        Container contentPane = getContentPane();
        contentPane.add(south, BorderLayout.SOUTH);
        contentPane.add(east, BorderLayout.EAST);
        
        // Add listeners
        
        // When the application closes, drop the Listings table and close the connection to MySQL
        addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent wEvent) {      
        	    myDB.close(false);
            }
        });
        
        getButton.addActionListener(new GetListener());  // Add the listener for the getButton (GetListener inner class defined below)
        // when the UI first displays, do an empty lookup so the center panel doesn't look funny
        getButton.doClick();
        lNameField.requestFocus();    // set focus to last name field (most common lookup)
    }

    public DatabaseManager getDBManager(){
        return myDB;
    }
    
    /* inner class GetListener */
    class GetListener implements ActionListener {  // Gets the entries from the text fields

        public void actionPerformed(ActionEvent aEvent) {
            // Get whatever the user entered, trim any white space and change to upper case
            String last  = lNameField.getText().trim().toUpperCase();
            String first = fNameField.getText().trim().toUpperCase();
            String ac    = areaCodeField.getText().trim().toUpperCase();
            String pre   = prefixField.getText().trim().toUpperCase();
            String sfx   = suffixField.getText().trim().toUpperCase();
            
            // Replace any single quote chars w/ space char or SQL will think the ' is the end of the string
            last  = last.replace('\'', ' ');
            first = first.replace('\'', ' ');
            ac    = ac.replace('\'', ' ');
            pre   = pre.replace('\'', ' ');
            sfx   = sfx.replace('\'', ' ');
            // Get rid of the last result displayed if there is one
            if(scrollPane != null)
                getContentPane().remove(scrollPane);
            // Only execute the query if one or more fields have data, else just display an empty table
            if(last.length()  > 0 ||
             first.length() > 0 ||
             ac.length()    > 0 ||
             pre.length()   > 0 ||
             sfx.length()   > 0) {
                // build the query and execute it. Provide the results to the table model
                myDB.doGetQuery(buildQuery(last, first, ac, pre, sfx));
                ResultSet rset = myDB.getResultSet();
                tblModel = new ListingsTableModel(rset);
                table = new JTable(tblModel);
            } else {
                table = new JTable();
            }
            // Allows the user to only delete one record at a time
            table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
            // Add the table with the results to the contentPane and display it.
            scrollPane = new JScrollPane(table);
            getContentPane().add(scrollPane, BorderLayout.CENTER);
            pack();
            doLayout();
        }

        public String buildQuery(String last, String first, String ac, String pre, String sfx) {
            String whereClause = " where";
            // Build the where clause
            if(last.length() > 0)
                whereClause += (" LAST_NAME = '" + last + "'");

            if(first.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" FIRST_NAME = '" + first + "'");
            }

            if(ac.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" AREA_CODE = '" + ac + "'");
            }

            if(pre.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" PREFIX = '" + pre + "'");
            }

            if(sfx.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" SUFFIX = '" + sfx + "'");
            }

            return "select LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX from Listings" + whereClause;
        }
    }   // End GetListener inner class
}

We've got a few errors, all referring to a ListingTablesModel. We need to define this ListingTablesModel so that our GUI can display our entries in a table format.

In the java4_Lesson13 project, create a ListingsTableModel class as shown:

Type ListingsTableModel as shown in blue:

CODE TO TYPE: ListingsTableModel
package greenDB;

import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.table.AbstractTableModel;

class ListingsTableModel extends AbstractTableModel {
    /** The result set from the Listings table to be displayed */
    private ResultSet rs;

    public ListingsTableModel(ResultSet rs) {
        this.rs = rs;
    }

    public int getRowCount() {
        try {
            rs.last();
            return rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public int getColumnCount() {
        return 3;
    }

    public String getColumnName(int column) {
        try {
            String colName = rs.getMetaData().getColumnName(column + 1);
            // Return column names that look better than the database column names.
            // Since getColumnCount always returns 3, we only look for first 3 columns in
            // the result set.
            if(colName.equals("LAST_NAME"))
                return "Last Name";
            else if(colName.equals("FIRST_NAME"))
                return "First Name";
            else if(colName.equals("AREA_CODE"))
                return "Phone Number";
            else return colName;      // Should never get here.

        } catch (SQLException e) {
            e.printStackTrace();
            return "";
        }
    }

    public Object getValueAt(int row, int column) {
        try {
            rs.absolute(row + 1);
            // for the 3rd column in the results, combine all of the phone number fields for output
            if(column == 2)
                return "(" + rs.getObject(column + 1) + ") " + rs.getObject(column + 2) + "-" + rs.getObject(column + 3);
            else
                return rs.getObject(column + 1);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}  // End ListingsTableModel class

Go to java.sql.ResultSet and read over the methods invoked in this class, and all of the getx() methods therein:

Save ListingsTableModel.

Go back to the PhoneBookFrame class and its Instance Variables and uncomment the code that declares the ListingsTableModel, as shown in blue:

CODE TO EDIT: PhoneBookFrame
package greenDB;

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

public class PhoneBookFrame extends JFrame {
    /** The initial user interface width, in pixels */
    private static final int WIDTH  = 577;
    /** The initial user interface height, in pixels */
    private static final int HEIGHT = 466;
    /** Provides methods for displaying a SQL result set in a JTable */
    private ListingsTableModel tblModel;
    /** Used to display the SQL result set in a cell format */
    private JTable table;
    /** A scrollable view for the SQL result set */
    private JScrollPane scrollPane;
    /** A text field for entering the phone listing's last name */
    private JTextField lNameField    = new JTextField(10);
    /** A text field for entering the phone listing's first name */
    private JTextField fNameField    = new JTextField(10);
    /** A text field for entering the phone listing's area code */
    private JTextField areaCodeField = new JTextField(2);
    /** A text field for entering the phone listing's prefix */
    private JTextField prefixField   = new JTextField(2);
    /** A text field for entering the phone listing's extension */
    private JTextField suffixField   = new JTextField(3);
    /** Database Operations */
    private DatabaseManager myDB;
    
    public PhoneBookFrame() {
        String [] info = PasswordDialog.login(this);  // static login so can call from class
        // create and initialize the listings table
        myDB = new DatabaseManager(info[0], info[1]);
        // should have access so make GUI   
        JButton getButton = new JButton("Get");  // get the listing
        JButton add       = new JButton("+");    // add a listing
        JButton rem       = new JButton("-");    // remove a listing
        JLabel  space     = new JLabel(" ");
        // set the window size and title
        setTitle("Simple Phone Book");
        setSize(WIDTH, HEIGHT);
        // if user presses Enter, get button pressed
        getRootPane().setDefaultButton(getButton);
        // create the panel for looking up listing
        JPanel south = new JPanel();
        south.setLayout(new FlowLayout(FlowLayout.LEFT));
        
        south.add(new JLabel("Last:"));
        south.add(lNameField);
        south.add(new JLabel(" First:"));
        south.add(fNameField);
        south.add(new JLabel("  Phone:  ("));
        south.add(areaCodeField);
        south.add(new JLabel(") "));
        south.add(prefixField);
        south.add(new JLabel("-"));
        south.add(suffixField);
        south.add(new JLabel("   "));
        south.add(getButton);
        // create the panel for adding and deleting listings
        JPanel  east           = new JPanel();
        GridBagLayout gb       = new GridBagLayout();
        GridBagConstraints gbc = new GridBagConstraints();
        east.setLayout(gb);
        add.setFont(new Font("SansSerif", Font.BOLD, 12));
        rem.setFont(new Font("SansSerif", Font.BOLD, 12));
        
        gbc.fill = GridBagConstraints.BOTH;
        gbc.gridwidth = GridBagConstraints.REMAINDER;
        gb.setConstraints(add, gbc);
        gb.setConstraints(space, gbc);
        gb.setConstraints(rem, gbc);
        east.setLayout(gb);
        east.add(add);
        east.add(space);
        east.add(rem);
        
        // add the panels
        Container contentPane = getContentPane();
        contentPane.add(south, BorderLayout.SOUTH);
        contentPane.add(east, BorderLayout.EAST);
        // Add listeners
        // When the application closes, drop the Listings table and close the connection to MySQL
        addWindowListener(
            new WindowAdapter() {
                public void windowClosing(WindowEvent wEvent) {      
                    myDB.close(false);  // We will want to save our additions to the PhoneBook, so don't drop table
                }
        });
        
        getButton.addActionListener(new GetListener());  // Add the listener for the getButton (GetListener inner class defined below)
        
        // when the UI first displays, do an empty lookup so the center panel doesn't look funny
        getButton.doClick();
        lNameField.requestFocus();                                      // set focus to last name field (most common lookup)
    }
    
    public DatabaseManager getDBManager(){
        return myDB;
    }
    
    /* inner class GetListener */
    class GetListener implements ActionListener {  // Gets the entries from the text fields
    
        public void actionPerformed(ActionEvent aEvent) {
            // Get whatever the user entered, trim any white space and change to upper case
            String last  = lNameField.getText().trim().toUpperCase();
            String first = fNameField.getText().trim().toUpperCase();
            String ac    = areaCodeField.getText().trim().toUpperCase();
            String pre   = prefixField.getText().trim().toUpperCase();
            String sfx   = suffixField.getText().trim().toUpperCase();
            
            // Replace any single quote chars w/ space char or SQL will think the ' is the end of the string
            last  = last.replace('\'', ' ');
            first = first.replace('\'', ' ');
            ac    = ac.replace('\'', ' ');
            pre   = pre.replace('\'', ' ');
            sfx   = sfx.replace('\'', ' ');
            // Get rid of the last result displayed if there is one
            if(scrollPane != null)
                getContentPane().remove(scrollPane);
            // Only execute the query if one or more fields have data, else just display an empty table
            if(last.length()  > 0 ||
             first.length() > 0 ||
             ac.length()    > 0 ||
             pre.length()   > 0 ||
             sfx.length()   > 0) {
                // build the query and execute it. Provide the results to the table model
                myDB.doGetQuery(buildQuery(last, first, ac, pre ,sfx));
                ResultSet rset = myDB.getResultSet();
                tblModel = new ListingsTableModel(rset);
                table = new JTable(tblModel);
            } else {
                table = new JTable();
            }
            // Allows the user to only delete one record at a time
            table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
            // Add the table with the results to the contentPane and display it.
            scrollPane = new JScrollPane(table);
            getContentPane().add(scrollPane, BorderLayout.CENTER);
            pack();
            doLayout();
        }
        
        public String buildQuery(String last, String first, String ac, String pre, String sfx) {
            String whereClause = " where";
            // Build the where clause
            if(last.length() > 0)
                whereClause += (" LAST_NAME = '" + last + "'");
                
            if(first.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" FIRST_NAME = '" + first + "'");
            }
            
            if(ac.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" AREA_CODE = '" + ac + "'");
            }
            
            if(pre.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" PREFIX = '" + pre + "'");
            }
            
            if(sfx.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" SUFFIX = '" + sfx + "'");
            }
        
            return "select LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX from Listings" + whereClause;
        }
    }   // End GetListener inner class
}

Save PhoneBookFrame.

Once ListingsTableModel is saved and its variable declared (uncommented) in PhoneBookFrame, your programs should be free of errors, at least for the moment.

Run it (from SimplePhoneBook). Type John in the First Name text field.

Click Get to display our database table entries that have a first name of John:

While you're there, notice the area codes in the phone numbers. The other text fields are listening as well. Keep John in the First Name text field, and type 707 in the Phone ( ) area code field. Press Enter to display our database table entries that have a first name of John and a phone area code of 707:

Isn't that cool? We're on a roll! Now let's incorporate the other listeners.

In the java4_Lesson13 project, add a PhoneDocumentListener class as shown:

Type PhoneDocumentListener as shown in blue:

CODE TO TYPE: PhoneDocumentListener
package greenDB;

import javax.swing.JTextField;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;

class PhoneDocumentListener implements DocumentListener {
    /** The phone number text field to which this listener applies */
    private JTextField txtField;
    /** The number of characters that will cause focus to be transferred */
    private int numsAllowed;
    
    public PhoneDocumentListener(JTextField tf, int numsAllowed) {
        txtField = tf;
        this.numsAllowed = numsAllowed;
    }
    
    public void insertUpdate(DocumentEvent dEvent) {
        if(dEvent.getDocument().getLength() == numsAllowed)
            txtField.transferFocus();
    }
    
    /** Empty implementation. Method necessary for implementation of DocumentListener */
    public void removeUpdate(DocumentEvent dEvent) {}
    /** Empty implementation. Method necessary for implementation of DocumentListener */
    public void changedUpdate(DocumentEvent dEvent) {}
} // End PhoneDocumentListener class

Save it.

In the java4_Lesson13 project, create PhoneFocusListener as shown:

Type PhoneFocusListener as shown in blue:

CODE TO TYPE: PhoneFocusListener
package greenDB;

import java.awt.event.FocusEvent;
import java.awt.event.FocusListener;
import javax.swing.JTextField;

class PhoneFocusListener implements FocusListener {

    /** an event generated as a result of focus being gained on this telephone number field.  */
    public void focusGained(FocusEvent fEvent) {
        JTextField tf = (JTextField)fEvent.getSource();
        tf.setText("");
    }
    
    /** Not implemented */
    public void focusLost(FocusEvent fEvent){}
    
} // End PhoneFocusListener class

Save it.

We need to add these listeners to our PhoneBookFrame. While we're there, we'll also add the listeners for the add (+) and remove (-) buttons to the JFrame.

Edit PhoneBookFrame as shown in blue:

CODE TO EDIT: PhoneBookFrame
package greenDB;

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

class PhoneBookFrame extends JFrame {
    /** The initial user interface width, in pixels */
    private static final int WIDTH  = 577;
    /** The initial user interface height, in pixels */
    private static final int HEIGHT = 466;
    /** Provides methods for displaying a SQL result set in a JTable */
    private ListingsTableModel tblModel;
    /** Used to display the SQL result set in a cell format */
    private JTable table;
    /** A scrollable view for the SQL result set */
    private JScrollPane scrollPane;
    /** A text field for entering the phone listing's last name */
    private JTextField lNameField    = new JTextField(10);
    /** A text field for entering the phone listing's first name */
    private JTextField fNameField    = new JTextField(10);
    /** A text field for entering the phone listing's area code */
    private JTextField areaCodeField = new JTextField(2);
    /** A text field for entering the phone listing's prefix */
    private JTextField prefixField   = new JTextField(2);
    /** A text field for entering the phone listing's extension */
    private JTextField suffixField   = new JTextField(3);
    /** Database Operations */
    private DatabaseManager myDB;
 
    public PhoneBookFrame() {
        String [] info = PasswordDialog.login(this);  // static login so can call from class
        // create and initialize the listings table
        myDB = new DatabaseManager(info[0], info[1]);
        // Should have access so make GUI   

        JButton getButton = new JButton("Get");  // get the listing
        JButton add       = new JButton("+");    // add a listing
        JButton rem       = new JButton("-");    // remove a listing
        JLabel  space     = new JLabel(" ");
        // set the window size and title
        setTitle("Simple Phone Book");
        setSize(WIDTH, HEIGHT);
        // if user presses Enter, get button pressed
        getRootPane().setDefaultButton(getButton);
        // create the panel for looking up listing
        JPanel south = new JPanel();
        south.setLayout(new FlowLayout(FlowLayout.LEFT));
        
        south.add(new JLabel("Last:"));
        south.add(lNameField);
        south.add(new JLabel(" First:"));
        south.add(fNameField);
        south.add(new JLabel("  Phone:  ("));
        south.add(areaCodeField);
        south.add(new JLabel(") "));
        south.add(prefixField);
        south.add(new JLabel("-"));
        south.add(suffixField);
        south.add(new JLabel("   "));
        south.add(getButton);
        
        // create the panel for adding and deleting listings
        JPanel  east           = new JPanel();
        GridBagLayout gb       = new GridBagLayout();
        GridBagConstraints gbc = new GridBagConstraints();
        east.setLayout(gb);
        add.setFont(new Font("SansSerif", Font.BOLD, 12));
        rem.setFont(new Font("SansSerif", Font.BOLD, 12));
        
        gbc.fill = GridBagConstraints.BOTH;
        gbc.gridwidth = GridBagConstraints.REMAINDER;
        gb.setConstraints(add, gbc);
        gb.setConstraints(space, gbc);
        gb.setConstraints(rem, gbc);
        east.setLayout(gb);
        east.add(add);
        east.add(space);
        east.add(rem);
        
        // add the panels
        Container contentPane = getContentPane();
        contentPane.add(south, BorderLayout.SOUTH);
        contentPane.add(east, BorderLayout.EAST);
        
        // Add listeners
        // When the application closes, drop the Listings table and close the connection to MySQL
        addWindowListener(
                new WindowAdapter() {
                    public void windowClosing(WindowEvent wEvent) {      
        	            myDB.close(false);
                    }
                });
    
        areaCodeField.addFocusListener(new PhoneFocusListener());
        areaCodeField.getDocument().addDocumentListener(new PhoneDocumentListener(areaCodeField, 3));
        
        prefixField.addFocusListener(new PhoneFocusListener());
        prefixField.getDocument().addDocumentListener(new PhoneDocumentListener(prefixField, 3));
        
        suffixField.addFocusListener(new PhoneFocusListener());
        suffixField.getDocument().addDocumentListener(new PhoneDocumentListener(suffixField, 4));
        
        add.addActionListener(new AddListingListener(this));  // add (+) listener--define in own class
        
        // remove (-) listener--delete the highlighted listing from the result set and database
        rem.addActionListener(
            new ActionListener() {
                public void actionPerformed(ActionEvent aEvent) {
                    try {
                        int selected = table.getSelectedRow();
                        ResultSet rset  = myDB.getResultSet();
                        if(selected != -1 && selected < tblModel.getRowCount()) {
                            rset.absolute(table.getSelectedRow() + 1);
                            rset.deleteRow();
                            table.repaint();
                            table.clearSelection();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
        });
 
        getButton.addActionListener(new GetListener());  // Add the listener for the getButton (GetListener inner class defined below)
        // when the ui first displays do an empty lookup so the center panel doesn't look funny
        getButton.doClick();
        lNameField.requestFocus();    // set focus to last name field (most common lookup)
    }

    public DatabaseManager getDBManager(){
	    return myDB;
    }
    /* inner class GetListener */
    class GetListener implements ActionListener {  // Gets the entries from the text fields
    
        public void actionPerformed(ActionEvent aEvent) {
            // Get whatever the user entered, trim any white space and change to upper case
            String last  = lNameField.getText().trim().toUpperCase();
            String first = fNameField.getText().trim().toUpperCase();
            String ac    = areaCodeField.getText().trim().toUpperCase();
            String pre   = prefixField.getText().trim().toUpperCase();
            String sfx   = suffixField.getText().trim().toUpperCase();
            
            // Replace any single quote chars w/ space char or SQL will think the ' is the end of the string
            last  = last.replace('\'', ' ');
            first = first.replace('\'', ' ');
            ac    = ac.replace('\'', ' ');
            pre   = pre.replace('\'', ' ');
            sfx   = sfx.replace('\'', ' ');
            // Get rid of the last result displayed if there is one
            if(scrollPane != null)
                getContentPane().remove(scrollPane);
            // Only execute the query if one or more fields have data, else just display an empty table
            if(last.length()  > 0 ||
             first.length() > 0 ||
             ac.length()    > 0 ||
             pre.length()   > 0 ||
             sfx.length()   > 0) {
                // build the query and execute it. Provide the results to the table model
                myDB.doGetQuery(buildQuery(last, first, ac, pre ,sfx));
                ResultSet rset = myDB.getResultSet();
                tblModel = new ListingsTableModel(rset);
                table = new JTable(tblModel);
            } else {
                table = new JTable();
            }
            // Allows the user to only delete one record at a time
            table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
            // Add the table with the results to the contentPane and display it.
            scrollPane = new JScrollPane(table);
            getContentPane().add(scrollPane, BorderLayout.CENTER);
            pack();
            doLayout();
        }
        
        public String buildQuery(String last, String first, String ac, String pre, String sfx) {
            String whereClause = " where";
            // Build the where clause
            if(last.length() > 0)
                whereClause += (" LAST_NAME = '" + last + "'");
                
            if(first.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" FIRST_NAME = '" + first + "'");
            }
        
            if(ac.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" AREA_CODE = '" + ac + "'");
            }
            
            if(pre.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" PREFIX = '" + pre + "'");
            }
            
            if(sfx.length() > 0) {
                if(whereClause.length() > 6)
                    whereClause += " AND";
                whereClause += (" SUFFIX = '" + sfx + "'");
            }

            return "select LAST_NAME, FIRST_NAME, AREA_CODE, PREFIX, SUFFIX from Listings" + whereClause;
        }
    } // End GetListener inner class
}     

There's only one error; we might have expected it--we didn't define the AddListingListener.

Save it. Now, we'll allow the user to add entries.

In the java4_Lesson13 project, add the AddListingListener class as shown:

Type AddListingListener as shown in blue:

CODE TO TYPE: AddListingListener
package greenDB;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

class AddListingListener implements ActionListener {
    /** The SimplePhoneBook application frame */
    PhoneBookFrame pbf;
    
    public AddListingListener(PhoneBookFrame pbFrame) {
        pbf = pbFrame;
    }
    
    public void actionPerformed(ActionEvent aEvent) {
        AddListingDialog addDialog = new AddListingDialog(pbf);
        addDialog.setVisible(true);
    }
}  // End AddListingListener class 

Save it. Can you tell what our next class will be?

In the java4_Lesson13 project, add an AddListingDialog class as shown:

Type AddListingDialog as shown in blue below:

CODE TO TYPE: AddListingDialog
package greenDB;

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;

class AddListingDialog extends JDialog {
    /** A text field for entering the new phone listing's last name */
    private JTextField lNameField    = new JTextField(16);
    /** A text field for entering the new phone listing's first name */
    private JTextField fNameField    = new JTextField(16);
    /** A text field for entering the new phone listing's area code */
    private JTextField areaCodeField = new JTextField(2);
    /** A text field for entering the new phone listing's prefix */
    private JTextField prefixField   = new JTextField(2);
    /** A text field for entering the new phone listing's extension */
    private JTextField suffixField   = new JTextField(3);
    /** A button which, when clicked, will add the new listing to the Listings table */
    private JButton addButton;
    
    public AddListingDialog(final JFrame owner) {
        // set the dialog title and size
        super(owner, "Add Listing", true);
        setSize(280, 150);
        
        // Create the center panel which contains the fields for entering the new listing
        JPanel center = new JPanel();
        center.setLayout(new GridLayout(3, 2));
        center.add(new JLabel(" Last Name:"));
        center.add(lNameField);
        center.add(new JLabel(" First Name:"));
        center.add(fNameField);
        
        // Here we create a panel for the phone number fields and add it to the center panel.
        JPanel pnPanel = new JPanel();
        pnPanel.add(new JLabel("("));
        pnPanel.add(areaCodeField);
        pnPanel.add(new JLabel(") "));
        pnPanel.add(prefixField);
        pnPanel.add(new JLabel("-"));
        pnPanel.add(suffixField);
        center.add(new JLabel(" Phone Number:"));
        center.add(pnPanel);
        
        // Create the south panel, which contains the buttons
        JPanel south = new JPanel();
        addButton    = new JButton("Add");
        JButton cancelButton = new JButton("Cancel");
        addButton.setEnabled(false);
        south.add(addButton);
        south.add(cancelButton);
        
        // Add listeners to the fields and buttons
        lNameField.getDocument().addDocumentListener(new InputListener());
        fNameField.getDocument().addDocumentListener(new InputListener());
        areaCodeField.getDocument().addDocumentListener(new InputListener());
        prefixField.getDocument().addDocumentListener(new InputListener());
        suffixField.getDocument().addDocumentListener(new InputListener());
        
        areaCodeField.getDocument().addDocumentListener(new PhoneDocumentListener(areaCodeField, 3));
        prefixField.getDocument().addDocumentListener(new PhoneDocumentListener(prefixField, 3));
        suffixField.getDocument().addDocumentListener(new PhoneDocumentListener(suffixField, 4));
        
        areaCodeField.addFocusListener(new PhoneFocusListener());
        prefixField.addFocusListener(new PhoneFocusListener());
        suffixField.addFocusListener(new PhoneFocusListener());
        
        // listeners to close the window
        addButton.addActionListener(
            new ActionListener() {
                public void actionPerformed(ActionEvent aEvent) {
                    // ((PhoneBookFrame)owner).doInsertQuery(buildQuery());
                    DatabaseManager ownersDB = ((PhoneBookFrame)owner).getDBManager();
                    ownersDB.doInsertQuery(buildQuery());
                    dispose();
                }
        });
        
        cancelButton.addActionListener(
            new ActionListener() {
                public void actionPerformed(ActionEvent aEvent) {
                    dispose();
                }
        });
        
        // Add the panels to the dialog window
        Container contentPane = getContentPane();
        contentPane.add(center, BorderLayout.CENTER);
        contentPane.add(south,  BorderLayout.SOUTH);
    }
    
    public String buildQuery() {
        // Get the data entered by the user, trim the white space and change to upper case
        String query = "";
        String last  = lNameField.getText().trim().toUpperCase();
        String first = fNameField.getText().trim().toUpperCase();
        String ac    = areaCodeField.getText().trim().toUpperCase();
        String pre   = prefixField.getText().trim().toUpperCase();
        String sfx   = suffixField.getText().trim().toUpperCase();
        
        // Replace any single quote chars with a space char so the string will not get truncated by SQL
        last  = last.replace('\'', ' ');
        first = first.replace('\'', ' ');
        ac    = ac.replace('\'', ' ');
        pre   = pre.replace('\'', ' ');
        sfx   = sfx.replace('\'', ' ');
        
        // build  and return the insert statement
        return new String("insert into Listings values ('" + last + "', '" +
         first + "', '" +
         ac + "', '" +
         pre + "', '" +
         sfx + "')");
    }
    
    /* inner class InputListener */
    class InputListener implements DocumentListener {
        public void insertUpdate(DocumentEvent dEvent) {
            // If first name and last name have data and phone number is complete
            // enable the add button, give it focus and make it clickable if
            // user presses Enter.
            if(lNameField.getDocument().getLength()     > 0 &&
             fNameField.getDocument().getLength()     > 0 &&
             areaCodeField.getDocument().getLength() == 3 &&
             prefixField.getDocument().getLength()   == 3 &&
             suffixField.getDocument().getLength()   == 4) {
             
                addButton.setEnabled(true);
                if(dEvent.getDocument() == suffixField.getDocument()) {
                    addButton.requestFocus();
                    getRootPane().setDefaultButton(addButton);
                }
            }
        }

        public void removeUpdate(DocumentEvent dEvent) {
            // If last name or first name don't have data or phone number
            // is  not complete, disable the Add button.
            if(lNameField.getDocument().getLength()   == 0 ||
             fNameField.getDocument().getLength()   == 0 ||
             areaCodeField.getDocument().getLength() < 3 ||
             prefixField.getDocument().getLength()   < 3 ||
             suffixField.getDocument().getLength()   < 4 )

                addButton.setEnabled(false);
        }
        
        /** Empty implementation. Method necessary for implementation of DocumentListener */
        public void changedUpdate(DocumentEvent dEvent) {}
        
    } // End InputListener inner class
  
} // End AddListingDialog class  

Save it. We should be all set now!

Run it from SimplePhoneBook.

In the Phone field, type 314825 without moving the mouse or using the Tab key--see how the "focus" moves to the other Phone field area? That's really convenient for the user.

Now, click Get. The entry that has those first 6 digits in the phone number is retrieved.

Clear the Phone number fields, and in the First name field, type Edgar and press Enter.

So there ARE names in our table other than John! Now let's add some entries of our own.

Click the AddButton (+). Type a name in the Last Name field. The Add button is not an option--can you see where this was set in your code?

Add the remaining information into the Add Listing Dialog Box. Note that when all of the fields have values, Add is enabled. Click Add now.

Note One SQL statement and/or query does not implicitly invoke others. It is up to the programmer to tell the application what to do.

To see your new entry, query for it, using the appropriate text field(s).

Play with the GUI. You can cut and paste from one text field to another and there's cool ways to of tabs, etc. And, with each capability you notice, look at the code and see how it was done--or, if it was inherited, how someone (Java? Swing?) did all the work for you!

Additional Resources

We've created a good application and learned some of the basics of JDBC. But there's still a lot more out there. Here are a few more useful resources:

Oracle's JDBC page has links to:

We've illustrated many facets of JDBC, but lots of additional techniques are available. Among other tasks, you may want to:

But wait--there's more:

And of course, there are books:

Database Programming with JDBC & Java

The next lesson has additional resource links for SQL and documentation capabilities, because we want you to write great Java code!