The IBM Data Access Beans is a new feature of VisualAge for Java 2.0. It provides an easy way to access relational data through Java's JDBC interface. The beans are integrated with the Visual Composition Editor to allow you to visually develop database applications.

In this tutorial you will learn how to visually create an application that accesses and displays data from a database. You will use the IBM Data Access Select bean to access the data in the database and the IBM Data Access DBNavigator bean to navigate through the result set. You will also learn how to connect the Select bean to UI beans, such as JTextFields and JTables, to display the results of the database query and to update the database.

The tutorial develops an application that accesses tables containing department and employee information and displays the department number and department name in textfields along with a table containing all of the department member names. Figure 1 shows the running application after the Execute button has been clicked and the database query has been executed.

Figure 1. Running DepartmentExample

The application was designed using the DEPARTMENT and EMPLOYEE tables defined in the IBM's DB2 UDB Sample database. If you are using another database, you must first define a DEPARTMENT table with columns DEPTNO and DEPTNAME and an EMPLOYEE table with columns WORKDEPT, FIRSTNME, and LASTNAME. You must then populate the tables with appropriate data. Once you have the tables defined and populated, you are ready to begin.

This tutorial assumes you are familiar with the VisualAge for Java Visual Composition Editor and have read the online documentation for the IBM Data Access Beans. To view the online documentation, on the WorkBench, select Help and click on Tasks. Expand the Developing Programs Visually heading. The last entry Access Relational Data describes the IBM Data Access Beans.

The steps in the tutorial are:

1. Install the IBM Data Access Beans Feature
2. Create the DepartmentExample Class
3. Build the DepartmentExample JFrame
4. Define the Database Queries
5. Make the Connections
6. Run the Application
7. Modify the Supplied Example Code

1. Install the IBM Data Access Beans Feature

1. The IBM Data Access Beans are shipped as a VisualAge Feature. To install the Data Access Beans, use the Quick Start menu (F2) from the Work Bench. When the Quick Start menu appears, highlight Features, choose Add Feature and click OK. Highlight IBM Data Access Beans from the feature list and click OK to add the Data Access Beans to the workspace.
2. When the feature is loaded you should see a new project named IBM Data Access Beans in the workspace.
3. Add the JDBC drivers that you will be using to your workspace classpath through the Options menu from the Workbench. Highlight Resources and add the drivers to your workspace classpath.

2. Create the DepartmentExample Class

1. Decide what project and package you want to contain the DepartmentExample class. You can either use an existing project and package or create new ones.
2. Create a new Class called DepartmentExample. Specify an appropriate project and package for the new class. The superclass for DepartmentExample should be com.sun.java.swing.JFrame. Ensure the Compose the class visually check box is selected. Click Finish.

3. Build the DepartmentExample JFrame

First you need to assemble the visual UI components and the non-visual components of the application.

1. When the Visual Composition Editor appears, resize the grey JFrame so it takes up about half of the free-form surface space.
2. Add JLabels, JTextFields and a JTable to the frame, and format them to appear as in Figure 1. Future references to these beans will use their default names provided by VisualAge for Java.
3. Switch from the Swing Palette to the Database Palette and drop a DBNavigator bean on the JFrame. Double-click the DBNavigator1 bean to bring up the property editor. Remove the unnecessary buttons from DBNavigator1 by setting the value for the properties showCommit, showDelete, showInsert, showRefresh, and showRollback to False.
4. Drop two Select beans on the non-visual free-form surface.

The Visual Composition work area should look like Figure 2.



Figure 2. DepartmentExample JFrame

4. Define the Database Queries

In order to retrieve data from the database, use the query property of the Select bean to define connection parameters, as well as defining what data to retrieve from the database.

