databaseloader.java

MacroWeka扩展了著名数据挖掘工具weka

  }
  
  /** Reads an instance from a database.
   * @param rs the ReusltSet to load
   * @throws Exception if instance cannot be read
   * @return an instance read from the database
   */  
  private Instance readInstance(ResultSet rs) throws Exception{
  
      FastVector instances = new FastVector();
      ResultSetMetaData md = rs.getMetaData();
      int numAttributes = md.getColumnCount();
      double[] vals = new double[numAttributes];
      m_structure.delete();
      for(int i = 1; i <= numAttributes; i++) {
	switch (m_DataBaseConnection.translateDBColumnType(md.getColumnTypeName(i))) {
	case STRING :
	  String str = rs.getString(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    Double index = (Double)m_nominalIndexes[i - 1].get(str);
	    if (index == null) {
              index = new Double(m_structure.attribute(i-1).addStringValue(str));
	    }
	    vals[i - 1] = index.doubleValue();
	  }
	  break;
	case BOOL:
	  boolean boo = rs.getBoolean(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    vals[i - 1] = (boo ? 1.0 : 0.0);
	  }
	  break;
	case DOUBLE:
	  //	  BigDecimal bd = rs.getBigDecimal(i, 4); 
	  double dd = rs.getDouble(i);
	  // Use the column precision instead of 4?
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    //	    newInst.setValue(i - 1, bd.doubleValue());
	    vals[i - 1] =  dd;
	  }
	  break;
	case BYTE:
	  byte by = rs.getByte(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    vals[i - 1] = (double)by;
	  }
	  break;
	case SHORT:
	  short sh = rs.getByte(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    vals[i - 1] = (double)sh;
	  }
	  break;
	case INTEGER:
	  int in = rs.getInt(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    vals[i - 1] = (double)in;
	  }
	  break;
	case LONG:
	  long lo = rs.getLong(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    vals[i - 1] = (double)lo;
	  }
	  break;
	case FLOAT:
	  float fl = rs.getFloat(i);
	  if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
	    vals[i - 1] = (double)fl;
	  }
	  break;
	case DATE:
          Date date = rs.getDate(i);
          if (rs.wasNull()) {
	    vals[i - 1] = Instance.missingValue();
	  } else {
            // TODO: Do a value check here.
            vals[i - 1] = (double)date.getTime();
          }
          break;
	default:
	  vals[i - 1] = Instance.missingValue();
	}
      }
       Instance inst = new Instance(1.0, vals);
       //get rid of m_idColumn
       if(m_DataBaseConnection.getUpperCase())
              m_idColumn = m_idColumn.toUpperCase();
       if(m_structure.attribute(0).name().equals(m_idColumn)){
            inst.deleteAttributeAt(0);
            m_oldStructure.add(inst);
            inst = m_oldStructure.instance(0);
            m_oldStructure.delete(0);
       }
       else{
        //instances is added to and deleted from the structure to get the true nominal values instead of the index of the values.
        m_structure.add(inst);
        inst = m_structure.instance(0);
        m_structure.delete(0);
       }
       return inst;
       
  }

  /**
   * Read the data set incrementally---get the next instance in the data 
   * set or returns null if there are no
   * more instances to get. If the structure hasn't yet been 
   * determined by a call to getStructure then method does so before
   * returning the next instance in the data set.
   *
   * @return the next instance in the data set as an Instance object or null
   * if there are no more instances to be read
   * @exception IOException if there is an error during parsing
   */
  public Instance getNextInstance() throws IOException {

      
    if (m_DataBaseConnection == null) 
      throw new IOException("No source database has been specified"); 
    if (getRetrieval() == BATCH) {
      throw new IOException("Cannot mix getting Instances in both incremental and batch modes");
    }
    //pseudoInremental: Load all instances into main memory in batch mode and give them incrementally to user
    if(m_pseudoIncremental){
        if (m_structure == null){
            setRetrieval(NONE);  
            getStructure();
        }
        setRetrieval(INCREMENTAL);
        if(m_datasetPseudoInc.numInstances() > 0){
            Instance current = m_datasetPseudoInc.instance(0);
            m_datasetPseudoInc.delete(0);
            return current;
        }
        else{
            resetStructure();
            return null;
        }
    }
    //real incremental mode. At the moment(version 1.0) only for MySQL and HSQLDB (Postgres not tested, should work)
    setRetrieval(INCREMENTAL);
    try{
        if(!m_DataBaseConnection.isConnected())
            connectToDatabase();
        //if no header determined yet, do it
        if(m_structure == null)
            m_structure = getStructure();
        //if no key columns specified by user, try to detect automatically
        if(m_firstTime && m_orderBy.size() == 0){
            if(!checkForKey())
                throw new Exception("A unique order cannot be detected automatically.\nYou have to use SELECT * in your query to enable this feature.\nMaybe JDBC driver is not able to detect key.\nDefine primary key in your database or use -P option (command line) or enter key columns in the GUI.");
        }
        if(m_firstTime){
            m_firstTime = false;
            m_rowCount = getRowCount();
        }
        //as long as not all rows has been loaded
        if(m_counter < m_rowCount){
            if (m_DataBaseConnection.execute(limitQuery(m_query,m_counter,m_choice)) == false) {
                throw new Exception("Tuple could not be retrieved.");
            }
            m_counter++;
            ResultSet rs = m_DataBaseConnection.getResultSet();
            rs.next();
            Instance current = readInstance(rs);
            rs.close();
            return current;
        }
        else{
            m_DataBaseConnection.disconnectFromDatabase();
            resetStructure();
            return null;
        }
    }catch(Exception ex) {
        printException(ex);
    }
    return null;
  }
  
  
  
  /** Gets the setting
   * @return the current setting
   */  
  public String[] getOptions() {
      
    Vector options = new Vector();
    
    options.add("-Q"); 
    options.add(getQuery());
    
    StringBuffer text = new StringBuffer();
    for (int i = 0; i < m_orderBy.size(); i++) {
      if (i > 0)
        text.append(", ");
      text.append((String) m_orderBy.elementAt(i));
    }
    options.add("-P"); 
    options.add(text.toString());
    
    if (m_inc)
      options.add("-I");
    
    return (String[]) options.toArray(new String[options.size()]);
  }
  
  /** Lists the available options
   * @return an enumeration of the available options
   */  
  public java.util.Enumeration listOptions() {
      
     FastVector newVector = new FastVector(3);

     newVector.addElement(new Option("\tSQL query of the form SELECT <list of columns>|* FROM <table> [WHERE] to execute (default Select * From Results0).",
				     "Q",1,"-Q <query>"));
     newVector.addElement(new Option("\tList of column names uniquely defining a DB row (separated by ', ').\n\tUsed for incremental loading."
        +"\n\tIf not specified, the key will be determined automatically, if possible with the used JDBC driver.\n\tThe auto ID column created by the DatabaseSaver won't be loaded.",
				     "P",1,"-P<list of column names>"));
     newVector.addElement(new Option("\tSets incremental loading", "I", 0,
				    "-I"));
     
     return  newVector.elements();
  }
  
  /** Sets the options.
   *
   * Available options are:
   * -Q the query to specify which tuples to load<br>
   * The query must have the form:
   * SELECT *|<column-list> FROM <table> [WHERE}
   * (default: SELECT * FROM Results0).<p>
   *
   * -P comma separted list of columns that are a unqiue key <br>
   * Only needed for incremental loading, if it cannot be detected automatically<p>
   *
   * -I <br>
   * Sets incremental loading
   *
   * @param options the options
   * @throws Exception if options cannot be set
   */  
  public void setOptions(String[] options) throws Exception {
      
    String optionString, keyString;
    optionString = Utils.getOption('Q',options);
    keyString = Utils.getOption('P',options);
    reset();
    if (optionString.length() != 0)
      setQuery(optionString);
    m_orderBy.removeAllElements();
    m_inc = Utils.getFlag('I', options);
    if(m_inc){
        StringTokenizer st = new StringTokenizer(keyString, ",");
        while (st.hasMoreTokens()) {
            String column = st.nextToken();
            column = column.replaceAll(" ","");
            m_orderBy.addElement(column);
        }
    }
  }
  
  /**Prints an exception
   * @param ex the exception to print
   */  
  private void printException(Exception ex){
  
      System.out.println("\n--- Exception caught ---\n");
	while (ex != null) {
		System.out.println("Message:   "
                                   + ex.getMessage ());
                if(ex instanceof SQLException){
                    System.out.println("SQLState:  "
                                   + ((SQLException)ex).getSQLState ());
                    System.out.println("ErrorCode: "
                                   + ((SQLException)ex).getErrorCode ());
                    ex = ((SQLException)ex).getNextException();
                }
                else
                    ex = null;
		System.out.println("");
	}
      
      
  }

  /** Main method.
   * @param options the options
   */
  public static void main(String [] options) {
    
      DatabaseLoader atf;
      try {
	atf = new DatabaseLoader();
        atf.setOptions(options);
        atf.setSource();
        if(!atf.m_inc)
            System.out.println(atf.getDataSet());
        else{
            System.out.println(atf.getStructure());
            Instance temp;
            do {
            temp = atf.getNextInstance();
            if (temp != null) {
                System.out.println(temp);
            }
            } while (temp != null);
        }
      } catch (Exception e) {
	e.printStackTrace();
        System.out.println("\n"+e.getMessage());
      }
    
  }
  
  
  
}