recording.java

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

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

/**
 * This class connects to an Oracle database with a simple
 * Recording table and maps the data in this class to/from
 * that table.  One instance of this class corresponds to one
 * row in the OR_RECORDINGS table.
 */
public class Recording {

  // --------------------------------------------------------------
  // Section 1: Java Bean methods
  // --------------------------------------------------------------

  /**
   * Holds the synthetic primary key of the OR_RECORDINGS table.
   */
  private long recordingId;

  /**
   * Holds the title of the Recording.
   */
  private String recordingTitle;

  /**
   * Holds the artist that made the Recording.
   */
  private String recordingArtist;

  /**
   * Holds the catalog number of the Recording for ordering.
   */
  private String catalogNumber;

  /**
   * Holds the selling price of the Recording.
   */
  private double listPrice;

  /**
   * Main constructor for the class.
   */
  public Recording(String recordingTitle, String recordingArtist, 
                   String catalogNumber, double listPrice) {
    setRecordingTitle(recordingTitle);
    setRecordingArtist(recordingArtist);
    setCatalogNumber(catalogNumber);
    setListPrice(listPrice);
  }

  /**
   * Retrieves the database ID of the Recording.
   */
  public long getRecordingId() {
    return recordingId;
  } 

  /**
   * Sets the database ID of the Recording.
   */
  private void setRecordingId(long recordingId) {
    this.recordingId = recordingId;
  } 

  /**
   * Retrieves the title of the Recording.
   */
  public String getRecordingTitle() {
    return recordingTitle;
  } 

  /**
   * Sets the title of the Recording.
   */
  public void setRecordingTitle(String recordingTitle) {
    this.recordingTitle = recordingTitle;
  } 

  /**
   * Retrieves the artist that made the Recording.
   */
  public String getRecordingArtist() {
    return recordingArtist;
  } 

  /**
   * Sets the artist that made the Recording.
   */
  public void setRecordingArtist(String recordingArtist) {
    this.recordingArtist = recordingArtist;
  } 

  /**
   * Retrieves the catalog number of the Recording.
   */
  public String getCatalogNumber() {
    return catalogNumber;
  } 

  /**
   * Sets the catalog number of the Recording.
   */
  public void setCatalogNumber(String catalogNumber) {
    this.catalogNumber = catalogNumber;
  } 

  /**
   * Retrieves the selling price of the Recording.
   */
  public double getListPrice() {
    return listPrice;
  } 

  /**
   * Sets the selling price of the Recording.
   */
  public void setListPrice(double listPrice) {
    this.listPrice = listPrice;
  } 



  // --------------------------------------------------------------
  // Section 2: Methods to map the class to the database
  // --------------------------------------------------------------

  /**
   * Helper method to iterate through a result set and create
   * one Recording object for each row, returning them in a Vector.
   */
  private static Vector resultSetToRecordings(ResultSet resultSet) 
          throws SQLException {
    Vector recordings = new Vector();
    Recording recording;

    while (resultSet.next()) {

      // Retrieve values from the resultset
      long recordingId = resultSet.getLong(1);
      String recordingTitle = resultSet.getString(2);
      String recordingArtist = resultSet.getString(3);
      String catalogNumber = resultSet.getString(4);
      double listPrice = resultSet.getDouble(5);

      // Construct and populate all variables
      recording = new Recording(recordingTitle, recordingArtist, 
                                catalogNumber, listPrice);
      recording.setRecordingId(recordingId);

      // Add to output vector
      recordings.addElement(recording);
    } 

    return recordings;
  } 

