jdbcdatabasecontainer.java

一个java程序编写的svm支持向量机小程序

package edu.udo.cs.mySVMdb.Container;
import java.sql.*;
//import oracle.sql.*;
//import oracle.jdbc.driver.*;
import java.util.Vector;

public class JDBCDatabaseContainer
{
    /**
     * Implementation of a container in a database
     * @author Stefan R黳ing
     * @version 1.0
     */

    protected int dim;
    protected int train_size;
    protected int test_size;
    public double[] Exp;
    public double[] Dev;
    String select_text;

    /** 
     * Name of the examples 
     */
    protected String examples_name;

    /** 
     * Name of the test examples 
     */
    protected String test_examples_name;

    /** 
     * Name of the model
     */
    protected String model_name;

    /** 
     * Name of the parameters
     */
    protected String parameters_name;

    /**
     * Name of the predictions
     */
    protected String predictions_name;

    /**
     * JDBC connection object
     */
    protected Connection conn;    

    /**
     * Database URL
     */
    protected String db_url = "jdbc:oracle:oci8:@some_instance";

    /**
     * Database login
     */
    protected String db_login = "scott";

    /**
     * Database password
     */
    protected String db_password = "tiger";

    /**
     * mapping int -> key
     */
    public String[] keys;

    /**
     * mapping int -> key
     */
    protected String[] test_keys;

    /** 
     * Name of the column containing the examples key.
     */
    protected String key_column;

    /** 
     * Names of the columns containing the examples attributes to be used in SQL query.
     */
    protected String x_columns;
    public String[] x_column;

    /** 
     * Name of the column containing the examples y values.
     */
    protected String y_column;


    /**
     * Entry in y_column that is the positive class
     */
    protected String target_concept;

    /** 
     * Name of the column containing the predicted y values.
     * Usually equals y_column
     */
    protected String pred_column = "Y";

    double[] alphas;
    double[] ys;
    double b;

    /**
     * Prepared Statement for get_example
     */
    PreparedStatement get_example_statement;

    /**
     * Prepared Statement for get_param
     */
    PreparedStatement get_param_statement;

    /**
     * Class constructor with default parameters. Just calls super.
     */
    public JDBCDatabaseContainer(){
    };

    /**
     * Class constructor with parameters. Just calls super.
     * @param params Array of parameters in the form "name:value".
     * @exception Exception on unknown parameter
     */
    public JDBCDatabaseContainer(String[] params)
	throws Exception
    {
	init(params);
    };


    /**
     * Initialize container access
     * @param params Names of the parameter table and connection info
     * @exception Exception on unknown parameter
     */
    public void init(String[] params)
	throws Exception
    {
	// System.out.println("init("+params[0]+") called");
	int pos;
	String param;
	String value;
	String parameter_table=null;
	// get connection info
	if(params != null){
	    for(int i=0;i<params.length;i++){
		if(params[i] != null){
		    // null values being ignored
		    pos = (params[i]).indexOf(":");
		    if(pos >= 0){
			param = ((params[i]).substring(0,pos)).toLowerCase();
			value = (params[i]).substring(pos+1);
			if(param.equals("db_url")){
			    db_url = value;
			}
			else if(param.equals("db_login")){
			    db_login = value;
			}
			else if(param.equals("db_password")){
			    db_password = value;
			}
			else{
			    parameter_table = params[i];
			};
		    }
		    else{
			parameter_table = params[i];
		    };
		};
	    };
	};

	// open connection
	open();

	// read params from parameter_table
	read_params(parameter_table);
    };


    /**
     * Registers the oracle driver and opens the database connection.
     * @exception Exception On database connection error
     */
    protected void open()
	throws Exception
    {
	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
	conn = DriverManager.getConnection(db_url,db_login,db_password);
	conn.setAutoCommit(false);
    };


