Copyright © 2005 Scientific Applications. All Rights Reserved.

com.sciapp.table.db
Class DefaultPivotTableModel

java.lang.Object
  extended by javax.swing.table.AbstractTableModel
      extended by com.sciapp.table.db.AbstractDatabaseTableModel
          extended by com.sciapp.table.db.DefaultPivotTableModel
All Implemented Interfaces:
CacheableTableModel, DatabaseTableModel, PivotTableModel, Serializable, TableModel
Direct Known Subclasses:
MSAccessPivotTableModel

public class DefaultPivotTableModel
extends AbstractDatabaseTableModel
implements PivotTableModel

DefaultPivotTableModel is the default PivotTableModel implementation to use for cross tab queries. You need to supply DefaultPivotTableModel with two database table definitions in its sole constructor. The first (nonPivotDefinition) defines the non-pivot column(s) to display. These are placed to the left of the model. The second (functionDefinition) defines the sql group function to evaluate. In this definition, the getColumnName method refers to the name of the column as it is shown on the table, while its getDatabaseColumn method should contain the sql group function that will contain the database column in parentheses.

Example:

Suppose we have the database table with columns (id, company, city, sales, tax, orderDate).

DefaultDatabaseTableDefinition nonPivot;
nonPivot = new DefaultDatabaseTableDefinition(new String[]{"Company Name", "City"}, null, new String[] {"company", "city"});
DefaultDatabaseTableDefinition functions;
functions = new DefaultDatabaseTableDefinition(new String[]{"Yearly sales", "Yearly tax"}, new Class[]{Integer.class, Integer.class}, new String[] { "sum(sales)", "sum(tax)" });
Supposing table is the database table and c the database connection:
DefaultPivotTableModel ptm = new DefaultPivotTableModel(c, nonPivot, functions, "year(orderDate)", table);

If you have more than one function in your function definition, you can also use a GroupTableHeader:
//table is an AdvancedJTable
GroupTableHeader groupHeader = (GroupTableHeader) table.getTableHeader();
ptm.prepareGroupTableHeader(groupHeader);

See Also:
Serialized Form

Nested Class Summary
 class DefaultPivotTableModel.SQLFilterPivotConverter
          SQLFilterConverter should take care of pivot columns.
 
Field Summary
protected  DatabaseTableDefinition aggregateDefinition
          the database definition for all the columns.
protected  Connection c
          The current connection to the database
protected  SQLFilterConverter converter
          utility class that converts a TableFilter to an sql 'where' clause.
protected  boolean cursors
          boolean indicating if a cursor will be used to fetch data from the database.
protected  MeasureDefinition functionDefinition
          the database definition for the function columns.
protected  String havingSQL
          a string which is used to hold the havingclause of the sql statement
protected  String joinStatement
          a string representing the table or tables and the criteria (if any) that will be used to fetch the database data.
protected  DatabaseTableDefinition nonPivotDefinition
          the database definition for the columns that are not pivoted.
protected  String orderSQL
          a string which is used to hold the order byclause of the sql statement
protected  String[] pivotColumns
          the pivot columns.
protected  PivotDefinition pivotDefinition
          the pivot definition
protected  String pivotStatement
          a string representing the table or tables and the criteria (if any) that will be used to fetch the pivot values.
protected  Object[] pivotValues
          the pivot values.
protected  String whereSQL
          a string which is used to hold the where clause of the sql statement
 
Fields inherited from class com.sciapp.table.db.AbstractDatabaseTableModel
cache, dbFilter, dbSorter, rangedModel, rowCount
 
Fields inherited from class javax.swing.table.AbstractTableModel
listenerList
 
Constructor Summary
DefaultPivotTableModel(Connection c, DatabaseTableDefinition nonPivotDefinition, DatabaseTableDefinition functionDefinition, String pivotColumn, String table)
          Constructs a DefaultPivotTableModel.
DefaultPivotTableModel(Connection c, DatabaseTableDefinition nonPivotDefinition, MeasureDefinition functionDefinition, PivotDefinition pivotDefinition, String joinStatement, String pivotStatement)
          Constructs a DefaultPivotTableModel.
 
Method Summary
 void fireTableChanged(TableModelEvent e)
          Forwards the given notification event to all TableModelListeners that registered themselves as listeners for this table model.
 Class getColumnClass(int column)
          Returns Object.class regardless of columnIndex.
 int getColumnCount()
          Returns the number of columns in the model.
 String getColumnName(int column)
          Returns a default name for the column using spreadsheet conventions: A, B, C, ... Z, AA, AB, etc. If column cannot be found, returns an empty string.
 String[] getPivotColumns()
          Returns the pivot columns.
 int getRowCount()
          Returns the number of rows in the model. A JTable uses this method to determine how many rows it should display. This method should be quick, as it is called frequently during rendering.
 Object getValueAt(int row, int column)
          Returns the value for the cell at columnIndex and rowIndex.