1. Select the query property of the Select1 bean and click the button to the right of the query property to bring up the Query property editor.
2. The next step is to define a database access class to contain the JDBC connection information and the SQL query information. Click New on the Query property editor. Specify a package and class name for the database access class. Click OK to create the database access class.
3. On the Connection page of the Query property editor, click Add to create a new connection alias. Specify a connection name. Specify the JDBC driver and the URL for your data source. To automatically commit any changes to the database, select Auto-commit.
4. Specify a user ID and password and check Prompt for logon ID and password before connecting. This will allow you to override the userid and password at run time. Click on Test Connection to verify that you can connect to the database. Click OK to create the connection alias.
5. Now that the connection has been defined, the next step is to create an SQL specification. Switch from the Connection page to the SQL page of the Query property editor.
6. Select an existing database access class to contain the new SQL Specification, or create a new one. To create a new one, click Add. Then, on the New SQL Specification dialog specify a name for the SQL specification and click OK. By default, the SQL Assist Smartguide will help you create the SQL statement.
7. Only the tables whose schema matches the user ID that was used to connect to the database are initially displayed. If you want to access a table in another schema click View Schema(s) on the SQL Assist dialog to specify the schema whose tables you would like to view.
8. In the SQL Assist SmartGuide:
• On the Tables page, check the DEPARTMENT table.
• On the Columns page, add the DEPTNO and DEPTNAME columns to the Columns to include list.
9. Click Finish. A new SQL specification will be created in the database access class.
10. Click OK on the query property editor to save the specification for the query property. When you click OK, the SQL specification that has been associated with the Select bean is executed. This method defines the column names that will then be displayed on the Connectable Features dialog. If any exceptions are raised when the SQL specification is executed, no column names will be included on the Connectable Features dialog.
11. Select the query property of the Select2 bean. Bring up the Query property editor.
12. On the Connection page, select the previously defined connection alias. The Select2 bean will then share the same connection as the Select1 bean.
13. On the SQL page, click Add and define a new SQL specification to query the Employee table.
14. In the SQL Assist SmartGuide:
• On the Tables page, check the EMPLOYEE table.
• On the Condition 1 page, select the WORKDEPT column, the is exactly equal to operator, and add a :DEPT entry in the Values list. A colon prefix on the value indicates to the SQL Assist that a parameter will be used for the value
• On the Columns page, add the FIRSTNME and LASTNAME columns to the Columns to include list
15. Switch to the SQL page. The SQL query should look like this:
    
    
    
    
    Figure 3. SQL Assist SQL page
    
16. Click Finish to create the SQL specification. In the Query property editor, click OK  to save the query specification.

5. Make the Connections

To display the data retrieved by the Select beans, you have to make connections between the Select beans and the UI components.

1. To display the department number, make a property-to-property connection between the JTextField1 text property and the Select1 DEPARTMENT.DEPTNO_String property. By using the _String property, the value of the column will be automatically converted to a String value.
2. To display the department name, make a property-to-property connection between the JTextField2 text property and the Select1 DEPARTMENT.DEPTNAME_String property. To enable updates of the department name, you must select an event to trigger the update. Display the properties for this connection and set the Source event to actionPerformed.
3. To enable the DBNavigator1 bean to invoke methods on the Select1 bean, make a property-to-property connection between the DBNavigator1 model property and the Select1 this property.
4. To set the data model for the JTable1 bean to the Select2 bean, make a property-to-property connection between the JTable model property and the Select2 this property.
5. To display a message if any exceptions are raised when the DBNavigator1 bean executes methods on the Select1 bean, make an event-to-property connection between the DBNavigator1 exceptionOccurred event and the JTextField3 text property. Display the properties for this connection and click pass event data. The message for any exception raised by the Select1 bean will be displayed in the JTextField3 bean.
6. To set the parameter needed by the Select2 bean, make an event-to-property connection between the Select1 currentRow event and the Select2 Parm_DEPT property. The currentRow event is considered an expert feature, so check the Show expert features checkbox on the Connectable features dialog to display the event. To set the parameter needed for this connection, make a connection between the connection's value property and the Select1 DEPARTMENT.DEPTNO property.
7. To invoke the Select2 bean's execute() method when the current row is changed in the Select1 bean, make a event-to-method connection between the Select1 currentRow event and the Select2 execute method.
8. This connection must be ordered after the connection that sets the parameter needed for the query. Right-click the Select1 bean and select Reorder connections from. Examine the connections with source feature of currentRow. Ensure the connection with the target feature of Parm_DEPT is listed before the connection with the target feature of execute().
9. Click on Bean -> Save Bean to generate the code for this application

