Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.
A QueryDataSet
component is a JDBC-specific DataSet
that manages a JDBC data provider, as defined in the query
property. You can use a QueryDataSet
component in JBuilder to extract data from a data source into a StorageDataSet
component. This action is called "providing". Once the data is provided, you can view and work with the data locally in data-aware components. When you want to save the changes back to your database, you must resolve the data. The DataExpress architecture is discussed in more detail in "Understanding JBuilder database applications".
QueryDataSet
components enable you to use SQL statements to access, or provide, data from your database. You can add a QueryDataSet
component directly to your application, or add it to a data module to centralize data access and control business logic.
To query a SQL table, you need the following components, which can be supplied programmatically or with JBuilder design tools:
Database
The Database
component encapsulates a database connection through JDBC to the SQL server and also provides lightweight transaction support.
A QueryDataSet
component provides the functionality to run a query statement (with or without parameters) against tables in a SQL database, and stores the result set from the execution of the query.
The QueryDescriptor
object stores the query properties, including the database to be queried, the query string to execute, and optional query parameters.
The QueryDataSet
has built-in functionality to fetch data from a JDBC data source. However, the built-in functionality (in the form of the default resolver) does much more than fetch data. It also generates the appropriate SQL INSERT, UPDATE, and DELETE queries for saving changes back to the data source after it has been fetched.
The following properties of the QueryDescriptor
object affect query execution. These properties can be set visually in the query
property editor. For a discussion of the query
property editor and its tools and properties, see "Understanding the query dialog".
Property | Effect |
---|---|
database |
Specifies what Database connection object to run the query against. |
query |
A SQL statement (typically a SELECT statement). |
parameters |
An optional ReadWriteRow from which to fill in parameters, used for parameterized queries. |
executeOnOpen |
Causes the QueryDataSet to execute the query when it is first opened. This is useful for presenting live data at design time. You may also want this enabled at run time. |
loadOption |
An optional integer value that defines the method of loading data into the data set. Options are:
|
A QueryDataSet
can be used in three different ways to fetch data.
QueryDataSet
.
The following tutorial shows how to retrieve data using a QueryDataSet
component. This example also demonstrates how to attach the resulting data set to a JdbTable
for data viewing and editing.
The finished example for this tutorial is available as a completed project in the /samples/DataExpress/QueryProvider
directory of your JBuilder installation.
To create the application and retrieve data from a table,
Select File|Close All, then File|New.
Select the Design tab to activate the UI designer.
Click the Database
component on the Data Express tab of the component palette, then click anywhere in the UI designer or the component tree to add the component to the application. database1
is added to the DataExpress folder in the component tree, and selected by default.
Click in the connection
property value field in the Inspector, then click the ellipsis button to open the Connection property editor for database1
.
Set the connection
properties to the JDataStore sample EMPLOYEE table, as follows:
Property Name | Value |
Driver | com.borland.datastore.jdbc.DataStoreDriver |
URL | Use the Browse button to browse to /jbuilder/samples/JDataStore/datastores/employee.jds on your system, then click Open.
|
Username | Enter your name |
Password | not required |
The connection
dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed beside the button. When the connection is successful, click OK.
You can view the code generated by the designer for this step by selecting the Source tab and looking for the ConnectionDescriptor
code. Click the Design tab to continue.
For more information on connecting to databases, see "Connecting to a database".
QueryDataSet
component query
property value field in the Inspector for queryDataSet1
, then click the ellipsis button to open the Query property editor.
Set the following properties:
Property name | Value |
Database | database1 |
SQL Statement | select * from employee |
Click Test Query to ensure that the query is runnable. When the area beneath the button indicates Success, as shown below, click OK to close the dialog.
Switch to the More dbSwing tab on the component palette and add a DBDisposeMonitor
to the application. This component will close the JDataStore when the window is closed.
Set the dataAwareComponentContainer
property for dBDisposeMonitor1
to 'this'.
contentPane (BorderLayout)
in the component tree. (Black sizing nibs around the edges of the panel in the designer show it is selected.)
JdbNavToolBar
component into the designer at the top, center of the panel and set its constraints property to NORTH.
JdbStatusLabel
component into the designer at the bottom, center of the panel and set its constraints property to SOUTH.
TableScrollPane
component into the designer into the center of the panel, and set its constraints property to CENTER.
JdbTable
component into the center of tableScrollPane1
and set its dataSet
property to queryDataSet1
.
You'll notice that the designer displays a table with live data.
The application looks like this in the designer:
The application looks like this when it is running:
To save the changes back to the data source, you can use the Save Changes button on the toolbar component or, for more control on how changes will be saved, create a custom data resolver, as described in the topic "Saving changes back to your data source".