Databases and Java: Processing Information
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.


Getting Results

In this lesson, we'll add methods to our code that will allow us to:

Databases and SQL

In the previous lesson, we connected to the database, created a table, and put information into it. If we use JDBC to populate the table or put the information into the database, we use an instance of the Statement interface to execute SQL statements like these:

  • CREATE TABLE (makes whole tables)
  • INSERT (adds a row)
  • DELETE (removes a row)
  • UPDATE (changes an existing value in a column or columns)

We used an instance statement of Statement to execute a CREATE and INSERT. This course uses the technique that uses JDBC, but information can be inserted into a table in any of these ways:

  1. Using the database or IDE graphical interface.
  2. Using ANT (and .xml files).
  3. Using JDBC SQL written into an application.

Although we've only created one table in our example, we can add and manipulate any number of tables using the same techniques. For instance, we can JOIN related data from multiple tables. However, in this lesson, we'll continue to use our simple example and start the procedure for retrieving information.

The JDBC returns results from a Statement's query in a ResultSet object, so we need an instance of the ResultSet interface to hold our results. The ResultSet interface provides methods for retrieving and manipulating the results of queries; particularly, it provides getter methods (such as getBoolean() and getLong()) for retrieving column values from the current row.

For specifics and examples, see the Oracle tutorial Relational Database Overview.

ResultSet

A ResultSet is the table of results from your query. This table can have one or more rows. You need to manipulate these results to get the information you want from the table. Although we usually look at a table as a two-dimensional array, the JDBC provides the ResultSet to manipulate this array to extract whatever information you need. We'll go over some examples in this lesson and the next, but the Oracle tutorial is always a good source for more information: Retrieving Values from Result Sets—and of course, the API. Go to the java.sql.ResultSet interface and read the introduction.

Getting Information About Information

It's tough to anticipate the best ways to manipulate data when we're not even sure which data is present. We know how to make for loops to go through two-dimensional arrays, but in the database table, we don't know how many rows exist! We can't write code that goes to some arrays.length because we don't even know that we have an array--we only have information returned from a table. We need to know how many rows are in that information. In other words, we need information about the information we are getting! The JDBC helps us by providing MetaData classes.

Metadata
  • metaknowledge is knowledge about knowledge.
  • metalanguage is a language about languages.
  • metatheory is the theory about theories.
  • metadata is data about data.

Go to the java.sql package. Scroll through the Interface Summary. We see some interesting names:

  • DatabaseMetaData
  • ParameterMetaData
  • ResultSetMetaData

These interfaces can be instantiated by instances of the objects that help identify the data. Let's take a look at a method that illustrates this idea.

In the java4_Lesson11 Project, edit DatabaseManager as shown in blue:

CODE TO EDIT: DatabaseManager
package db;

import java.sql.*;

public class DatabaseManager {

    private Connection connection;                                        // The database connection object.
    private Statement statement;                                          // the database statement object, used to execute SQL commands.

    public DatabaseManager (String username, String password ) {          // the constructor for the database manager
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username; 
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return;
        }
        try {                                                             // Establish the database connection, create a statement for execution of SQL commands.
            connection = DriverManager.getConnection (url, username, password );   // username and password are passed into this Constructor
            // Get the DatabaseMetaData object and display
            // some information about the connection

            DatabaseMetaData aboutDB = connection.getMetaData();

            System.out.println("\nConnected to " + aboutDB.getURL());
            System.out.println("Driver         " + aboutDB.getDriverName());
            System.out.println("Version        " + aboutDB.getDriverVersion());

            statement  = connection.createStatement();
            statement.execute ("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18) );");    // create a table in the database                                       
        }
        catch (SQLException exception ) {
            System.out.println ("\n*** SQLException caught ***\n");
            while (exception != null) 
            {                                                                         // tell us the problem
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();

                System.out.println ( "" );
            }
        }
        catch ( java.lang.Exception exception ) {
            exception.printStackTrace();
        }
    }

    public void addEntry (String name, String phoneNumber ){                       // adds an entry to the Phone Book
       try
       {
           statement.execute ( "insert into PhoneBook values ('" + name + "', '" + phoneNumber + "');" );
       }
       catch ( SQLException exception ) 
       {
           System.out.println ("\n*** SQLException caught ***\n");

           while ( exception != null) 
           {
               System.out.println ("SQLState:    " + exception.getSQLState()  );
               System.out.println ("Message:     " + exception.getMessage()   );
               System.out.println ("Error code:  " + exception.getErrorCode() );

               exception = exception.getNextException ();   
               System.out.println ( "" );
           }
       }
       catch(java.lang.Exception exception ) 
       {
           exception.printStackTrace();
       }
    }
}

