DatabaseTableModel

Overview

Package

Class

Tree

Deprecated

Index

Help

JIDE 3.5.15

PREV CLASS NEXT CLASS

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

com.jidesoft.database
Class DatabaseTableModel

java.lang.Object
  javax.swing.table.AbstractTableModel
      com.jidesoft.database.DatabaseTableModel

All Implemented Interfaces:: AutoFilterTableHeaderAdapter, AutoFilterTableModel, ContextSensitiveTableModel, IFilterableTableModel, ISortableTableModel, Serializable, TableModel

Direct Known Subclasses:: CrudDatabaseTableModel

public class DatabaseTableModel
extends AbstractTableModel
implements ISortableTableModel, IFilterableTableModel, ContextSensitiveTableModel
extends AbstractTableModel
implements ISortableTableModel, IFilterableTableModel, ContextSensitiveTableModel

DatabaseTableModel uses an existing database connection and a SQL from statement to query the record from a database and display them in a table model. The from statement could has one database table or multiple database tables using join statement. The columns in this table model are decided by an optional SQL select statement you specified. If you didn't specify, we will use "*" which means all the available fields.

By default, we use ResultSet.TYPE_SCROLL_SENSITIVE to create a SQL statement to do the query. If your database doesn't support this, we will fall back to TYPE_INSENSITIVE then TYPE_FORWARD_ONLY. If so, the performance could be an issue when you scroll down many pages as we have to skip all records one by one. For

The main feature of this DatabaseTableModel is not just to display the database table but also to sort and filter the data using the built-in sort and filter features provided by the database, however, using the APIs provided by the SortableTableModel and FilterableTableModel provided in JIDE Grids. Usually you get a better performance using the database to do the sorting and filtering.

In DatabaseTableModel, we would try our best to set appropriate cell renderer by using the information from the ResultSetMetaData.

The folowing meta data information will be considered in ConverterContext:

 boolean isCurrency(int column) throws SQLException; // use default currency converter if isConsiderCurrencyField() returns true
 int getScale(int column) throws SQLException;       // show the digit number after decimal point
 int getPrecision(int column) throws SQLException;   // show the integer part no larger than precision

The following meta data information will be considered in validate(int, Object):

 int isNullable(int column) throws SQLException;     // check it in validateNullable(int, Object)
 boolean isSigned(int column) throws SQLException;   // check it in validateSigned(int, Object)
 int getPrecision(int column) throws SQLException;   // check it in validatePrecision(int, Object)

Other:

 boolean isReadOnly(int column) throws SQLException; // make it not editable in isCellEditable(int, int)

The following meta data information will not be considered:

 int getColumnDisplaySize(int column) throws SQLException; // no way to set preferred width to table from table model
 int getColumnCount() throws SQLException;
 boolean isAutoIncrement(int column) throws SQLException;
 boolean isSearchable(int column) throws SQLException;
 String getColumnLabel(int column) throws SQLException;
 String getColumnName(int column) throws SQLException;
 String getSchemaName(int column) throws SQLException;
 String getTableName(int column) throws SQLException;
 String getCatalogName(int column) throws SQLException;
 int getColumnType(int column) throws SQLException;
 String getColumnTypeName(int column) throws SQLException;
 String getColumnClassName(int column) throws SQLException;
 boolean isWritable(int column) throws SQLException; // it's database access related
 boolean isDefinitelyWritable(int column) throws SQLException; // it's database access related
 boolean isCaseSensitive(int column) throws SQLException; // searching case sensitive

See Also:: Serialized Form

Nested Class Summary

Nested classes/interfaces inherited from interface com.jidesoft.grid.ISortableTableModel
`ISortableTableModel.SortItem`

Nested classes/interfaces inherited from interface com.jidesoft.grid.IFilterableTableModel
`IFilterableTableModel.FilterItem`

Nested classes/interfaces inherited from interface com.jidesoft.grid.AutoFilterTableHeaderAdapter
`AutoFilterTableHeaderAdapter.FilterTitleFormatter`

