org.locomotive.util
Class SortableTable

java.lang.Object
  |
  +--org.locomotive.util.SortableTable

public class SortableTable
extends java.lang.Object

SortableTable is a data structure that allows for easy and persistent storage of java.sql.ResultSets. SortableTables, unlike ResultSets, are not tied to any database connections, and so can be maintained without tying up database connections. SortableTables also have the added functionality of sorting by column. Both columns and rows may be added after the object is constructed. However, adding columns is very expensive, and rows which are added can only be added to the end of the structure.


Field Summary
static int ASCENDING
          used to denote an ascending sort
static int DATE
          used to denote a Date comparison for sorting
static int DESCENDING
          used to denote a descending sort
static int NUMBER
          used to denote a number comparison for sorting
static int STRING
          used to denote a String comparison for sorting
 
Constructor Summary
SortableTable()
          Creates a SortableTable.
SortableTable(int rowCapacity, int columnCapacity)
          Creates a SortableTable whose initial size is given by the specified parameters.
SortableTable(int rowCapacity, int columnCapacity, int rowCapacityIncrement, int columnCapacityIncrement)
          creates a sortable table with the specified number of rows and columns.
SortableTable(SortableTable source, int startRow, int endRow)
          Creates a new table out of a subset of rows of another table All the columns of the source table are copied to the destination table.
 
Method Summary
 void changeNullsToString(int column, java.lang.String alt)
          change nulls in a column to an empty string ("")
 int doDelete(java.sql.Connection conn, java.lang.String tableName)
          Deletes all the Rows in the SortableTable.
 int doDelete(java.sql.Connection conn, java.lang.String tableName, java.lang.String[] constraintCols)
          deletes all the rows in the sortableTable from the named table.
 int doInsert(java.sql.Connection conn, java.lang.String tableName)
          Inserts each row of the SortableTable into a table in the database.
 int doUpdate(java.sql.Connection conn, java.lang.String tableName, java.lang.String[] constraintCols)
          Updates a table in the database based on the information in the SortableTable.
 java.util.Enumeration elements()
          Returns an enumeration of all the objects in the table.
 void formatDateColumn(int column, java.lang.String format)
          change an entire column of timestamps to a column of strings containing formatted dates.
 java.lang.Object get(int rowIndex, int columnIndex)
          returns the object stored at the given indexes.
 java.lang.Object get(int rowIndex, java.lang.String columnName)
          returns the object stored at the given indexes.
 int getColumnIndex(java.lang.String name)
          returns the column index denoted by the specified column name.
 java.lang.String getColumnName(int columnIndex)
          returns the name of the columns at the specified column index.
 int getNumColumns()
          returns the number of columns in the table.
 int getNumRows()
          returns the number of rows in the table.
 int getSQLType(int columnIndex)
          returns the column's SQL type.
 void insert(java.lang.Object obj, int rowIndex, int columnIndex)
          inserts an object at the given indexes.
 void insert(java.lang.Object obj, int rowIndex, java.lang.String columnName)
          inserts an object at the given row and column name.
 void load(java.sql.ResultSet rs, boolean setNames)
          loads data into the table from a ResultSet, starting at the first row and first column in the table
 void load(java.sql.ResultSet rs, int startRow, int startColumn, boolean setNames)
          loads data into the table from a ResultSet, beginning at the row whose index is specified, into the columns beginning with and following the columns whose index is specified.
static SortableTable loadFromQuery(java.sql.Connection conn, java.lang.String query)
          loads a query specified by the string into the SortableTable that's returned.
static SortableTable loadQuery(java.sql.Connection conn, java.lang.String query)
          loads a query specified by the string into the SortableTable that's returned.
static java.lang.String printSQLType(int SQLType)
          takes an int from java.sql.Types and returns it's name
 java.lang.Object remove(int rowIndex, int columnIndex)
          removes an object stored at the specified indexes from the table
 java.util.Enumeration rows()
          returns an Enumeration of the rows in the table.
 void setColumnCapacity(int columnCapacity)
          sets the number of columns to the specified size.
 void setColumnName(int columnIndex, java.lang.String name)
          sets the name of the column at the specified column index.
 void setRowCapacity(int rowCapacity)
          sets the number of rows to the specified size.
 void setSQLType(int columnIndex, int SQLType)
          Sets the column SQL Type.
 void sort(int columnIndex, int type, int order)
          sorts the table by the specified column index.
 void sort(java.lang.String columnName, int type, int order)
          sorts the table by the specified column name.
 java.lang.String toHTML()
          returns an HTML representation of the table, for easy data checking of handlers.
 java.lang.String toString()
          Returns a string representation of the table.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 

Field Detail

ASCENDING

public static final int ASCENDING
used to denote an ascending sort

DESCENDING

public static final int DESCENDING
used to denote a descending sort

NUMBER

public static final int NUMBER
used to denote a number comparison for sorting

STRING

public static final int STRING
used to denote a String comparison for sorting

