databaseutils.java

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

    m_DatabaseURL = newDatabaseURL;
  }

  /**
   * Returns the tip text for this property
   * 
   * @return 		tip text for this property suitable for
   * 			displaying in the explorer/experimenter gui
   */
  public String debugTipText() {
    return "Whether debug information is printed.";
  }
  
  /**
   * Sets whether there should be printed some debugging output to stderr or not
   * 
   * @param d 		true if output should be printed
   */
  public void setDebug(boolean d) {
    m_Debug = d;
  }

  /**
   * Gets whether there should be printed some debugging output to stderr or not
   * 
   * @return 		true if output should be printed
   */
  public boolean getDebug() {
    return m_Debug;
  }

  /**
   * Returns the tip text for this property
   * 
   * @return 		tip text for this property suitable for
   * 			displaying in the explorer/experimenter gui
   */
  public String usernameTipText() {
    return "The user to use for connecting to the database.";
  }

  /** 
   * Set the database username 
   *
   * @param username 	Username for Database.
   */
  public void setUsername(String username){
    m_userName = username; 
  }
  
  /** 
   * Get the database username 
   *
   * @return 		Database username
   */
  public String getUsername(){
    return m_userName;
  }

  /**
   * Returns the tip text for this property
   * 
   * @return 		tip text for this property suitable for
   * 			displaying in the explorer/experimenter gui
   */
  public String passwordTipText() {
    return "The password to use for connecting to the database.";
  }

  /** 
   * Set the database password
   *
   * @param password 	Password for Database.
   */
  public void setPassword(String password){
    m_password = password;
  }
  
  /** 
   * Get the database password
   *
   * @return  		Password for Database.
   */
  public String getPassword(){
    return m_password;
  }

  /**
   * Opens a connection to the database
   *
   * @throws Exception 	if an error occurs
   */
  public void connectToDatabase() throws Exception {
    if (m_Debug) {
      System.err.println("Connecting to " + m_DatabaseURL);
    }
    if (m_Connection == null) {
      if (m_userName.equals("")) {
	try {
	  m_Connection = DriverManager.getConnection(m_DatabaseURL);
	} catch (java.sql.SQLException e) {
	  
	  // Try loading the drivers
	  for (int i = 0; i < DRIVERS.size(); i++) {
	    try {
	      Class.forName((String)DRIVERS.elementAt(i));
	    } catch (Exception ex) {
	      // Drop through
	    }
	  }
	  m_Connection = DriverManager.getConnection(m_DatabaseURL);
	}
      } else {
	try {
	  m_Connection = DriverManager.getConnection(m_DatabaseURL, m_userName,
						     m_password);
	} catch (java.sql.SQLException e) {
	  
	  // Try loading the drivers
	  for (int i = 0; i < DRIVERS.size(); i++) {
	    try {
	      Class.forName((String)DRIVERS.elementAt(i));
	    } catch (Exception ex) {
	      // Drop through
	    }
	  }
	  m_Connection = DriverManager.getConnection(m_DatabaseURL, m_userName,
						     m_password);
	}
      }
    }
    m_Connection.setAutoCommit(m_setAutoCommit);
  }

  /**
   * Closes the connection to the database.
   *
   * @throws Exception 	if an error occurs
   */
  public void disconnectFromDatabase() throws Exception {
    if (m_Debug) {
      System.err.println("Disconnecting from " + m_DatabaseURL);
    }
    if (m_Connection != null) {
      m_Connection.close();
      m_Connection = null;
    }
  }
  
  /**
   * Returns true if a database connection is active.
   *
   * @return 		a value of type 'boolean'
   */
  public boolean isConnected() {
    return (m_Connection != null);
  }

  /**
   * Executes a SQL query.
   *
   * @param query 	the SQL query
   * @return 		true if the query generated results
   * @throws SQLException if an error occurs
   */
  public boolean execute(String query) throws SQLException {
    m_PreparedStatement = m_Connection.prepareStatement(
	query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    
    return(m_PreparedStatement.execute());
  }

  /**
   * Gets the results generated by a previous query.
   *
   * @return 		the result set.
   * @throws SQLException if an error occurs
   */
  public ResultSet getResultSet() throws SQLException {
    return m_PreparedStatement.getResultSet();
  }
  
  /**
   * Checks that a given table exists.
   *
   * @param tableName 	the name of the table to look for.
   * @return 		true if the table exists.
   * @throws Exception 	if an error occurs.
   */
  public boolean tableExists(String tableName) throws Exception {
    if (m_Debug) {
      System.err.println("Checking if table " + tableName + " exists...");
    }
    DatabaseMetaData dbmd = m_Connection.getMetaData();
    ResultSet rs;
    if (m_checkForUpperCaseNames) {
      rs = dbmd.getTables (null, null, tableName.toUpperCase(), null);
    } else if (m_checkForLowerCaseNames) {
      rs = dbmd.getTables (null, null, tableName.toLowerCase(), null);
    } else {
      rs = dbmd.getTables (null, null, tableName, null);
    }
    boolean tableExists = rs.next();
    if (rs.next()) {
      throw new Exception("This table seems to exist more than once!");
    }
    rs.close();
    if (m_Debug) {
      if (tableExists) {
	System.err.println("... " + tableName + " exists");
      } else {
	System.err.println("... " + tableName + " does not exist");
      }
    }
    return tableExists;
  }

  /**
   * processes the string in such a way that it can be stored in the
   * database, i.e., it changes backslashes into slashes and doubles single 
   * quotes.
   * 
   * @param s		the string to work on
   * @return		the processed string
   */
  public static String processKeyString(String s) {
    return s.replaceAll("\\\\", "/").replaceAll("'", "''");
  }
  
  /**
   * Executes a database query to see whether a result for the supplied key
   * is already in the database.           
   *
   * @param tableName 	the name of the table to search for the key in
   * @param rp 		the ResultProducer who will generate the result if 
   * 			required
   * @param key 	the key for the result
   * @return 		true if the result with that key is in the database 
   * 			already
   * @throws Exception 	if an error occurs
   */
  protected boolean isKeyInTable(String tableName,
				 ResultProducer rp,
				 Object[] key)
    throws Exception {

    String query = "SELECT Key_Run"
      + " FROM " + tableName;
    String [] keyNames = rp.getKeyNames();
    if (keyNames.length != key.length) {
      throw new Exception("Key names and key values of different lengths");
    }
    boolean first = true;
    for (int i = 0; i < key.length; i++) {
      if (key[i] != null) {
	if (first) {
	  query += " WHERE ";
	  first = false;
	} else {
	  query += " AND ";
	}
	query += "Key_" + keyNames[i] + '=';
	if (key[i] instanceof String) {
	  query += "'" + processKeyString(key[i].toString()) + "'";
	} else {
	  query += key[i].toString();
	}
      }
    }
    boolean retval = false;
    if (execute(query)) {
      ResultSet rs = m_PreparedStatement.getResultSet();
      if (rs.next()) {
	retval = true;
	if (rs.next()) {
	  throw new Exception("More than one result entry "
			      + "for result key: " + query);
	}
      }
      rs.close();
    }
    return retval;
  }

  /**
   * Executes a database query to extract a result for the supplied key
   * from the database.           
   *
   * @param tableName 	the name of the table where the result is stored
   * @param rp 		the ResultProducer who will generate the result if 
   * 			required
   * @param key 	the key for the result
   * @return 		true if the result with that key is in the database 
   * 			already
   * @throws Exception 	if an error occurs
   */
  public Object[] getResultFromTable(String tableName,
					 ResultProducer rp,
					 Object [] key)
    throws Exception {

    String query = "SELECT ";
    String [] resultNames = rp.getResultNames();
    for (int i = 0; i < resultNames.length; i++) {
      if (i != 0) {
	query += ", ";
      }
      query += resultNames[i];
    }
    query += " FROM " + tableName;
    String [] keyNames = rp.getKeyNames();
    if (keyNames.length != key.length) {
      throw new Exception("Key names and key values of different lengths");
    }
    boolean first = true;
    for (int i = 0; i < key.length; i++) {
      if (key[i] != null) {
	if (first) {
	  query += " WHERE ";
	  first = false;
	} else {
	  query += " AND ";
	}
	query += "Key_" + keyNames[i] + '=';
	if (key[i] instanceof String) {
	  query += "'" + processKeyString(key[i].toString()) + "'";
	} else {
	  query += key[i].toString();
	}
      }
    }
    if (!execute(query)) {
      throw new Exception("Couldn't execute query: " + query);
    }
    ResultSet rs = m_PreparedStatement.getResultSet();
    ResultSetMetaData md = rs.getMetaData();
    int numAttributes = md.getColumnCount();
    if (!rs.next()) {
      throw new Exception("No result for query: " + query);
    }
    // Extract the columns for the result
    Object [] result = new Object [numAttributes];
    for(int i = 1; i <= numAttributes; i++) {
      switch (translateDBColumnType(md.getColumnTypeName(i))) {
	case STRING : 
	  result[i - 1] = rs.getString(i);
	  if (rs.wasNull()) {
	    result[i - 1] = null;
	  }
	  break;
	case FLOAT:
	case DOUBLE:
	  result[i - 1] = new Double(rs.getDouble(i));
	  if (rs.wasNull()) {
	    result[i - 1] = null;
	  }
	  break;
	default:
	  throw new Exception("Unhandled SQL result type (field " + (i + 1)
	      + "): "
	      + DatabaseUtils.typeName(md.getColumnType(i)));
      }
    }
    if (rs.next()) {
      throw new Exception("More than one result entry "
			  + "for result key: " + query);
    }
    rs.close();
    return result;
  }

  /**
   * Executes a database query to insert a result for the supplied key
   * into the database.           
   *
   * @param tableName 	the name of the table where the result is stored