com.softtech.jdbc
Class SQLResults

java.lang.Object
  com.softtech.jdbc.SQLResults

public class SQLResults
extends java.lang.Object

SQLResults stores the results of a sql query in the form of objects stored in an Arraylist container object. It contains handy methods like getLong(), getDate(), getString(), etc. for accessing fields in a SQLResults (result set).

Here is a complete code example:

   driverName = "oracle.jdbc.driver.OracleDriver";
   connURL = "jdbc:oracle:thin:@SNOWMASS:1521:WDEV";
   username = "wdevprja";
   password = "password";
   ConnectionPool conPool = new ConnectionPool(int numPooledCon,
                                               String driverName,
                                               String conURL,
                                               String username,
                                               String password);

   SQLExecutor sqlExec = new SQLExecutor(conPool);
   SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM INV");
   String out = "";
   for (int row=0; row < res.getRowCount(); row++)
      out += res.getLong(row, "TEST_ID") + " " + res.getString(row, "NOTES") + " " +
             res.getDate(row, "TEST_DT") + " " + res.getDouble(row, "AMOUNT") + " " +
             res.getString(row, "CODE") + "\n";
   System.out.println(out);
 

There is also a handy toString() method which can be used to generate a text table of the entire contents of the SQLResults result set. Here is a code example:

   System.out.println(res.toString());  //output results as a text table
 

For a simple employee table, the output of this toString() method will look something like:

 EMPID       FNAME       LNAME
 ----------------------------------
 1.0         mark        cook
 2.0         carlos      moya
 3.0         Jeff        Smith
 

Author:

Jeff S Smith

Field Summary