Save and run it (from PhoneBook). You'll see something like this:

Even though you may exit the application by clicking the red Terminate square, you should also either "Remove Launch" (single X) or "Remove All Terminated Launches" by clicking the double X:

In databases, when a query returns a ResultSet, it is returning a table of data. ResultSets are used to retrieve data so that it can be manipulated. Suppose you want to loop through the data to find something, but you don't know how many rows or columns of data exist, so you don't know how many times to loop. This would be a perfect time to use ResultSetMetaData.

Creating a Table

If one doesn't exist already, we'll create a new PhoneBook table. By doing this, we won't need to drop the database table; we can keep the information we have just submitted in it for the next time we access it. We'll use both the ResultSet and the ResultSetMetaData interfaces.

In the java4_Lesson11 Project, edit DatabaseManager as shown in blue below:

CODE TO EDIT: DatabaseManager
package db;

import java.sql.*;

public class DatabaseManager {

    private Connection connection;  // The database connection object.
    private Statement statement;    // the database statement object, used to execute SQL commands.

    public DatabaseManager (String username, String password ) {          // the constructor for the database manager
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username;
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return;
        }

        try {                                                                     // Establish the database connection, create a statement for execution of SQL commands.
            connection = DriverManager.getConnection (url, username, password );  // username and password are passed into this Constructor
   
            statement  = connection.createStatement(); 
            DatabaseMetaData aboutDB = connection.getMetaData ();
            // do more useful things with the meta class
            String [] tableType = {"TABLE"};
            ResultSet rs = aboutDB.getTables(null, null, "PhoneBook",  tableType);  // for more info about this method, see the getTables method in DatabaseMetaData in the API

            if (!inspectForTable(rs))                                              // use this method (written below) to see if we already have the table PhoneBook
        	    statement.execute ("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18) );");  // if we do NOT already have one, we want to do this 
            rs.close();                                                             // in this example, the ResultSet is local, so close it here
        }
        catch (SQLException exception ) {
            System.out.println ("\n*** SQLException caught ***\n");
            while (exception != null) 
            {                                                                          // tell us the problem
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();

                System.out.println ( "" );
            }
        }
        catch ( java.lang.Exception exception ) {
            exception.printStackTrace();
        }
    }

    public void addEntry (String name, String phoneNumber ){                       // adds an entry to the Phone Book
        try
        {
            statement.execute ( "insert into PhoneBook values ('" + name + "', '" + phoneNumber + "');" );
        }
        catch ( SQLException exception ) 
        {
            System.out.println ("\n*** SQLException caught ***\n");

            while ( exception != null) 
            {
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );

                exception = exception.getNextException ();   
                System.out.println ( "" );
            }
        }
        catch(java.lang.Exception exception ) 
        {
            exception.printStackTrace();
        }
    }
   
    private static boolean inspectForTable (ResultSet rs)  throws SQLException {  // will be caught when used
        int i;
        ResultSetMetaData rsmd = rs.getMetaData ();                               // Get the ResultSetMetaData.  This will be used for information about the columns.
        int numCols = rsmd.getColumnCount ();                                     // Get the number of columns in the result set

        for (i=1; i<=numCols; i++) {                                           // Display column headings
            if (i > 1) System.out.print(", ");                                    // just to show what is there for our curiosity
            System.out.print(rsmd.getColumnLabel(i));
        }
        System.out.println("");

        boolean more = rs.next ();
        while (more) {                     // Display data, fetching until end of the result set
                                           // Loop through each row, getting the column data and displaying
            for (i=1; i<=numCols; i++) 
            {
                System.out.print(rs.getString(i)+"\n");   
                if (rsmd.getColumnLabel(i) == "TABLE_NAME")
                    if (rs.getString(i).equals("PhoneBook"))
                    {
                        System.out.println("Found one that equals " + rs.getString(i));   // is PhoneBook there already or not?
                        return true;                                                      // it is, tell the method that inquired
                    }
            }
            System.out.println("");
            more = rs.next ();                                                        // Fetch the next result set row
        }
        return false;                                                                  // went though all of the rows and it was not there
    }
}

