cmu.survey.jsp.tags
Class DatabaseQuery

java.lang.Object
  cmu.survey.jsp.tags.DatabaseQuery

public class DatabaseQuery
extends java.lang.Object

Forms the backbone for retrieving things from the database, and has tons of database-related helper features.
It starts the connection with the database through setupConnection(), does queries through doQuery(), and retrieves the fields with getField().
There is also a large set of related parameters for formatting these in the best possible way. and there are a large variety of helper functions for pulling and saving things in the database.

Field Summary

static java.text.SimpleDateFormat	dateFormatAmerican
static java.text.SimpleDateFormat	dateTimeFormatAmerican
static java.text.SimpleDateFormat	msAccessDF
static java.text.SimpleDateFormat	msAccessDTF
static java.text.SimpleDateFormat	msAccessTF
static java.text.SimpleDateFormat	mysqlDF Used to generate a date object from a mySQL date string
static java.text.SimpleDateFormat	mysqlDTF Used to generate a date object from a mySQL date string
static java.text.SimpleDateFormat	mysqlTF Used to generate a date object from a mySQL time string
static java.text.SimpleDateFormat	timeFormatAmerican

Constructor Summary

DatabaseQuery(DatabaseConnection databaseConnectionToUse)

DatabaseQuery(DatabaseConnection databaseConnectionToUse, TableStorageMySQL tableStorage)

DatabaseQuery(javax.servlet.jsp.tagext.TagSupport superclassTag)

Method Summary

