Querying a database

A QueryDataSet component is a JDBC-specific DataSet that manages a JDBC provider of data, as provided in the query property. Using a QueryDataSet component in JBuilder, you can extract data from a server into a data set. This action is called "providing". Once the data is provided, you can view and work with the data locally in data-aware controls. You can store your data to local memory (MemoryStore) or to a local single-file database with a hierarchical directory structure (DataStore). When you want to save the changes back to your database, you must resolve the data. This process 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. To query a SQL table, you need the following components, which can be provided programmatically, or by using JBuilder design tools.

The QueryDataSet has built-in functionality to fetch data from a JDBC data source. 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 Dissecting the query dialog.
PropertyEffect

database Specifies what Database connection object to run the query against.
query A Java String representation of 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:
  1. Default: load all data up front.

  2. Asynchronous: 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.

  3. Fetch as Needed: load the rows as they are needed.

  4. 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 provide data to an application using a QueryDataSet component. This example also demonstrates how to attach the resulting data set to a GridControl for data viewing and editing.

The finished example for this tutorial is available as a completed project in the samples\borland\samples\tutorial\dataset\QueryProvide directory of your JBuilder installation under the file name QueryProvide.jpr. The running application, including the addition of the information on resolving a query, looks like this:

Populating a data set

To create the application and populate a data set from a table,

  1. Select File|Close All. Select File|New. Double-click the Application icon and accept all defaults to create a new application.

  2. Select the Frame file in the Navigation pane. Select the Design tab to activate the UI Designer.

  3. Click the Database component on the Data Express tab of the Component palette, then click in the Component tree to add the component to the application.

    Open the connection property editor for the Database component by double-clicking the connection property in the Inspector. Set the connection properties to the Local InterBase sample employee table, as follows:
    Property name Value
    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    The code generated by the designer for this step is:

    database1.setConnection(new borland.sql.dataset.ConnectionDescriptor
       ("jdbc:odbc:DataSet Tutorial", "SYSDBA", "masterkey", false,
        "sun.jdbc.odbc.JdbcOdbcDriver"));

    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 in the gray area below the button. When the connection is successful, click OK.

    For more information on connecting to databases, see Connecting to a database.

  4. Add a QueryDataSet component to the Designer by clicking on the QueryDataSet component on the Data Express tab and then clicking in the Component tree. Select the query property of the QueryDataSet component in the Inspector and set the following properties:
    Property name Value
    Database database1
    SQL Statement select * from employee
    Place SQL text in resource bundle unchecked

    Click Test Query to ensure that the query is runnable. When the gray area beneath the button indicates Success, click OK to close the dialog.

    The code generated by this step is:

    queryDataSet1.setQuery(new borland.sql.dataset.QueryDescriptor(database1, "select * from employee", null, true, DataSetLoadOption.LOAD_ALL_ONCE));

Creating the UI

To view the data in your application,

  1. Add a GridControl component from the JBCL tab to the Designer. Set its dataSet property to queryDataSet1. You'll notice that the grid fills with data.

  2. Add a NavigatorControl component from the JBCL tab to the Designer. Set its dataSet property to queryDataSet1. This will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.

  3. Add a StatusBar control from the JBCL tab to the Designer. Set its dataSet property to queryDataSet1. Among other information, the status bar displays the current record.

  4. Select File|Project Properties. On the Run/Debug page, check Send Run Output To Execution Log. Click OK. Using the Execution Log instead of the Console Window enables you to view a list of activities as they occur, while running or debugging. You can also type notes into this window, and save the contents of the window to a text file. To open this window, choose View|Execution Log.

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

To save changes back to the data source, you can use the Save Changes button on the navigator control or, for more control on how changes will be saved, see Saving changes back to your data source.

As a next step, you might want to edit and modify data, save changes back to your data source, filter data, sort data, or set various properties and methods of the resulting QueryDataSet.

Opening and closing data sets

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.

Ensuring that a query is updatable

When JBuilder executes a query, it attempts to make sure that the query is updatable and that it can be resolved back to the database. If JBuilder determines that the query is not updatable, it will try to modify the query to make it updatable, typically by adding columns to the SELECT clause.

If a query is found to not be updatable and JBuilder cannot make it updatable by changing the query, the resulting data set will be read-only.

To make any data set updatable, 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).

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 updatable. You can solve this problem by writing a custom resolver.