databasehandler.java

著名的开源仿真软件yale

/*
 *  YALE - Yet Another Learning Environment
 *  Copyright (C) 2002, 2003
 *      Simon Fischer, Ralf Klinkenberg, Ingo Mierswa, 
 *          Katharina Morik, Oliver Ritthoff
 *      Artificial Intelligence Unit
 *      Computer Science Department
 *      University of Dortmund
 *      44221 Dortmund,  Germany
 *  email: [e-mail]
 *  web:   http://yale.cs.uni-dortmund.de/
 *
 *  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., 59 Temple Place, Suite 330, Boston, MA 02111-1307
 *  USA.
 */
package edu.udo.cs.yale.tools;

import edu.udo.cs.yale.example.Attribute;

import java.sql.*;
import java.io.*;
import java.util.*;

/**
 *  This class hides the database. Using {@link DatabaseHandler#connect(String,String,boolean)}, 
 *  you can extablish a connection to the database. Once connected, queries and
 *  updates are possible.
 *
 *  @version $Id: DatabaseHandler.java,v 2.12 2003/09/10 13:02:07 fischer Exp $
 */
public class DatabaseHandler {

    private String databaseName;
    private String databaseURL;
    private String driver;
    private Connection myCon;

    /**
     * Additional constructor. Here you can set two more parameters that will
     * be set to default values if you don't use this constructor. You should
     * only make use of this constructor if you know exactly what you are doing.
     *
     * @param dbname Name of the database to be queried.
     * @param urlPrefix Prefix of the database's URL. (?)
     * @param driverClass Name of the Java Oracle Driver. (?)
     */
    public DatabaseHandler(String dbname, String urlPrefix, String driverClass) {
        databaseName = dbname;
        databaseURL = urlPrefix + databaseName;
        driver = driverClass;
        myCon = null;
    }

    public String getDatabaseName() {
	return databaseName;
    }

    /**
     * Establishes a connection to the database. Afterwards, queries and updates
     * can be executed using the methods this class provides.
     *
     * @param username Name with which to log in to the database.
     * @param passwd Password with which to log in to the database.
     * @param autoCommit If TRUE, all changes to the database will be committed
     *        automatically. If FALSE, the commit()-Method has to be called to
     *        make changes permanent.
     */
    public void connect(String username, String passwd, boolean autoCommit)
	throws SQLException {
        if (myCon != null) {
	    throw new SQLException("connect: Connection to database '"+databaseName+"' already exists!"); 
	}

        try {
	    Class.forName(driver);  
	} catch (ClassNotFoundException cnfe) { 
	    throw new SQLException("connect: Could not find Oracle Driver Class '" + driver + "'!"); 
	}

        myCon = DriverManager.getConnection(databaseURL, username, passwd);
	myCon.setAutoCommit(autoCommit);
    }

    /** Closes the connection to the database. */
    public void disconnect() throws SQLException {
        if (myCon == null) {
	    throw new SQLException("Disconnect: was not connected.");  
	}

        myCon.close();
        myCon = null;
    }

    /**
     * Executes the given SQL-Query. Only SQL-statements starting with "SELECT"
     * (disregarding case) will be accepted. Any errors will result in an
     * SQL-Exception being thrown.
     *
     * @param sqlQuery An SQL-String.
     * @return A ResultSet-Object with the results of the query. The ResultSet
     *          is scrollable, but not not updatable. It will not show changes
     *          to the database made by others after this ResultSet was obtained.
     */
    public ResultSet query(String sqlQuery) throws SQLException {
        if (myCon == null) {
	    throw new SQLException("Could not query database '" +databaseName+ "': not connected.");  
	}
        if (!sqlQuery.toLowerCase().startsWith("select")) {
	    throw new SQLException("Query: Only SQL-Statements starting with SELECT are allowed: "+sqlQuery);  
	}

        Statement st = myCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	ResultSet rs = st.executeQuery(sqlQuery);
        return rs;
    } 

