Querying a database

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:

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".
PropertyEffect

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:
  • Load All Rows: load all data up front.

  • Load Rows Asynchronously: causes the fetching of DataSet rows to be performed on a separate thread. This allows the DataSet data to be accessed and displayed as the QueryDataSet is fetching rows from the database connection.

  • Load As Needed: load the rows as they are needed.

  • Load One Row At A Time: load as needed and replace the previous row with the current. Useful for high-volume batch-processing applications.

A QueryDataSet can be used in three different ways to fetch data.


Tutorial: Querying a database using the JBuilder UI

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.

Note: We strongly recommended that before starting this tutorial you take the beginning database tutorial, called "Tutorial: An introduction to JBuilder database applications", to become familiar with using the visual design tools.

The finished example for this tutorial is available as a completed project in the /samples/DataExpress/QueryProvider directory of your JBuilder installation.

Retrieving data by querying a database

To create the application and retrieve data from a table,

  1. Select File|Close All, then File|New.

  2. Double-click the Application icon and accept all defaults to create a new application. Frame1.java will be opened by default in the content pane.
  3. Select the Design tab to activate the UI designer.

  4. 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.

  5. Click in the connection property value field in the Inspector, then click the ellipsis button to open the Connection property editor for database1.

  6. 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".

  7. Now add a QueryDataSet component  to your application from the Data Express tab of the component palette.
  8. Click in the query property value field in the Inspector for queryDataSet1, then click the ellipsis button to open the Query property editor.
  9. 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.

    QueryDescriptor

  10. Switch to the More dbSwing tab on the component palette and add a DBDisposeMonitor dbmonitoricon.gif to the application. This component will close the JDataStore when the window is closed.

  11. Set the dataAwareComponentContainer property for dBDisposeMonitor1 to 'this'.

  12. Choose File|Save All.

Creating the UI

Now create the UI for viewing and navigating the data in your application. Select the dbSwing tab on the component palette, and do the following:

  1. Select contentPane (BorderLayout) in the component tree. (Black sizing nibs around the edges of the panel in the designer show it is selected.)
  2. Drop a JdbNavToolBar component into the designer at the top, center of the panel and set its constraints property to NORTH.
  3. Drop a JdbStatusLabel component into the designer at the bottom, center of the panel and set its constraints property to SOUTH.
  4. Drop a TableScrollPane component into the designer into the center of the panel, and set its constraints property to CENTER.
  5. Drop a 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:

    Query tutorial in the designer

  6. Select Run|Run Project to run the application and browse the data set.

    The application looks like this when it is running:

    Query tutorial as a running application

    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".