Save and run it (from PhoneBook). Terminate it and run it again. The SQL Exception alerting you to an existing table should no longer appear. We've added a few extra printlns in our code so we can observe what's been returned. We can remove them later.

Closing Properly

In the objective for the last lesson, you added a method to close(). We'll include it here too, but we'll also give the user the option to either keep the information or drop the table.

Edit DatabaseManager as shown in blue:

CODE TO EDIT: DatabaseManager
package db;

import java.sql.*;

public class DatabaseManager {

    private Connection connection;                                        // The database connection object.
    private Statement statement;                                          // the database statement object, used to execute SQL commands.

    public DatabaseManager (String username, String password ) {          // the constructor for the database manager
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username;
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return;
        }

        try {                                                             // Establish the database connection, create a statement for execution of SQL commands.
            connection = DriverManager.getConnection (url, username, password );  // username and password are passed into this Constructor
            statement  = connection.createStatement();  

            DatabaseMetaData aboutDB = connection.getMetaData ();
            // do more useful things with the meta class
            String [] tableType = {"TABLE"};
            ResultSet rs = aboutDB.getTables(null, null, "PhoneBook",  tableType);  // for more info about this method, see the getTables method in DatabaseMetaData in the API

            if (!inspectForTable (rs))                                    // use this method to see if we already have the table PhoneBook
        	    statement.execute ("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18) );");     // if we do NOT already have one, we want to do this 
            rs.close();                                                             // in this example, the ResultSet is local - so close it here                                     
        }
        catch (SQLException exception ) {
            System.out.println ("\n*** SQLException caught ***\n");
            while (exception != null) 
            {                                                                         // tell us the problem
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();
                System.out.println ( "" );
            }
        }
        catch ( java.lang.Exception exception ) {
            exception.printStackTrace();
        }
    }

    public void addEntry (String name, String phoneNumber ){                       // adds an entry to the Phone Book
        try
        {
            statement.execute ( "insert into PhoneBook values ('" + name + "', '" + phoneNumber + "');" );
        }
        catch ( SQLException exception ) 
        {
            System.out.println ("\n*** SQLException caught ***\n");
            while ( exception != null) 
            {
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();   
                System.out.println ( "" );
            }
        }
        catch(java.lang.Exception exception ) 
        {
            exception.printStackTrace();
        }
    } 

    private static boolean inspectForTable (ResultSet rs)  throws SQLException {  // will be caught when used
        int i;
        ResultSetMetaData rsmd = rs.getMetaData ();                               // Get the ResultSetMetaData. This will be used for information about the columns.
        int numCols = rsmd.getColumnCount ();                                     // Get the number of columns in the result set
        for (i=1; i<=numCols; i++) {                                           // Display column headings
            if (i > 1) System.out.print(", ");                                    // just to show what is there for our curiosity
                System.out.print(rsmd.getColumnLabel(i));
        }
        System.out.println("");

        boolean more = rs.next ();
        while (more) {                                                             // Display data, fetching until end of the result set
            // Loop through each row, getting the column data and displaying
            for (i=1; i<=numCols; i++) 
            {
                System.out.print(rs.getString(i)+"\n");   
                if (rsmd.getColumnLabel(i) == "TABLE_NAME")
                    if (rs.getString(i).equals("PhoneBook"))
                    {
                        System.out.println("Found one that equals " + rs.getString(i));   // is PhoneBook there already or not?
                        return true;                                                      // it is, tell the method that inquired
                    }
            }
            System.out.println("");
            more = rs.next ();                                                        // Fetch the next result set row
        }
        return false;                                                                  // went though all of the rows and it was not there
    }
    
    public void close(boolean remove) {                                                       // drops the table and properly closes the database
        try
        {
            if (remove) 
            {
                statement.execute("drop table PhoneBook;");
            }
            statement.close();
            connection.close();
        }
        catch (SQLException exception) 
        {
            System.out.println("\n*** SQLException caught ***\n");
            while (exception != null) 
            {
                System.out.println("SQLState:  " + exception.getSQLState());
                System.out.println("Message:   " + exception.getMessage());
                System.out.println("ErrorCode: " + exception.getErrorCode());
                exception = exception.getNextException ();   
                System.out.println("");
            }
        }
        catch(java.lang.Exception exception ) 
        {
            exception.printStackTrace();
        }
    }
}