    /**
     * Reads model from database
     * @exception Exception on SQL error
     */
    public void read_model()
	throws Exception
    {
	read_trainset_meta_info();

	Statement stmt = conn.createStatement();
	ResultSet rset;
	rset = stmt.executeQuery("select count(*) from "+model_name);
	rset.next();
	train_size = rset.getInt(1);
	keys = new String[train_size];
	alphas = new double[train_size];
	rset.close();
	rset = stmt.executeQuery("select key, alpha from "+model_name+" where key is not null");
	int i=0;
	while(rset.next()){
	    keys[i] = rset.getString(1);
	    alphas[i] = rset.getDouble(2);
	    i++;
	};
	rset.close();
	rset = stmt.executeQuery("select alpha from "+model_name+" where key is null");
	if(rset.next()){
	    b = rset.getDouble(1);
	}
	else{
	    // ???
	    b = 0;
	};

	get_example_statement = conn.prepareStatement("select "+x_columns+" from "+examples_name+" where "+key_column+" = ?");

	rset.close();
	stmt.close();
    };


    /**
     * Generates and writes prediction of examples in table test_examples_name
     * @exception Exception on SQL error
     */
    public void write_prediction()
	throws Exception
    {
	// prediction can go into new table or row of test_examples_name


	// check if table or row exist


	// create prepared statement to read

	// cread batch statement to write


	// predict


	//	stmt.close();
    };


    /**
     * Writes model to database
     * @exception Exception on SQL error
     */
    public void write_model()
	throws Exception
    {
	Statement delstmt = conn.createStatement();
	// drop temporary table and create persistent table
	delstmt.executeQuery("drop table "+model_name);
	delstmt.executeQuery("create table "+model_name+" (key varchar(60), alpha number)");
	//delstmt.close();
	PreparedStatement stmt = conn.prepareStatement("insert into "+model_name+" values (?,?)");
	stmt.clearBatch();
	int i;
	for(i=0;i<train_size;i++){
	    if(alphas[i] != 0.0){
		stmt.setString(1,keys[i]);
		stmt.setDouble(2,alphas[i]);
		stmt.addBatch();
	    };
	};
	stmt.setNull(1,java.sql.Types.VARCHAR);
	stmt.setDouble(2,b);
	stmt.addBatch();
	stmt.executeBatch();

	// create view for prediction
	String viewname = get_param("view_name");
	if(viewname != null){
	    try{
		delstmt.executeQuery(
		   "create view "+viewname+" as select x."+key_column
		   +" as key, b.alpha + (select sum(z.alpha * ("+select_text
		   +")) from "+model_name+" z, "+examples_name
		   +" y where z.key = y."+key_column+") as svm_pred from "
		   +model_name+" b, "+examples_name+" x where b.key is null");
	    }
	    catch(Exception e){
		System.out.println("ERROR: could not create view "+viewname+", error message: "+e.getMessage());
	    };
	};
	stmt.close();
	delstmt.close();
    };


    /**
     * Writes all cached data to database and closes the database connection.
     * @exception Exception on SQL error
     */
    public void close()
	throws Exception
    {
	if(get_example_statement != null){
	    get_example_statement.close();
	};
	if(get_param_statement != null){
	    get_param_statement.close();
	};
	conn.commit();
	conn.close();
    };


    /**
     * Read parameters from given location.
     * @exception Exception if an error opening the container occurred
     */
    public void read_params(String location)
	throws Exception
    {
	// no reading actually done
	parameters_name = location;

	get_param_statement = conn.prepareStatement("select value from " + parameters_name + " where parameter = ?");

	// dummy call to check if table exists
	get_param("model_name");
    };


    /**
     * Prepare data structures for learning
     * @exception Exception if an error occured
     */
    public void init_for_learning()
	throws Exception
    {
	// model table must exist
	model_name = get_param("model_name");
	if(model_name == null){
	    model_name = "svm_model";
	};
	Statement stmt = conn.createStatement();
	try{
	    // test if table exists
	    stmt.executeQuery("select count(*) from "+model_name);
	}
	catch(SQLException e){
	    // table does not exist, create one
	    stmt.clearBatch();
	    stmt.addBatch("create global temporary table "+model_name+" (key varchar(60), alpha number)");
	    // stmt.addBatch("create table "+model_name+" (key varchar(60), alpha number)");
	    //	    stmt.addBatch("create unique index "+model_name+"_key_index on "+model_name+"(key)");
	    stmt.executeBatch();
	};
	stmt.close();

	// read examples
	examples_name = get_param("trainset");
	read_trainset();
    };