void	clearForNextUsage() Called for repeated usage of a DatabaseQuery.
void	clearLastExceptionThrown()
void	close()
boolean	doQuery(java.lang.String aDatabaseQuery) Execute the current databaseQuery and leaves the results in the resultSet object.
void	finalize()
java.lang.String	formatDateSQL(java.util.Calendar cal) Takes a string and a format that it is in, and returns the format that SQL can understand.
java.lang.String	formatDateSQL(java.util.Date theDate) Takes a string and a format that it is in, and returns the format that SQL can understand.
java.lang.String	formatDateTimeSQL(java.util.Calendar theDate) Takes a Calendar and returns the format that SQL can understand.
java.lang.String	formatDateTimeSQL(java.util.Date theDate) Takes a Date and returns the format that SQL can understand.
java.lang.String	formatDateTimeSQL(java.lang.String currentString, java.text.DateFormat currentFormat) Takes a string and a format that it is in, and returns the format that MySQL can understand.
static java.lang.String	formatDateTimeViewable(java.util.Calendar theDate)
static java.lang.String	formatDateTimeViewable(java.util.Date theDate)
static java.lang.String	formatDateViewable(java.util.Calendar theDate)
static java.lang.String	formatDateViewable(java.util.Date theDate)
java.lang.String	formatNumberSQL(int num)
java.lang.String	formatNumberSQL(java.lang.Number num)
java.lang.String	formatNumberSQL(java.lang.Object num) Formats a number for the database.
java.lang.String	formatTimeSQL(java.util.Calendar theDate) Takes a Calendar and returns the format that SQL can understand.
java.lang.String	formatTimeSQL(java.util.Date theDate) Takes a Date returns it in a format that MySQL can understand.
java.util.Calendar	getCalendar() Use the following three methods instead of making your own Date or Calendar from scratch.
java.sql.Connection	getDatabaseConnection()
java.lang.String	getDatabaseQuery()
java.util.Calendar	getDateCalendarField(java.lang.String fieldName)
java.util.Date	getDateField(java.lang.String fieldName)
java.util.Calendar	getDateFieldCal(java.lang.String fieldName)
java.util.Calendar	getDateFrom(java.util.Calendar cale, java.sql.Time time)
java.util.Calendar	getDateFrom(java.util.Calendar cale, java.sql.Time time, boolean rollZerothHourToNextDay)
java.util.Calendar	getDateTimeCalendarField(java.lang.String fieldName)
java.util.Date	getDateTimeField(java.lang.String fieldName)
java.lang.String	getField(int column) For the current row in the resultSet, will return the value of the given field.
java.lang.String	getField(java.lang.String fieldName) For the current row in the resultSet, will return the value of the given field.
java.util.Vector<java.lang.String>	getFields()
java.util.Vector<java.lang.String>	getFields(java.lang.String fieldsToShow)
java.lang.Integer	getIntegerField(java.lang.String fieldName)
int	getIntField(java.lang.String fieldName) If the database has NULL in the field, this will return 0.
java.lang.Throwable	getLastExceptionThrown()
int	getNumRows()
HasErrorInfo	getObjectWithErrorInfo()
java.sql.ResultSet	getScrollableResultSet(java.lang.String aDatabaseQuery) Execute the current databaseQuery and leaves the results in the resultSet object.
boolean	getSendErrorMessages()
java.sql.Time	getSQLTimeField(java.lang.String fieldName)
DatabaseConnection	getTheConnection()
java.sql.Time	getTimeField(java.lang.String fieldName)
java.util.Calendar	getTimeFieldCal(java.lang.String fieldName)
java.sql.Timestamp	getTimestampField(java.lang.String fieldName)
java.util.TimeZone	getTimeZone()
java.sql.ResultSet	getUnscrollableResultSet(java.lang.String aDatabaseQuery) Execute the current databaseQuery and leaves the results in the resultSet object.
boolean	isDatabaseOpen()
boolean	moveToNextRecord()
java.util.Calendar	parseDateCalendarFromSQL(java.lang.String dateTime) Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.
java.util.Date	parseDateFromSQL(java.lang.String time) Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.
static java.util.Calendar	parseDateTimeCalendarFromAnyFormat(java.lang.String dateTime) Takes a String in the default database format and parses it into a Calendar.
java.util.Calendar	parseDateTimeCalendarFromSQL(java.lang.String dateTime) Takes a String in the default database format and parses it into a Calendar.
static java.util.Date	parseDateTimeFromAnyFormat(java.lang.String dateTime) Takes a String in the default database format and parses it into a Date.
java.util.Date	parseDateTimeFromSQL(java.lang.String dateTime) Takes a String in the default database format and parses it into a Date.
java.util.Date	parseDateTimeFromSQL(java.lang.String date, java.lang.String time) Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.
java.util.Date	parseTimeFromSQL(java.lang.String time) Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.
void	resetFieldsForNextRecord() Nulls out the hash table containing the row from the recordset
protected void	saveErrorMessage(java.lang.String error)
protected void	saveErrorMessage(java.lang.String error, java.lang.Throwable e)
protected void	saveErrorMessage(java.lang.Throwable e)
void	setDatabaseQuery(java.lang.String value)
void	setObjectWithErrorInfo(HasErrorInfo info)
void	setSendErrorMessages(boolean emailTheErrorMessages)
void	setTimeZone(java.util.TimeZone zone)
void	setupConnection()
void	setupConnection(DatabaseConnection theNewConnection) Used to initialize the connection object, statements, and JSPWriter (out).

Methods inherited from class java.lang.Object

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

Field Detail

mysqlTF

public static final java.text.SimpleDateFormat mysqlTF

Used to generate a date object from a mySQL time string

mysqlDF

public static final java.text.SimpleDateFormat mysqlDF

Used to generate a date object from a mySQL date string

mysqlDTF

public static final java.text.SimpleDateFormat mysqlDTF

Used to generate a date object from a mySQL date string

msAccessDTF

public static final java.text.SimpleDateFormat msAccessDTF

msAccessDF

public static final java.text.SimpleDateFormat msAccessDF

msAccessTF

public static final java.text.SimpleDateFormat msAccessTF

dateFormatAmerican

public static final java.text.SimpleDateFormat dateFormatAmerican

dateTimeFormatAmerican

public static final java.text.SimpleDateFormat dateTimeFormatAmerican

timeFormatAmerican

public static final java.text.SimpleDateFormat timeFormatAmerican

Constructor Detail

DatabaseQuery

public DatabaseQuery(DatabaseConnection databaseConnectionToUse)

DatabaseQuery

public DatabaseQuery(DatabaseConnection databaseConnectionToUse,
                     TableStorageMySQL tableStorage)

DatabaseQuery

public DatabaseQuery(javax.servlet.jsp.tagext.TagSupport superclassTag)

Method Detail

setSendErrorMessages