Save it.

Now we need to accommodate the option in the user interface. In the java4_Lesson11 project, edit UserInterface as shown in blue:

CODE TO EDIT: UserInterface
 
package db;

import java.sql.*;
import java.util.*;

public class UserInterface {

    private DatabaseManager database;  // the reference to the DatabaseManager object, 
                                       // handles all requests to access the database

    public UserInterface(DatabaseManager theDatabaseManager) {
	    database = theDatabaseManager;
    }

    public void start() {
        Scanner in = new Scanner (System.in);
        while (true) {                             // Continue until the user enters a quit command 
            System.out.println ("Click in the Console," 
             + "\n then Enter a command: (choose)" 
             + "\n A (then Enter) to Add a phone book entry," 
             + "\n K (then Enter) to Exit and Keep the entries," 
             + "\n or Q (then Enter) to Quit and Remove the entries: " );

            String command = in.nextLine();

            if (command.charAt(0) == 'A')
            {
                System.out.println ("Enter name: ");
                String name = in.nextLine();
                System.out.println ("Enter phone number: ");
                String phoneNumber = in.nextLine();
                database.addEntry (name, phoneNumber);  // Add this entry to the database.
            }
            else if (command.charAt(0) == 'K')
            {
                System.out.println("Bye");
                database.close(false);      // The user entered the quit command, but does not want to delete info.
                return;
            }
            else if (command.charAt(0) != 'Q')
            {
                System.out.println ("Invalid command. Please enter either A, K, or Q.");
            } 
            else                                                                 // command is Q
            {
                System.out.println("Bye");
                database.close(true);                                             // The user entered the quit command, so shutdown the database and return.
                return;
            }
        }
    }
}

Save and run it (from PhoneBook). Exit with K to Keep the table. Run it again.

Seeing Table Contents

Is there even anything in there? Let's find out.

Edit DatabaseManager as shown in blue:

CODE TO EDIT: DatabaseManager
package db;

import java.sql.*;

public class DatabaseManager {

    private Connection connection;                                        // The database connection object.
    private Statement statement;                                          // the database statement object, used to execute SQL commands.
    private ResultSet resultSet;                                          // results from a database query
    
    public DatabaseManager (String username, String password ) {          // the constructor for the database manager
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username;
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return;
        }

