com.sciapp.table.db
Class DefaultPivotTableModel

java.lang.Object
  javax.swing.table.AbstractTableModel
      com.sciapp.table.db.AbstractDatabaseTableModel
          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