public void setSendErrorMessages(boolean emailTheErrorMessages)

getSendErrorMessages

public boolean getSendErrorMessages()

saveErrorMessage

protected void saveErrorMessage(java.lang.Throwable e)

saveErrorMessage

protected void saveErrorMessage(java.lang.String error,
                                java.lang.Throwable e)

saveErrorMessage

protected void saveErrorMessage(java.lang.String error)

getLastExceptionThrown

public java.lang.Throwable getLastExceptionThrown()

clearLastExceptionThrown

public void clearLastExceptionThrown()

formatDateSQL

public java.lang.String formatDateSQL(java.util.Date theDate)

Takes a string and a format that it is in, and returns the format that SQL can understand.

formatDateSQL

public java.lang.String formatDateSQL(java.util.Calendar cal)

Takes a string and a format that it is in, and returns the format that SQL can understand.

formatTimeSQL

public java.lang.String formatTimeSQL(java.util.Date theDate)

Takes a Date returns it in a format that MySQL can understand.

formatDateTimeSQL

public java.lang.String formatDateTimeSQL(java.util.Date theDate)

Takes a Date and returns the format that SQL can understand.

formatDateTimeSQL

public java.lang.String formatDateTimeSQL(java.util.Calendar theDate)

Takes a Calendar and returns the format that SQL can understand.

formatTimeSQL

public java.lang.String formatTimeSQL(java.util.Calendar theDate)

Takes a Calendar and returns the format that SQL can understand.

formatNumberSQL

public java.lang.String formatNumberSQL(java.lang.Number num)

formatNumberSQL

public java.lang.String formatNumberSQL(int num)

formatNumberSQL

public java.lang.String formatNumberSQL(java.lang.Object num)

Formats a number for the database. Written for the special case where you have a Vector (or other storage structure) that has both Number and String in it -- String for the sole case of having a null, blank, value -- the only valid string is "". Parses both of these properly.

formatDateTimeSQL

public java.lang.String formatDateTimeSQL(java.lang.String currentString,
                                          java.text.DateFormat currentFormat)

Takes a string and a format that it is in, and returns the format that MySQL can understand.

formatDateTimeViewable

public static java.lang.String formatDateTimeViewable(java.util.Calendar theDate)

formatDateTimeViewable

public static java.lang.String formatDateTimeViewable(java.util.Date theDate)

formatDateViewable

public static java.lang.String formatDateViewable(java.util.Calendar theDate)

formatDateViewable

public static java.lang.String formatDateViewable(java.util.Date theDate)

parseDateTimeFromSQL

public java.util.Date parseDateTimeFromSQL(java.lang.String dateTime)

Takes a String in the default database format and parses it into a Date.

parseDateTimeFromAnyFormat

public static java.util.Date parseDateTimeFromAnyFormat(java.lang.String dateTime)

Takes a String in the default database format and parses it into a Date.

Returns:

a Date from the dateTime, or null if it failed (i.e. bad formatting in the string).

parseDateTimeCalendarFromSQL

public java.util.Calendar parseDateTimeCalendarFromSQL(java.lang.String dateTime)

Takes a String in the default database format and parses it into a Calendar.

parseDateTimeCalendarFromAnyFormat

public static java.util.Calendar parseDateTimeCalendarFromAnyFormat(java.lang.String dateTime)

Takes a String in the default database format and parses it into a Calendar.

Returns:

a Calendar from the dateTime, or null if it failed (i.e. bad formatting in the string).

parseDateCalendarFromSQL

public java.util.Calendar parseDateCalendarFromSQL(java.lang.String dateTime)

Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.

parseDateTimeFromSQL

public java.util.Date parseDateTimeFromSQL(java.lang.String date,
                                           java.lang.String time)

Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.

parseDateFromSQL

public java.util.Date parseDateFromSQL(java.lang.String time)

Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.

parseTimeFromSQL

public java.util.Date parseTimeFromSQL(java.lang.String time)

Takes a String in the default database format and parses it into a Date and returns the format that MySQL can understand.

getDateFrom

public java.util.Calendar getDateFrom(java.util.Calendar cale,
                                      java.sql.Time time,
                                      boolean rollZerothHourToNextDay)

getDateFrom