        try {                                                                       // Establish the database connection, create a statement for execution of SQL commands.
            connection = DriverManager.getConnection (url, username, password );     // username and password are passed into this Constructor
            statement  = connection.createStatement();  

            DatabaseMetaData aboutDB = connection.getMetaData ();
                                                                                  // do more useful things with the meta class
            String [] tableType = {"TABLE"};
            ResultSet rs = aboutDB.getTables(null, null, "PhoneBook",  tableType);   // check out the getTables method in DatabaseMetaData to see more about this method

            if (!inspectForTable (rs))                                              // use this method to see if we already have the table PhoneBook
                statement.execute ("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18) );");     // if we do NOT already have one, we want to do this 
            rs.close();                                                             // in this example, the ResultSet is local - so close it here                                    
        }
        catch (SQLException exception ) {
            System.out.println ("\n*** SQLException caught ***\n");
            while (exception != null) 
            {                                                                         // tell us the problem
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();

                System.out.println ( "" );
            }
        }
        catch ( java.lang.Exception exception ) {
            exception.printStackTrace();
        }
    }

    public void addEntry (String name, String phoneNumber ){                       // adds an entry to the Phone Book
        try
        {
            statement.execute ( "insert into PhoneBook values ('" + name + "', '" + phoneNumber + "');" );
        }
        catch ( SQLException exception ) 
        {
            System.out.println ("\n*** SQLException caught ***\n");

            while ( exception != null) 
            {
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );

                exception = exception.getNextException ();   
                System.out.println ( "" );
            }
        }
        catch(java.lang.Exception exception ) 
        {
            exception.printStackTrace();
        }
    } 

    private static boolean inspectForTable (ResultSet rs)  throws SQLException {  // will be caught when used
        int i;
        ResultSetMetaData rsmd = rs.getMetaData ();                                // Get the ResultSetMetaData.  This will be used for information about the columns.
        int numCols = rsmd.getColumnCount ();                                      // Get the number of columns in the result set

        // for (i=1; i<=numCols; i++) {                                               // Display column headings
            //if (i > 1) System.out.print(", ");                                      // just to show what is there for our curiosity
                // System.out.print(rsmd.getColumnLabel(i));
        //}
        //System.out.println("");

        boolean more = rs.next ();
        while (more) {                                                             // Display data, fetching until end of the result set
            // Loop through each row, getting the column data and displaying
            for (i=1; i<=numCols; i++) 
            {
                //System.out.print(rs.getString(i)+"\n");   
                if (rsmd.getColumnLabel(i) == "TABLE_NAME")
                    if (rs.getString(i).equals("PhoneBook"))
                    {
                        System.out.println("Found one that equals " + rs.getString(i));   // is PhoneBook there already or not?
                        return true;                                                      // it is, tell the method that inquired
                    }
            }
            System.out.println("");
            more = rs.next ();                                                        // Fetch the next result set row
       }
       return false;                                                                  // went though all of the rows and it was not there
    }

    public void getEntries(){                                               // returns a ResultSet containing all entries in the phone book
        try
        {
            resultSet = statement.executeQuery("SELECT * FROM PhoneBook"); // call the query and get a ResultSet
        
            ResultSetMetaData metaData = resultSet.getMetaData();            // Get the ResultSetMetaData.
            int numCols = metaData.getColumnCount();                         // Get the number of columns in the result set
            int i;                                   
            System.out.println("");
            for (i=1;  i <= numCols;  i++) 
            {
                if (i > 1) System.out.print("\t\t\t\t"); 
                System.out.print ( metaData.getColumnLabel(i) );
            }
            System.out.println("");
            System.out.println("");

            boolean more = resultSet.next();                           // Display data, fetching until end of the result set
            while (more)                                                // Loop through each column, getting the column data and displaying
            {
                for (i = 1;  i <= numCols;  i++) 
                {
                    System.out.print  (resultSet.getString(i) + "\t\t\t\t" );
                }
                System.out.println("");
                more = resultSet.next();                                 // Go to the next result set row
            }		
            resultSet.close();
            System.out.println("");

        }
        catch (SQLException exception) 
        {
            System.out.println ("\n*** SQLException caught ***\n");

            while ( exception != null) 
            {
                System.out.println("SQLState:   " + exception.getSQLState());
                System.out.println("Message:    " + exception.getMessage());
                System.out.println("Error code: " + exception.getErrorCode());

                exception = exception.getNextException();   
                System.out.println("");
            }
        }
        catch (java.lang.Exception exception ) 
        {
            exception.printStackTrace();
        }
    }

    public void close(boolean remove){                                                       // drops the table and properly closes the database
        try
        {
            if (remove) 
                statement.execute("drop table PhoneBook;");
            statement.close();
            connection.close();
        }
        catch (SQLException exception) 
        {
            System.out.println("\n*** SQLException caught ***\n");

            while (exception != null) 
            {
                System.out.println("SQLState:   " + exception.getSQLState());
                System.out.println("Message:    " + exception.getMessage());
                System.out.println("Error code: " + exception.getErrorCode());

                exception = exception.getNextException();   
                System.out.println("");
            }
        }
        catch(java.lang.Exception exception) 
        {
            exception.printStackTrace();
        }
    }
}