  /**
   * Helper method to find zero to many Recording objects given
   * a SQL query, returning them in a Vector.
   */
  private static Vector findRecordingsBySQLQuery(String sqlQuery) 
          throws Exception {

    Connection databaseConnection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try {

      // Get a new connection and statement
      databaseConnection = getConnection();
      statement = databaseConnection.createStatement();

      // Run the query
      resultSet = statement.executeQuery(sqlQuery);

      // Translate the result set into Recording objects
      return resultSetToRecordings(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();
      } 

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

  /**
   * Finds zero to many Recording objects that have a title
   * like the input string, returning them in a Vector.
   */
  public static Vector findByTitle(String likeRecordingTitle) 
          throws Exception {
    String sqlQuery = "select * from OR_RECORDINGS " +
                      "where RECORDING_TITLE like '" + likeRecordingTitle +
                      "'";

    return findRecordingsBySQLQuery(sqlQuery);
  } 

  /**
   * Finds zero to many Recording objects that have an artist
   * like the input string, returning them in a Vector.
   */
  public static Vector findByArtist(String likeRecordingArtist) 
          throws Exception {
    String sqlQuery = "select * from OR_RECORDINGS " +
                      "where RECORDING_ARTIST like '" +
                      likeRecordingArtist + "'";

    return findRecordingsBySQLQuery(sqlQuery);
  } 


  /**
   * Inserts a new Recording object in the database.  Uses an
   * Oracle sequence to get a unique identifier for the table key.
   */
  public void create() throws Exception {

    Connection databaseConnection = null;
    Statement statement = null;
    Statement statement2 = null;
    ResultSet resultSet = null;

    try {

      // Get a new connection and statements
      databaseConnection = getConnection();

      // Set up a transaction for the two SQL statements
      databaseConnection.setAutoCommit(false);

      statement = databaseConnection.createStatement();
      statement2 = databaseConnection.createStatement();

      // Insert the row
      String insertSQL = "insert into OR_RECORDINGS ( " + " RECORDING_ID, " +
                         " RECORDING_TITLE, " + " RECORDING_ARTIST, " +
                         " CATALOG_NUMBER, " + " LIST_PRICE) " +
                         "values ( " + " RECORDINGS_SEQ.NEXTVAL, " + "'" +
                         getRecordingTitle() + "', " + "'" +
                         getRecordingArtist() + "', " + "'" +
                         getCatalogNumber() + "', " + getListPrice() +
                         ") ";

      statement.executeUpdate(insertSQL);

      // execute query to get recordingId
      String idSQL = "select RECORDINGS_SEQ.CURRVAL from DUAL";
      resultSet = statement2.executeQuery(idSQL);

      // retrieve from result set
      resultSet.next();
      setRecordingId(resultSet.getLong(1));

      // Commit the transaction
      databaseConnection.commit();

    } 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();
      } 
      if (statement2 != null) {
        statement2.close();
      } 


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

  } 

  /**
   * Updates the non-key values of the current Recording object
   * in the database.
   */
  public void update() throws Exception {

    Connection databaseConnection = null;
    Statement statement = null;

    try {

      // Get a new connection and statement
      databaseConnection = getConnection();
      statement = databaseConnection.createStatement();

      // Update the row
      String updateSQL = "update OR_RECORDINGS" + " set RECORDING_TITLE = '" +
                         getRecordingTitle() + "', " +
                         " RECORDING_ARTIST = '" + getRecordingArtist() +
                         "', " + " CATALOG_NUMBER = '" +
                         getCatalogNumber() + "', " + " LIST_PRICE = " +
                         getListPrice() + " where RECORDING_ID = " +
                         getRecordingId();

      statement.executeUpdate(updateSQL);

    } finally {

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

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

  } 

  /**
   * Deletes the current Recording object from the database.
   */
  public void delete() throws Exception {

    Connection databaseConnection = null;
    Statement statement = null;

    try {

      // Get a new connection and statement
      databaseConnection = getConnection();
      statement = databaseConnection.createStatement();

      // Insert the row
      String deleteSQL = "delete from OR_RECORDINGS " +
                         " where RECORDING_ID = " + getRecordingId();

      statement.executeUpdate(deleteSQL);

    } finally {

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

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

  } 



  // --------------------------------------------------------------
  // Section 3: Methods to make the class self-contained
  // --------------------------------------------------------------

  /**
   * Prints the contents of all variables.
   */
  public String toString() {
    return "Recording: " + getRecordingId() + "\n  Title: " +
           getRecordingTitle() + "\n  Artist: " + getRecordingArtist() +
           "\n  Catalog Number: " + getCatalogNumber() +
           "\n  List Price: " + getListPrice();
  } 

  /**
   * 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";

  /**
   * 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 static 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);
  } 

  /**
   * Helper method to output a set of Recording objects
   * in a Vector to System.out.
   */
  private static void outputRecordings(Vector recordings) {

    Recording recording;
    Enumeration e = recordings.elements();

    while (e.hasMoreElements()) {
      recording = (Recording) e.nextElement();

      // Print to output stream
      System.out.println("\n" + recording);
    } 

  } 

  /**
   * Main method to test the capabilities above.  CRUDs the
   * database by creating 4 recordings, reading them back,
   * updates the price on one and reads it back, and
   * deletes all 4.
   */
  public static void main(String[] args) {
    Recording rec1, rec2, rec3, rec4;

    try {
      rec1 = new Recording("Cuts Like A Knife", "Bryan Adams", 
                           "392-0000022", 11.99);

      rec2 = new Recording("Working Class Dog", "Rick Springfield", 
                           "RS-32-1133", 10.99);

      rec3 = new Recording("Come And Join Us", "Petra", "303-9388293", 
                           11.99);

      rec4 = new Recording("Lucky Town", "Bruce Springsteen", 
                           "BK-30-23993", 13.99);

      // Create the recordings in the database
      rec1.create();
      rec2.create();
      rec3.create();
      rec4.create();

      // Read the recordings back and output
      System.out.println("\nYou should see 4 recordings:");

      Vector springArtistRecordings = Recording.findByArtist("%Spring%");
      outputRecordings(springArtistRecordings);

      Vector recordingsStartWithC = Recording.findByTitle("C%");
      outputRecordings(recordingsStartWithC);

      // Update recording 1 price
      rec1.setListPrice(7.99);
      rec1.update();

      // Read record back and make sure update made it
      System.out.println("\nThis recording should have a price of 7.99");
      Vector updatedRecording = Recording.findByArtist("Bryan Adams");
      outputRecordings(updatedRecording);

      // Delete all recordings
      rec1.delete();
      rec2.delete();
      rec3.delete();
      rec4.delete();

      // Read all recordings back, shouldn't be any
      System.out.println("\nDeleted all, shouldn't be any recordings:");
      Vector allRecordings = Recording.findByArtist("%");
      outputRecordings(allRecordings);

    } catch (Exception e) {
      e.printStackTrace();
    } 
  } 
}