All of the connections are shown in Figure 4.



Figure 4. DepartmentExample Application with connections

6. Run the Application

Now you are ready to run the application.

1. In the DepartmentExample window, click Run to start the application. When the application window appears, all of the display fields are empty because the Select beans have not yet been executed.
2. Before the query has been executed, if you click on any button in the DBNavigator1 bean except Execute, a message in the JTextField3 bean will report that an SQL statement has not been executed..
3. Click Execute in the DBNavigator1 bean. All of the buttons will be grayed out while the connection is made to the database and the result set is retrieved. The Database Logon ID and Password dialog will appear to allow you to override the user ID and password that you specified in the alias. Click OK to connect to the database and retrieve the result set. The department number and name fields will display the first row of the result set.
4. Because the currentRow property value has changed, the property change event for the currentRow property will be triggered. This will trigger the connections that set the parameter for the Select2 bean and execute the Select2 bean.
5. After the Select2 bean has executed the query and retrieved the result set, a tableModelChanged event is triggered. The JTable1 bean is a registered listener for this event. The JTable1 bean will populate the table with the employee names for the current department.
6. Click on Next in the DBNavigator1 bean. The department number and name change, as well as the employee names in the JTable1 bean.
7. Update the department name that is displayed and press Enter.  This triggers the actionPerformed event, which  triggers the connection that updates the department name in the Select1 bean's cache.
8. To update the value in the database, click Next in the DBNavigator1 bean. Since the cache has been changed, the Select1 bean will automatically update the changed values to the database.
9. If you want to update the database without changing the position of the current row, modify the application to explicitly invoke the Select1 bean's updateRow() method.

7. Modify the Supplied Example Code

A VisualAge for Java interchange file is attached that contains the above example. You can import the file into your repository and then add the IBM Data Access Beans Example project into your workspace. Before you can execute the example, you must change the properties of the Select1 and Select2 bean to correspond to your database environment.

1. Bring up the DepartmentExample class in the Visual Composition Editor. Double-click on the Select1 bean to display its properties.
2. Select the query property of the Select1 bean and click the button to the right of the query property to bring up the Query property editor. The selected connection is connectDepartmentExample. Click Edit and change the JDBC driver, URL, user ID, and password as appropriate for your environment. You will have to uncheck Prompt for logon ID and password before connecting before you can enter a different userid and password. Recheck Prompt for logon ID and password before connecting and click Test Connection to verify that you can connect to the database, then click OK to generate the modified connection alias.
3. Switch from the Connection page to the SQL page of the Query property editor. The SQL specification is departmentQuery. Examine the SQL statement that is displayed. Unless you have the same schema and tables defined in your environment, you must create a new SQL specification. Click Add to add a new SQL specification. Follow the steps described above in 4.4 through 4.9 to define a new SQL specification for the department query.
4. Select the query property of the Select2 bean. Bring up the Query property editor. Since the Select2 bean shares the connection with the Select1 bean, the connection should already be defined correctly. Switch to the SQL page.
5. The SQL specification is employeeQuery. Examine the SQL statement that is displayed. Unless you have the same schema and tables defined in your environment, you must create a new SQL specification. Click Add to add a new SQL specification. Follow the steps described above in 4.12 through 4.15 to define a new SQL specification for the employee query.
6. Once you have modified the query properties to match your database environment, you will be able to execute the application as described in 6. Run the application.

Summary

Using VisualAge for Java with the IBM Data Access Beans, you can develop database applications without manually writing any code. With minimal knowledge of JDBC and SQL, you can easily develop applications that accesses, displays, and updates data in a database.

About the Author

Becky Nin is the team leader for the IBM Data Access Beans project at IBM's Santa Teresa Laboratory.