Save it.

We have lots of new additions to the DatabaseManager; let's give our users more capabilities to keep up with those additions.

Edit UserInterface as shown in blue:

CODE TO EDIT: UserInterface
 
package db;

import java.util.*;

public class UserInterface {

    private DatabaseManager database;                           // the reference to the DatabaseManager object, 
                                                                // handles all requests to access the database
    public UserInterface(DatabaseManager theDatabaseManager) {
	    database = theDatabaseManager;
    }

    public void start() {
        Scanner in = new Scanner (System.in);
        while (true) {                                      // Continue until the user enters a quit command
            System.out.println ("Click in the Console,"  
             + "\n then Enter a command: (choose)" 
             + "\n A (then Enter) to Add a phone book entry, " 
             + "\n D (then Enter) to Display all phone book entries," 
             + "\n K (then Enter) to exit and Keep the entries," 
             + "\n or Q (then Enter) to Quit and remove the entries: " );

            String command = in.nextLine();

            if ( command.charAt(0) == 'A' )
            {
                System.out.println ("Enter name: ");
                String name = in.nextLine();
                System.out.println ("Enter phone number: ");
                String phoneNumber = in.nextLine();
                database.addEntry (name, phoneNumber);  // Add this entry to the database.
            }
            else if (command.charAt(0) == 'D')
            {
                database.getEntries();  // Query the database for the resultSet  
            }
            else if (command.charAt(0) == 'K' )
            {
                System.out.println("Bye");
                database.close(false);               // The user entered the quit command, but does not want to delete info.
                return;
            }
            else if ( command.charAt(0) != 'Q' )
            {
                System.out.println ("Invalid command, please enter either A, D, K, or Q.");
            } 
            else                                     // command is Q
            {
                System.out.println("Bye");
                database.close(true);                // The user entered the quit command, so shutdown the database and return.
                return;
            }
      }
   }
}

NoteDid you notice the modularity here? After adding each method, we made a minor edit to the user interface.

Save and run it (from PhoneBook.java). Use the D option to see all the entries that may be there already. Type Q to Quit--this calls the close() method of DatabaseManager, which drops the table and closes all open connections to the database.

Run it again. Since it was closed properly, there shouldn't be any errors. Use the D option to see that all of the entries were removed. Add a few new entries and try the D command again. Exit with the K option. In fact, try both the Q and K options until you feel confident that they behave as expected.

SQL Commands

Look over the code in this Observe box:

OBSERVE: DatabaseManager Stripped
package db;
                                                            
import java.sql.*;

public class DatabaseManager {

    private Connection connection;                                                
    private Statement statement;
    private ResultSet resultSet;                                                   

    public DatabaseManager (String username, String password ) {                                               // connect
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username;          
        Class.forName ("com.mysql.jdbc.Driver");                                                       
        connection = DriverManager.getConnection (url, username, password );      
        statement  = connection.createStatement();
        statement.execute ("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18) );");                                         
    }

