msqlcardfile.java

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 msql.*;

public class MsqlCardFile extends CardFileAbstract {

  Msql msql;
  int currentRow;
  Vector cardfileKeys;

  public MsqlCardFile(String[] argv) {

    super(argv);

  }

  public void login (String argv[]) {

    // instantiate a new Msql object

    msql = new Msql();

    // 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();

    // connect to the localhost and use the database
    // named 'sample'

    try {
      msql.Connect("localhost");
      msql.SelectDB("sample");
    }
    catch(MsqlException e ) {
      e.printStackTrace();      
    }

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

    getKeys();


  }

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

    // 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.

      MsqlResult    result = msql.Query("select * from cardfile where id = " +
					cardfileKeys.elementAt(currentRow));
      
      // ahhh... catalog data. Since each textfield is
      // represented in the columnmap hash, keyed by
      // the column name to which it corresponds, I
      // can use the array of column names to map each
      // column in the result set to the fields on the
      // form.

      MsqlFieldDesc field[]  = result.ListFields();

      // get the number of columns

      int cols = result.NumFields();

      // retrieve the row

      String row[];
      row = result.FetchRow();

      // loop over each column, up until the number indicated
      // by the call the NumFields()

      for(int i=0; i < cols; i++) {

	// get the name of the column from field[i].FieldName()
	
	String col_name = field[i].FieldName();

	// this gets the object from columnmap (a TextField) which
	// is keyed by the name of the column in col_name
	// here, we simply call the setText() method of that TextField
	// object to the value of the column.

	((TextField) columnmap.get(col_name)).setText(row[i]);
	  
      }

    } 
    catch (MsqlException e) {
      e.printStackTrace();
    }
    catch (ArrayIndexOutOfBoundsException e) {
      // ahhh, just ignore it!
    }
  }


  /**
   * 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...
   */
  public void getKeys() {

    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

      MsqlResult    result = msql.Query("select id from cardfile");
      String row[];
      while(( row = result.FetchRow()) != null){
	cardfileKeys.addElement(row[0]);
      }

    } 
    catch (MsqlException 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.
    //
    // Assuming, of course, that bambi hasn't released a version
    // of mSQL which supports this natively :-)

    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() + "', " +
                                   txt_id.getText() + ")";

    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

      MsqlResult    result = msql.Query(sql);
      cardfileKeys.addElement(txt_id.getText());
      currentRow = cardfileKeys.indexOf(txt_id.getText());

      // 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(MsqlException e ) {
      e.printStackTrace();      
    }
 
  }

  /**
   *
   * update()
   *
   * Send an update to the mSQL server.
   *
   */
  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

      MsqlResult    result = msql.Query(sql);

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

      getRow();
    } 
    catch(MsqlException 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
      
      MsqlResult    result = msql.Query("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 (MsqlException e) {
      e.printStackTrace();
    }
  }

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

    // make a new MsqlCardFile, pack() it and show() it.
    MsqlCardFile cardfile = new MsqlCardFile(args);
    cardfile.pack();
    cardfile.show();

  }

}