tinysql.java

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

/*
 *
 * tinySQL.java 
 *
 * Copyright 1996, Brian C. Jepson
 *                 ([e-mail])
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *
 * A trivial implementation of SQL in an abstract class.
 * Plug it in to your favorite non-SQL data source, and
 * QUERY AWAY!
 *
 */
import java.util.*;
import java.lang.*;
import java.io.*;

public abstract class tinySQL {

  // This is the InputStream from which the parser reads.
  // 
  InputStream SQLStream; 

  // This is the last SQL Statement processed by sqlexec(String s).
  // Note that sqlexec() does *not* support this.
  //
  String sqlstatement = "";

  /**
   *
   * Constructs a new tinySQL object.
   *
   */
  public tinySQL() {

  }

  /**
   *
   * Reads SQL statements from System.in() and returns a
   * tsResultSet for the last statement executed. This is
   * really only good for testing.
   * 
   * @exception tinySQLException
   *
   */
  public tsResultSet sqlexec() throws tinySQLException {

    SQLStream = (InputStream) System.in;
    System.err.println("Reading SQL Statements from STDIN...");
    return sql();

  }

  /**
   *
   * Processes the SQL Statement contained in s and returns
   * a tsResultSet.
   *
   * @param s String containing a SQL Statement
   * @exception tinySQLException
   *
   */
  public tsResultSet sqlexec(String s) throws tinySQLException {

    sqlstatement = s;

    ByteArrayInputStream st = new ByteArrayInputStream(s.getBytes());
    SQLStream = (InputStream) st;
    return sql();

  }