    public void addEntry (String name, String phoneNumber ){                                                   // add entries         
        statement.execute ( "insert into PhoneBook values ('" + name + "', '" + phoneNumber + "');" );    
    }

    public ResultSet getEntries(){                                                                             // retrieve                                                 
        return statement.executeQuery ( "SELECT * FROM PhoneBook");
    }      

    public void close(){                                                                                        // close                                                     
        statement.execute ("drop table PhoneBook;");
        statement.close();
        connection.close();     
    }
}

We've removed all of the try/catch clauses and System.out.println statements to show what's left of the DatabaseManager (other than the Meta questions). You might be tempted to try to program database applications without all of those try/catch clauses, but that would make your application unstable and, for all intents and purposes, unusable. In fact, Java won't even let you do it. SQLExceptions are not RuntimeExceptions and therefore must be handled.

We used a wild card (*) in the SELECT query to retrieve all of the entries from our data. Explore more options using SELECT with these resources:

Logging In

So now we have a running application, but what user wants to use the command line to enter commands? We'll begin to fix this problem by creating a tool to log in. In the next lesson, we'll retool the whole application to take advantage of the information we now know about Swing.

In the java4_Lesson11 project, create a PasswordDialog class:

Type PasswordDialog as shown in blue:

CODE TO TYPE: PasswordDialog
package db;

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

class PasswordDialog extends JDialog implements ActionListener {
    private JTextField user;
    private JPasswordField password;
    private String username, passwd;
    private static String [] info;
    private static boolean set = false;

    public PasswordDialog(final JFrame owner) {
        // set the dialog title and size
        super(owner, "Login", true);
        setSize(280, 150);
        user = new JTextField(10);
        user.addActionListener(this);
        password = new JPasswordField(10);
        password.addActionListener(this);
        // Create the center panel which contains the fields for entering information
        JPanel center = new JPanel();
        center.setLayout(new GridLayout(3, 2));     // 3 rows leaves a nice space between
        center.add(new JLabel(" Enter UserName:"));
        center.add(user);
        center.add(new JLabel(" Enter Password:"));
        center.add(password);
        // Create the south panel which contains the buttons
        JPanel south = new JPanel();
        JButton submitButton = new JButton("Submit");
        submitButton.setActionCommand("SUBMIT");
        submitButton.addActionListener(this);
        
        JButton helpButton = new JButton("Help");
        south.add(submitButton);
        south.add(helpButton);
        // Add listeners to the buttons
        helpButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent aEvent) {  // The user has asked for help
                JOptionPane.showMessageDialog(owner,
                "Your username and password are the same as those\n" +
                "you use to access your O'Reilly School of Technology courses.\n");
            }
        }); 
        // Add the panels to the dialog window
        Container contentPane = getContentPane();
        contentPane.add(center, BorderLayout.CENTER);
        contentPane.add(south,  BorderLayout.SOUTH);
    }

    public void actionPerformed(ActionEvent e) {
        String cmd = e.getActionCommand();
        if ("SUBMIT".equals(cmd)) 
        {                               // Process the inputs.
            username = user.getText();
            char[] input = password.getPassword();
            passwd = new String(input);
            // to verify it is working, print the name and password--remove this line later!
            System.out.println("User is " + username + ", password is " + passwd); 
            info = new String[2];
            info[0] = username;
            info[1] = passwd;
            set = true;       // now can send info back
            dispose();
        }
    }

    public static void main(String [] args) {  // create the frame first and then give it that frame as owner
        JFrame frame = new JFrame();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        final PasswordDialog addPassword = new PasswordDialog(frame);
        addPassword.setVisible(true);
        System.exit(0);  // terminates  
    }
}

Save and run it. That's much more efficient than going into Eclipse frames to set variables. Of course, normally we wouldn't just run a Login Dialog and stop there, so the main() method might seem a little weird as it is.

Let's add a method to access the PasswordDialog from other classes, and change it so it doesn't print the username and password.