Field Summary
`protected Connection`	`_connection`
`protected ConverterContext[]`	`_converterContexts` converter context for columns which was configured on `refreshData(com.jidesoft.grid.SortItemSupport, com.jidesoft.grid.FilterItemSupport)` After configuration, this field will be used at `getConverterContextAt(int, int)` You can either override `getConverterContextAt(int, int)` or `configureConverterContexts()` to gain more control on the cell renderers.
`protected FilterItemSupport`	`_filterItemSupport`
`protected String`	`_fromStatement`
`protected String`	`_preStatement`
`protected ResultSetTableModel`	`_resultSetTableModel`
`protected String`	`_selectStatement`
`protected SortItemSupport`	`_sortItemSupport`
`protected Statement`	`_statement`

Fields inherited from class javax.swing.table.AbstractTableModel
`listenerList`

Fields inherited from interface com.jidesoft.grid.ISortableTableModel
`SORT_PRIORITY_FIFO, SORT_PRIORITY_FILO`

Fields inherited from interface com.jidesoft.grid.IFilterableTableModel
`ALL_COLUMNS, ANY_COLUMNS, IDENTIFIER_ALL_COLUMNS, IDENTIFIER_ANY_COLUMNS`

Constructor Summary
`DatabaseTableModel(Connection connection, String fromStatement)` Creates a DatabaseTableModel.
`DatabaseTableModel(Connection connection, String selectStatement, String fromStatement)` Creates a DatabaseTableModel.
`DatabaseTableModel(Connection connection, String selectStatement, String fromStatement, boolean updatable)` Creates a DatabaseTableModel.
`DatabaseTableModel(Connection connection, String preStatement, String selectStatement, String fromStatement)` Creates a DatabaseTableModel.
`DatabaseTableModel(Connection connection, String preStatement, String selectStatement, String fromStatement, boolean updatable)` Creates a DatabaseTableModel.

