net.xoetrope.optional.data.sql
Class DatabaseTable

java.lang.Object
  net.xoetrope.optional.data.sql.DatabaseTable

Direct Known Subclasses:

CachedDatabaseTable

public class DatabaseTable
extends java.lang.Object

A holder for information about a database table and the result set obtained from a database query. This class provides some basic help to build queries to JDBC database tables and managing the interaction with the DataConnection object

Copyright (c) Xoetrope Ltd. 2001-2005

$Revision: 2.10 $ License: see license.txt

Field Summary

protected static boolean	caseSensitive
protected DataConnection	connection
protected java.lang.String	connName
protected XProject	currentProject
protected int	currentRow
protected java.lang.String	defaultWhereClause
protected boolean	dirty
protected boolean	distinct
protected java.lang.String[]	fieldNames
protected int[]	fieldTypes
static int	NORMAL_UPDATE The database will attempt to synchronize periodically
protected int	numFields
protected int	numRows
static int	OPTIMISTIC_UPDATE The database will only attempt to synchronize when absolutely necessary
protected java.lang.String	orderField
protected java.lang.String[]	params
static int	PARANOID_UPDATE The database will always attemp to synchronize
protected java.lang.String	queryFields
protected java.lang.String	queryTable
protected java.sql.ResultSet	RS
protected java.lang.String	sqlStatement
protected boolean	writable

Constructor Summary

DatabaseTable(XProject project, java.lang.String tableName)
Create a new database table wrapper

DatabaseTable(XProject project, java.lang.String sql, java.lang.String conn, boolean allowWrites)
Create a new database table wrapper

DatabaseTable(XProject project, java.lang.String tableName, java.lang.String fields, java.lang.String where, java.lang.String conn, boolean allowWrites)
Create a new database table wrapper

Method Summary

protected void	cacheData() Cache and close the result set
void	commit() Explicitly force a commit
int	executeUpdate(java.lang.String sql) Execute an update statement
protected void	finalize()
void	first() Move to the first record in the result set.
java.lang.String	getConnName() Get the connection name
int	getCurrentRow() Gets the current row.
boolean	getDoesEscapeProcessing() Gets the escape processing flag for the underlying JDBC implementation
int	getFieldIndex(java.lang.String fieldName) Get the index (zero based) of a named field
java.lang.String	getFieldName(int fieldIdx) Get the name of a field
java.lang.String	getFields() Gets the fields used for the query
long	getNextPseudoId(java.lang.String tableName) Get the next PSUEDOID for the names table
int	getNumFields() get the number of fields in the table
int	getNumRows() Get the number of rows/records in the resultset/table
java.sql.PreparedStatement	getPreparedStatement(java.lang.String sql) Get a prepared statement.
java.sql.PreparedStatement	getPreparedStatement(java.lang.String sql, java.lang.String connName, boolean forwardOnly) Get a prepared statement.
java.lang.String	getSQL(java.lang.String where) Get the SQL for the SELECT statement or return the SQL that was specified
java.lang.String	getTableName() Gets the table name
void	getTableName(java.lang.String name) Get the table name
java.lang.String	getValue(int fieldIdx) Get a field value
java.lang.String	getValue(int rowIdx, int fieldIdx) Get a field value
java.lang.String	getWhereClause() Gets the value of any where clause
boolean	hasMoreRows() Are there more records/rows?
boolean	isDistinct() Check if the table is set to retrieve distinct rows.
void	last() Move to the last record in the result set
boolean	next() Move to the next record in the result set
boolean	previous() Move to the previous record in the result set
void	releasePreparedStatement(java.sql.PreparedStatement preparedStatement) Frees the prepared statement
void	retrieve() Retrieve values using the default where clause or the params array if a PreparedStatement is to be used.
void	retrieve(java.lang.String where) Retrieve values using the specified where clause.
void	retrieve(java.lang.String[] params) Retrieve values using a Prepared statement.
void	setAutoCommit(boolean state) Set the auto commit state
static void	setCaseSensitive(boolean state) Set the case sensitivity for database comparisons.
void	setCurrentRow(int idx) Sets the current row.
void	setDistinct(boolean isDistinct) Set the table to retrieve distinct rows
void	setDoesEscapeProcessing(boolean escapeProcessing) Set the escape processing flag for the underlying JDBC implementation
void	setOrderField(java.lang.String fld) Set the field name(s) for the ORDER clause
void	setParams(java.lang.String[] prms) If params are set then a PreparedStatement will be used in the query
void	setSql(java.lang.String sql) Set the SQL Statement for the query
void	setValue(int rowIdx, int fieldIdx, java.lang.String value) Set a field value