protected  void initColumns()
          Initializes the DefaultPivotTableModel.
 boolean isValueCached(int row, int column)
          Determines whether the value at the specified cell has been evaluated.
 void orderByClause()
          This method is called whenever sorting is requested.
 void prepareGroupTableHeader(GroupTableHeader groupableHeader)
          Utility method that creates the necessary group table headers according to the definition of this pivot table model.
 int readRowCount()
          Reads and returns the row count from the underlying data source.
protected  String readRowCountStatement()
          Retrieves the sql statement for the readRowCount method.
 String[] retrievePivotColumns(String pivotColumn)
          Retrieves the pivot columns from the underlying data source.
protected  String retrievePivotColumnsStatement(String pivotColumn)
          Retrieves the sql statement for the retrievePivotColumns method.
 List retrieveRows(int from, int to)
          Fetches multiple rows from the underlying data source.
protected  String retrieveRowsStatement(int from, int to)
          Retrieves the sql statement for the retrieveRows method.
 void setCursors(boolean useCursors)
          Tells DefaultPivotTableModel to use cursors when fetching a range of rows from the database.
 void whereClause()
          This method is called whenever filtering is requested.
 
Methods inherited from class com.sciapp.table.db.AbstractDatabaseTableModel
getCache, getFilterTableModel, getSortTableModel, getUncachedRows, isCountCached, isRangedModel, retrieveFromDB, retrieveFromDB, setCache, setRangedModel
 
Methods inherited from class javax.swing.table.AbstractTableModel
addTableModelListener, findColumn, fireTableCellUpdated, fireTableDataChanged, fireTableRowsDeleted, fireTableRowsInserted, fireTableRowsUpdated, fireTableStructureChanged, getListeners, getTableModelListeners, isCellEditable, removeTableModelListener, setValueAt
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 
Methods inherited from interface javax.swing.table.TableModel
addTableModelListener, isCellEditable, removeTableModelListener, setValueAt
 

Field Detail

pivotStatement

protected String pivotStatement
a string representing the table or tables and the criteria (if any) that will be used to fetch the pivot values.


pivotDefinition

protected PivotDefinition pivotDefinition
the pivot definition


pivotColumns

protected String[] pivotColumns
the pivot columns.


pivotValues

protected Object[] pivotValues
the pivot values.


converter

protected SQLFilterConverter converter
utility class that converts a TableFilter to an sql 'where' clause.


nonPivotDefinition

protected DatabaseTableDefinition nonPivotDefinition
the database definition for the columns that are not pivoted.


functionDefinition

protected MeasureDefinition functionDefinition
the database definition for the function columns.


aggregateDefinition

protected DatabaseTableDefinition aggregateDefinition
the database definition for all the columns.


cursors

protected boolean cursors
boolean indicating if a cursor will be used to fetch data from the database.


c

protected transient Connection c
The current connection to the database


joinStatement

protected String joinStatement
a string representing the table or tables and the criteria (if any) that will be used to fetch the database data.


whereSQL

protected String whereSQL
a string which is used to hold the where clause of the sql statement


orderSQL

protected String orderSQL
a string which is used to hold the order byclause of the sql statement


havingSQL

protected String havingSQL
a string which is used to hold the havingclause of the sql statement

Constructor Detail

DefaultPivotTableModel

public DefaultPivotTableModel(Connection c,
                              DatabaseTableDefinition nonPivotDefinition,
                              DatabaseTableDefinition functionDefinition,
                              String pivotColumn,
                              String table)
Constructs a DefaultPivotTableModel.

Parameters:
c - the database connection.
nonPivotDefinition - these columns will appear on the left side of the table.
functionDefinition - these columns will appear next.
pivotColumn - the pivot column
table - the table of the crosstab query

DefaultPivotTableModel

public DefaultPivotTableModel(Connection c,
                              DatabaseTableDefinition nonPivotDefinition,
                              MeasureDefinition functionDefinition,
                              PivotDefinition pivotDefinition,
                              String joinStatement,
                              String pivotStatement)
Constructs a DefaultPivotTableModel.

Parameters:
c - the database connection.
nonPivotDefinition - these columns will appear on the left side of the table.
functionDefinition - these columns will appear next.
pivotDefinition - the pivot definition.
joinStatement - a string epresenting the table or tables and the criteria (if any) that will be used to fetch the database data.
pivotStatement - a string epresenting the table or tables and the criteria (if any) that will be used to fetch the pivot columns.
Method Detail

fireTableChanged

public void fireTableChanged(TableModelEvent e)
Forwards the given notification event to all TableModelListeners that registered themselves as listeners for this table model.

Overrides:
fireTableChanged in class AbstractDatabaseTableModel
Parameters:
e - the event to be forwarded
See Also:
AbstractTableModel.addTableModelListener(javax.swing.event.TableModelListener), TableModelEvent, EventListenerList

