instrumentcustomernames.java

此程序都是企业级 的数据库开发程序 全面揭示了JAVA对数据库的操作

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

import org.jlf.log.*;

/**
 * This class connects to the Oracle demo database and reads
 * customer names from the CUSTOMER table, outputting
 * them to System.out.  Along the way,
 * it logs information to the JLF Logging Framework,
 * so you can debug the program should something go wrong.
 */
public class InstrumentCustomerNames {

  /**
   * 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.
   * 
   * @return JDBC Connection to the Oracle demo database if successful
   */
  private Connection getConnection() {

    try {

      // 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);
    } catch (Exception e) {

      // Something went wrong with connection, log it
      throw new AppError("Error opening connection to database " 
                         + ORACLE_DEMO_DB_URL, e, SQLLog.getInstance(), 
                                               Log.CRITICAL_ERROR_LEVEL);
    } 
  } 

  /**
   * Outputs the customer names from the result set to System.out.
   */
  private void printResultSet(ResultSet resultSet) {

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

    try {
      while (resultSet.next()) {

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

      // Something went wrong retrieving result set, log it
      throw new AppError("Error retrieving result set rows from '" 
                         + resultSet + "'", e, SQLLog.getInstance(), 
                                            Log.CRITICAL_ERROR_LEVEL);
    } 
  } 

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

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

      // Log the fact we got a connection so we remember to clean up
      SQLLog.trace("Opened JDBC Statement " + statement 
                   + " at InstrumentCustomerNames.readCustomers()");

      // Log that we are trying to execute the query
      SQLLog.info("Executing SQL Query: '" + SQL_QUERY + "' on database " 
                  + ORACLE_DEMO_DB_URL);

      // Start a timer
      AppInstrument queryInstrument = 
        new AppInstrument("executing SQL Query '" + SQL_QUERY, 
                          SQLLog.getInstance());

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

      // Log time to execute query
      queryInstrument.logTime();

      // Log that we aquired a result set to clean up
      SQLLog.trace("Retrieved JDBC ResultSet " + resultSet 
                   + " at InstrumentCustomerNames.readCustomers()");

      // Print result set
      printResultSet(resultSet);

    } catch (SQLException e) {

      // Something went wrong with query, log it
      throw new AppError("Error querying database " + ORACLE_DEMO_DB_URL, 
                         e, SQLLog.getInstance(), Log.CRITICAL_ERROR_LEVEL);
    } 
    finally {

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

          // Log the fact we closed the result set
          SQLLog.trace("Closed JDBC ResultSet " + resultSet);
        } catch (SQLException e) {

          // Something went wrong closing result set, log it
          throw new AppError("Error closing result set " + resultSet, e, 
                             SQLLog.getInstance(), Log.ERROR_LEVEL);
        } 
      } 

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

          // Log the fact we closed the statement
          SQLLog.trace("Closed JDBC Statement " + statement);
        } catch (SQLException e) {

          // Something went wrong closing statement, log it
          throw new AppError("Error closing statement " + statement, e, 
                             SQLLog.getInstance(), Log.ERROR_LEVEL);
        } 
      } 
    } 
  } 

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

    try {

      // Get the connection
      databaseConnection = getConnection();

      // Log the fact we got a connection so we remember to clean up
      SQLLog.trace("Opened JDBC Connection " + databaseConnection 
                   + " at InstrumentCustomerNames.runQuery()");

      // Read customers with that connection
      readCustomers(databaseConnection);
    } 
    finally {

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

          // Log the fact we closed the connection
          SQLLog.trace("Closed JDBC Connection " + databaseConnection);
        } catch (SQLException e) {

          // Something went wrong closing connection, log it
          throw new AppError("Error closing connection to database '" 
                             + ORACLE_DEMO_DB_URL, e, SQLLog.getInstance(), 
                                                   Log.ERROR_LEVEL);
        } 

      } 
    } 
  } 

  /**
   * 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) {
    InstrumentCustomerNames InstrumentCustomerNames = 
      new InstrumentCustomerNames();

    InstrumentCustomerNames.runQuery();
  } 
}