jdbccardfile.java

jdbc 的实例源码

/**
 *
 * jdbcCardFile - implementation of CardFileAbstract for jdbc
 *
 * Copyright 1996 John Wiley & Sons, Inc. All Rights Reserved. Reproduction
 * or translation of this work beyond that permitted in Section 117 of the 1976
 * United States Copyright Act without the express written permission of the
 * copyright owner is unlawful. Requests for further information should be 
 * addressed to Permissions Department, John Wiley & Sons, Inc. The 
 * purchaser may make back-up copies for his/her own use only and not for 
 * distribution or resale. The Publisher assumes no responsibility for errors, 
 * omissions, or damages, caused by the use of this  software or from the use
 * of the information contained herein.
 *
 */

import java.awt.*;
import java.lang.*;
import java.util.*;
import java.sql.*;
import java.net.URL;

public class jdbcCardFile extends CardFileAbstract {


  Connection con;      // the jdbc connection
  Statement stmt;      // the jdbc statement
  int currentRow;      // row number of the current row
  Vector cardfileKeys;  // A vector to hold all primary keys

  // the constructor
  //
  public jdbcCardFile(String[] argv) {
    super(argv);
  }

  // logs you into the database and
  // sets up a Connection
  //
  public void login (String[] argv) {

    if (argv.length == 0) {
      System.out.println("You must supply a URL to a JDBC data source.");
      System.out.println("");
      System.out.println("Example:");
      System.out.println("java DDL jdbc:odbc:DATA_SOURCE_NAME;" +
                         "UID=userid;PWD=password");
      System.exit(0);
    }

    // the Vector holds the primary key (customer id) for
    // each row in the table. currentRow holds the index 
    // of the one we are currently viewing.
    
    currentRow = 0;
    cardfileKeys = new Vector();

    try {

      // register all of the JDBC classes you might use
      // you can comment out or remove the ones you
      // are not using.
      //
      Class.forName("textFileDriver").newInstance();               // the tinySQL textFile driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); // JDBC-ODBC bridge
      Class.forName("com.sybase.jdbc.SybDriver").newInstance();    // Sybase
      Class.forName("com.imaginary.sql.msql.MsqlDriver").newInstance();    // mSQL

      String url = argv[0];

      // the user might have passed in a user name or password,
      // so try to read those in, as well
      //
      String user, pwd;
      if (argv.length > 1) {
        user = argv[1];
      } else {
        user = "";
      }
      if (argv.length > 2) {
        pwd = argv[2];
      } else {
        pwd = "";
      }

      // make a connection to the specified URL
      //
      con = DriverManager.getConnection(url, user, pwd);

      // get a Statement object from the Connection
      //
      stmt = con.createStatement();

    } catch( Exception e ) {

      System.out.println(e.getMessage());
      e.printStackTrace();

    }

    // call getKeys() to populate cardfileKeys with unique identifiers
    // for all the keys in the table

    getKeys();
    System.out.println("CardFile started successfully.");

  }

