edu.upenn.crimson.io
Class Database

java.lang.Object
  edu.upenn.crimson.io.Database

public class Database
extends java.lang.Object

Functions related to opening, closing, and quering a Database.

Version:

$Id: Database.java,v 1.33 2009/07/16 16:19:52 fisher Exp $

Notes:

Should see if can optimize db access by maintaining one Statement that is created every time a database connection is made.

Field Summary

static java.sql.Connection	dbConnection This is a reference to the actual database connection.
static java.lang.String	password Need to store this in the case of using a web proxy.

Constructor Summary

Database()

Method Summary

static void	close() Closes the connection to the database and sets dbConnection to null.
static boolean	commit() This will for a commit.
static void	createTable(java.lang.String table) This will create a table in the current database.
static java.util.ArrayList	dbToArray(java.lang.String sql)
static boolean	delete(java.lang.String table, java.lang.String id) Removes a record from the table.
static boolean	execUpdate(java.lang.String sql) This will execute SQL commands that update the database (ie CREATE, DROP, INSERT, DELETE, etc).
static java.sql.Connection	getConnection() Get the DBConnection.
static java.sql.Statement	getStatement() Get a the database Statement object necessary to perform a database query.
static java.lang.StringBuilder	ImportQuery(java.lang.String sql) This is a specialized version of proxyToArray() to import queries.
static boolean	isOpen() Returns true if a database connection exists or using proxy server and false otherwise.
static boolean	isProxy() This will return true if using a web proxy.
static void	open(java.lang.String password) Opens a connection to the database, using the default username and password.
static void	open(java.lang.String username, java.lang.String password, java.lang.String server, int port, java.lang.String database) Opens a connection to the database.
static java.util.ArrayList	proxyToArray(java.lang.String sql)
static java.lang.String	readClob(java.lang.Object object) This will load the specified CLOB and return the CLOB data as a String.
static java.util.HashSet	readClobSet(java.lang.Object object) This will load the specified CLOB and return the CLOB data as a HashSet.
static java.util.ArrayList	sqlToArray(java.lang.String sql) This will execute SQL queries of the database.
static java.sql.Clob	sqlToClob(java.lang.String sql) This will execute SQL queries of the database.
static java.util.ArrayList	sqlToHash(java.lang.String sql) This will execute SQL queries of the database.
static boolean	tableExists(java.lang.String table) Test if the table exists in the current database.
static void	testConnection() This will perform a simple query of the database to test if the database connection is functional.
static boolean	writeClob(java.lang.String table, java.lang.String id, java.lang.String field, java.lang.String value) This will store 'value' as a CLOB in 'id'.
static boolean	writeClobSet(java.lang.String table, java.lang.String id, java.lang.String field, java.util.HashSet value) This will store 'value' as a CLOB in 'id'.
static boolean	writeDataClob(java.lang.String partitionID, java.lang.String speciesID, java.lang.String field, java.lang.String value) This will store 'value' as a CLOB in the PART_DATA table.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

dbConnection

public static java.sql.Connection dbConnection

This is a reference to the actual database connection.

password

public static java.lang.String password

Need to store this in the case of using a web proxy.

Constructor Detail

Database

public Database()

Method Detail

getConnection

public static java.sql.Connection getConnection()

Get the DBConnection.

getStatement

public static java.sql.Statement getStatement()

Get a the database Statement object necessary to perform a database query. Don't use the default statement because then every time the statement is used, it would over write the previous results.

isOpen

public static boolean isOpen()

Returns true if a database connection exists or using proxy server and false otherwise.

open

public static void open(java.lang.String password)

Opens a connection to the database, using the default username and password.

Notes:

This is a very insecure way of handling the password.

open

public static void open(java.lang.String username,
                        java.lang.String password,
                        java.lang.String server,
                        int port,
                        java.lang.String database)

Opens a connection to the database. The default username and database will be set to the values provided here.

Notes:

This is a very insecure way of handling the password.

close

public static void close()

Closes the connection to the database and sets dbConnection to null. dbSource isn't set to null so that the user can easily reconnect to the existing database if desired.

testConnection

public static void testConnection()

This will perform a simple query of the database to test if the database connection is functional.

isProxy

public static boolean isProxy()

This will return true if using a web proxy.

tableExists

public static boolean tableExists(java.lang.String table)

Test if the table exists in the current database. If no database is open then this will return false.

createTable

public static void createTable(java.lang.String table)

This will create a table in the current database. This only needs to be done once for each database.

delete

public static boolean delete(java.lang.String table,
                             java.lang.String id)

Removes a record from the table. This returns true if it completes without error.

readClob

public static java.lang.String readClob(java.lang.Object object)
                                 throws java.sql.SQLException

This will load the specified CLOB and return the CLOB data as a String. This throws the exception to allow for error handling by the calling method. This allows for inputs that are either String or Clob, to simplify proxy coding which returns a String instead of a Clob.

Throws:

java.sql.SQLException

readClobSet

public static java.util.HashSet readClobSet(java.lang.Object object)
                                     throws java.sql.SQLException

This will load the specified CLOB and return the CLOB data as a HashSet. This allows for inputs that are either String or Clob, to simplify proxy coding which returns a String instead of a Clob.

Throws:

java.sql.SQLException

writeClob

public static boolean writeClob(java.lang.String table,
                                java.lang.String id,
                                java.lang.String field,
                                java.lang.String value)

This will store 'value' as a CLOB in 'id'.

writeClobSet

public static boolean writeClobSet(java.lang.String table,
                                   java.lang.String id,
                                   java.lang.String field,
                                   java.util.HashSet value)

This will store 'value' as a CLOB in 'id'.

writeDataClob

public static boolean writeDataClob(java.lang.String partitionID,
                                    java.lang.String speciesID,
                                    java.lang.String field,
                                    java.lang.String value)

This will store 'value' as a CLOB in the PART_DATA table.

Notes:

The partitionID and speciesID must be upper case. We don't do that here because it's more computationally efficient to make them caps in the routine that is calling this method.

sqlToArray

public static java.util.ArrayList sqlToArray(java.lang.String sql)

This will execute SQL queries of the database. The output will be returned as an ArrayList with each line being a different element in the list. If the query fails, then will return null. If only one column then each array element will be an Object. If more than one column then each array element will be an array of Objects. Note that this will append a ';' to the end of the sql command being submitted to the proxy server.

proxyToArray

public static java.util.ArrayList proxyToArray(java.lang.String sql)

dbToArray

public static java.util.ArrayList dbToArray(java.lang.String sql)

sqlToClob

public static java.sql.Clob sqlToClob(java.lang.String sql)

This will execute SQL queries of the database. The output will be returned as a single Clob. If the query fails, then will return null.

Notes:

This will only work for results with only 1 column and 1 row (ie a single field/column from a single table record).

sqlToHash

public static java.util.ArrayList sqlToHash(java.lang.String sql)

This will execute SQL queries of the database. This can be used, for example, to run Oracle 'SELECT' statements. The output is returned in an ArrayList containing HashMaps, with each map corresponding to one row of data. The keys of the map are the column names.

ImportQuery

public static java.lang.StringBuilder ImportQuery(java.lang.String sql)

This is a specialized version of proxyToArray() to import queries.

execUpdate

public static boolean execUpdate(java.lang.String sql)

This will execute SQL commands that update the database (ie CREATE, DROP, INSERT, DELETE, etc). This will return true if it completes or false on error. COMMIT will automatically be run after the execution of the sql statement.

commit

public static boolean commit()

This will for a commit.