DATE

public static final int DATE
used to denote a Date comparison for sorting
Constructor Detail

SortableTable

public SortableTable()
Creates a SortableTable.
 Defaults: initial row capacity = 10;   initial column capacity = 15
           row capacity increment = 10;   column capacity increment = 15

SortableTable

public SortableTable(int rowCapacity,
                     int columnCapacity)
Creates a SortableTable whose initial size is given by the specified parameters.
 Defaults: row resize increment = 10;   column resize increment = 15
 

SortableTable

public SortableTable(int rowCapacity,
                     int columnCapacity,
                     int rowCapacityIncrement,
                     int columnCapacityIncrement)
creates a sortable table with the specified number of rows and columns.

SortableTable

public SortableTable(SortableTable source,
                     int startRow,
                     int endRow)
Creates a new table out of a subset of rows of another table All the columns of the source table are copied to the destination table. Column indexes are preserved- i.e. column 4 in the source table will also be column 4 in the destination table. The column names of the source table will be preserved also.
Parameters:
source - the table to load the data from
startRow - the row of the source table to begin loading from
endRow - the final row to load into the new table
Method Detail

getNumRows

public int getNumRows()
returns the number of rows in the table.

getNumColumns

public int getNumColumns()
returns the number of columns in the table.

setRowCapacity

public void setRowCapacity(int rowCapacity)
sets the number of rows to the specified size. Note that if the column size is set smaller than the present number of rows, rows will be deleted!

setColumnCapacity

public void setColumnCapacity(int columnCapacity)
sets the number of columns to the specified size. Note that if the column size is set smaller than the present number of rows, rows will be deleted! Will not actually resize the data structure, however. This is a very expensive method, and should be avoided if possible

load

public void load(java.sql.ResultSet rs,
                 int startRow,
                 int startColumn,
                 boolean setNames)
          throws java.sql.SQLException
loads data into the table from a ResultSet, beginning at the row whose index is specified, into the columns beginning with and following the columns whose index is specified.
Parameters:
rs - the ResultSet to load the information from
startRow - the index of the row to begin loading into
startColumn - the index of the column to begin loading into
setNames - whether to set the column names using the ResultSetMetaData. Will also set the SQLType.
Throws:
java.sql.SQLException - if the resultSetMetaData cannot be accessed

load

public void load(java.sql.ResultSet rs,
                 boolean setNames)
          throws java.sql.SQLException
loads data into the table from a ResultSet, starting at the first row and first column in the table
Parameters:
rs - the ResultSet to load the information from
setNames - whether to set the column names using the ResultSetMetaData
Throws:
java.sql.SQLException - if the resultSetMetaData cannot be accessed

insert

public void insert(java.lang.Object obj,
                   int rowIndex,
                   int columnIndex)
inserts an object at the given indexes. The table is resized if the index is greater than the present table size.

insert

public void insert(java.lang.Object obj,
                   int rowIndex,
                   java.lang.String columnName)
            throws java.lang.IllegalArgumentException
inserts an object at the given row and column name. The table is resized if the row index is greater than the present table size.
Throws:
java.lang.IllegalArgumentException - if the column name is not valid

remove

public java.lang.Object remove(int rowIndex,
                               int columnIndex)
removes an object stored at the specified indexes from the table
Returns:
the object being removed. Note that removed objects will not resize the number of rows or columns. the only real way to remove entire rows or columns is to resize the table using setColumnCapacity and setRowCapacity... Sorry!

get

public java.lang.Object get(int rowIndex,
                            int columnIndex)
returns the object stored at the given indexes. Returns null if no is stored at those indexes. Returns null if the indexes are out of bounds

get

public java.lang.Object get(int rowIndex,
                            java.lang.String columnName)
returns the object stored at the given indexes. Returns null if no object is stored at those indexes. Or if the indexes are out of bounds, or if the column name does not exist

setColumnName

public void setColumnName(int columnIndex,
                          java.lang.String name)
sets the name of the column at the specified column index.

getColumnName

public java.lang.String getColumnName(int columnIndex)
returns the name of the columns at the specified column index. Returns null if the name is not set, or if the index is out of the array bounds

getColumnIndex

public int getColumnIndex(java.lang.String name)
returns the column index denoted by the specified column name. Returns -1 if the specified name is not a column name

setSQLType

public void setSQLType(int columnIndex,
                       int SQLType)
Sets the column SQL Type. Useful in case the an element of the column is null but must be reinserted into the database. The actual int value for the SQL type is specified by the XOPEN SQL standard.
Parameters:
columnIndex - the column whose SQL Type is to be set
SQLType - one of the types specified in java.sql.Types
See Also:
java.sql.Types

getSQLType

public int getSQLType(int columnIndex)
returns the column's SQL type. Usefule for inserting nulls into the database using prepared statements. The actual int value for the SQL type is specified by the XOPEN SQL standard.
Parameters:
columnIndex - the column whose type to return.
Returns:
the SQLType, or Types.NULL if it is not set
See Also:
java.sql.Types

