databaseutils.java

Java 编写的多种数据挖掘算法 包括聚类、分类、预处理等

/*
 *    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.
 */

/*
 *    DatabaseUtils.java
 *    Copyright (C) 1999 Len Trigg
 *
 */


package weka.experiment;

import weka.core.Utils;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 * DatabaseUtils provides utility functions for accessing the experiment
 * database. The jdbc
 * driver and database to be used default to "jdbc.idbDriver" and
 * "jdbc:idb=experiments.prp". These may be changed by creating
 * a java properties file called DatabaseUtils.props in user.home or
 * the current directory. eg:<p>
 *
 * <code><pre>
 * jdbcDriver=jdbc.idbDriver
 * jdbcURL=jdbc:idb=experiments.prp
 * </pre></code><p>
 *
 * @author Len Trigg ([e-mail])
 * @version $Revision: 1.24 $
 */
public class DatabaseUtils implements Serializable {

  /** The name of the table containing the index to experiments */
  public static final String EXP_INDEX_TABLE = "Experiment_index";

  /** The name of the column containing the experiment type (ResultProducer) */
  public static final String EXP_TYPE_COL    = "Experiment_type";

  /** The name of the column containing the experiment setup (parameters) */
  public static final String EXP_SETUP_COL   = "Experiment_setup";
  
  /** The name of the column containing the results table name */
  public static final String EXP_RESULT_COL  = "Result_table";

  /** The prefix for result table names */
  public static final String EXP_RESULT_PREFIX = "Results";

  /** The name of the properties file */
  protected static String PROPERTY_FILE
    = "weka/experiment/DatabaseUtils.props";

  /** Holds the jdbc drivers to be used (only to stop them being gc'ed) */
  protected Vector DRIVERS = new Vector();

  /** Properties associated with the database connection */
  protected Properties PROPERTIES;



  /* Type mapping used for reading experiment results */
  public static final int STRING = 0;
  public static final int BOOL = 1;
  public static final int DOUBLE = 2;
  public static final int BYTE = 3;
  public static final int SHORT = 4;
  public static final int INTEGER = 5;
  public static final int LONG = 6;
  public static final int FLOAT = 7;
  public static final int DATE = 8; 
 
  
  /** Database URL */
  protected String m_DatabaseURL;

  
  /* returns key column headings in their original case. Used for
     those databases that create uppercase column names. */
  protected String attributeCaseFix(String columnName){
    if (m_checkForUpperCaseNames==false){
      return(columnName);
    }
    String ucname = columnName.toUpperCase();
    if (ucname.equals(EXP_TYPE_COL.toUpperCase())){
      return (EXP_TYPE_COL);
    } else if (ucname.equals(EXP_SETUP_COL.toUpperCase())){
      return (EXP_SETUP_COL);
    } else if (ucname.equals(EXP_RESULT_COL.toUpperCase())){
      return (EXP_RESULT_COL);
    } else {
      return(columnName);
    }
    
    
  }
  
  /**
   * 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;
  }

  /** 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);
  }

  /** 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);
  }

 
  /**
   * translates the column data type string to an integer value that indicates
   * which data type / get()-Method to use in order to retrieve values from the
   * database (see DatabaseUtils.Properties, InstanceQuery()). Blanks in the type 
   * are replaced with underscores "_", since Java property names can't contain blanks.
   * @param type the column type as retrieved with java.sql.MetaData.getColumnTypeName(int)
   * @return an integer value that indicates
   * which data type / get()-Method to use in order to retrieve values from the
   */
  int translateDBColumnType(String type) {

    try {
      // Oracle, e.g., has datatypes like "DOUBLE PRECISION"
      // BUT property names can't have blanks in the name, hence replace blanks
      // with underscores "_":
      type = type.replaceAll(" ", "_");
      return Integer.parseInt(PROPERTIES.getProperty(type));
    } catch (NumberFormatException e) {
      throw new IllegalArgumentException("Unknown data type: " + type + ". Add entry " +
					 "in weka/experiment/DatabaseUtils.props.");
    }
  }
 
  /** The prepared statement used for database queries. */
  protected PreparedStatement m_PreparedStatement;
 
   
  /** The database connection */
  protected Connection m_Connection;


  /** True if debugging output should be printed */
  protected boolean m_Debug = false;
  
  /** Database username */
  protected String m_userName="";

  /** Database Password */
  protected String m_password="";

  /** mappings used for creating Tables. Can be overridden in DatabaseUtils.props*/
  protected String m_stringType="LONGVARCHAR";
  protected String m_intType="INT";
  protected String m_doubleType="DOUBLE";
  

  /* For databases where Tables and Columns are created in upper case */
  protected boolean m_checkForUpperCaseNames=false;

  /* setAutoCommit on the database? */
  protected boolean m_setAutoCommit=true;

  /* create index on the database? */
  protected boolean m_createIndex=false;


