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

/**
 * This class connects to the Oracle demo database and reads
 * customer names from the CUSTOMER table, outputing
 * them to System.out.  It does very little logging, only
 * catching a single exception in the main program so you know
 * what to catch in an interactive debugger,
 * should something go wrong.
 */
public class BareCustomerNames {

  /**
   * Constant for the name of the JDBC Driver class connecting
   * to the Oracle demo database.
   */
  private static final String ORACLE_JDBC_DRIVER_CLASS_NAME = 
    "oracle.jdbc.driver.OracleDriver";

  /**
   * Constant for the name of the URL for where the Oracle
   * JDBC driver will connect to the database.
   */
  private static final String ORACLE_DEMO_DB_URL = 
    "jdbc:oracle:thin:@dbserver:1521:database";

  /**
   * Constants for the user to log into the Oracle demo database.
   */
  private static final String ORACLE_DEMO_DB_USERID_PROPERTY = "user";
  private static final String ORACLE_DEMO_DB_USERID = "beg";

  /**
   * Constants for the password to log into the Oracle demo database.
   */
  private static final String ORACLE_DEMO_DB_PASSWORD_PROPERTY = "password";
  private static final String ORACLE_DEMO_DB_PASSWORD = "java";

  /**
   * Constant for the SQL Query to execute
   */
  private static final String SQL_QUERY = "select CUSTOMERFIRSTNAME from CUSTOMERS";

  /**
   * Helper method to retrieve a JDBC connection from
   * the Oracle demo database.  Note, this code must be called
   * in a try block with a finally block which closes the connection.
   * 
   * @throws Exception if the connection cannot be created
   * 
   * @return JDBC Connection to the Oracle demo database if successful
   */
  private Connection getConnection() throws Exception {

    // Create a new instance of the driver manager class, so
    // it initializes itself for use and registers with the
    // JDBC DriverManager
    Class.forName(ORACLE_JDBC_DRIVER_CLASS_NAME).newInstance();

    // Create connection properties object
    Properties connectionProperties = new Properties();

    connectionProperties.put(ORACLE_DEMO_DB_USERID_PROPERTY, 
                             ORACLE_DEMO_DB_USERID);

    connectionProperties.put(ORACLE_DEMO_DB_PASSWORD_PROPERTY, 
                             ORACLE_DEMO_DB_PASSWORD);

    // create a connection
    return DriverManager.getConnection(ORACLE_DEMO_DB_URL, 
                                       connectionProperties);
  } 

  /**
   * Outputs the customer names from the result set to System.out.
   * 
   * @throws SQLException if a JDBC API fails
   */
  private void printResultSet(ResultSet resultSet) throws SQLException {

    // Output a header for the data
    System.out.println("Customer Name");
    System.out.println("-------------");

    while (resultSet.next()) {

      // Retrieve and print the first column
      System.out.println(resultSet.getString(1));
    } 
  } 

  /**
   * Given an opened JDBC connection, reads customer names
   * from the CUSTOMER table.
   */
  private void readCustomers(Connection connection) throws SQLException {
    Statement statement = null;
    ResultSet resultSet = null;

    // Get a new statement and run the query
    try {
      statement = connection.createStatement();

      // Execute query
      resultSet = statement.executeQuery(SQL_QUERY);

      // Print result set
      printResultSet(resultSet);
    } 
    finally {

      // If we got a result set, close it
      if (resultSet != null) {
        resultSet.close();
      } 

      // If we got a statement, close it
      if (statement != null) {
        statement.close();
      } 
    } 
  } 

  /**
   * Main method for the simple program.  Creates a connection
   * to the database, reads customer names from the database,
   * and cleans up.
   * 
   * @throws Exception on failure
   */
  public void runQuery() throws Exception {
    Connection databaseConnection = null;

    try {

      // Get the connection
      databaseConnection = getConnection();

      // Read customers with that connection
      readCustomers(databaseConnection);

    } 
    finally {

      // If we got a connection, close it
      if (databaseConnection != null) {
        databaseConnection.close();
      } 
    } 
  } 

  /**
   * Main method to run the simple program.  Creates a new instance
   * of this class to read and log customer names.
   */
  public static void main(String[] args) {
    BareCustomerNames logCustomerNames = new BareCustomerNames();

    try {
      logCustomerNames.runQuery();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  } 
}