Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.
This set of topics includes tips to help you
QueryDataSet
and a QueryProvider
. For enhancing performance during data retrieval, eliminate the query analysis that the QueryProvider
performs by default when a query is executed for the first time. See "Persisting metadata of a query" for information on doing this.
Set the loadOption
property on the Query/ProcedureDataSet
s 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, and in sequential order.
For large result sets, use a JDataStore
to improve performance. With this option, the data is saved to disk rather than to memory.
Cache SQL statements. By default, DataExpress will cache prepared statements for both queries and stored procedures if java.sql.Connection.getMetaData().getMaxStatements()
returns a value greater than 10. You can force statement caching in JBuilder by calling Database.setUseStatementCaching(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 (statement closed in a finalize()
method).
QueryDataSet.closeStatement()
,
ProcedureDataSet.closeStatement()
, QueryProvider.closeStatement()
, or
ProcedureProvider.closeStatement()
is called.
To enhance performance during data inserts/deletes/updates:
Set the Resolver
property to a
QueryResolver
.
UpdateMode
property of this QueryResolver
to UpdateMode.KEY_COLUMNS
.
These actions weaken the optimistic concurrency used, but reduce the number of parameters set for an update/delete operation.
Set your Database
's useTransactions
property to false
. This property is true
by default if the database supports transactions. When it is true, each insert, delete, or update statement is treated as a separate, automatically-committed transaction. When you set useTransactions
to false
, the statements are all processed in a single transaction.
Database
or Connection
's commit()
method to complete the transaction (or call rollback()
to discard all the changes).
Disable the resetPendingStatus
flag in the
Database.saveChanges()
method to achieve further performance benefits. 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.
To do this,
QueryDataSet
in the designer, right-click it, and select Activate Designer.
StorageDataSet
's metaUpdate
property to NONE.
StorageDataSet
's tableName
property to the table name for single table queries.
Column
's rowID
property for the columns so that they uniquely and efficiently identify a row.
Column
's visible
or hidden
property.
precision
, scale
, and searchable
to appropriate values. These properties are not needed if the metaDataUpdate
property is in something other than NONE.
Column
's tableName
property set for multi-table queries.
Column
's serverColumnName
property set to the name of the column in the corresponding physical table if an alias is used for a column in the query.
Database
and DataSet
are implicitly opened when components bound to them open. When you are not using a visual component, you must explicitly open a DataSet
. "Open" propagates up and "close" propagates down, so opening a DataSet
implicitly opens a Database
. A Database
is never implicitly closed.
When JBuilder executes a query, it attempts to make sure that the query is updateable and that it can be resolved back to the database. If JBuilder determines that the query is not updateable, it will try to modify the query to make it updateable, typically by adding columns to the SELECT clause.
If a query is found to not be updateable and JBuilder cannot make it updateable by changing the query, the resulting data set will be read-only.
To make any data set updateable, set the updateMetaData
property to NONE and specify the data set's table name and unique row identifier columns (some set of columns that can uniquely identify a row, such as columns of a primary or unique index). See "Persisting metadata of a query" for information on how to do this.
You can query a SQL view, but JBuilder will not indicate that the data was received from a SQL view as opposed to a SQL table, so there is a risk the data set will not be updateable. You can solve this problem by writing a custom resolver.