    /** Adds a column for the given attribute to the table with name tableName. */
    public void addColumn(Attribute attribute, String tableName) throws SQLException {
        Statement st = myCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
	String query = 
	    "alter table " + tableName + " add \"" + 
	    getDatabaseName(attribute) + "\" " + 
	    (attribute.isNominal() ? "varchar(256)" : "real");
        st.execute(query);
    }

    /** Removes the column of the given attribute from the table with name tableName. */
    public void removeColumn(Attribute attribute, String tableName) throws SQLException {
        Statement st = myCon.createStatement();
	String query = 
	    "alter table " + tableName + " drop column \"" + 
	    getDatabaseName(attribute) + "\"";
        st.execute(query);
    }

    /**
     * Counts the number of records in the given result set.
     *
     * @param rs The ResultSet.
     */
    public int countRecords(ResultSet rs) throws SQLException {
        int numberOfRows = 0;
        LogService.logMessage("Counting records...", LogService.MINIMUM);

        // move the cursor in the ResultSet to the beginning:
        rs.beforeFirst();

        while (rs.next()) {
            numberOfRows++;
        }
        rs.beforeFirst();

	LogService.logMessage("...counted "  + numberOfRows + " records.", LogService.MINIMUM);  
	return numberOfRows;
    }

    /**
     * Makes all changes to the database permanent.
     */
    public void commit() throws SQLException {
        if ((myCon == null) || myCon.isClosed()) {
	    throw new SQLException("Could not commit: no open connection to database '" + databaseName + "' !");  
	}
        myCon.commit();
    }

    /** Returns for the given SQL-type the name of the corresponding Yale-Type
     *  from edu.udo.cs.yale.tools.Ontology. */
    public static int getYaleTypeIndex(int sqlType) {
	switch (sqlType) {
	case Types.BIGINT:
	case Types.INTEGER:
	case Types.TINYINT:
	case Types.SMALLINT:
	    return Ontology.INTEGER;  

	case Types.FLOAT:
	case Types.REAL:
	case Types.DECIMAL:
	case Types.DOUBLE:
	    return Ontology.REAL;   
	
	case Types.NUMERIC:
	    return Ontology.NUMERICAL;   
	    
        case Types.CHAR:
	case Types.VARCHAR:
	case Types.CLOB:
	case Types.BINARY:
	case Types.BIT:
	case Types.LONGVARBINARY:
	case Types.BLOB:
	case Types.JAVA_OBJECT:
	case Types.STRUCT:
	case Types.VARBINARY:
	case Types.LONGVARCHAR:
	    return Ontology.NOMINAL;   
	
        case Types.DATE:
	case Types.TIME:
	case Types.TIMESTAMP:
	    return Ontology.ORDERED;   

	default:
	    return Ontology.NOMINAL;
	}
    }


    /** Creates a list of attributes reflecting the result set's column meta data. */
    public static List createAttributes(ResultSet rs) throws SQLException {
	List attributes = new LinkedList();

        if (rs == null) { 
	    throw new IllegalArgumentException("Cannot create attributes: ResultSet must not be null!");  
	}
        
        ResultSetMetaData metadata;
        try {
	    metadata = rs.getMetaData();  
	} catch (NullPointerException npe) {
	    throw new RuntimeException("Could not create attribute list: ResultSet object seems closed."); 
	}

	int numberOfColumns = metadata.getColumnCount();
	
	for (int column = 1; column <= numberOfColumns; column++) {
	    String name = metadata.getColumnName(column);
	    Attribute attribute = new Attribute(name,
						getYaleTypeIndex(metadata.getColumnType(column)),
						Ontology.SINGLE_VALUE,
						column,
						null);
	    attributes.add(attribute);
	}
	return attributes;
    }

    public static String getDatabaseName(Attribute attribute) {
	String name = attribute.getName();
//	name = name.toUpperCase();
//  	name = name.replace('(', '_');
//  	name = name.replace(')', '_');
	return name;
    }

}