getColumnClass

public Class getColumnClass(int column)
Returns Object.class regardless of columnIndex.

Specified by:
getColumnClass in interface TableModel
Specified by:
getColumnClass in class AbstractDatabaseTableModel
Parameters:
column - the column being queried
Returns:
the Object.class

getColumnCount

public int getColumnCount()
Returns the number of columns in the model. A JTable uses this method to determine how many columns it should create and display by default.

Specified by:
getColumnCount in interface TableModel
Returns:
the number of columns in the model
See Also:
TableModel.getRowCount()

getColumnName

public String getColumnName(int column)
Returns a default name for the column using spreadsheet conventions: A, B, C, ... Z, AA, AB, etc. If column cannot be found, returns an empty string.

Specified by:
getColumnName in interface TableModel
Specified by:
getColumnName in class AbstractDatabaseTableModel
Parameters:
column - the column being queried
Returns:
a string containing the default name of column

getPivotColumns

public String[] getPivotColumns()
Returns the pivot columns.

Returns:
the pivot columns.

getRowCount

public int getRowCount()
Returns the number of rows in the model. A JTable uses this method to determine how many rows it should display. This method should be quick, as it is called frequently during rendering.

Specified by:
getRowCount in interface TableModel
Overrides:
getRowCount in class AbstractDatabaseTableModel
Returns:
the number of rows in the model
See Also:
TableModel.getColumnCount()

getValueAt

public Object getValueAt(int row,
                         int column)
Returns the value for the cell at columnIndex and rowIndex.

Specified by:
getValueAt in interface TableModel
Overrides:
getValueAt in class AbstractDatabaseTableModel
Parameters:
row - the row whose value is to be queried
column - the column whose value is to be queried
Returns:
the value Object at the specified cell

initColumns

protected void initColumns()
Initializes the DefaultPivotTableModel. The aggregateDefinition is created by iterating through the nonPivotDefinition, functionDefinition and pivot columns.


isValueCached

public boolean isValueCached(int row,
                             int column)
Determines whether the value at the specified cell has been evaluated.

Specified by:
isValueCached in interface CacheableTableModel
Overrides:
isValueCached in class AbstractDatabaseTableModel
Parameters:
row - the cell's row index
column - the cell's column index
Returns:
true if the cell value at (row, column) has already been evaluated.

orderByClause

public void orderByClause()
This method is called whenever sorting is requested. Preferrably, this method should reconstruct the sql select query according to the sorting columns of the internal SortTableModel instance.

Specified by:
orderByClause in class AbstractDatabaseTableModel

prepareGroupTableHeader

public void prepareGroupTableHeader(GroupTableHeader groupableHeader)
Utility method that creates the necessary group table headers according to the definition of this pivot table model.

Parameters:
groupableHeader - the group tableheader to configure appropriately.

readRowCount

public int readRowCount()
Reads and returns the row count from the underlying data source.

Specified by:
readRowCount in interface DatabaseTableModel
Returns:
the row count

readRowCountStatement

protected String readRowCountStatement()
Retrieves the sql statement for the readRowCount method.

Returns:
the sql statement as a string

retrievePivotColumns

public String[] retrievePivotColumns(String pivotColumn)
Retrieves the pivot columns from the underlying data source. Note that the pivot column need not be a column name, but also a column function. e.g. year(orderDate)

Specified by:
retrievePivotColumns in interface PivotTableModel
Parameters:
pivotColumn - a column or a column function used as the pivot.
Returns:
the pivot columns.

retrievePivotColumnsStatement

protected String retrievePivotColumnsStatement(String pivotColumn)
Retrieves the sql statement for the retrievePivotColumns method.

Parameters:
pivotColumn - the pivot column
Returns:
the sql statement as a string

retrieveRows

public List retrieveRows(int from,
                         int to)
Fetches multiple rows from the underlying data source. The following condition must be true: from >= to. The List returned must contain the row elements starting at from. However, the List may contain less elements than those specified in the range.

Specified by:
retrieveRows in interface DatabaseTableModel
Parameters:
from - the starting row
to - the ending row
Returns:
the retrieved rows

retrieveRowsStatement

protected String retrieveRowsStatement(int from,
                                       int to)
Retrieves the sql statement for the retrieveRows method.

Parameters:
from - the starting row
to - the ending row
Returns:
the sql statement as a string

setCursors

public void setCursors(boolean useCursors)
Tells DefaultPivotTableModel to use cursors when fetching a range of rows from the database.

Parameters:
useCursors - true if cursors are to be used.

whereClause

public void whereClause()
This method is called whenever filtering is requested. Preferrably, this method should reconstruct the sql select query according to the table filter of the internal FilterTableModel instance.

Specified by:
whereClause in class AbstractDatabaseTableModel

Copyright © 2005 Scientific Applications. All Rights Reserved.