Methods inherited from class java.lang.Object

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

Field Detail

PARANOID_UPDATE

public static final int PARANOID_UPDATE

The database will always attemp to synchronize

See Also:

Constant Field Values

NORMAL_UPDATE

public static final int NORMAL_UPDATE

The database will attempt to synchronize periodically

See Also:

Constant Field Values

OPTIMISTIC_UPDATE

public static final int OPTIMISTIC_UPDATE

The database will only attempt to synchronize when absolutely necessary

See Also:

Constant Field Values

connection

protected DataConnection connection

numFields

protected int numFields

numRows

protected int numRows

currentRow

protected int currentRow

dirty

protected boolean dirty

queryFields

protected java.lang.String queryFields

queryTable

protected java.lang.String queryTable

RS

protected java.sql.ResultSet RS

distinct

protected boolean distinct

orderField

protected java.lang.String orderField

sqlStatement

protected java.lang.String sqlStatement

defaultWhereClause

protected java.lang.String defaultWhereClause

connName

protected java.lang.String connName

writable

protected boolean writable

caseSensitive

protected static boolean caseSensitive

params

protected java.lang.String[] params

fieldNames

protected java.lang.String[] fieldNames

fieldTypes

protected int[] fieldTypes

currentProject

protected XProject currentProject

Constructor Detail

DatabaseTable

public DatabaseTable(XProject project,
                     java.lang.String tableName)

Create a new database table wrapper

Parameters:

project - the owner project

tableName - the table name

DatabaseTable

public DatabaseTable(XProject project,
                     java.lang.String sql,
                     java.lang.String conn,
                     boolean allowWrites)

Create a new database table wrapper

Parameters:

project - the owner project

sql - the SQL query for the table

conn - the connection name

allowWrites - true if the result set is to be updatable

DatabaseTable

public DatabaseTable(XProject project,
                     java.lang.String tableName,
                     java.lang.String fields,
                     java.lang.String where,
                     java.lang.String conn,
                     boolean allowWrites)

Create a new database table wrapper

Parameters:

project - the owner project

tableName - the table name

fields - the fields to retrieve

where - the where clause to use in the query

conn - the connection name

allowWrites - true if the result set is to be updatable

Method Detail

setSql

public void setSql(java.lang.String sql)

Set the SQL Statement for the query

Parameters:

sql - the new SQL statement

setParams

public void setParams(java.lang.String[] prms)

If params are set then a PreparedStatement will be used in the query

Parameters:

prms - the array of params to populate the query.

setDistinct

public void setDistinct(boolean isDistinct)

Set the table to retrieve distinct rows

Parameters:

isDistinct - true to use the DISTINCT keyword in the SQL query

isDistinct

public boolean isDistinct()

Check if the table is set to retrieve distinct rows. If the distinct flag is used then the SQL queries will be created with the DISTINCT keyword.

Returns:

the distinct flag

setOrderField

public void setOrderField(java.lang.String fld)

Set the field name(s) for the ORDER clause

Parameters:

fld - the field name or names that will be used as the ORDER BY clause such that the text ORDER BY is inserted into to the SQL query

getValue

public java.lang.String getValue(int fieldIdx)

Get a field value

Parameters:

fieldIdx - the (zero based) field index

Returns:

the value

getValue

public java.lang.String getValue(int rowIdx,
                                 int fieldIdx)

Get a field value

Parameters:

rowIdx - the (zero based) row index

fieldIdx - the (zero based) field/column index

Returns:

the field value

setValue

public void setValue(int rowIdx,
                     int fieldIdx,
                     java.lang.String value)

Set a field value

Parameters:

value - the new field value

rowIdx - the (zero based) row index

fieldIdx - the (zero based) field/column index

getFieldIndex

public int getFieldIndex(java.lang.String fieldName)

Get the index (zero based) of a named field

Parameters:

fieldName - the field name

Returns:

the index

getFieldName

public java.lang.String getFieldName(int fieldIdx)

Get the name of a field

Parameters:

fieldIdx - the field index (zero based)

Returns:

the index

retrieve

public void retrieve()

Retrieve values using the default where clause or the params array if a PreparedStatement is to be used.

retrieve

public void retrieve(java.lang.String where)

Retrieve values using the specified where clause. If a SQL statement has been specified in its entirety then the where clause argument is ignored.

Parameters:

