tinysql.java

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

      boolean eof = false;        // did we hit eof?
      boolean haveRecord = false; // did we get a record or not?

      // if a table was found at the current level, then we should
      // try to get another row from it.
      //
      if (levelFound) {

        // get the current table
        //
        tinySQLTable jtbl = (tinySQLTable) tables.get(current);

        // skip to the next undeleted record; at some point,
        // this will run out of records, and found will be
        // false.
        //
        boolean found = false;

        while (jtbl.NextRecord()) {
          if (!jtbl.isDeleted()) {
            found = true;
            break;
          }
        }

        if (found) {

          // add each column for this table to
          // the record; record is a tsRow object that
          // is used to hold the values of the current
          // row. It represents every row in every table,
          // and is not added to the result set Vector
          // until we have read a row in the last table
          // in the table list.
          //
          Enumeration cols = jtbl.column_info.keys();
          while (cols.hasMoreElements()) {

            String column_name = (String) cols.nextElement();
            record.put(column_name, jtbl.GetCol(column_name));
 
          }

          // since we were just able to get a row, then
          // we are not at the end of file
          //
          eof = false;
       
          // If the table we are processing is not the last in
          // the list, then we should increment level and loop
          // to the top.
          //
          if (level < t.size()) {

            // increment level
            //
            level++;
            
            // add the next table in the list of tables to
            // the tbl_list, the Hashtable of "to be processed"
            // tables.
            //
            String next_tbl = (String) t.elementAt( level - 1);
            tbl_list.put( next_tbl, new Integer(level) );
  
          } else {
            
            // if the table that was just processed is the last in
            // the list, then we have drilled down to the bottom;
            // all columns have values, and we can add it to the
            // result set. The next time through, the program
            // will try to read another row at this level; if it's
            // found, only columns for the table being read will
            // be overwritten in the tsRow.
            //
            // Columns for the other table will be left alone, and 
            // another row will be added to the result set. Here
            // is the essence of the Cartesian Product which is
            // being built here.
            //
            haveRecord = true;

          }

        } else {

          // we didn't find any more records at this level.
          // Reset the record pointer to the top of the table,
          // and decrement level. We have hit end of file here.
          //
          level--;
          eof = true; 
          jtbl.GoTop();

        }

      } else {

        // no tables were found at this level; back up a level
        // and see if there's any up there.
        //
        level--;
      }

      // if we got a record, then add it to the result set.
      //
      if (haveRecord) {
         resultSet.addElement( (tsRow) record.clone() );
      }

    }

    // filter the result set. The result set Vector just created is
    // a Cartesian Product, which represents all possible combinations
    // of rows in all tables. The TestResult method will evaluate the 
    // WHERE clause for each row, and determine if it qualifies
    // to be included in the final result set.
    //
    for (int y = 0; y < resultSet.size(); y++) {

      tsRow rec =  (tsRow) resultSet.elementAt(y);

      if (TestResult(rec, w, tables)) {
        jrs.rows.addElement(rec);
      }

    }

    // close all the tables
    //
    tbl_enum = tables.elements();
    while (tbl_enum.hasMoreElements()) {
      ( (tinySQLTable) tbl_enum.nextElement() ).close();
    }

    // return a result set
    //
    return jrs;

  }
  
  /*
   *
   * Evaluate a record based on a where clause Vector.
   * The WHERE clause Vector contains a four element
   * array of Strings:
   *
   * Element 0: The type of operation
   * Element 1: The left hand of the expression
   * Element 2: The comparison (=, <>, etc.)
   * Element 3: The right hand of the expression
   *
   */
  private boolean TestResult (tsRow rec, Vector w, Hashtable tables) 
    throws tinySQLException {

    // w is a Vector containing all of the WHERE clause arrays.
    // Since tinySQL does not support OR within WHERE clauses,
    // the processing of these is pretty simple. If one fails,
    // throw out the row.
    //
    for(int i = 0; i < w.size(); i++) {

      // get the WHERE clause info array
      //
      String[] info = (String[]) w.elementAt(i);

      String op         = info[0];
      String left       = info[1];
      String comparison = info[2];
      String right      = info[3];

      // JOIN
      //
      // Simple column to column comparison
      //
      if (op.equals("JOIN")) {

        // get the datatype for each column
        //
        String ltype = findTableForColumn(tables, left).ColType(left);
        String rtype = findTableForColumn(tables, right).ColType(right);

        // if the types don't match, throw an Exception
        //
        if (!ltype.equals(rtype)) {
          throw new tinySQLException("Data type mismatch.");
        }

        // if it's a character comparison, reset the op to
        // STRING_COMPARE, and set the right hand side of the
        // expression to the value of the right hand column.
        // Another lame feature of tinySQL is that all
        // expressions must include at least one column, and
        // it must be on the left.
        //
        if (ltype.equals("CHAR")) {
          op = "STRING_COMPARE";
          right = (String) rec.get(info[3]);
          if (right == null) {
            throw new tinySQLException("Invalid column: " +  info[3]);
          } 
        }

        // if it's a character comparison, reset the op to
        // INT_COMPARE, and set the right hand side of the
        // expression to the value of the right hand column.
        //
        if (ltype.equals("NUMERIC")) {
          op = "INT_COMPARE";
          right = (String) rec.get(info[3]);
          if (right == null) {
            throw new tinySQLException("Invalid column: " +  info[3]);
          }
        }

      }

      // column to string comparison?
      //
      if (op.equals("STRING_COMPARE")) {

        // attempt to read the column value
        //
        String colval = (String) rec.get(left);
        if (colval == null) {
          throw new tinySQLException("Invalid column: " + left);
        }

        // if the comparison is =, then do a simple
        // startsWith comparison. That way, if the
        // column is "FOOBAR", it will match "FOO". 
        //
        if (comparison.equals("=")) {
          if (!colval.startsWith(right)) {
            return false;
          }
        }

        // if it's <>, then throw it out for matching the
        // right-hand value.
        //
        if (comparison.equals("<>")) {
          if (colval.startsWith(right)) {
            return false;
          }
        }

      }

      // column to integer comparison?
      //
      if (op.equals("INT_COMPARE")) {

        long colval, rval;
        String colstr = null;

        // attempt to read the column value
        //
        colstr = (String) rec.get(left);
        if (colstr == null) {
          throw new tinySQLException("Invalid column: " + left);
        }

        // try to convert the column value and the 
        // right hand value to numeric
        //
        try {
          colval = Long.parseLong( colstr.trim() );
        } catch (Exception e) {
          throw new tinySQLException( e.getMessage() + 
            ": Could not convert [" + colstr.trim() + "] to numeric.");
        }

        try {
          rval = Long.parseLong(right.trim());
        } catch (Exception e) {
          throw new tinySQLException( e.getMessage() + 
            ": Could not convert [" + right.trim() + "] to numeric.");
        }


        // if the comparison is simply =, throw it out
        // if it doesn't match (!=)
        //
        if (comparison.equals("=")) {
          if (colval != rval) {
            return false;
          }
        }

        // if the comparison is simply <> throw it out
        // if it does match (==)
        //
        if (comparison.equals("<>")) {
          if (colval == rval) {
            return false;
          }
        }

      }

    }

    // if none of the comparisons failed, return true
    //
    return true;

  }

  /**
   *
   * Given a column name, and a Hashtable containing tables,
   * try to find which table "owns" a given column.
   *
   */
  private tinySQLTable findTableForColumn (Hashtable tables, String col_name) 
    throws tinySQLException {

    tinySQLTable tbl;

    // process each table in the tables Hashtable
    //
    Enumeration enum = tables.elements();
    while (enum.hasMoreElements()) {

      // retrieve the tinySQLTable object
      //
      tbl = (tinySQLTable) enum.nextElement();

      // get the table's column info, and check to 
      // see if it contains the column name in question.
      // if so, return the tinySQLTable object.
      //
      Hashtable column_info = tbl.column_info;

      if (column_info.containsKey(col_name)) {
        return tbl;
      }
    }

    // looks like we couldn't find the column, so throw an exception
    //
    throw new tinySQLException("Column " + col_name + " not found.");
  }

  /*
   *
   * Delete rows which match a where clause.
   *
   */
  private void DeleteStatement (String table_name, Vector w) 
    throws tinySQLException {

    // create the table
    //
    tinySQLTable jtbl = getTable(table_name);

    // put this table into the tables hash
    //
    Hashtable tables = new Hashtable();
    tables.put(table_name, jtbl);

    // process each row in the table
    //
    jtbl.GoTop();
    while (jtbl.NextRecord()) {

      // ignore deleted rows.
      //
      if (!jtbl.isDeleted()) {
      
        // create a new tsRow object, and add each column
        // to it.
        //
        tsRow rec = new tsRow();

        // add each column for this table to
        // the tsRow object 
        //
        Enumeration cols = jtbl.column_info.keys();
        while (cols.hasMoreElements()) {
          String column_name = (String) cols.nextElement();
          rec.put(column_name, jtbl.GetCol(column_name));
        }

        // invoke TestResult to see if the table matches
        // the WHERE clause(s) - if so, delete it.
        //
        if (TestResult(rec, w, tables)) {
          jtbl.DeleteRow();
        }
      }
    }
    jtbl.close();
  }

  /*
   *
   * Update rows which match a WHERE clause
   *
   */
  private void UpdateStatement(String table_name, Vector c, Vector v, Vector w) 
    throws tinySQLException {

    // create the table
    //
    tinySQLTable jtbl = getTable(table_name);

    // put this table into the tables hash
    //
    Hashtable tables = new Hashtable();
    tables.put(table_name, jtbl);

    // process each row in the table
    //
    jtbl.GoTop();
    while (jtbl.NextRecord()) {

      // ignore deleted rows.
      //
      if (!jtbl.isDeleted()) {
       
        // create a new tsRow object, and add each column
        // to it.
        //
        tsRow rec = new tsRow();

        // add each column for this table to
        // the tsRow object 
        //
        Enumeration cols = jtbl.column_info.keys();
        while (cols.hasMoreElements()) {
          String column_name = (String) cols.nextElement();
          rec.put(column_name, jtbl.GetCol(column_name));
        }

        // invoke TestResult to see if the table matches
        // the WHERE clause(s) - if so, update it with the 
        // columns and values Vectors.
        //
        if (TestResult(rec, w, tables)) {
          jtbl.UpdateCurrentRow(c, v);
        }
      }
    }
    jtbl.close();
  }

  /*
   *
   * Issue an insert statement
   *
   */
  private void InsertStatement (String table_name, Vector c, Vector v) 
    throws tinySQLException {

    // create the tinySQLTable object
    //
    tinySQLTable jtbl = getTable(table_name);

    // insert a row, and update it with the c and v Vectors
    //
    jtbl.InsertRow();
    jtbl.UpdateCurrentRow(c, v);
    jtbl.close();

  }

  /*
   *
   * Creates a table given a table_name, and a Vector of column
   * definitions.
   *
   * The column definitions are an array of Strings having the following
   * elements:
   *
   * Element 1: Data type
   * Element 2: Column name
   * Element 3: Size
   *
   */
  abstract void CreateTable (String table_name, Vector v) 
    throws IOException, tinySQLException;

  /*
   *
   * Drops a table by name
   *
   */
  abstract void DropTable (String table_name) 
    throws tinySQLException;

  /*
   *
   * Create a tinySQLTable object by table name
   *
   */
  abstract tinySQLTable getTable(String table_name) throws tinySQLException;

}