org.safs

Class DatabaseUtils

java.lang.Object

org.safs.DatabaseUtils

public class DatabaseUtils
extends java.lang.Object

Purpose: Utilities to find a database connection using java.sql.DriverManager; can also getValues.

Since:

Oct 23, 2003
Oct 23, 2003 (DBauman) Original Release

Author:

Doug Bauman

Field Summary

Fields

Modifier and Type	Field and Description
static java.lang.String	DEFAULT_DRIVER Holds the default JDBC driver.
static java.lang.String	ORACLE_DRIVER

Constructor Summary

Constructors

Constructor and Description
DatabaseUtils()

Method Summary

Modifier and Type	Method and Description
static void	closeAll(java.sql.ResultSet rs, java.sql.Statement st, java.sql.Connection conn) Convenience method for closing sql objects.
static int	execDeleteStatement(java.sql.Connection con, java.lang.String table, java.lang.String whereCl) Purpose: execute delete statement, can be used to do update or insert Assumptions: Note: this version does not take into account special characters.
static int	execStatement(java.sql.Connection con, java.lang.String strStatement) Purpose: execute statement, can be used to do update or insert Assumptions: Note: this version does not take into account special characters.
static java.sql.Connection	getConnection(java.lang.String jdbcUrl, java.lang.String username, java.lang.String password) Opens a connection to the JDBC database indicated by the 'dbName' parameter.
static java.lang.String	getDBVal(java.lang.Object m) Purpose: getDBVal as a string if the type is "java.lang.Double", "java.sql.Timestamp" or "java.sql.Date" the we remove trailing .0 if exists.
static int	getNumColumns(java.sql.Connection con, java.lang.String strStatement, java.lang.String table, java.lang.String field, java.lang.String attr, java.lang.Object value, java.util.ArrayList columns) Purpose: simple get(all), as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table"; in order to get number of columns, and/or to get the names of the columns in parameter 'columns' Assumptions: Note: this version does not take into account special characters.
static int	getNumRows(java.sql.Connection con, java.lang.String table, java.lang.String wherePart) Purpose: get number of rows
static java.sql.ResultSet	getResultSet(java.sql.Connection con, java.lang.String strStatement, java.lang.String table, java.lang.String field, java.lang.String attr, java.lang.Object value, java.sql.Statement[] st) Purpose: simple query, as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table" Assumptions: Note: this version does not take into account special characters.
static java.util.List	getRow(java.sql.ResultSet rs) Purpose: get the next row from the ResultSet as a Collection Assumptions: that rs.next() was already called and is true.
static java.util.List	getRow(java.sql.ResultSet rs, java.sql.ResultSetMetaData meta, int numberOfColumns, java.lang.Integer maxColCount) Purpose: get the next row from the ResultSet as a Collection Assumptions: that rs.next() was already called and is true
static java.util.List	getValue(java.sql.Connection con, java.lang.String strStatement, java.lang.String table, java.lang.String field, java.lang.String attr, java.lang.Object value, java.util.ArrayList columns) Purpose: simple get, as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table" Assumptions: Note: this version does not take into account special characters.
static java.util.List	getValues(java.sql.Connection con, java.lang.String strStatement, java.lang.String table, java.lang.String field, java.lang.String attr, java.lang.Object value, java.util.ArrayList columns, java.lang.Integer maxRowCount, java.lang.Integer maxColCount) Purpose: simple get(all), as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table" Assumptions: Note: this version does not take into account special characters.
static java.lang.Class	loadDriver(java.lang.String driver) load the driver, if 'driver' is null then uses DEFAULT_DRIVER
static void	main(java.lang.String[] arg) test main method.
static void	printHeader(java.lang.StringBuffer buf, java.lang.String table, java.lang.String col) Purpose: print the header
static void	printValues(java.lang.StringBuffer buf, java.util.List list, java.util.ArrayList columns) Purpose: print the values of 'list' to 'buf'
protected static java.lang.String	setupSelect(java.lang.StringBuffer buf, java.lang.String table, java.lang.String field, java.lang.String attr, java.lang.Object value) Purpose: setup select statement
static void	writefile(java.lang.String filename, java.util.Collection list, java.lang.String delim) write to file 'filename' the toString() values contained in list.

Methods inherited from class java.lang.Object

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

Field Detail

DEFAULT_DRIVER

public static final java.lang.String DEFAULT_DRIVER

Holds the default JDBC driver. Value is "sun.jdbc.odbc.JdbcOdbcDriver".

See Also:

Constant Field Values

ORACLE_DRIVER

public static final java.lang.String ORACLE_DRIVER

See Also:

Constant Field Values

Constructor Detail

DatabaseUtils

public DatabaseUtils()

Method Detail

loadDriver

public static java.lang.Class loadDriver(java.lang.String driver)
                                  throws java.sql.SQLException

load the driver, if 'driver' is null then uses DEFAULT_DRIVER