  /**
   *
   * Read SQL Statements from the SQLStream, and
   * return a result set for the last SQL Statement
   * executed.
   *
   * @exception tinySQLException
   *
   */
  protected tsResultSet sql() throws tinySQLException {

    // the result set
    //
    tsResultSet rs = null;

    try {

      // Instantiate a new parser object which reads from
      // the SQLStream.
      //
      parser parse_obj = new parser(SQLStream);

      // let the parser do its thing
      //
      parse_obj.parse();

      // retrieve the action vector from the parser. As
      // the parser parses the SQL Statement, it fills
      // the action vector with information about each
      // SQL Statement in the batch. This information
      // takes the form of a Hashtable.
      //
      Vector actions = parse_obj.action_obj.getActions();


      // each Hashtable corresponds to an SQL Statement. Although
      // tinySQL does not return multiple result sets, it will
      // nevertheless process each one in turn, and return a
      // result set for the last statement executed.
      //
      // Here, tinySQL processes each Hashtable in the actions Vector.
      //
      for (int i = 0; i < actions.size(); i++) {
              
        // get the Hashtable
        //
        Hashtable h = (Hashtable) actions.elementAt(i);
  
        //
        // SQL UPDATE
        //
        // If this statement is an UPDATE statement, then the
        // following components of the Hashtable are significant:
        //
        // String table_name - the name of the affected table
        //
        // Vector columns    - a list of columns to be updated
        //
        // Vector values     - a list of values with which to
        //                     update the columns (both the columns
        //                     and values must be in the same order)
        //
        // Vector where      - a list of WHERE clauses. tinySQL
        //                     assumes AND, but support for OR
        //                     will be added in later versions.
        //
        if ( ((String)h.get("TYPE")).equals("UPDATE") ) {
        
          String table_name = (String) h.get("TABLE");
          Vector columns    = (Vector) h.get("COLUMNS");
          Vector values     = (Vector) h.get("VALUES");
          Vector where      = (Vector) h.get("WHERE");

          UpdateStatement (table_name, columns, values, where);
          rs = new tsResultSet();
 
        }

        //
        // SQL DELETE
        //
        // If this statement is an DELETE statement, then the
        // following components of the Hashtable are significant:
        //
        // String table_name - the name of the affected table
        //
        // Vector where      - a list of WHERE clauses. 
        //
        if ( ((String)h.get("TYPE")).equals("DELETE") ) {
        
          String table_name = (String) h.get("TABLE");
          Vector where      = (Vector) h.get("WHERE");

          DeleteStatement (table_name, where);
          rs = new tsResultSet();

        }

        //
        // SQL SELECT
        //
        // If this statement is a SELECT statement, then the 
        // following components of the Hashtable are significant:
        //
        // Vector tbls    - a list of affected tables
        //
        // Vector columns - a list of columns to be retrieved
        //
        // Vector where   - a list of WHERE clauses. 
        //
        if ( ((String)h.get("TYPE")).equals("SELECT") ) {
        
          Vector tbls    = (Vector) h.get("TABLES");
          Vector columns = (Vector) h.get("COLUMNS");
          Vector where   = (Vector) h.get("WHERE");

          rs = SelectStatement (tbls, columns, where);

        }

        //
        // SQL INSERT
        //
        // If this statement is an INSERT statement, then the
        // following components of the Hashtable are significant:
        //
        // String table_name - the name of the affected table
        //
        // Vector columns    - a list of columns to be updated
        //
        // Vector values     - a list of values with which to
        //                     update the columns (both the columns
        //                     and values must be in the same order)
        //
        if ( ((String)h.get("TYPE")).equals("INSERT") ) {
          
          String table_name = (String) h.get("TABLE");
          Vector columns = (Vector) h.get("COLUMNS");
          Vector values = (Vector) h.get("VALUES");

          InsertStatement (table_name, columns, values);
          rs = new tsResultSet();

        }

        //
        // SQL CREATE TABLE
        //
        // If this statement is a CREATE TABLE statement, then the
        // following components of the Hashtable are significant:
        //
        // String table_name - the name of the affected table
        //
        // Vector columns    - a list of columns to be created
        //
        // 
        if ( ((String)h.get("TYPE")).equals("CREATE") ) {

          String table_name = (String) h.get("TABLE");
          Vector columns = (Vector) h.get("COLUMN_DEF");

          CreateTable (table_name, columns);
          rs = new tsResultSet();

        }

        //
        // SQL DROP TABLE
        //
        // If this statement is a DROP TABLE statement, then the
        // following components of the Hashtable are significant:
        //
        // String table_name - the name of the affected table
        //
        //
        if ( ((String)h.get("TYPE")).equals("DROP_TABLE") ) {
 
          DropTable( (String) h.get("TABLE") );
          rs = new tsResultSet();
        }

      }

    } catch (Exception e) {
      e.printStackTrace();
      throw new tinySQLException(e.getMessage() + ": " + sqlstatement);
    }

    return rs;
  }