where - the SQL where statement (without the WHERE)

retrieve

public void retrieve(java.lang.String[] params)

Retrieve values using a Prepared statement. The PreparedStatement is populated with the params array.

Parameters:

params - the params which will populate the PreparedStatement

getPreparedStatement

public java.sql.PreparedStatement getPreparedStatement(java.lang.String sql)
                                                throws java.sql.SQLException

Get a prepared statement. Make sure to call releasePreparedStatement when done with the statement

Parameters:

sql - the sql query string or null to have the table build one

Returns:

the new statement

Throws:

java.sql.SQLException

getPreparedStatement

public java.sql.PreparedStatement getPreparedStatement(java.lang.String sql,
                                                       java.lang.String connName,
                                                       boolean forwardOnly)
                                                throws java.sql.SQLException

Get a prepared statement. Make sure to call releasePreparedStatement when done with the statement

Parameters:

sql - the sql query string or null to have the table build one

connName - the connection name

forwardOnly - true for a forward only cursor or false for a scroll insensitive cursor

Returns:

the new statement

Throws:

java.sql.SQLException

releasePreparedStatement

public void releasePreparedStatement(java.sql.PreparedStatement preparedStatement)
                              throws java.sql.SQLException

Frees the prepared statement

Parameters:

preparedStatement - the statement to free/release

Throws:

java.sql.SQLException

executeUpdate

public int executeUpdate(java.lang.String sql)
                  throws java.lang.Exception

Execute an update statement

Parameters:

sql - the UPDATE SQL statement

Throws:

java.lang.Exception

cacheData

protected void cacheData()
                  throws java.sql.SQLException

Cache and close the result set

Throws:

java.sql.SQLException

getNumRows

public int getNumRows()

Get the number of rows/records in the resultset/table

Returns:

the number of rows

setCurrentRow

public void setCurrentRow(int idx)

Sets the current row.

Parameters:

idx - the new row index - 0 based

getCurrentRow

public int getCurrentRow()

Gets the current row.

Returns:

the new row index - 0 based

first

public void first()

Move to the first record in the result set.

last

public void last()

Move to the last record in the result set

next

public boolean next()

Move to the next record in the result set

Returns:

true on success, false otherwise

previous

public boolean previous()

Move to the previous record in the result set

Returns:

true on success, false otherwise

hasMoreRows

public boolean hasMoreRows()

Are there more records/rows?

Returns:

true if the current row is not on the last row in the table

finalize

protected void finalize()
                 throws java.lang.Throwable

Overrides:

finalize in class java.lang.Object

Throws:

java.lang.Throwable

getNumFields

public int getNumFields()

get the number of fields in the table

Returns:

the number of fields

getTableName

public java.lang.String getTableName()

Gets the table name

Returns:

the table name

getTableName

public void getTableName(java.lang.String name)

Get the table name

Parameters:

name - the table name

getConnName

public java.lang.String getConnName()

Get the connection name

Returns:

the connection names

getWhereClause

public java.lang.String getWhereClause()

Gets the value of any where clause

Returns:

the where clause

getFields

public java.lang.String getFields()

Gets the fields used for the query

Returns:

the where clause

setCaseSensitive

public static void setCaseSensitive(boolean state)

Set the case sensitivity for database comparisons. This setting is application wide

Parameters:

state - true for case-sensitive comparisons of field names

getSQL

public java.lang.String getSQL(java.lang.String where)

Get the SQL for the SELECT statement or return the SQL that was specified

Parameters:

where - the where clause if any

Returns:

the SQL statement

getNextPseudoId

public long getNextPseudoId(java.lang.String tableName)

Get the next PSUEDOID for the names table

Parameters:

tableName - the table name whose ID is to be inspected

Returns:

the next ID or a negative value if invalid

commit

public void commit()
            throws java.sql.SQLException

Explicitly force a commit

Throws:

java.sql.SQLException

setAutoCommit

public void setAutoCommit(boolean state)
                   throws java.sql.SQLException

Set the auto commit state

Parameters:

true - to turn auto commit on

Throws:

java.sql.SQLException

getDoesEscapeProcessing

public boolean getDoesEscapeProcessing()

Gets the escape processing flag for the underlying JDBC implementation

Returns:

true for setEscapeProcssing( true )

setDoesEscapeProcessing

public void setDoesEscapeProcessing(boolean escapeProcessing)

Set the escape processing flag for the underlying JDBC implementation

Parameters:

doesEscapeProcessing - true to setEscapeProcssing( true )