import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/*
 * Before this compiles you must:
 * 
 *    - install and start a MySQL server on your local machine on port 3306
 *    - know the root password for your SQL server (replace XXX below)
 *    - create a database "myaddressbook" (or change DB below)
 *    - create two tables with rows as follows:
 *          names: ID, first, last
 *          emails: ID, type, email
 *      (or change the SELECT query below)
 *    - get the MySQL JConnector JDBC bridge (from our home page)
 *    - put "mysql-connector-java-5.1.5-bin.jar" in the compile-path for this project
 * 
 * Now compile and run this program
 *    
 * As preparation for Friday, create a GUI-based shell ready to receive
 * JDBC code.
 */

public class SQLTestText
{
    private static final String USER = "root";
    private static final String PWD = "xxx";
    private static final String DB = "myaddressbook";
    private final static String URL = "jdbc:mysql://localhost:3306/";
    private final static String DRIVER = "com.mysql.jdbc.Driver";

    public static void main(String args[])
    {
        try
        {
            Class.forName(DRIVER).newInstance();

            Connection connection = DriverManager.getConnection(URL + DB, USER, PWD);
            System.out.println("Connection established ...");
            
            String query = "SELECT names.first, names.last, emails.email " 
                             + "FROM names,emails "
                             + "WHERE names.id = emails.id AND type = 'work'";
            Statement stmt = connection.createStatement();
            ResultSet results = stmt.executeQuery(query);
            while (results.next())
            {
                String first = results.getString(1);
                String last = results.getString(2);
                String email = results.getString(3);
                System.out.println(last + ", " + first + " has email: " + email);
            }
            results.close();
            stmt.close();
            connection.close();
        }
        catch(Exception ex)
        {
            System.out.println("Error: " + ex);
        }
    }
}