private int	columnCount number of columns returned by the sql query
private java.util.ArrayList	columnNames ArrayList containing the column names returned by the sql query
private int	dbType database type (e.g.
private java.util.ArrayList	results ArrayList containing the results of the sql query
private int	toStringFormatWidth formatted width of each field included in toString()

Constructor Summary

SQLResults(int dbType, int columnCount)
Constructor creates ArrayList objects and initializes the dbType and columnCount

Method Summary

boolean	add(java.lang.Object o) Adds an object (a field value from a SELECT) to the sql results container)
void	addColumnName(java.lang.String columnName) Adds a column name to the columnNames list
private java.lang.String	formatWithSpaces(java.lang.String s) Formats a string by adding spaces on the end if the string is shorter than toStringFormatWidth, or truncates string if it is too long.
boolean	getBoolean(int row, int col) Gets the value of the field corresponding to (row, col) as a boolean
boolean	getBoolean(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a boolean
int	getColumnCount() Getter for number of columns
private int	getColumnIndex(java.lang.String columnName) Given a columnName, return the corresponding index
java.util.ArrayList	getColumnNames() Getter for SQL column names
java.sql.Date	getDate(int row, int col) Gets the value of the field corresponding to (row, col) as a java.sql.Date
java.sql.Date	getDate(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a java.sql.Date
double	getDouble(int row, int col) Gets the value of the field corresponding to (row, col) as a double
double	getDouble(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a double
float	getFloat(int row, int col) Gets the value of the field corresponding to (row, col) as a float
float	getFloat(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a float
int	getInt(int row, int col) Gets the value of the field corresponding to (row, col) index as an int
int	getInt(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as an int
long	getLong(int row, int col) Gets the value of the field corresponding to row, col as a long
long	getLong(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a long
java.lang.Object	getObject(int row, int col) GetObject returns an object in the ArrayList results given a row and column index
java.util.ArrayList	getResults() Getter for the entire SQL results object
int	getRowCount() Gets the number of rows returned by query
java.lang.String	getString(int row, int col) Gets the value of the field corresponding to (row, col) index as a String
java.lang.String	getString(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a String
java.sql.Time	getTime(int row, int col) Gets the value of the field corresponding to (row, col) as a java.sql.Time
java.sql.Time	getTime(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a java.sql.Time
java.sql.Timestamp	getTimestamp(int row, int col) Gets the value of the field corresponding to (row, col) as a java.sql.Timestamp
java.sql.Timestamp	getTimestamp(int row, java.lang.String columnName) Gets the value of the field corresponding to row, columnName as a java.sql.Timestamp
boolean	isNull(int row, int col) Determines if field given by (row,col) is null
boolean	isNull(int row, java.lang.String columnName) Determines if field given by (row, columnName) is null
void	setToStringFormatWidth(int toStringFormatWidth) Setter for string format width (used in toString() method)
java.util.List	toList() Returns the SQLResults in the form of a List
java.lang.String	toString() Returns the contents of SQLResults in a text table.

Methods inherited from class java.lang.Object

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

Field Detail

results

private java.util.ArrayList results

ArrayList containing the results of the sql query

columnNames

private java.util.ArrayList columnNames

ArrayList containing the column names returned by the sql query

columnCount

private int columnCount

number of columns returned by the sql query

dbType

private int dbType

database type (e.g. DatabaseType.ORACLE), needed for exception handling

toStringFormatWidth

private int toStringFormatWidth

formatted width of each field included in toString()

Constructor Detail

SQLResults

public SQLResults(int dbType,
                  int columnCount)

Constructor creates ArrayList objects and initializes the dbType and columnCount

Parameters:

dbType -

columnCount -

Method Detail

getResults

public java.util.ArrayList getResults()

Getter for the entire SQL results object

Returns:

sql results

getColumnCount

public int getColumnCount()

Getter for number of columns

Returns:

count of columns in the result set

getColumnNames

public java.util.ArrayList getColumnNames()

Getter for SQL column names

Returns:

arraylist containing the column names

toList

public java.util.List toList()

Returns the SQLResults in the form of a List

Returns:

List

setToStringFormatWidth

public void setToStringFormatWidth(int toStringFormatWidth)

Setter for string format width (used in toString() method)

Parameters:

toStringFormatWidth -

add

public boolean add(java.lang.Object o)

Adds an object (a field value from a SELECT) to the sql results container)

Parameters:

o -

getRowCount

public int getRowCount()

Gets the number of rows returned by query

Returns:

number of rows

getObject

public java.lang.Object getObject(int row,
                                  int col)

GetObject returns an object in the ArrayList results given a row and column index

Parameters:

row -

col -

Returns:

object

addColumnName

public void addColumnName(java.lang.String columnName)

Adds a column name to the columnNames list

Parameters:

columnName - columnName to add

getColumnIndex

private int getColumnIndex(java.lang.String columnName)

Given a columnName, return the corresponding index

Parameters:

columnName -

Returns:

columnName corresponding to index

getInt

public int getInt(int row,
                  java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as an int

Parameters:

row -

columnName -

Returns:

int value of field

getInt

public int getInt(int row,
                  int col)

Gets the value of the field corresponding to (row, col) index as an int

Parameters:

row -

col -

Returns:

int value of field

getLong

public long getLong(int row,
                    java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a long

Parameters:

row - int

columnName - String

Returns:

long value of field

getLong

public long getLong(int row,
                    int col)

Gets the value of the field corresponding to row, col as a long

Parameters:

row -

col -

Returns:

long

getString

public java.lang.String getString(int row,
                                  int col)

Gets the value of the field corresponding to (row, col) index as a String

Parameters:

row - int

col - int

Returns:

String value of field

getString

public java.lang.String getString(int row,
                                  java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a String

Parameters:

row - int

columnName - String

Returns:

String value of field

getBoolean

public boolean getBoolean(int row,
                          int col)

Gets the value of the field corresponding to (row, col) as a boolean

Parameters:

row - int

col - int

Returns:

boolean value of field

getBoolean

public boolean getBoolean(int row,
                          java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a boolean

Parameters:

row - int

columnName - String

Returns:

boolean value of field

getDate

public java.sql.Date getDate(int row,
                             int col)

Gets the value of the field corresponding to (row, col) as a java.sql.Date

Parameters:

row - int

col - int

Returns:

Date value of field

getDate

public java.sql.Date getDate(int row,
                             java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a java.sql.Date

Parameters:

row - int

columnName - String

Returns:

Date value of field

getTime

public java.sql.Time getTime(int row,
                             int col)

Gets the value of the field corresponding to (row, col) as a java.sql.Time

Parameters:

row - int

col - int

Returns:

Time value of field

getTime

public java.sql.Time getTime(int row,
                             java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a java.sql.Time

Parameters:

row - int

columnName - String

Returns:

Time value of field

getTimestamp

public java.sql.Timestamp getTimestamp(int row,
                                       int col)

Gets the value of the field corresponding to (row, col) as a java.sql.Timestamp

Parameters:

row - int

col - int

Returns:

Timestamp value of field

getTimestamp

public java.sql.Timestamp getTimestamp(int row,
                                       java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a java.sql.Timestamp

Parameters:

row - int

columnName - String

Returns:

Timestamp value of field

getDouble

public double getDouble(int row,
                        int col)

Gets the value of the field corresponding to (row, col) as a double

Parameters:

row - int

col - int

Returns:

double value of field

getDouble

public double getDouble(int row,
                        java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a double

Parameters:

row - int

columnName - String

Returns:

double value of field

getFloat

public float getFloat(int row,
                      int col)

Gets the value of the field corresponding to (row, col) as a float

Parameters:

row - int

col - int

Returns:

float value of field

getFloat

public float getFloat(int row,
                      java.lang.String columnName)

Gets the value of the field corresponding to row, columnName as a float

Parameters:

row - int

columnName - String

Returns:

float value of field

toString

public java.lang.String toString()

Returns the contents of SQLResults in a text table. This method is useful for debugging SQL statements.

Overrides:

toString in class java.lang.Object

Returns:

String

formatWithSpaces

private java.lang.String formatWithSpaces(java.lang.String s)

Formats a string by adding spaces on the end if the string is shorter than toStringFormatWidth, or truncates string if it is too long.

Parameters:

s - String

Returns:

String

isNull

public boolean isNull(int row,
                      int col)

Determines if field given by (row,col) is null

Parameters:

row -

col -

Returns:

true if null

isNull

public boolean isNull(int row,
                      java.lang.String columnName)

Determines if field given by (row, columnName) is null

Parameters:

row -

col -

Returns:

true if null