Method Summary
`void`	`addFilter(Filter filter)` Adds a filter to all columns.
`void`	`addFilter(IFilterableTableModel.FilterItem filterItem)` Adds a FilterItem.
`void`	`addFilter(int column, Filter filter)` Adds a filter to the specified column.
`void`	`addFilterableTableModelListener(FilterableTableModelListener l)` Adds a listener to the list that's notified each time a change to the filter occurs.
`void`	`addSortListener(SortListener l)` Adds the specified listener to receive SortEvents pane events from this SortableTableModel.
`protected Statement`	`adjustStatement(Statement statement, String sql)` This method is called before every single sql queries to give subclass a chance to override to create their own statement.
`void`	`clearFilters()` Removes all filters from all columns.
`void`	`close()`
`void`	`commit()`
`protected void`	`configureConverterContexts()` Configure converter context for columns by the information contained in meta data.
`protected String`	`convertElementToString(Filter filter, Object value)` Converts the value defined in the filter to String so that it can be used in SQL.
`protected String`	`createCountStatement(FilterItemSupport filterItemSupport)` Creates the sql statement to find out the row count in the result set.
`protected String`	`createOrderStatement(SortItemSupport sortItemSupport)` Create order statement from sort item support.
`protected String`	`createPossibleValuesStatement(int columnIndex)`
`protected Statement`	`createReadonlyStatement()` Creates a read-only statement.
`protected ResultSetTableModel`	`createResultSetTableModel(ResultSet resultSet, int rowCount)`
`protected StringBuffer`	`createSqlStatement(String preStatement, String selectStatement, String fromStatement, SortItemSupport sortItemSupport, FilterItemSupport filterItemSupport)` Creates a full SQL statement.
`protected Statement`	`createStatement()` Creates a Statement.
`protected String`	`createWhereStatement(FilterItemSupport filterItemSupport)`
`void`	`fireFilterAdded(int column, Filter filter)`
`void`	`fireFilterChanged(FilterableTableModelEvent e)` Forwards the given notification event to all `FilterableTableModelListeners` that registered themselves as listeners for this table model.
`void`	`fireFilterRemoved(int column, Filter filter)`
`void`	`fireSortEvent()` Fires sort event.
`void`	`fireSortingEvent()` Fires sort event.
`int`	`getActualRowAt(int row)` Gets the actual row.
`Class<?>`	`getCellClassAt(int rowIndex, int columnIndex)` Gets the type at cell (row, column).
`Class<?>`	`getColumnClass(int columnIndex)`
`int`	`getColumnCount()`
`Map<String,String>`	`getColumnMapping()`
`String`	`getColumnName(int column)`
`int`	`getColumnSortRank(int column)` In the case of sort by multiple columns, this method will return the rank of this column within all sorted columns.
`ConverterContext`	`getConverterContextAt(int rowIndex, int columnIndex)` Gets the converter context at cell (row, column).
`DynamicTableFilter[]`	`getDynamicTableFilters(int modelIndex)` Gets all the `DynamicTableFilter`s.
`EditorContext`	`getEditorContextAt(int rowIndex, int columnIndex)` Gets the editor context at cell (row, column).
`FilterableTableModelListener[]`	`getFilterableTableModelListeners()` Returns an array of all the FilterableTableModel listeners registered on this filter.
`Icon`	`getFilterIcon(int column)` Gets the filter icon for a particular column.
`List<IFilterableTableModel.FilterItem>`	`getFilterItems()` Gets all the FilterItems added to this FilterableTableModel.
`Filter[]`	`getFilters(int column)` Gets the filters for the specified column.
`AutoFilterTableHeaderAdapter.FilterTitleFormatter`	`getFilterTitleFormatter(int column)` Gets the formatter that will format the title for the AutoFilterTableHeader.
`ListCellRenderer`	`getListCellRenderer(int column)` Gets the list cell renderer for the drop down filter list.
`int[]`	`getMasterSortColumns()` Gets the master sort columns.
`int`	`getMaximumSortColumns()` Gets the maximum columns can be sorted at once.
`Object[]`	`getPossibleValues(int columnIndex, Comparator comparator)` Gets all possible values of the table model.
`Object[]`	`getPossibleValuesAndConverters(int columnIndex, Comparator comparator)`
`int`	`getRowCount()`
`int`	`getSortedRowAt(int actualRow)` Gets the visual row.
`List<ISortableTableModel.SortItem>`	`getSortingColumns()` Gets the sorting columns.
`SortListener[]`	`getSortListeners()` Returns an array of all the `SortListener`s added to this `SortableTableModel` with `addSortListener`.
`int`	`getSortPriority()` Gets the sort priority.
`StringConverter`	`getTitleConverter(int column)` Gets the title converter to convert the column name.
`Object`	`getValueAt(int rowIndex, int columnIndex)`
`boolean`	`hasFilter()` Checks if the `FilterableTableModel` has any filters.
`boolean`	`hasFilter(int columnIndex)` Checks if the `FilterableTableModel` has any filters on the specified column.
`protected void`	`initDatabase()`
`void`	`invalidateCache()`
`void`	`invalidateCache(int row)`
`boolean`	`isAdjusting()` Checks if the FilterableTableModel is adjusting.
`boolean`	`isAllowCustomFilter(int column)` Checks if the `AutoFilterTableHeader` allows custom filter.
`boolean`	`isAllowMultipleValues(int column)` Checks if the `AutoFilterTableHeader` allows multiple values as the filter for the column index.
`boolean`	`isAndMode()` Sets the logic of filters on the same column.
`boolean`	`isCellEditable(int rowIndex, int columnIndex)`
`boolean`	`isColumnAscending(int column)` Checks if the column is sorted ascendingly.
`boolean`	`isColumnAutoFilterable(int column)` Checks if the column is auto-filterable.
`boolean`	`isColumnFilterable(int column)` Checks if the column is filterable for the filters that are added to `IFilterableTableModel.ALL_COLUMNS`.
`boolean`	`isColumnSortable(int column)` Check if a certain column is sortable.
`boolean`	`isColumnSorted(int column)` Checks if the column is sorted.
`boolean`	`isColumnVisible(int column)` Checks if the column is visible.
`boolean`	`isConsiderCurrencyField()` Get the flag if you want to consider the isCurrency field in metaData.
`boolean`	`isFilteringPaused()` Not supported in DatabaseTableModel.
`boolean`	`isFiltersApplied()` Checks if the filters are in effect.
`boolean`	`isMultiColumnSortable()` Does this table allow sort by multiple columns.
`boolean`	`isSameConverterAt(int columnIndex)` Check if each converter in this column for each row is the same.
`boolean`	`isSortable()` Checks if the sortable table model is sortable.
`boolean`	`isUpdatable()`
`boolean`	`isUseTableCellRenderer(int column)` Checks if the table cell renderer will be used for the list.
`boolean`	`isValuePredetermined(int column)` Checks if the column is type-filterable.
`protected StringBuffer`	`prepareWhereStatement(IFilterableTableModel.FilterItem filterItem)` Convert the existing filter to where statement string.
`void`	`refresh()` Reapply all filters after they are changed.
`void`	`refreshData(SortItemSupport sortItemSupport, FilterItemSupport filterItemSupport)` Resubmit the SQL statement.
`void`	`removeAllFilters()` Removes all filters that are added using `IFilterableTableModel.addFilter(Filter)`.
`void`	`removeAllFilters(int column)` Removes all filters from the specified column.
`void`	`removeFilter(Filter filter)` Removes the filter from all columns.
`void`	`removeFilter(IFilterableTableModel.FilterItem filterItem)` Removes the filter item.
`void`	`removeFilter(int column, Filter filter)` Removes the filter from the specified column.
`void`	`removeFilterableTableModelListener(FilterableTableModelListener l)` Removes a listener from the list that's notified each time a change to the filter occurs.
`void`	`removeSortListener(SortListener l)` Removes the specified SortListener so that it no longer receives SortEvents from this SortableTableModel .
`void`	`reset()` Resets.
`void`	`resort()` Resort the table.
`void`	`reverseColumnSortOrder(int column)` Reverses the sort order of the column.
`void`	`setAdjusting(boolean adjusting)` Sets the FilterableTableModel to adjusting mode.
`void`	`setAndMode(boolean andMode)` Sets the logic among the filters.
`void`	`setColumnMapping(Map<String,String> columnMapping)` Sets the column name mapping.
`void`	`setColumnSortable(int column, boolean sortable)` Sets a column sortable or not sortable.
`void`	`setConsiderCurrencyField(boolean considerCurrencyField)` Set the flag if you want to consider the isCurrency field in metaData.
`void`	`setFilteringPaused(boolean pause)` Not supported in DatabaseTableModel.
`void`	`setFiltersApplied(boolean apply)` Applies or unapplies the filters.
`void`	`setMasterSortColumns(int[] masterSortColumns)` Sets the master sort column.
`void`	`setMaximumSortColumns(int maximumSortColumns)` Set the maximum number of columns that can be sorted at once.
`void`	`setMultiColumnSortable(boolean multiColumnSortable)` Set the value if this table allows sort by multiple columns.
`void`	`setSortable(boolean sortable)` Sets the table model sortable.
`void`	`setSortingColumns(List<ISortableTableModel.SortItem> list)` Sets the soring columns.
`void`	`setSortPriority(int sortPriority)` Sets the sort priority.
`void`	`setUpdatable(boolean updatable)`
`void`	`setValueAt(Object aValue, int rowIndex, int columnIndex)`
`protected void`	`sort()`
`void`	`sortColumn(int column)` Sort the column, equals to sortColumn(column, false).
`void`	`sortColumn(int column, boolean reset)` If reset is true, it will remove all existing sort-by columns and only sorts by `column`.
`void`	`sortColumn(int column, boolean reset, boolean ascending)` Sorts a column.
`void`	`toggleSortOrder(int column, boolean extend)` Toggles the sort order on the specified column.
`void`	`unsortColumn(int column)` Unsorts the column.
`boolean`	`validate(int columnIndex, Object value)` The interface for the table to check if the input value satisfy the request from the meta data of the database.
`boolean`	`validateNullable(int columnIndex, Object value)` The interface for the table to check if the input value is null and meta data prohibits null value.
`boolean`	`validatePrecision(int columnIndex, Object value)` The interface for the table to check if the input value exceeds the precision meta data allows.
`boolean`	`validateSigned(int columnIndex, Object value)` The interface for the table to check if the input value is negative and meta data prohibits negative value.

