databaseutilities.java

一个数据挖掘系统的源码

/**
 *
 *   AgentAcademy - an open source Data Mining framework for
 *   training intelligent agents
 *
 *   Copyright (C)   2001-2003 AA Consortium.
 *
 *   This library is open source software; you can redistribute it
 *   and/or modify it under the terms of the GNU Lesser General
 *   Public License as published by the Free Software Foundation;
 *   either version 2.0 of the License, or (at your option) any later
 *   version.
 *
 *   This library 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 Lesser General Public
 *   License along with this library; if not, write to the Free
 *   Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
 *   MA  02111-1307 USA
 *
 */

package org.agentacademy.modules.dataminer.misc;

/**
 * <p>Title: The Data Miner prototype</p>
 * <p>Description: A prototype for the DataMiner (DM), the Agent Academy (AA) module responsible for performing data mining on the contents of the Agent Use Repository (AUR). The extracted knowledge is to be sent back to the AUR in the form of a PMML document.</p>
 * <p>Copyright: Copyright (c) 2002</p>
 * <p>Company: CERTH</p>
 * @author asymeon
 * @version 0.3
 */


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import org.apache.log4j.Logger;
import weka.experiment.ResultProducer;

public class DatabaseUtilities {

    private static final String DEFAULT_DATABASE_NAME = "ALTEC";
    private static final String DEFAULT_URL = "jdbc:microsoft:sqlserver://danae.ee.auth.gr:1433/" + DEFAULT_DATABASE_NAME;
    private static final String DEFAULT_USER = "administrator";//"username";
    private static final String DEFAULT_PASS = "#3e8wros!";//"password";


    /** 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";


    private Connection con;
    private Statement stmt;
    private String url, user, pass;
    public static Logger                log = Logger.getLogger(DatabaseUtilities.class);
    protected boolean Debug = true;

  /**
   * 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
   */
  static public 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";
    }
  }

  /**
   * Opens a connection to the database
   *
   * @exception Exception if an error occurs
   */

     public void connectToDatabase (){
        url = "jdbc:microsoft:sqlserver://danae.ee.auth.gr:1433;DatabaseName=ALTEC;User=asymeon;Password=asym7694";
/*
        user = "sa";
        pass = "#3e8wros!";
*/
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            con = DriverManager.getConnection(url);
            log.debug("Connection successful");
            stmt = con.createStatement();
            System.out.println("Create statement successful");

        }
        catch (SQLException e) {
            System.out.println("*** DatabaseOpen.<init>: SQL Exception> "+e);
        }
        catch (ClassNotFoundException e) {
            System.out.println("*** DatabaseOpen.<init>: "+e);
        }
    }

/**
   * Closes the connection to the database.
   *
   * @exception Exception if an error occurs
   */
  public void disconnectFromDatabase() throws Exception {

    if (Debug) {
      System.err.println("Disconnecting from " + url);
    }
    if (con != null) {
      con.close();
      con = null;
      stmt = null;
    }
  }

  /**
   * Returns true if a database connection is active.
   *
   * @return a value of type 'boolean'
   */
  public boolean isConnected() {

    return (con != null);
  }

  /**
   * Executes a SQL query.
   *
   * @param query the SQL query
   * @return true if the query generated results
   * @exception SQLException if an error occurs
   */
  public boolean execute(String query) throws SQLException {

    return stmt.execute(query);
  }


    /**
   * Gets the results generated by a previous query.
   *
   * @return the result set.
   * @exception SQLException if an error occurs
   */

  public ResultSet getResultSet() throws SQLException {

    return stmt.getResultSet();
  }

  /**
   * Checks that a given table exists.
   *
   * @param tableName the name of the table to look for.
   * @return true if the table exists.
   * @exception Exception if an error occurs.
   */
  public boolean tableExists(String tableName) throws Exception {

    if (Debug) {
      System.err.println("Checking if table " + tableName + " exists...");
    }
    DatabaseMetaData dbmd = con.getMetaData();
    ResultSet 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 (Debug) {
      if (tableExists) {
	System.err.println("... " + tableName + " exists");
      } else {
	System.err.println("... " + tableName + " does not exist");
      }
    }
    return tableExists;
  }

  /**
   * 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
   * @exception 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 += '\'' + key[i].toString() + '\'';
	} else {
	  query += key[i].toString();
	}
      }
    }
    boolean retval = false;
    if (stmt.execute(query)) {
      ResultSet rs = stmt.getResultSet();
      int numAttributes = rs.getMetaData().getColumnCount();
      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
   * @exception 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 += '"' + key[i].toString() + '"';
	} else {
	  query += key[i].toString();
	}
      }
    }
    if (!stmt.execute(query)) {
      throw new Exception("Couldn't execute query: " + query);
    }
    ResultSet rs = stmt.getResultSet();
    ResultSetMetaData md = rs.getMetaData();
    int numAttributes = md.getColumnCount();
    if (!rs.next()) {
      throw new Exception("No result for query: " + query);
    }