Common database applications tasks

Once you've completed the providing phase of your application and have the data in an appropriate JBCL DataSet component, you're ready to work on the core functionality of your application and its user interface. This chapter demonstrates typical database application features.

A design feature of the JBCL is that the manipulation of data is independent of how the data was obtained. Regardless of which type of DataSet component you use to obtain the data, you manipulate it and connect it to controls in exactly the same way. Most of the examples in this chapter use the QueryDataSet component, but you can replace this with the TableDataSet or any StorageDataSet subclass without having to change code in the main body of your application.

Each sample is created using the JBuilder AppBrowser and design tools. Wherever possible, we'll use these tools to generate source Java code. Where necessary, we'll show you what code to modify, where, and how, to have your application perform a particular task.

These tutorials assume that you are comfortable using the JBuilder environment and do not provide detailed steps on how to use the user interface. If you're not yet comfortable with JBuilder, refer to the introductory tutorial in this manual or the User Guide chapter on JBuilder's visual design tools.

All of the following examples and tutorials involve accessing SQL data stored in a remote database. These examples use the sample files included with Local InterBase Server. This data is accessed using JDBC and the JDBC-ODBC Bridge software. For instructions on how to setup and configure Local InterBase, JDBC and the JDBC-ODBC bridge, see Installing and setting up JBuilder for database applications.

If you're having difficulties running the database tutorials that connect to Local InterBase sample databases, the troubleshooting topic provides common connection errors.

We encourage you to use the samples as guides when adding these functions to your application. Finished projects and Java source files are provided in the JBuilder samples directory (JBuilder\samples\borland\tutorial\dataset by default) for many of these tutorials, with comments in the source file where appropriate. All files referenced by these examples are found in the JBuilder samples directory, or in the InterBase examples directory.

To create a database application, you first need to connect to a database and provide data to a DataSet. Providing data sets up a query that will be used for each of the following database tutorials. These options can be used in any combination, for example, you could choose to temporarily hide all employees whose last names start with letters between "M" and "Z". You could sort the employees that are visible by their first names.


Providing data

This topic provides the steps for providing data to a QueryDataSet for use with the tutorials in this chapter. A query can be used to join tables, select only some rows, select only some columns, and sort. The query that will be used in these tutorials is:
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE EMP_NO < 10

This SQL statement selects only some columns (EMP_NO, FIRST_NAME, LAST_NAME) and some rows (WHERE EMP_NO < 10) from a table (EMPLOYEE).

To query a database table,

  1. Select File|Close All from the menu. Select File|New from the menu. Double-click the Application icon and accept all defaults to create a new application. Optionally, name the files to make referring to them easier in the following tutorials. In Step 2 of the Application Wizard, check Generate Status Bar. Click Finish.

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

  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.

    Double-click the query property of the QueryDataSet component in the Inspector and set the following properties:
    Property name Value
    Database database1
    SQL Statement SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEE WHERE EMP_NO < 10
    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.

To view the data in your application we need to add some UI components and bind them to the data set. To do this,

  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. A StatusBar control was added to the application in the Application Wizard. It displays at the bottom of the frame. Among other information, the status bar displays the current record. To bind it to the data set, select it in the Component tree, and set its dataSet property to queryDataSet1.

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

The EMPLOYEE data set contains 42 records and 11 fields. In the status bar for this application, you will notice that the grid only contains 5 records and 3 fields. Remember that the SQL statement we used to create this data set limited the number of rows and columns we retrieved into the data set. To retrieve all records we could have used the SQL statement
SELECT * FROM EMPLOYEE

For more information on providing data to your application, see the Accessing data chapter.

The running application should look like this: