outline.java

有关JDBC的使用一些编程实例,有关与数据库连接的代码

/*
 * outline.java
 *
 * Displays data in a hierarchical outline format.
 *
 * 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.net.URL;
import java.sql.*;
import java.lang.*;
import java.util.*;

class outline {

  // a JDBC connection, and a Statement object
  //
  Connection con;
  Statement stmt;

  /**
   *
   * Constructs a new outline object.
   * @param url a URL to  a valid JDBC data source.
   * @param user a username
   * @param pwd a password
   *
   */
  public outline(String url, String user, String pwd) {
    
    // invoke the connect() method
    connect(url, user, pwd);
  }

  /*
   *
   * make a JDBC connection.
   *
   */
  public void connect (String url, String user, String pwd) {

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

    try {

      // ask the DriverManager to find
      // a suitable driver for this URL.
      //
      con = DriverManager.getConnection(url, user, pwd);

      // create a statement object. This is the
      // gateway into the world of playing
      // with fun things like SQL statements
      // and results
      //
      stmt = con.createStatement();

    } catch (SQLException e) {

      // invoke SQLErr in case of SQL exceptions
      SQLErr(e);
      System.exit(0);
    } catch (Exception e) {
      e.printStackTrace ();
    }
  }

  /**
   * 
   * close the statement and the connection
   *
   */
  public void finalize() throws Throwable {

    try {

      stmt.close();
      con.close();

    } catch (SQLException e) {
      SQLErr(e);
    } catch (Exception e) {
      e.printStackTrace ();
    }
    super.finalize();

  }
  
  /**
   *
   * traverse the tree, and invoke addNode() for each
   * node on the tree.
   * In order for this to work, this method
   * needs a few pieces of metadata.
   *
   * @param tbl the name of the table containing the tree
   * @param idcol the primary key of that table
   * @param parcol the self-referencing foreign key into the parent row.
   * @param lbl the name of the "label", or descriptive column in the table.
   *
   */
  public void traverseTree(String tbl, String idcol, 
                           String parcol, String lbl) {
    try {

      // get all the rows from the table containing the
      // outline.
      //
      ResultSet rs = stmt.executeQuery(
                       "SELECT " + parcol + ", " + lbl + ", " + idcol +
                       " FROM " + tbl);

      int current = 0;

      // move the result set into a Hashtable, which is
      // keyed by the id column.
      //
      Hashtable rs_cached = new Hashtable();
      boolean wasTopLevelFound = false;

      while (rs.next()) {

        // Create another Hashtable, which will
        // actually contain the label and parent
        // id. This is the information that
        // gets stored in the rs_cached Hashtable.
        //
        Hashtable row = new Hashtable();

        // retrieve the parent id
        //
        int parent_id = rs.getInt(parcol);

        // put the parent id into the row Hashtable
        // as an Integer object, and the label value
        // as a String object.
        //
        row.put(parcol, new Integer(parent_id));
        row.put(lbl,  rs.getString(lbl));
 
        // find the current id, which is the key for
        // the rs_cached Hashtable.
        //
        int current_id = rs.getInt(idcol);
 
        // put the row into the Hashtable
        //
        rs_cached.put(new Integer(current_id), row);

        // find the top-level row; it will have a 
        // parent id of 0. You can only have one of
        // these in the table.
        //
        if (parent_id == 0) {

          // get the *one* row from the table which has
          // a parent column reference to 0. In order for
          // any of these tables to work, it needs to have
          // a top-level row which has no parent. This row
          // should have zero in its parcol.
          //
          current = current_id;
          wasTopLevelFound = true;

        }
      }

      if (!wasTopLevelFound) {
         throw new Exception("Could not locate a top level row.");
      }

      // begin processing the result set which was
      // cached. start out at level 1 (the top)
      //
      int level = 1;

      // elem is a Hashtable which contains all of
      // the nodes as this method traverses the tree.
      // the current value of the current row (the top
      // level item in this case) is used as the Hash
      // key, and the value is its level in the tree.
      //
      // This Hashtable is used to keep a list of
      // "to be processed" rows. At first, just the top
      // level item is in there. As we process each row,
      // this program adds each row's children to the
      // elem Hashtable, thus growing the list of "to
      // be processed" rows.
      //  
      Hashtable elem = new Hashtable();
      elem.put(new Integer(current), new Integer(1));

      // keep digging in the tree until
      // nothing's left!
      //
      while ( level > 0 ) {

        boolean levelFound = false;

        // search for an element in the
        // "to be processed" hash which is
        // at the current value of level
        //
        Enumeration keys = elem.keys();
        while (keys.hasMoreElements()) {

          Integer hashkey = (Integer) keys.nextElement();
          int currLevel  = ((Integer) elem.get(hashkey)).intValue();
    
          // as soon as an element is found whose level
          // is equal to the one currently being processed,
          // grab it's primary key (the hashkey), flag
          // levelfound, and break!
    
          if (currLevel == level) {
            current = hashkey.intValue(); levelFound = true; break;
          }
        }    

        // if we managed to find an unprocessed element which
        // has the same level as the one we're processing,
        // then we can add it to the tree, and see if it has
        // any children.
        //  
        // If we didn't find it, we should decrement level,
        // and see if there's any elements one level up. We
        // can keep doing this until we have tried all the
        // levels (until level = 0).
        //
        if (levelFound) {

          // retrieve the row which has current as
          // its primary key
          //
          Hashtable currow = ((Hashtable) rs_cached.get(new Integer(current)));
          String label = (String) currow.get(lbl);
 
          // remove this (now) processed item from
          // the elem Hashtable
          //
          elem.remove(new Integer(current));

          int children = 0;

          // search rs_cached for any children of the row
          // that was just processed. First, produce an
          // Enumeration of keys in rs_cached, and process
          // each of those.
          //
          Enumeration rskeys = rs_cached.keys();
          while (rskeys.hasMoreElements()) {
      
            // get the current key
            //
            Integer currKey = (Integer) rskeys.nextElement();

            // pull the cached row out of rs_cached.
            //
            Hashtable testRow = (Hashtable) rs_cached.get(currKey);

            // get the parent id from this row.
            //
            int parent_id = ((Integer) testRow.get(parcol)).intValue();

            // is this row a child of the one which was processed 
            // earlier?
            //
            if (parent_id == current) {

              // add the children's levels to the elem
              // Hashtable, keying into their level,
              // which is equal to level + 1
              //
              elem.put(currKey, new Integer(level + 1));
              children++;

            }
          }

          // add the item which was just processed to the tree
          //
          addNode(level, children, label.trim());

          // increment each level; this lets us visit the children
          // of this row, if we added any.
          //
          level++;
  
        } else {

          // no more rows at this level, go one up!
          //
          level--;
        }

      }

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


  /**
   *
   * Adds a node to the tree display. This method should be subclassed
   * if you want the node to be displayed in a widget.
   *
   * @param level the level within the tree (starts at 1)
   * @param children the number of children
   * @param label the text of the item
   *
   */
  void addNode (int level, int children, String label) {
    
    // create some indentation; two spaces for every 
    // level works good...
    //
    String value = "";    
    for (int j = 0; j < level; j++) {
      value += "  ";
    }
    value += label; // add the label to the padding

    // print out the value
    System.out.println(value);

  }

  /**
   *
   * mindless error handler which prints out state, message,
   * and vendor info for SQL errors.
   */
  public void SQLErr(SQLException e) {
    while (e != null) {
      System.out.println("SQLState: " + e.getSQLState());
      System.out.println("Message:  " + e.getMessage());
      System.out.println("Vendor:   " + e.getErrorCode());
      e.printStackTrace();
      e = e.getNextException();
      System.out.println("");
    }
  }

  /*
   * A static main(), which instantiates this object with 
   * some test data.
   */
  public static void main (String argv[]) {
   
    // require a parameter with a jdbc: URL, and complain
    // if it's not there.
    //
    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 outline jdbc:odbc:DATA_SOURCE_NAME;" +
                     "UID=userid;PWD=password");
      System.exit(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 = "";
    }
    
    // create a new outline object,
    // and invoke its traverseTree() method.
    //
    outline tr = new outline(argv[0], user, pwd);
    tr.traverseTree("tree", "tree_id", "parent_id", "name");

  }

}