Methods inherited from class javax.swing.table.AbstractTableModel
`addTableModelListener, findColumn, fireTableCellUpdated, fireTableChanged, fireTableDataChanged, fireTableRowsDeleted, fireTableRowsInserted, fireTableRowsUpdated, fireTableStructureChanged, getListeners, getTableModelListeners, removeTableModelListener`

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, removeTableModelListener`

Field Detail

_resultSetTableModel

protected ResultSetTableModel _resultSetTableModel

_connection

protected Connection _connection

_statement

protected Statement _statement

_fromStatement

protected String _fromStatement

_selectStatement

protected String _selectStatement

_preStatement

protected String _preStatement

_converterContexts

protected ConverterContext[] _converterContexts

converter context for columns which was configured on

refreshData(com.jidesoft.grid.SortItemSupport,
 com.jidesoft.grid.FilterItemSupport)

After configuration, this field will be used at getConverterContextAt(int, int)

You can either override getConverterContextAt(int, int) or configureConverterContexts() to gain more control on the cell renderers.

_sortItemSupport

protected SortItemSupport _sortItemSupport

_filterItemSupport

protected FilterItemSupport _filterItemSupport

Constructor Detail

DatabaseTableModel

public DatabaseTableModel(Connection connection,
                          String fromStatement)
                   throws SQLException

Creates a DatabaseTableModel.

Parameters:

connection - a database connection.

fromStatement - a SQL from statement. It could be

FROM first_table
FROM first_table, second_table
FROM first_table LEFT JOIN second_table
FROM first_table INNER JOIN second_table

Throws:

SQLException - if a database related error happens. It could be because the connection is closed.

DatabaseTableModel

public DatabaseTableModel(Connection connection,
                          String selectStatement,
                          String fromStatement)
                   throws SQLException

Creates a DatabaseTableModel.

Parameters:

connection - a database connection.

selectStatement - a SQL select statement. If null, we default it to "*". If you use AS statement to change the column name, make sure you also call setColumnMapping(java.util.Map) to set the mapping.

fromStatement - a SQL from statement. It could be

FROM first_table
FROM first_table, second_table
FROM first_table LEFT JOIN second_table
FROM first_table INNER JOIN second_table

Throws:

SQLException - if a database related error happens. It could be because the connection is closed.

DatabaseTableModel

public DatabaseTableModel(Connection connection,
                          String preStatement,
                          String selectStatement,
                          String fromStatement)
                   throws SQLException

Creates a DatabaseTableModel.

Parameters:

connection - a database connection.

preStatement - a pre-clause. It will appear at the beginning of all sql statements. For example, WITH abc as ( select dummy from dual ) select * from abc. "WITH abc as ( select dummy from dual )" will be the pre statement. "abc" will be the from statement.

selectStatement - a SQL SELECT statement. Do not include the SELECT word in the statement. Just include everything after the SELECT. If null, we default it to "*". If you use AS statement to change the column name, make sure you also call setColumnMapping(java.util.Map) to set the mapping.

fromStatement - a SQL FROM clause. Do not include the FROM word in the statement. Just include everything after the FROM. It could be

FROM first_table
FROM first_table, second_table
FROM first_table LEFT JOIN second_table
FROM first_table INNER JOIN second_table

Throws:

SQLException - if a database related error happens. It could be because the connection is closed.

DatabaseTableModel

public DatabaseTableModel(Connection connection,
                          String selectStatement,
                          String fromStatement,
                          boolean updatable)
                   throws SQLException

Creates a DatabaseTableModel.

Parameters:

connection - a database connection.

fromStatement - a SQL FROM clause. Do not include the FROM word in the statement. Just include everything after the FROM. It could be

FROM first_table
FROM first_table, second_table
FROM first_table LEFT JOIN second_table
FROM first_table INNER JOIN second_table

updatable - if the table model is updatable (or editable)

Throws:

SQLException - if a database related error happens. It could be because the connection is closed.

DatabaseTableModel

public DatabaseTableModel(Connection connection,
                          String preStatement,
                          String selectStatement,
                          String fromStatement,
                          boolean updatable)
                   throws SQLException

Creates a DatabaseTableModel.

Parameters:

connection - a database connection.

fromStatement - a SQL FROM clause. Do not include the FROM word in the statement. Just include everything after the FROM. It could be

FROM first_table
FROM first_table, second_table
FROM first_table LEFT JOIN second_table
FROM first_table INNER JOIN second_table

updatable - if the table model is updatable (or editable)

Throws:

SQLException - if a database related error happens. It could be because the connection is closed.

Method Detail

initDatabase

protected void initDatabase()
                     throws SQLException

Throws:: SQLException

setColumnMapping

public void setColumnMapping(Map<String,String> columnMapping)

Sets the column name mapping. It maps from the new column name to the original column name as specified in the SQL select statement. For example if the select statement is "ID, CategoryName as Category, ProductName as Product, Amount, OrderDate as Date", the mapping will be like this.

 Map mapping = new HashMap();
 mapping.put("CATEGORYNAME", "CATEGORY");
 mapping.put("PRODUCTNAME", "PRODUCT");
 mapping.put("ORDERDATE", "DATE");

Please note it has to be all upper cases.

Parameters:: columnMapping - the column name mapping.

getColumnMapping

public Map<String,String> getColumnMapping()

getColumnCount

public int getColumnCount()

Specified by:: getColumnCount in interface TableModel

getColumnName

public String getColumnName(int column)

Specified by:: getColumnName in interface TableModel
Overrides:: getColumnName in class AbstractTableModel

getConverterContextAt

public ConverterContext getConverterContextAt(int rowIndex,
                                              int columnIndex)

Description copied from interface: ContextSensitiveTableModel

Gets the converter context at cell (row, column).

For a special row index like -1, please return the default converter context for the entire column if there is any.

Specified by:: getConverterContextAt in interface ContextSensitiveTableModel

Parameters:: rowIndex - the row index; columnIndex - the column index
Returns:: converter context

getEditorContextAt

public EditorContext getEditorContextAt(int rowIndex,
                                        int columnIndex)

Description copied from interface: ContextSensitiveTableModel

Gets the editor context at cell (row, column).

For a special row index like -1, please return the default editor context for the entire column if there is any.

Specified by:: getEditorContextAt in interface ContextSensitiveTableModel

Parameters:: rowIndex - the row index; columnIndex - the column index
Returns:: editor context

getCellClassAt

public Class<?> getCellClassAt(int rowIndex,
                               int columnIndex)

Description copied from interface: ContextSensitiveTableModel

Gets the type at cell (row, column).

For a special row index like -1, please return the default column class for the entire column if there is any.

Specified by:: getCellClassAt in interface ContextSensitiveTableModel

Parameters:: rowIndex - the row index; columnIndex - the column index
Returns:: type

getColumnClass

public Class<?> getColumnClass(int columnIndex)

Specified by:: getColumnClass in interface TableModel
Overrides:: getColumnClass in class AbstractTableModel

getRowCount

public int getRowCount()

Specified by:: getRowCount in interface TableModel

getValueAt

public Object getValueAt(int rowIndex,
                         int columnIndex)

Specified by:: getValueAt in interface TableModel