  /*
   *
   * Execute an SQL Select Statement
   *
   */
  private tsResultSet SelectStatement (Vector t, Vector c, Vector w) 
    throws tinySQLException {

    // this is a Hashtable of all tables participating
    // in the Select
    //
    Hashtable tables = new Hashtable();

    // instantiate a new tsResultSet
    //
    tsResultSet jrs = new tsResultSet();

    // Some enumerations which will be used later
    //
    Enumeration col_keys, tbl_enum;

    // for loop index
    //
    int i;

    // instantiate a table object for each
    // table in the list of tables
    //
    for (i = 0; i < t.size(); i++) {
   
      // the t Vector contains all the table names
      // in the SELECT. For each one of these, create
      // a new table
      //
      String table_name = (String) t.elementAt(i);
      tinySQLTable jtbl = getTable(table_name);

      // put each table object into the tables
      // Hashtable. They are keyed by name, so
      // they can be retrieved by name in 
      // subsequent operations
      //
      tables.put( table_name, jtbl );
      jtbl.GoTop();

    }

    // the c_remove Vector is a list of columns to be removed
    // from the c Vector. At present, this is only the '*' columns
    // and they are only removed after they have been expanded.
    // 
    // These need to be removed, since '*' is not a valid column
    // name.
    //
    Vector c_remove = new Vector();

    // This code iterates over each named column in the c
    // Vector. If a column is found named '*', it is expanded
    // to all columns in the query. Since this early version
    // of tinySQL does not support table aliasing, it's probably
    // best if the '*' is the only item in the column specification.
    //
    int c_size = c.size();
    for(i = 0; i < c_size; i++) {

      // is this item a '*'?
      //
      if ( ((String) c.elementAt(i)).equals("*") ) {

        // get an enumeration of all tables
        //
        tbl_enum = tables.elements();

        // process each element in the enumeration
        //
        while (tbl_enum.hasMoreElements()) {
        
          // get the next table in the enumeration
          //
          tinySQLTable jtbl = (tinySQLTable) tbl_enum.nextElement();

          // retrieve an enumeration of columns for that table
          //
          col_keys = jtbl.column_info.keys();

          // process each column in the enumeration
          //
          while (col_keys.hasMoreElements()) {

            // add each column to the c Vector
            //
            String col_name = (String) col_keys.nextElement();
            c.addElement(col_name);
          }
        }

        // add the column that had the '*' to the
        // c_remove Vector
        //
        c_remove.addElement( new Integer(i) );
      }
    }

    // remove the '*' items from c
    //
    for (i = 0; i < c_remove.size(); i++) {
      c.removeElementAt( ((Integer) c_remove.elementAt(i)).intValue() );
    }

    // the columns Hashtable is keyed by table name. It contains
    // a Vector of columns just for that table.
    // 
    Hashtable columns = new Hashtable();

    // process each element in the c Vector
    //
    for (i = 0; i < c.size(); i++) {

      // get the column name
      //
      String column = (String) c.elementAt(i);

      // find the table that "owns" this column
      //
      tinySQLTable jtbl = findTableForColumn(tables, column);

      // add this column to the list of columns 
      // keyed by table name.
      //
      Vector cols;

      // is this table already represented in the hash
      // of columns? If it is, retrieve the Vector which
      // represents all the columns added for that table.
      // Otherwise, created a new Vector.
      //
      if (columns.containsKey(jtbl.table)) {
        cols = (Vector) columns.get(jtbl.table);
      } else {
        cols = new Vector();
      }

      // add this column to the cols Vector and put
      // it in the columns Hashtable, keyed by
      // table name.
      //
      cols.addElement(column);
      columns.put( jtbl.table, cols);

      // create a new tsColumn object and add it to the result
      // set's column field. This can be used for metadata 
      // lookups.
      //
      tsColumn jsc = new tsColumn(column);
      jsc.type = jtbl.ColType(column);
      jsc.size = jtbl.ColSize(column);

      jrs.columns.addElement(jsc);

    }

    // the table scan here is an iterative tree expansion, similar to
    // the algorithm shown in the outline example in Chapter 5.
    //

    // which table level are we on?
    //
    int level = 1;

    // create a hashtable to enumerate the tables to
    // be scanned
    //
    Hashtable tbl_list = new Hashtable();

    // put the name of the first table into it
    //
    String current = (String) t.elementAt(0);
    tbl_list.put( current, new Integer(1) );

    // create a row object; this is added to the
    // result set
    //
    tsRow record = new tsRow();

    // the resultSet vector is a temporary holding
    // pen for the result set; later on, data in the rows 
    // in this Vector are evaluated before being added
    // to  the real result set. This is a pretty
    // inefficient; things would be better if WHERE
    // clauses were evaluated as (or before) the initial
    // result set is retrieved.
    //
    // Subsequent versions of tinySQL will optimize this
    // quite a bit.
    //
    Vector resultSet = new Vector();

    // keep retrieving rows until we run out of rows to
    // process.
    //
    while ( level > 0 ) {
      
      boolean levelFound = false;

      // find an item within the tbl_list
      // which has the same level as the
      // one we're on.
      //
      Enumeration keys = tbl_list.keys();
      while (keys.hasMoreElements()) {
        
        // get the next element in the "to be processed"
        // Hashtable, and find out its level, storing this
        // value in currLevel.
        //
        String hashkey = (String) keys.nextElement();
        int currLevel  = ((Integer) tbl_list.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; levelFound = true; break;
        }
      }