  /**
   * Reads properties and sets up the database drivers
   *
   * @throws Exception if an error occurs
   */
  public DatabaseUtils() throws Exception {

    try {
      PROPERTIES = Utils.readProperties(PROPERTY_FILE);
   
      // Register the drivers in jdbc DriverManager
      String drivers = PROPERTIES.getProperty("jdbcDriver",
					    "jdbc.idbDriver");

      if (drivers == null) {
	throw new Exception("No jdbc drivers specified");
      }
      // The call to newInstance() is necessary on some platforms
      // (with some java VM implementations)
      StringTokenizer st = new StringTokenizer(drivers, ", ");
      while (st.hasMoreTokens()) {
	String driver = st.nextToken();
	boolean result;
	try {
	  Class.forName(driver);
	  DRIVERS.addElement(driver);
	  result = true;
	}
	catch (Exception e) {
	  result = false;
	}
	if (m_Debug || !result) 
	  System.err.println(
	      "Trying to add JDBC driver: " + driver 
	      + " - " + (result ? "Success!" : "Error, not in CLASSPATH?"));
      }
    } catch (Exception ex) {
      System.err.println("Problem reading properties. Fix before continuing.");
      System.err.println(ex);
    }

    m_DatabaseURL = PROPERTIES.getProperty("jdbcURL",
					   "jdbc:idb=experiments.prp");
    m_stringType=PROPERTIES.getProperty("CREATE_STRING");
    m_intType=PROPERTIES.getProperty("CREATE_INT");
    m_doubleType=PROPERTIES.getProperty("CREATE_DOUBLE");
    String uctn=PROPERTIES.getProperty("checkUpperCaseNames");
    if (uctn.equals("true")) {
      m_checkForUpperCaseNames=true;
    }else {
      m_checkForUpperCaseNames=false;
    }
    uctn=PROPERTIES.getProperty("setAutoCommit");
    if (uctn.equals("true")) {
      m_setAutoCommit=true;
    } else {
      m_setAutoCommit=false;
    }
    uctn=PROPERTIES.getProperty("createIndex");
    if (uctn.equals("true")) {
      m_createIndex=true;
    } else {
      m_createIndex=false;
    }
  }

  /**
   * Converts an array of objects to a string by inserting a space
   * between each element. Null elements are printed as ?
   *
   * @param array the array of objects
   * @return a value of type 'String'
   */
  public static String arrayToString(Object [] array) {

    String result = "";
    if (array == null) {
      result = "<null>";
    } else {
      for (int i = 0; i < array.length; i++) {
	if (array[i] == null) {
	  result += " ?";
	} else {
	  result += " " + array[i];
	}
      }
    }
    return result;
  }

  /**
   * Returns the name associated with a SQL type.
   *
   * @param type the SQL type
   * @return the name of the type
   */
  public static String typeName(int type) {
  
    switch (type) {
    case Types.BIGINT :
      return "BIGINT ";
    case Types.BINARY:
      return "BINARY";
    case Types.BIT:
      return "BIT";
    case Types.CHAR:
      return "CHAR";
    case Types.DATE:
      return "DATE";
    case Types.DECIMAL:
      return "DECIMAL";
    case Types.DOUBLE:
      return "DOUBLE";
    case Types.FLOAT:
      return "FLOAT";
    case Types.INTEGER:
      return "INTEGER";
    case Types.LONGVARBINARY:
      return "LONGVARBINARY";
    case Types.LONGVARCHAR:
      return "LONGVARCHAR";
    case Types.NULL:
      return "NULL";
    case Types.NUMERIC:
      return "NUMERIC";
    case Types.OTHER:
      return "OTHER";
    case Types.REAL:
      return "REAL";
    case Types.SMALLINT:
      return "SMALLINT";
    case Types.TIME:
      return "TIME";
    case Types.TIMESTAMP:
      return "TIMESTAMP";
    case Types.TINYINT:
      return "TINYINT";
    case Types.VARBINARY:
      return "VARBINARY";
    case Types.VARCHAR:
      return "VARCHAR";
    default:
      return "Unknown";
    }
  }

  /**
   * Returns the tip text for this property
   * @return tip text for this property suitable for
   * displaying in the explorer/experimenter gui
   */
  public String databaseURLTipText() {
    return "Set the URL to the database.";
  }

  /**
   * Get the value of DatabaseURL.
   *
   * @return Value of DatabaseURL.
   */
  public String getDatabaseURL() {
    
    return m_DatabaseURL;
  }
  
  /**
   * Set the value of DatabaseURL.
   *
   * @param newDatabaseURL Value to assign to DatabaseURL.
   */
  public void setDatabaseURL(String newDatabaseURL) {
    
    m_DatabaseURL = newDatabaseURL;
  }

  /**
   * 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);
	}
      }
    }
    if (m_setAutoCommit){
      m_Connection.setAutoCommit(true);
    } else {
      m_Connection.setAutoCommit(false);
    }
  }

  /**
   * 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);
    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 == true){
      rs = dbmd.getTables (null, null, tableName.toUpperCase(), 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");