package net.xoetrope.optional.data.sql;
import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSetMetaData;
import net.xoetrope.debug.DumpWriter;
import net.xoetrope.xui.data.table.XTableModel;
import net.xoetrope.xui.XProject;
import net.xoetrope.xui.build.BuildProperties;
import net.xoetrope.xui.data.XModel;
import net.xoetrope.xui.data.XRowSelector;
/**
* <p>A wrapper for a database table allowing it to integrate with the XModel</p>
* <p>The table model consists of a set of DatabaseRowModel objects and these in
* turn consist of DatabaseFieldModel nodes. These objects all act as wrappers for
* the DatabaseTable object and do not themselves maintain data.</p>
* <p>Copyright: Copyright (c) 2003<br>
* License: see license.txt</p>
* $Revision: 2.12 $
* License: see license.txt
*/
public class DatabaseTableModel extends XTableModel implements DumpWriter, XRowSelector
{
CachedDatabaseTable xtable;
boolean dirty;
boolean readOnly;
private boolean doesEscapeProcessing;
private static boolean allowNull = false;
protected String modelId;
protected XProject currentProject;
/**
* Create a new model node for a database table
* @param project the owner project
*/
public DatabaseTableModel( XProject project )
{
currentProject = project;
dirty = true;
readOnly = true;
}
/**
* Set a flag to determine how null values are treated.
* @param allow if false then nulls are return as zero or an empty string, if
* true the null is returned for string types and Double.NaN for doubles and
* Integer.MIN_VALUE for int fields and so on
*/
public static void setAllowNull( boolean allow )
{
allowNull = allow;
}
/**
* Ensures that the query is recalled. This is important where PreparedStatements
* are used to retrieve the data. The query itself does not change but the
* parameters do.
* @param isDirty boolean to set the dirty flag
*/
public void setDirty( boolean isDirty )
{
dirty = isDirty;
}
/**
* Sets the name attribute and constructs a new table of that name
* @param newName the new name
*/
public void setName( String newName )
{
modelId = newName;
}
/**
* Get the hashcode for this model node
* @return the hashcode
*/
public int hashCode()
{
return modelId.hashCode();
}
/**
* List the fields retrieved from the table
* @param tableName the database table name
*/
public void setupTable( String tableName )
{
setupTable( tableName, null, null );
}
/**
* List the fields retrieved from the table
* @param tableName the database table name
* @param fields the database field names, or null if all fields are to be retrieved
* @param whereClause the where clause, or null if all rows are to be selected
*/
public void setupTable( String tableName, String fields, String whereClause )
{
setupTable( tableName, fields, whereClause, null, false );
}
/**
* List the fields retrieved from the table
* @param writable true if the table is to be treated as writable
* @param tableName the database table name
* @param fields the database field names, or null if all fields are to be retrieved
* @param whereClause the where clause, or null if all rows are to be selected
* @param connName the connection name
*/
public void setupTable( String tableName, String fields, String whereClause, String connName, boolean writable )
{
xtable = new CachedDatabaseTable( currentProject, tableName, fields, whereClause, connName, writable );
xtable.setDoesEscapeProcessing( doesEscapeProcessing );
if ( modelId == null )
modelId = tableName;
dirty = true;
// sync();
}
/**
* Specify the sql statement in its entirety. In setting the entire SQL statement
* any other parameters are ignored.
* @param writable true if the table is to be treated as writeable
* @param sqlStr the full SQL statement
* @param connName the connection name
*/
public void setSqlStatement( String sqlStr, String connName, boolean writable )
{
String originalSql = null;
if ( xtable != null )
originalSql = xtable.getOriginalSql();
xtable = new CachedDatabaseTable( currentProject, originalSql == null ? sqlStr : originalSql, connName, writable );
xtable.setDoesEscapeProcessing( doesEscapeProcessing );
xtable.setSql( sqlStr );
dirty = true;
}
/*
* Retrieve the current SQL statement
*/
public String getOriginalSql()
{
return xtable.getOriginalSql();
}
/**
* Update the underlying database table using the specified SQL statement
* @return the number of rows affected by the update
* @param sqlStr the full UPDATE SQL statement
* @throws java.lang.Exception SqlException(s) thrown while performing the update
*/
public int executeUpdate( String sqlStr ) throws Exception
{
dirty = true;
return xtable.executeUpdate( sqlStr );
}
/**
* Set the table to retrieve distinct rows
* @param isDistinct true if the DISTINCT keyword is to be used in the query
*/
public void setDistinct( boolean isDistinct )
{
xtable.setDistinct( isDistinct );
}
/**
* Check if the table is set to retrieve distinct rows
* @return the distinct flag
*/
public boolean isDistinct()
{
return xtable.isDistinct();
}
/**
* Set the field(s) used ORDER clause of the SQL query
* @param fld the field name(s)
*/
public void setOrderField( String fld )
{
xtable.setOrderField( fld );
}
/**
* List the fields retrieved from the table
* @param tableName the database table name
* @param fields the database field names, or null if all fields are to be retrieved
* @param whereClause the where clause, or null if all rows are to be selected
* @param writable sets the table as writable, normally this should be false
*/
public void setupTable( String tableName, String fields, String whereClause, boolean writable )
{
xtable = new CachedDatabaseTable( currentProject, tableName, fields, whereClause, null, writable );
xtable.setDoesEscapeProcessing( doesEscapeProcessing );
if ( modelId == null )
modelId = tableName;
dirty = true;
// sync();
}
/**
* Sets the read only flag indicating whether or not the underlying table
* is read-only
* @param b true for a read-only table.
*/
public void setReadOnly( boolean b )
{
readOnly = b;
}
/**
* Syncs the model with the service response is the xtable has been changed
* or if the node has timed out.
*/
public synchronized void sync()
{
if ( dirty ) { //|| xtable.hasExpired()) {
xtable.retrieve();
dirty = false;
}
}
/**
* Get the value of the element located at the path in the element parameter
* If the attribName parameter is not null we get the value of the
* attributeValues
* @return The value of the XModel or the attribute
* @param element The path to the XModel we require
*/
public Object get( String element )
{
sync();
DatabaseRowModel drm = new DatabaseRowModel( this, xtable.getCurrentRow());
return drm.get( element );
}
/**
* Get the value of the element located at the path in the element parameter
* If the attribName parameter is not null we get the value of the
* attributeValues
* @param element The path to the XModel we require
* @param attribName The name of the attribute we require or null if we just
* want the XModel value
* @return The value of the XModel or the attribute
*/
// public Object get( String element, String attribName )
// {
// sync();
// return null;
// }
/**
* Set the value of the attribute in the XModel located at the elementName
* @param elementName The path to the XModel in the format 'base/foo
* @param attribName The name of the attribute whose value we require
* @param newObject The new value of the attribute
*/
public void set( String elementName, String attribName, Object newObject )
{
if ( readOnly )
return;
dirty = true;
fireModelUpdated();
}
/**
* Set the value of the XModel located at the elementName
* @param attribName The path to the XModel in the format 'base/foo
* @param newObject The new value of the XModel
*/
public void set( String attribName, Object newObject )
{
if ( readOnly )
return;
dirty = true;
fireModelUpdated();
}
/**
* Get the XModel at element i
* @param i The index of the values array
* @return The XModel at location i
*/
public XModel get( int i )
{
sync();
XModel databaseRowModel = new DatabaseRowModel( this, i );
databaseRowModel.setParent( this );
return databaseRowModel;
}
/**
* gets the value attribute
* @return the value of the model
*/
public Object get()
{
sync();
return this;
}
/**
* This method does not nothing it is provided merely as an implementation of the
* XModel interface. A child or attribute can be logically appended to a table node.
* @param id the node id
* @return null as nothing is appended
*/
public Object append( String id )
{
return null;
}
/**
* Sets the model value
* @param s the new value
*/
public void set( Object s )
{
if ( readOnly )
return;
dirty = true;
fireModelUpdated();
}
/**
* Get the value of an attribute (row)
* @param i The index of the attributeValues array whose value we want
* @return The string value of the attributeValues array at position i
*/
public Object getAttribValue( int i )
{
sync();
return xtable.getValue( i );
}
/**
* Get an attribute as a String object
* @param i The index of the attributeValues array whose value we want
* @return The string value of the attributeValues array at position i
*/
public String getAttribValueAsString( int i )
{
sync();
return xtable.getValue( i );
}
/**
* Get an attribute as a double value
* @param i The index of the attributeValues array whose value we want
* @return The string value of the attributeValues array at position i
*/
public double getAttribValueAsDouble( int i )
{
sync();
try {
String obj = xtable.getValue( i );
if ( obj == null ) {
if ( allowNull )
return Double.NaN;
return 0.0;
}
return Double.parseDouble( obj );
//return new Double( obj ).doubleValue();
}
catch ( NullPointerException e ) {
return 0.0;
}
catch ( ArrayIndexOutOfBoundsException e ) {
return 0.0;
}
}
/**
* Get an attribute as an int value
* @param i The index of the attributeValues array whose value we want
* @return The string value of the attributeValues array at position i
*/
public int getAttribValueAsInt( int i )
{
sync();
try {
String obj = xtable.getValue( i );
if ( obj == null ) {
if ( allowNull )
return Integer.MIN_VALUE;
}
return Integer.parseInt( obj );
//return new Integer( xtable.getValue( i ) ).intValue();
}
catch ( NullPointerException e ) {
return 0;
}
}
/**
* returns the index of the attribiteNames array whose value is the same
* as the attribName
* @param attribName The name of the attribute we are trying to locate
* @return The index of the attributeNames array containg the name
*/
public int getAttribute( String attribName )
{
sync();
return xtable.getFieldIndex( attribName );
}
// Table specific access methods----------------------------------------------
/**
* Get the table data.
*/
public void retrieve()
{
sync();
}
/**
* Moves the table's cursor to the first row
*/
public void first()
{
xtable.first();
}
/**
* Moves the table's cursor to the last row
*/
public void last()
{
xtable.last();
}
/**
* Moves the table's cursor to the next row
* @return true if the operation succeeded
*/
public boolean next()
{
return xtable.next();
}
/**
* Has the table more records/rows?
* @return true if the current row is not on the last row in the table
*/
public boolean hasMoreRows()
{
return xtable.hasMoreRows();
}
/**
* Moves the table's cursor to the previous row
* @return true if the operation succeeded
*/
public boolean previous()
{
return xtable.previous();
}
/**
* Provides iterative access to the table values. The current row in the table
* is used to index the data. This method does not implicitly
* retrieve the data so you must call the retrieve method explicitly.
* @return the data
*/
public String getValue()
{
return getFieldValue( 0, 0 );
}
/**
* Get a child value/node (a DatabaseRowModel) at the specified index
* @param i The index into the values array
* @return The XModel at position i of the values array.
*/
public XModel getValue( int i )
{
return get( i );
}
/**
* Provides iterative access to the table values. The current row in the table
* is used to index the data. This method does not implicitly
* retrieve the data so you must call the retrieve method explicitly.
* @param colIdx the column of field index
* @return the data
*/
public String getFieldValue( int colIdx )
{
return xtable.getValue( colIdx );
}
/**
* Provides random access to the table values. This method does not implicitly
* retrieve the data so you must call the retrieve method explicitly.
* @param rowIdx the row index
* @param colIdx the column of field index
* @return the data
*/
public String getFieldValue( int rowIdx, int colIdx )
{
return xtable.getValue( rowIdx, colIdx );
}
/**
* Set a field value
* @param colIdx the field index, zero based
* @param newValue the new field value
*/
public void setFieldValue( int colIdx, String newValue )
{
xtable.setValue( colIdx, newValue );
fireModelUpdated();
}
/**
* Set a field value
* @param rowIdx the row index, zero based
* @param colIdx the field index, zero based
* @param newValue the new field value
*/
public void setFieldValue( int rowIdx, int colIdx, String newValue )
{
xtable.setValue( rowIdx, colIdx, newValue );
fireModelUpdated();
}
// End of table specific access methods---------------------------------------
/**
* Gets the value attribute as a double value from the current record
* @param elementName the field/column name
* @return the field value
*/
public double getValueAsDouble( String elementName )
{
return Double.parseDouble( getFieldValue( xtable.getFieldIndex( elementName ) ) );
}
/**
* Gets the value attribute of the specified node as an int.
* @param elementName the field name
* @return the field value
*/
public int getValueAsInt( String elementName )
{
return Integer.parseInt( getFieldValue( xtable.getFieldIndex( elementName ) ) );
//return new Integer( getFieldValue( xtable.getFieldIndex( elementName ) ) ).intValue();
}
/**
* Gets the attribute name for field i. For this node the table field name is
* returned.
* @param i The index of the attributeNames array whose value we want
* @return The string value of the attributeNames array at position i
*/
public String getAttribName( int i )
{
return xtable.getFieldName( i );
}
/**
* Gets the name attribute, by default the table name
* @return the model node's ID
*/
public String getId()
{
return modelId;
}
/**
* Sets the attribute value
* @param i The index of the attributeValues array whose value we want
* @param value the value object
*/
public void setAttribValue( int i, Object value )
{
setFieldValue( i, value.toString() );
}
/**
* Gets the number of rows for this table
* @return the number of rows
*/
public int getNumChildren()
{
sync();
return xtable.getNumRows();
}
/**
* Get the number of fields in this TableModel
* @return the number of fields
*/
public int getNumAttributes()
{
return xtable.getNumFields();
}
/**
* Gets the model element tag name, e.g. 'Component' from the XML fragment
* <Component ....
* @return the model element name
*/
public String getTagName()
{
return "";
}
/**
* Find a set of rows with a specified field value
* @param fieldIdx the index of the search field
* @param value the search value
* @return the table model containing the selected rows. null is returned if
* this table has uncommitted updates
*/
public DatabaseTableModel findRows( int fieldIdx, String value )
{
// if ( dirty )
// return null;
DatabaseTableModel resTable = new DatabaseTableModel( currentProject );
resTable.setupTable( xtable.getTableName(), null, xtable.getFieldName( fieldIdx ) + "='" + value + "'" );
resTable.sync();
return resTable;
}
/**
* Find a set of rows with a specified field value
* @param where the search criteria
* @return the table model containing the selected rows. null is returned if
* this table has uncommitted updates
*/
public DatabaseTableModel findRows( String where )
{
return findRows( xtable.getFields(), where );
}
/**
* Used in the case of a PreparedStatement. Passed straight through to the xtable
* @param params The String array which will popuate the PreparedStatement.
*/
public void setParams( String[] params )
{
xtable.setParams( params );
}
/**
* Find a set of rows with a specified field value. The query differs depending
* on whether or not the table has been defind using the setupTable method. If
* not the where clause is appended to the 'GROUP BY', 'HAVING' or 'ORDER BY'
* clause - whichever is last.
* @param requestFields the fields to return
* @param where the search criteria
* @return the table model containing the selected rows. null is returned if
* this table has uncommitted updates
*/
public DatabaseTableModel findRows( String requestFields, String where )
{
// if ( dirty )
// return null;
DatabaseTableModel resTable = new DatabaseTableModel( currentProject );
if ( xtable.getTableName() != null ) {
String parentWhere = xtable.getWhereClause();
resTable.setupTable( xtable.getTableName(),
requestFields,
where + ( ( parentWhere != null ) ? " AND " + parentWhere : "" ),
xtable.getConnName(),
false );
resTable.setDistinct( xtable.isDistinct() );
resTable.setOrderField( xtable.orderField );
}
else {
String sql = xtable.getSQL( "" );
// Find the end of the specified SQL
String sqlStmt = sql.toUpperCase();
int endPos = sqlStmt.indexOf( "GROUP BY" );
if ( endPos < 0 )
endPos = sqlStmt.indexOf( "HAVING " );
if ( endPos < 0 )
endPos = sqlStmt.indexOf( "ORDER BY" );
// Append/insert the new where clause
if ( endPos > 0 )
sqlStmt = sql.substring( 0, endPos ) + where + " " + sql.substring( endPos );
else
sqlStmt = sql + " " + where;
resTable.setSqlStatement( sqlStmt, xtable.getConnName(), false );
}
resTable.sync();
return resTable;
}
/**
* Find a row with a specified field value
* @param fieldIdx the index of the search field
* @param value the search value
* @return the table row model containing the selected row. null is returned if
* this table has uncommitted updates
*/
public DatabaseRowModel findRow( int fieldIdx, String value )
{
if ( dirty )
return null;
return ( DatabaseRowModel )findRows( fieldIdx, value ).get( 0 );
}
/**
* Find a row with a specified field value
* @param where the search criteria
* @return the table row model containing the selected row. null is returned if
* this table has uncommitted updates
*/
public DatabaseRowModel findRow( String where )
{
if ( dirty )
return null;
return ( DatabaseRowModel )findRows( where ).get( 0 );
}
/**
* Look up a table by name in the overall model.
* @param tableName the table name
* @return the table node
*/
public static DatabaseTableModel getTable( XProject currentProject, String tableName )
{
return ( DatabaseTableModel )( (XModel)currentProject.getModel().get( tableName ) );
}
/**
* Write the contents of this model node to a stream
* @param w the write/stream
*/
public void dump( Writer w )
{
if ( BuildProperties.DEBUG ) {
try {
int numRows = xtable.getNumRows();
int numFields = xtable.getNumFields();
for ( int i = 0; i < numRows; i++ ) {
for ( int j = 0; j < numFields; j++ ) {
if ( j > 0 )
w.write( ", " );
w.write( xtable.getValue( i, j ) );
}
w.write( "\n" );
}
}
catch ( IOException ex ) {
}
}
}
/**
* Set the row selection index
* @param rowIdx the new row selection index (zero based)
*/
public void setSelectedRow( int rowIdx )
{
xtable.setCurrentRow( rowIdx );
}
/**
* Get the row selection index
* @return the current row selection index (zero based)
*/
public int getSelectedRow( )
{
return xtable.getCurrentRow();
}
/**
* Get the table's meta data
* @return the meta data from the JDBC result set
*/
public ResultSetMetaData getMetaData()
{
return xtable.getMetaData();
}
public CachedDatabaseTable getTable()
{
return xtable;
}
/**
* Gets the escape processing flag for the underlying JDBC implementation
* @return true for setEscapeProcssing( true )
*/
public boolean getDoesEscapeProcessing()
{
return doesEscapeProcessing;
}
/**
* Set the escape processing flag for the underlying JDBC implementation
* @param doesEscapeProcessing true to setEscapeProcssing( true )
*/
public void setDoesEscapeProcessing( boolean escapeProcessing )
{
doesEscapeProcessing = escapeProcessing;
}
}