logcustomernames.java

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

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.  Along the way,
 * it logs information to System.out and errors to System.err
 * so you can debug the program should something go wrong.
 */
public class LogCustomerNames {

  /**
   * 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.
   */
  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
      System.err.println("Error retrieving result set rows from '" 
                         + resultSet + "', caught exception " + e);
      e.printStackTrace();
    } 
  } 

  /**
   * 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
      System.out.println("Opened JDBC Statement " + statement 
                         + " at LogCustomerNames.readCustomers()");

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

      // Start a timer
      long queryStartTime = System.currentTimeMillis();

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

      // Calculate time to execute and log
      long queryTime = System.currentTimeMillis() - queryStartTime;
      System.out.println("SQL Query: '" + SQL_QUERY + "' took " + queryTime 
                         + " milliseconds to execute");

      // Log that we aquired a result set to clean up
      System.out.println("Retrieved JDBC ResultSet " + resultSet 
                         + " at LogCustomerNames.readCustomers()");

      // Print result set
      printResultSet(resultSet);

    } catch (SQLException e) {

      // Something went wrong with query, log it
      System.err.println("Error querying database '" + ORACLE_DEMO_DB_URL 
                         + "', caught exception " + e);
      e.printStackTrace();
    } 
    finally {

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

          // Log the fact we closed the connection
          System.out.println("Closed JDBC ResultSet " + resultSet);
        } catch (SQLException e) {

          // Something went wrong closing result set, log it
          System.err.println("Error closing result set " + resultSet 
                             + ", caught exception " + e);
          e.printStackTrace();
        } 
      } 

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

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

          // Something went wrong closing statement, log it
          System.err.println("Error closing statement " + statement 
                             + ", caught exception " + e);
          e.printStackTrace();
        } 
      } 
    } 
  } 

  /**
   * 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
      System.out.println("Opened JDBC Connection " + databaseConnection 
                         + " at LogCustomerNames.runQuery()");

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

    } catch (Exception e) {

      // Something went wrong with connection, log it
      System.err.println("Error opening connection to database '" 
                         + ORACLE_DEMO_DB_URL + "', caught exception " + e);
      e.printStackTrace();
    } 
    finally {

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

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

          // Something went wrong closing connection, log it
          System.err.println("Error closing connection to database '" 
                             + ORACLE_DEMO_DB_URL + "', caught exception " 
                             + e);
          e.printStackTrace();
        } 

      } 
    } 
  } 

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

    logCustomerNames.runQuery();
  } 
}