borland Packages Class Hierarchy dx.sql.dataset Package
java.lang.Object +----com.borland.dx.dataset.ReadRow +----com.borland.dx.dataset.ReadWriteRow +----com.borland.dx.dataset.DataSet +----com.borland.dx.dataset.StorageDataSet +----com.borland.dx.sql.dataset.QueryDataSet
Properties Methods Event Listeners
Implements AccessListener, ColumnDesigner, Designable, MasterNavigateListener, StatusListener, Serializable, EventListener
The QueryDataSet
class is an extension of its superclass (StorageDataSet
) and provides functionality to run a query statement (with or without parameters) against a table in a SQL database.
In any application that uses the QueryDataSet
, the following components are also required:
Database
component to handle
the JDBC connection to the SQL database
QueryDescriptor
object
to store the query properties
The data contained in a QueryDataSet
is the result of the most recent query. The "result set"
from the execution of the query is stored in the QueryDataSet
, which allows for much greater
flexibility in navigation of the resulting data. You specify how the data is loaded into the QueryDataSet
(asynchronously, as needed, etc.) by specifying its loadOption
property (stored in the associated QueryDescriptor
). The QueryDataSet
inherits the maxRows
property which allows
you to set the maximum number of rows that can be initially stored in the QueryDataSet
from a query
execution.
Once the data is stored in the QueryDataSet
, you manipulate it and connect it to UI controls in
exactly the same way as you would other StorageDataSet
components, without regard to which component is storing the data.
The QueryDataSet
component uses the QueryProvider
and QueryResolver
to perform the data providing and resolving functions.
By default, an attempt is automatically made to make a QueryDataSet
updatable so that changes made to the data it contains can be resolved back to its data source. The query is analyzed and row
identifiers are looked for: one or more columns that uniquely identify each row. This is required to save any changed data
back to the correct row of the original data. If you have not included these columns in your query's SELECT
statement, they are automatically added but the visibilty of such columns is set to "default". This
hides them from any data aware controls that this QueryDataSet
is connected to. You may change the
column's visibility to true if desired.
To prevent the addition of row ID columns and various metadata related properties on DataSet
and Column
components, set this component's metaDataUpdate
property to
MetaDataUpdate.NONE
.
If the rowId analysis fails or metaDataUpdate
is set to NONE
, you can make a QueryDataSet
updatable by setting one or more
of these properties (as applicable):
tableName
property of the StorageDataSet
to the table name that is the
data source of the QueryDataSet
.
rowID
property of the Column
components in the
StorageDataSet
. These row identifier columns are included in the query and the corresponding
columns in the QueryDataSet
are marked hidden by default.
readOnly
property to false (if not already).
To improve QueryDataSet
performance on data retrieval,
ResultSet
, disabling the metadata discovery mechanisms for fetch operations can make a big performance improvement. Specifically, set the
StorageDataSet.MetaDataUpdate
property to MetaDataUpdate.NONE
.
StorageDataSet.TableName
property to the table name.
Column.RowId
property for the columns that uniquely and efficiently identify a row.
QueryDataSet
only performs the metadata discovery operations the first time a query is run.
LoadOption
property on the QueryDataSet
or ProcedureDataSet
to
Load.ASYNCHRONOUS
or Load.AS_NEEDED
. You can also set this property to Load.UNCACHED
if you
will be reading the data one time in sequential order.
For large result sets, using a DataStore
can improve performance and save
a lot of memory with its caching/persistence support.
Statement caching. By default, DataExpress will cache prepared statements for both queries and stored procedures
if java.sql.Connection.getMetaData().getMaxStatements()
returns a value > 10. You can force statement caching by calling Database.setCacheStatements(true)
. The prepared statements that are cached are not closed until one of the following happens:
query
property) is
changed.
DataSet
component is garbage collected (the statement is closed
in a finalize()
method, QueryDataSet.closeStatement()
,
ProcedureDataSet.closeStatement()
, QueryProvider.closeStatement()
or
ProcedureProvider.closeStatement()
.
To improve performance when performing data inserts, deletes, and updates:
resolver
property to a
QueryResolver
and set the updateMode
property of this QueryResolver
to
UpdateMode.KEY_COLUMNS
. This weakens the optimistic concurrency used, but reduces the
number of parameters set for an update/delete operation.
For each call to Database.saveChanges()
, calls are made to
disable/enable a JDBC drivers autocommit mode. If your application calls
Database.saveChanges()
with the useTransactions
parameter set to
false, then these calls will not be made and the transaction will not be
commited.
By disabling the resetPendingStatus
flag in the
Database.saveChanges()
method, further performance benefits can be achieved. With this
disabled, DataExpress will not clear the RowStatus
state for all
inserted/deleted/updated rows. This is only desirable if you will not be calling saveChanges()
with new edits on the DataSet
without calling refresh()
first.
Note that if transactions are disabled, your application must call
Database.commit()
or Connection.commit()
.
In a master-detail relationship, if you set the fetchAsNeeded
property to true, you must include a WHERE clause in the detail query that matches the detail link column values to the master link column values.
For more information on master-detail relationships, see MasterLinkDescriptor
.
For examples on setting up a master-detail relationship, see "Establishing a master-detail relationship" in the Database Application Developer's Guide.
Column aliases that are specified in a SQL Select statement are supported. The name specified as the alias is stored in the columnName
property and is used to access the DataExpress API. The original data source name is stored in the serverColumnName
property for use when resolving changes back to the data source.
When a query joins two or more tables, it may return several columns that
have the same name (often the columns used to link the tables have the same name). Duplicate column names are handled by appending a number ("EMP_NO", "EMP_No1", etc.). The modified column name is stored in the columnName
property and is used to access the DataExpress API. The original server name is stored in the serverColumnName
property for (later) use when resolving data changes back to the data source.
These properties are also used for column aliases that are specified in SQL statements.
Table aliases
are also supported.
For example, in the following query:
select e.emp_no, e.last_name empl_last, p.last_name, phone_last
from employee e, phone_list p
where e.emp_no = p.emp_no and
e.last_name <> p.last_name
The employee table is assigned the alias "e" and its last_name column is given the alias "empl_last". In multi-table queries, aliases can be useful.
If a query is run against a synonym on an Oracle server, it is dependent on the support of synonyms in the JDBC driver to determine whether the query is updatable.
Queries run against SQL views are supported, however, they may not be resolvable depending on what actions the SQL view performed. For example, if the view simply filters out rows, the server may be able to handle resolving the edits. You should be aware that you risk making edits that cannot later be resolved back to the data source. More likely, you will need to write your own resolver logic to handle this situation.
For examples on using a QueryDataSet
, see "Querying a database" in the Database Application Developer's Guide.
For more information on resolving changes to a QueryDataSet
, see the QueryResolver
component.
public final boolean isAccumulateResults() public final void setAccumulateResults(boolean accumulate)Determines whether the provided data is accumulated over consecutive calls to the
executeQuery()
method (true) or not (false). If this property is disabled, subsequent executeQuery()
calls overwrite the existing DataSet
.
public final Database getDatabase()Read-only property that stores the
Database
object that holds the connection to the SQL server.
public final String getOriginalQueryString()Read-only property that returns the original query string, as specified in the Query String field of the
Query
property editor dialog. The query string may be automatically updated to include columns that form a unique rowID. Use this read accessor to obtain the original, unaltered query string.
public ReadWriteRow getParameterRow()Read-only property that returns the
ReadWriteRow
object that contains the parameters for the query.
DataSet.close()
before providing for another QueryDataSet
.
public void setProvider(Provider provider)Stores the
QueryProvider
associated with this QueryDataSet
component.
When setting this property, only a QueryProvider
is acceptable.
A DataSetException
of NEED_QUERY_PROVIDER
is generated if this property is not set.
public final QueryDescriptor getQuery() public final void setQuery(QueryDescriptor queryDescriptor)Specifies the QueryDescriptor object that stores query properties which make the
QueryDataSet
usable.
public void closeStatement()If
Database.isUseStatementCaching()
returns true, JDBC statements can be cached. By default these statements will be closed during garbage collection. If resources are scarce, the statement can be forced closed by calling this method.
public final void executeQuery()Calls refresh().
You can call the executeQuery()
method without an open DataSet
. However, if the DataSet
is open, executeQuery()
will close the DataSet
, execute the query, then re-open the DataSet
.
public final String getQueryString(StorageDataSet sds)Returns the query string executed against the data source. This query string may have been automatically altered; use
getOriginalQueryString()
to obtain the original, unaltered query string.
public void refetchRow(ReadWriteRow row)Fetches the original row from the data source based on the key field of the row you pass in. For example, if the key field of the row is "foobar", this method fetches the row in the
DataSet
with that key field.
public void refresh()Given that the database and query properties have been set, executes the query and populates the
DataSet
.
public boolean refreshSupported()Returns true.
public void saveChanges(DataSet dataSet)Calls
Database.saveChanges()
with the DataSet
object specified in its parameter.
public boolean saveChangesSupported()Returns true. Used internally by data-aware controls to determine if a
saveChanges()
type operation is supported.
public final void addAccessListener(AccessListener listener) public final void removeAccessListener(AccessListener listener)
public synchronized void addCalcAggFieldsListener(CalcAggFieldsListener listener) public synchronized void removeCalcAggFieldsListener(CalcAggFieldsListener listener)
public synchronized void addCalcFieldsListener(CalcFieldsListener listener) public synchronized void removeCalcFieldsListener(CalcFieldsListener listener)
public void addColumnChangeListener(ColumnChangeListener listener) public synchronized void removeColumnChangeListener(ColumnChangeListener listener)
public final void addDataChangeListener(DataChangeListener listener) public final void removeDataChangeListener(DataChangeListener listener)
public void addEditListener(EditListener listener) public synchronized void removeEditListener(EditListener listener)
public final synchronized void addLoadListener(LoadListener listener) public final synchronized void removeLoadListener(LoadListener listener)
public final void addMasterNavigateListener(MasterNavigateListener listener) public final void removeMasterNavigateListener(MasterNavigateListener listener)
public final void addNavigationListener(NavigationListener listener) public final void removeNavigationListener(NavigationListener listener)
public final void addOpenListener(OpenListener listener) public final void removeOpenListener(OpenListener listener)
public final void addRowFilterListener(RowFilterListener listener) public final void removeRowFilterListener(RowFilterListener listener)
public final void addStatusListener(StatusListener listener) public final void removeStatusListener(StatusListener listener)