sort

public void sort(java.lang.String columnName,
                 int type,
                 int order)
sorts the table by the specified column name. Uses a heap sort. Note that in most cases it will be a lot faster to sort a result set using an 'order by' sql clause in the query that the table is loaded from.
Parameters:
columnName - the column name to sort by
type - the kind of information in the column to sort (Given by the constants NUMBER, DATE, or STRING)
order - the order (given by the constants ASCENDING or DESCENDING) to sort by

sort

public void sort(int columnIndex,
                 int type,
                 int order)
sorts the table by the specified column index. Uses a heap sort. This is slightly faster than the other sorting method. Note that in most cases it will be a lot faster to sort a result set using an 'order by' sql clause in the query that the table is loaded from.
Parameters:
columnIndex - the column index to sort by
type - the kind of information in the column to sort (Given by the constants NUMBER, DATE, or STRING)
order - the order (given by the constants ASCENDING or DESCENDING) to sort by

rows

public java.util.Enumeration rows()
returns an Enumeration of the rows in the table. The objects in the enumeration are simply arrays of objects. Note that nextElement will return null if the next row in the enumeration is null...

elements

public java.util.Enumeration elements()
Returns an enumeration of all the objects in the table. It enumerates row by row- i.e. all the columns of the first row are returned, all the columns of the second row are returned, etc. Note that nextElement will return null if the next element in the table is null....

toString

public java.lang.String toString()
Returns a string representation of the table. All it does is write the row and column indexes of each object and call the objects toString() method, so be careful!
Overrides:
toString in class java.lang.Object

toHTML

public java.lang.String toHTML()
returns an HTML representation of the table, for easy data checking of handlers.

formatDateColumn

public void formatDateColumn(int column,
                             java.lang.String format)
change an entire column of timestamps to a column of strings containing formatted dates. The format is the default given to us by java.text.SimpleDateFormat
Parameters:
table - the table to update
column - the column to update
format - the format string to use
See Also:
SimpleDateFormat

changeNullsToString

public void changeNullsToString(int column,
                                java.lang.String alt)
change nulls in a column to an empty string ("")
Parameters:
table - the table to update
column - the column to update
alt - the string to substitute

loadQuery

public static SortableTable loadQuery(java.sql.Connection conn,
                                      java.lang.String query)
loads a query specified by the string into the SortableTable that's returned. Will return null if there is a SQLException.
See Also:

loadFromQuery

public static SortableTable loadFromQuery(java.sql.Connection conn,
                                          java.lang.String query)
                                   throws java.sql.SQLException
loads a query specified by the string into the SortableTable that's returned.
Throws:
SQLException, - passed up from the load (ResultSet, boolean) method.

doUpdate

public int doUpdate(java.sql.Connection conn,
                    java.lang.String tableName,
                    java.lang.String[] constraintCols)
Updates a table in the database based on the information in the SortableTable. Constructs a SQL statement of the form:
 UPDATE <tableName> 
        SET <column n name> = <column n value> 
        WHERE <constraintcol 0< = <column n value>
and runs it for each row in the sortableTable.
Parameters:
tableName - the name of the table to update
constraintCols - an array containing the names of the columns whose values should be used to constrain each update
Returns:
the number of rows updated, or -1 if there was an error

doInsert

public int doInsert(java.sql.Connection conn,
                    java.lang.String tableName)
Inserts each row of the SortableTable into a table in the database. Constructs a SQL statement of the form:
 INSERT INTO <tableName>
        (<column 1 name>, <column 2 name>, <column n name>)
        VALUES 
        (<column 1 value>, <column 2 value>, <column n value>)
and runs it for each row in the sortableTable.

doDelete

public int doDelete(java.sql.Connection conn,
                    java.lang.String tableName)
Deletes all the Rows in the SortableTable. Uses every row value as a constraints for the delete SQL statement, which is very inefficient. To set the columns to use as constraints explicitly use the other doDelete method.
Parameters:
tableName - the name of the table to update
Returns:
the number of rows deleted, or -1 if there was an error

doDelete

public int doDelete(java.sql.Connection conn,
                    java.lang.String tableName,
                    java.lang.String[] constraintCols)
deletes all the rows in the sortableTable from the named table. Constructs a SQL statement of the form:
 DELETE FROM  <tableName>
        WHERE <constraintCols 0> = <column n value>
          AND <constraintCols 1> = <column n value>
 
where the constraintCol names specify the column values to use as constraints. The statement is run for every row in the table.

Note also that if a row contains any null elements, then the row will not be deleted, because nulls cannot be recognized as valid constraints using the '=' operator in SQL.

Parameters:
tableName - the name of the table to update
constraintCols - an array containing the names of the columns whose values should be used to constrain each update
Returns:
the number of rows deleted, or -1 if there was an error

printSQLType

public static java.lang.String printSQLType(int SQLType)
takes an int from java.sql.Types and returns it's name