NoteThis example does not address security issues--it simply passes the information as elements in an array.

Edit PasswordDialog as shown in blue:

CODE TO EDIT: PasswordDialog
package db;

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

class PasswordDialog extends JDialog implements ActionListener{
    private JTextField user;
    private JPasswordField password;
    private String username, passwd;
    private static String [] info;
    private static boolean set = false;

    public PasswordDialog(final JFrame owner) {
        // set the dialog title and size
        super(owner, "Login", true);
        setSize(280, 150);
        user = new JTextField(10);
        user.addActionListener(this);
        password = new JPasswordField(10);
        password.addActionListener(this);
        // Create the center panel which contains the fields for entering information
        JPanel center = new JPanel();
        center.setLayout(new GridLayout(3, 2));      // 3 rows leaves a nice space between
        center.add(new JLabel(" Enter UserName:"));
        center.add(user);
        center.add(new JLabel(" Enter Password:"));
        center.add(password);
        // Create the south panel which contains the buttons
        JPanel south = new JPanel();
        JButton submitButton = new JButton("Submit");
        submitButton.setActionCommand("SUBMIT");
        submitButton.addActionListener(this);

        JButton helpButton = new JButton("Help");
        south.add(submitButton);
        south.add(helpButton);
        // Add listeners to the buttons
        helpButton.addActionListener( new ActionListener() {
            public void actionPerformed(ActionEvent aEvent) {   // The user has asked for help.
                JOptionPane.showMessageDialog(owner,
                "Your username and password are the same as those\n" +
                "you use to access your O'Reilly School of Technology courses.\n");
            }
        }); 
        // Add the panels to the dialog window
        Container contentPane = getContentPane();
        contentPane.add(center, BorderLayout.CENTER);
        contentPane.add(south,  BorderLayout.SOUTH);
    }

    public void actionPerformed(ActionEvent e) {
        String cmd = e.getActionCommand();
        if ("SUBMIT".equals(cmd)) 
        {                                     // Process the inputs.
            username = user.getText();
            char[] input = password.getPassword();
            passwd = new String(input);
            // to verify it is working, uncomment this line
            //System.out.println("User is " + username + " password is " + passwd);  
            info = new String[2];
            info[0] =username;
            info[1] = passwd;
            set = true;                       // now can send info back
            dispose();
        }
    }

    public static void main(String [] args){  // create the frame first and then give it that frame as owner
        JFrame frame = new JFrame();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        final PasswordDialog addPassword = new PasswordDialog(frame);
        addPassword.setVisible(true);  
    }
    
    public static String [] login(Object sender) {                               //  object who requested login is the  sender;
        JFrame frame = new JFrame();                                              // attempt is to make as reusable as possible
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        final PasswordDialog addPassword = new PasswordDialog(frame);
        addPassword.setVisible(true);  
        while (!set)                                                               // wait until user has put information in before returning values
            try {
                Thread.sleep(5000);
            }
            catch (InterruptedException e) {};
        return info;
    }
}

Save it. Now, let's link it to our database. Edit PhoneBook as shown in blue below:

CODE TO EDIT: PhoneBook
package db;

public class PhoneBook {

    public PhoneBook(){
        String [] info = PasswordDialog.login(this);                              // static login so can call from class
        DatabaseManager databaseManager = new DatabaseManager(info[0], info[1]);  // Create the database manager and pass login info.
        UserInterface userInterface = new UserInterface(databaseManager );        // Create access for user input
        userInterface.start();
    }
   
    public static void main ( String[] args ) {                                         // instantiate to start                                                                                       // args[1] must be the password to connect to the mysql database
        PhoneBook myApp = new PhoneBook();     
    }
}

Save and run it.

For other examples of improved login dialogs, see Swing Components and Oracle's Java Look and Feel Design Guidelines book (both are copyright-protected so we can't provide the code for them here).

You're doing great! We've unearthed a lot of valuable stuff so far!