  /**
   * getRow()
   *
   * retrieve a row from the table, using the one indicated by
   * cardfileKeys.elementAt(currentRow)
   *
   */
  public void getRow() {

    ResultSet rs; 

    // if there are no rows to process, just clear
    // the form and return...

    if (cardfileKeys.isEmpty()) {
      clearForm();
      return;
    }

    try {

      // issue a select statement to get the row which is
      // pointed to by currentRow. Unless we have an
      // integrity violation, this should only be one 
      // row.

      rs = stmt.executeQuery("SELECT * FROM cardfile WHERE id = " +
                              cardfileKeys.elementAt(currentRow));
      

      // get the result set's metadata
      //
      ResultSetMetaData meta = rs.getMetaData();

      // get the number of columns
      //
      int cols = meta.getColumnCount();

      // retrieve the row
      //
      rs.next();

      // loop until we reach the count of the number of columns
      //
      for(int i = 1; i <= cols; i++) {

        // get the name of the column from meta
        // 
        String col_name = meta.getColumnName(i);

        // this gets the object (a TextField) from columnmap which
        // is keyed by the name of the column in col_name.
        //

        TextField tf = (TextField) columnmap.get(col_name);
        if (tf != null) {
          // here, we simply call the setText() method of that TextField
          // object to the value of the column.
          //
          tf.setText(rs.getString(i));
        }
    
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }
    catch (ArrayIndexOutOfBoundsException e) {
      e.printStackTrace();
    }
  }


  /**
   * getKeys()
   *
   * This populates the cardfileKeys Vector with unique identifiers
   * for all of the rows in the cardfile table. This lets us buffer
   * all of the rows, without storing the values for each column.
   * As a result, we only have to worry about dirty data if someone
   * changes a key, which of course, you would *never* do...
   *
   * now, if someone else deletes or inserts a row, that's a
   * different problem. Handling that is an excercise left to
   * the reader...
   *
   */
  protected void getKeys() {

    ResultSet rs;

    try {

      // delete all the elements in cardfileKeys

      cardfileKeys.removeAllElements();

      // execute a query to get the id column for each of the
      // rows. Then, process each row and add the id column
      // to cardfileKeys

      rs = stmt.executeQuery("SELECT id FROM cardfile");

      while( rs.next() ) {
        cardfileKeys.addElement( rs.getString(1) );
      }

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

  /**
   *
   * save()
   *
   * Save the record we are editing to the table
   *
   */
  public void save() {

    // construct an insert statement, with values for each
    // column, including id. Teaching this system to auto-
    // increment id in a multi-user environment is an excercise
    // left up to the reader.
    //

    String new_id = txt_id.getText();
    try {
        Integer.parseInt(txt_id.getText());
    } catch (NumberFormatException e) {
        new_id = "0";
    }

    String sql = "INSERT INTO cardfile " +
                 "   (name, address, city, state, zip, country, phone, id)" +
                 "   VALUES (" + 
                             "'" + txt_name.getText() + "', " +
                             "'" + txt_address.getText() + "', " +
                             "'" + txt_city.getText() + "', " +
                             "'" + txt_state.getText() + "', " +
                             "'" + txt_zip.getText() + "', " +
                             "'" + txt_country.getText() + "', " +
                             "'" + txt_phone.getText() + "', " +
                                   new_id + ")";
    
    try {

      // if the query doesn't throw an exception, we can add the newly
      // created id to the cardfileKeys Vector, and set the currentRow
      // to the element which points to the new record
      //
      stmt.executeUpdate(sql);
      cardfileKeys.addElement(new_id);
      currentRow = cardfileKeys.indexOf(new_id);

      // call getRow() to refresh the form. This really shouldn't
      // be necessary, but it lets us know that the record was
      // saved correctly. Or not, as the case may be :-)

      getRow();

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

  /**
   *
   * update()
   *
   * Send an update to the database
   *
   */
  public void update() {

    // construct an update string for each of the columns
    // except for the id. This is used as the criteria
    // for the update, so we probably don't want to
    // update it...

    String sql = "UPDATE cardfile " +
                 " SET name    = '" + txt_name.getText() + "', " +
                 "     address = '" + txt_address.getText() + "', " +
                 "     city    = '" + txt_city.getText() + "', " +
                 "     state   = '" + txt_state.getText() + "', " +
                 "     zip     = '" + txt_zip.getText() + "', " +
                 "     country = '" + txt_country.getText() + "', " +
                 "     phone   = '" + txt_phone.getText() + "' " +
                 " WHERE id = " + txt_id.getText();
    try {

      // send the query

      stmt.executeUpdate(sql);

      // call getRow() to refresh the form. This really shouldn't
      // be necessary, but it lets us know what the update did.

      getRow();

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

  // if the next button was pushed, then I want to
  // increment currentRow. But, if that would push
  // it out past cardfileKeys.size(), I will just
  // wrap around to the beginning (zero).
  //
  public void nextRow() {

    if (currentRow + 1 == cardfileKeys.size()) {
      currentRow = 0;
    } else {
      currentRow++;
    }

    // call getRow() to update the form
    //
    getRow();
  }

  // if the user pushed the previous button, then
  // I want to decrement currentRow. If currentRow 
  // already zero, then decrementing further would
  // probably throw some evil exception, so I'll
  // set it to cardfileKeys.size() - 1, which is the
  // index of the last element.
  //
  public void prevRow() {

    if (currentRow  == 0) {
      currentRow = cardfileKeys.size() - 1;
    } else {
      currentRow--;
    }

    // call getRow() to update the form
    getRow();
  }


  /**
   * delRow()
   *
   * deletes the current row.
   *
   */
  public void delRow() {

    try {

      // issue the query to delete the row
      
      stmt.executeUpdate("DELETE FROM cardfile WHERE id = " +
                          cardfileKeys.elementAt(currentRow));

      // Oh yeah, don't forget to remove the element from
      // cardfileKeys.

      cardfileKeys.removeElement(cardfileKeys.elementAt(currentRow));

      // let's just be lazy and return to row 0...

      currentRow = 0;

      // call getRow() to refresh the form with the current record.

      getRow();

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

  // our little friend main, who makes it all happen
  
  public static void main(String[] argv) {

    // make a new jdbcCardFile, pack() it and show() it.
    jdbcCardFile cardfile = new jdbcCardFile(argv);
    cardfile.pack();
    cardfile.show();

  }

}