Parameters:

driver, - String

Returns:

Class, the class loaded with Class.forName

Throws:

java.sql.SQLException

getConnection

public static java.sql.Connection getConnection(java.lang.String jdbcUrl,
                                                java.lang.String username,
                                                java.lang.String password)
                                         throws java.sql.SQLException

Opens a connection to the JDBC database indicated by the 'dbName' parameter.

Parameters:

jdbcUrl - The database url

username - String, if null then uses the 1-parameter version of DriverManager.getConnection

password - String

Returns:

The connection.

Throws:

java.sql.SQLException - if the connection fails.

setupSelect

protected static java.lang.String setupSelect(java.lang.StringBuffer buf,
                                              java.lang.String table,
                                              java.lang.String field,
                                              java.lang.String attr,
                                              java.lang.Object value)

Purpose: setup select statement

Parameters:

buf, - StringBuffer

table, - String name of the table

field, - String the select field name

attr, - String the attr part for the where, if null then no where

value, - if attr!=null, then the value part of where, if null, then the where part will be "attr NOT NULL"

Returns:

String, the statement

getResultSet

public static java.sql.ResultSet getResultSet(java.sql.Connection con,
                                              java.lang.String strStatement,
                                              java.lang.String table,
                                              java.lang.String field,
                                              java.lang.String attr,
                                              java.lang.Object value,
                                              java.sql.Statement[] st)
                                       throws java.sql.SQLException

Purpose: simple query, as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table"
Assumptions: Note: this version does not take into account special characters. You need a more sophisticated mechanism to take those into account.

Parameters:

con, - Connection

strStatement, - String the statement, if null, then construct from the other parameters

table, - String name of the table

field, - String the select field name

attr, - String the attr part for the where, if null then no where

value, - if attr!=null, then the value part of where, if null, then the where part will be "attr NOT NULL"

st, - Statement[], pointer to a return'ed Statement

Returns:

ResultSet from the executeQuery on the statment

Throws:

java.sql.SQLException

execStatement

public static int execStatement(java.sql.Connection con,
                                java.lang.String strStatement)
                         throws java.sql.SQLException

Purpose: execute statement, can be used to do update or insert
Assumptions: Note: this version does not take into account special characters. You need a more sophisticated mechanism to take those into account.

Parameters:

con - Connection

strStatement - String the statement

Returns:

either the row count for INSERT, UPDATE or DELETE statements; or 0 for SQL statements that return nothing

Throws:

java.sql.SQLException

execDeleteStatement

public static int execDeleteStatement(java.sql.Connection con,
                                      java.lang.String table,
                                      java.lang.String whereCl)
                               throws java.sql.SQLException

Purpose: execute delete statement, can be used to do update or insert
Assumptions: Note: this version does not take into account special characters. You need a more sophisticated mechanism to take those into account.

Parameters:

con, - Connection

table, - String the table name

whereCl, - String the where clause

Returns:

either the row count for INSERT, UPDATE or DELETE statements; or 0 for SQL statements that return nothing

Throws:

java.sql.SQLException

getValue

public static java.util.List getValue(java.sql.Connection con,
                                      java.lang.String strStatement,
                                      java.lang.String table,
                                      java.lang.String field,
                                      java.lang.String attr,
                                      java.lang.Object value,
                                      java.util.ArrayList columns)
                               throws java.sql.SQLException

Purpose: simple get, as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table"
Assumptions: Note: this version does not take into account special characters. You need a more sophisticated mechanism to take those into account.

Parameters:

con, - Connection

strStatement, - String the statement, if null, then construct from the other parameters

table, - String name of the table

field, - String the select field name

attr, - String the attr part for the where, if null then no where

value, - if attr!=null, then the value part of where, if null, then the where part will be "attr NOT NULL"

columns, - if not null, then we add the columns to this list

Returns:

a 'List' which is represents the first row; if no rows, then null

Throws:

java.sql.SQLException

getValues

public static java.util.List getValues(java.sql.Connection con,
                                       java.lang.String strStatement,
                                       java.lang.String table,
                                       java.lang.String field,
                                       java.lang.String attr,
                                       java.lang.Object value,
                                       java.util.ArrayList columns,
                                       java.lang.Integer maxRowCount,
                                       java.lang.Integer maxColCount)
                                throws java.sql.SQLException

Purpose: simple get(all), as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table"
Assumptions: Note: this version does not take into account special characters. You need a more sophisticated mechanism to take those into account.

Parameters:

con, - Connection

strStatement, - String the statement, if null, then construct from the other parameters

table, - String name of the table

field, - String the select field name

attr, - String the attr part for the where, if null then no where

value, - if attr!=null, then the value part of where, if null, then the where part will be "attr NOT NULL"

columns, - if not null, then we add the columns to this list

maxRowCount, - if not null, then don't return more rows then specified