public java.util.Calendar getDateFrom(java.util.Calendar cale,
                                      java.sql.Time time)

getCalendar

public java.util.Calendar getCalendar()

Use the following three methods instead of making your own Date or Calendar from scratch. They will get the TimeZone from the database and use it. It should be moved to ObjectSQLStorage (is copied there) and other parts to other places where it can be used properly.

setupConnection

public void setupConnection(DatabaseConnection theNewConnection)

Used to initialize the connection object, statements, and JSPWriter (out). This should be done before any of those objects are used.

setupConnection

public void setupConnection()

doQuery

public boolean doQuery(java.lang.String aDatabaseQuery)

Execute the current databaseQuery and leaves the results in the resultSet object.

getScrollableResultSet

public java.sql.ResultSet getScrollableResultSet(java.lang.String aDatabaseQuery)

Execute the current databaseQuery and leaves the results in the resultSet object.

getUnscrollableResultSet

public java.sql.ResultSet getUnscrollableResultSet(java.lang.String aDatabaseQuery)

Execute the current databaseQuery and leaves the results in the resultSet object.

getDateTimeField

public java.util.Date getDateTimeField(java.lang.String fieldName)

getDateCalendarField

public java.util.Calendar getDateCalendarField(java.lang.String fieldName)

getDateTimeCalendarField

public java.util.Calendar getDateTimeCalendarField(java.lang.String fieldName)

getTimeField

public java.sql.Time getTimeField(java.lang.String fieldName)

getSQLTimeField

public java.sql.Time getSQLTimeField(java.lang.String fieldName)

getDateField

public java.util.Date getDateField(java.lang.String fieldName)

getTimeFieldCal

public java.util.Calendar getTimeFieldCal(java.lang.String fieldName)

getDateFieldCal

public java.util.Calendar getDateFieldCal(java.lang.String fieldName)

getTimestampField

public java.sql.Timestamp getTimestampField(java.lang.String fieldName)

getIntField

public int getIntField(java.lang.String fieldName)

If the database has NULL in the field, this will return 0.

Parameters:

fieldName -

Returns:

getIntegerField

public java.lang.Integer getIntegerField(java.lang.String fieldName)

getField

public java.lang.String getField(java.lang.String fieldName)

For the current row in the resultSet, will return the value of the given field. If the resultset is null, will return null

Parameters:

fieldName - The attribute who's value you want

Returns:

The value of the given attribute in the current row

getField

public java.lang.String getField(int column)

For the current row in the resultSet, will return the value of the given field. If the resultset is null, will return null. Unlike most java functions, this starts at "1", not at "0". This is a resultSet.getMetaData() feature -- and we try to stay consistent with it.

Parameters:

column - the column ID

Returns:

The value of the given attribute in the current row

resetFieldsForNextRecord

public void resetFieldsForNextRecord()

Nulls out the hash table containing the row from the recordset

moveToNextRecord

public boolean moveToNextRecord()

getFields

public java.util.Vector<java.lang.String> getFields()

getNumRows

public int getNumRows()

getFields

public java.util.Vector<java.lang.String> getFields(java.lang.String fieldsToShow)

close

public void close()

finalize

public void finalize()

Overrides:

finalize in class java.lang.Object

isDatabaseOpen

public boolean isDatabaseOpen()

setDatabaseQuery

public void setDatabaseQuery(java.lang.String value)

getDatabaseQuery

public java.lang.String getDatabaseQuery()

getDatabaseConnection

public java.sql.Connection getDatabaseConnection()

Returns:

getTheConnection

public DatabaseConnection getTheConnection()

Returns:

getObjectWithErrorInfo

public HasErrorInfo getObjectWithErrorInfo()

Returns:

setObjectWithErrorInfo

public void setObjectWithErrorInfo(HasErrorInfo info)

Parameters:

info -

getTimeZone

public java.util.TimeZone getTimeZone()

Returns:

setTimeZone

public void setTimeZone(java.util.TimeZone zone)

Parameters:

zone -

clearForNextUsage

public void clearForNextUsage()

Called for repeated usage of a DatabaseQuery. This clears all state information from the DatabaseQuery. It's basically a re-initialization to default values for all non-database, non-static info.