    public void read_trainset_meta_info()
	throws Exception
    {
	// read meta information
	Statement stmt = conn.createStatement();
	ResultSet rset;
	int i;

	key_column = get_param("key_column");
	if(key_column == null){
	    key_column = "ROWID";
	};
	y_column = get_param("y_column");
	if(y_column == null){
	    y_column = "Y";
	};

	rset = stmt.executeQuery("select count(*) from "+parameters_name+" where parameter = 'x_column'");
	rset.next();
	dim = rset.getInt(1);
	rset.close();
	if(dim > 0){
	    // read x_columns from parameters
	    x_columns = "";
	    x_column = new String[dim];
	    rset = stmt.executeQuery("select value from "+parameters_name+" where parameter = 'x_column'");
	    i=0;
	    while(rset.next()){
		x_column[i] = rset.getString(1);
		x_columns += x_column[i] + ",";
		i++;
	    };
	    rset.close();
	    x_columns = x_columns = x_columns.substring(0,x_columns.length()-1);
	}
	else{
	    // read x_columns from data table
	    rset = stmt.executeQuery("select * from "+examples_name);
	    ResultSetMetaData meta = rset.getMetaData();
	    int mydim = meta.getColumnCount();
	    if(mydim <= 0){
		throw(new Exception("No columns found in "+examples_name));
	    };
	    String name;
	    
	    // read x metadata
	    x_columns = "";
	    x_column = new String[mydim];
	    dim = 0;  // mydim can contain y and id
	    for(i=1;i<=mydim;i++){
		name = meta.getColumnName(i);
		if((! name.equals(y_column)) 
		   && (! name.equals(key_column))
		   // JDBC types that can be castet to a double:
		   && ((meta.getColumnType(i) == java.sql.Types.TINYINT)
		       || (meta.getColumnType(i) == java.sql.Types.SMALLINT)
		       || (meta.getColumnType(i) == java.sql.Types.INTEGER)
		       || (meta.getColumnType(i) == java.sql.Types.BIGINT)
		       || (meta.getColumnType(i) == java.sql.Types.REAL)
		       || (meta.getColumnType(i) == java.sql.Types.FLOAT)
		       || (meta.getColumnType(i) == java.sql.Types.DOUBLE)
		       || (meta.getColumnType(i) == java.sql.Types.DECIMAL)
		       || (meta.getColumnType(i) == java.sql.Types.NUMERIC)
		       || (meta.getColumnType(i) == java.sql.Types.BIT))
		   ){
		    x_columns += name + ",";
		    x_column[dim] = name;
		    dim++;
		};
	    };
	    if(dim == 0){
		throw(new Exception("No x-columns found in "+examples_name));
	    };
	    if(dim != mydim){
		String[] new_x_column = new String[dim];
		for(i=0;i<dim;i++){
		    new_x_column[i] = x_column[i];
		};
		x_column = new_x_column;
	    };
	    x_columns = x_columns.substring(0,x_columns.length()-1);
	    rset.close();
	};

	// scaling
	boolean do_scale_x = true;
	boolean do_scale_y = true;
	if(get_param("svm_type") != null){
	    if(get_param("svm_type").equals("pattern")){
		do_scale_y = false;
	    };
	};
	if(get_param("scale") != null ){ 
	    if(get_param("scale").equals("x")){
		do_scale_x = true;
		do_scale_y = false;
	    }
	    else if(get_param("scale").equals("xy")){
		do_scale_x = true;
		do_scale_y = true;
	    };
	};
	if(do_scale_x || do_scale_y){
	    Exp = new double[dim+1];
	    Dev = new double[dim+1];
	    String the_query = "select ";
	    for(i=0;i<dim;i++){
		the_query += "AVG("+x_column[i]+"), STDDEV("+x_column[i]+"), ";
	    };
	    the_query += "AVG("+y_column+"), STDDEV("+y_column+")";
	    the_query += "from "+examples_name;
	    rset = stmt.executeQuery(the_query);
	    rset.next();
	    for(i=0;i<=dim;i++){
		Exp[i] = rset.getDouble(2*i+1);
		Dev[i] = rset.getDouble(2*i+2);
	    };
	    if(! do_scale_y){
		Exp[dim] = 0;
		Dev[dim] = 1; 
	    };
	    rset.close();
	}
	else{
	    Exp = null;
	    Dev = null;
	};
	stmt.close();
    };


    /**
     * Read trainset from given location.
     * @exception Exception if an error opening the container occurred
     */
    public void read_trainset()
	throws Exception
    {