maxColCount, - if not null, then don't return more cols then specified

Returns:

List of 'List' which is a list of rows in the database, the rows are items for each column, as they are encountered

Throws:

java.sql.SQLException

getNumColumns

public static int getNumColumns(java.sql.Connection con,
                                java.lang.String strStatement,
                                java.lang.String table,
                                java.lang.String field,
                                java.lang.String attr,
                                java.lang.Object value,
                                java.util.ArrayList columns)
                         throws java.sql.SQLException

Purpose: simple get(all), as in: "SELECT field FROM table WHERE attr = 'value'" or if attr is null then simply "SELECT field FROM table"; in order to get number of columns, and/or to get the names of the columns in parameter 'columns'
Assumptions: Note: this version does not take into account special characters. You need a more sophisticated mechanism to take those into account.

Parameters:

con, - Connection

strStatement, - String the statement, if null, then construct from the other parameters

table, - String name of the table

field, - String the select field name

attr, - String the attr part for the where, if null then no where

value, - if attr!=null, then the value part of where, if null, then the where part will be "attr NOT NULL"

columns, - if not null, then we add the columns to this list

Returns:

int, number of columns

Throws:

java.sql.SQLException

getNumRows

public static int getNumRows(java.sql.Connection con,
                             java.lang.String table,
                             java.lang.String wherePart)
                      throws java.sql.SQLException

Purpose: get number of rows

Parameters:

con - Connection

table - String name of the table

wherePart - String the where part

Returns:

int, number of rows

Throws:

java.sql.SQLException

getRow

public static java.util.List getRow(java.sql.ResultSet rs)
                             throws java.sql.SQLException

Purpose: get the next row from the ResultSet as a Collection
Assumptions: that rs.next() was already called and is true. This version calls the getRow with the metadata and the column count

Parameters:

rs, - ResultSet

Returns:

List of items in the row, if rs.next() is null, then null

Throws:

java.sql.SQLException

getRow

public static java.util.List getRow(java.sql.ResultSet rs,
                                    java.sql.ResultSetMetaData meta,
                                    int numberOfColumns,
                                    java.lang.Integer maxColCount)
                             throws java.sql.SQLException

Purpose: get the next row from the ResultSet as a Collection
Assumptions: that rs.next() was already called and is true

Parameters:

rs, - ResultSet

meta, - ResultSetMetaData (obtained externally via rs.getMetaData())

numberOfColumns, - int (obtained externally via meta.getColumnCount())

maxColCount, - Integer

Returns:

List of items in the row, as they are encountered

Throws:

java.sql.SQLException

closeAll

public static void closeAll(java.sql.ResultSet rs,
                            java.sql.Statement st,
                            java.sql.Connection conn)
                     throws java.sql.SQLException

Convenience method for closing sql objects. Closing is tried on all objects, independent of errors/exceptions in previous closes.

Parameters:

rs - ResultSet to be closed

st - Statement (or subclass) to be closed

conn - Connection to be closed

Throws:

java.sql.SQLException - when any problem occurs while closing objects

main

public static void main(java.lang.String[] arg)

test main method. reads username and password as first two lines in c:\test.dat the remaining lines in the file are optional and include:
jdbcUrl,
tableName (default is
col (the select column, default is *)
dbName

printValues

public static void printValues(java.lang.StringBuffer buf,
                               java.util.List list,
                               java.util.ArrayList columns)

Purpose: print the values of 'list' to 'buf'

Parameters:

buf, - StringBuffer

list, - List

columns, - ArrayList

printHeader

public static void printHeader(java.lang.StringBuffer buf,
                               java.lang.String table,
                               java.lang.String col)

Purpose: print the header

Parameters:

buf, - StringBuffer

table, - String

col, - String

writefile

public static void writefile(java.lang.String filename,
                             java.util.Collection list,
                             java.lang.String delim)
                      throws java.io.IOException

write to file 'filename' the toString() values contained in list. Each item is written as a separate line using '\n' as the line separator regardless of operating system. Values are written in the system default character encoding.

If an item in the list is another Collection, then special handling is performed via the getDBVal function to potentially filter the value to be written. The items in this secondary Collection are treated as delimited fields and will be separated in the output by the field delimiter provided.

Parameters:

filename - String full absolute path filename of file to write.

list - Collection of lines to write.

delim - String field separator to use on values stored in Collections in the list.

Throws:

java.io.FileNotFoundException - if file cannot be created for any reason.

java.io.IOException - if an error occurs during write operations.

See Also:

FileUtilities.getSystemBufferedFileWriter(String), getDBVal(Object)

getDBVal

public static java.lang.String getDBVal(java.lang.Object m)

Purpose: getDBVal as a string if the type is "java.lang.Double", "java.sql.Timestamp" or "java.sql.Date" the we remove trailing .0 if exists.

Parameters:

m, - Object

Returns:

String, if m was null then ""