Using Databound Components to Access a Database

Contents -  Creating a Page That Includes a Drop Down List Component -  Connecting the Component to a Database -  Adding a Table Component -  Modifying the SQL Query -  Controlling the Rows That Are Displayed -  Doing More

Before you use this tutorial, you must have the NetBeans IDE 6.0 with Web and Java EE support installed on your system. Familiarize yourself with the basic parts of the IDE and read the Getting Started With NetBeans Visual Web JSF Development for an introduction to the NetBeans IDE's development environment.

This tutorial works with the following technologies and resources

JavaServer Faces Components/ Java EE Platform	1.2 with Java EE 5* 1.1 with J2EE 1.4
Travel Database	Required

* As of the date this tutorial was published, only the Sun Java System Application Server supported Java EE 5.

This tutorial has been tailored for use with the GlassFish v2 Application Server. If you are using a different server, consult the Release Notes and FAQs for known problems and workarounds. For detailed information about the supported servers and Java EE platform, see the Release Notes.

Creating a Page That Includes a Drop Down List Component

In this tutorial, you build a Travel Center application as shown in the following figure of the deployed application.

Figure 1: Deployed Travel Center Web Application

1. Create a new web application project named DataboundComponents that uses the GlassFish V2 Application Server and the Visual Web JavaServer Faces framework.

   The IDE creates the first page named Page1 and displays the page in the Visual Designer.
2. Expand the Basic node in the Palette window and drag a Label component onto the left side of the page. Type Select Name: and press Enter.
3. Drag a Drop Down List component to the right of the Label component.
4. In the Properties window, change the id to personIdDD.

5. Ctrl-Shift-Drag from the Drop Down List component to the Label component to associate the two components.

   The for property for the Label component is now set to personIdDD.

6. Drag a Message Group component from the Palette window onto an out-of-the-way place on the page, such as the upper-right corner of the page.

   This component is useful for diagnosing programming errors. You can make diagnostic messages appear in this component by calling the info(String), error(String), warn(String), or fatal(String) method. The Message Group component displays the value of the String argument. In addition, messages about runtime errors, validation errors, and conversion errors appear in this component by default.

Connecting the Component to a Database

The Services window, which appears on the left side of the IDE workspace, includes a Databases node. The Databases node shows all of the database drivers and connections that have been added to the IDE.

The NetBeans IDE comes with a sample Travel database that appears under the Databases node.

When you bind a database to a component, you create two layers between the component and the database table: the RowSet layer and the Data Provider layer. The RowSet layer makes the connection to the database, executes the queries, and manages the result set. The Data Provider layer provides a common interface for accessing many types of data, from rowsets, to Array objects, to Enterprise JavaBeans objects.

Typically, the only time that you work with the RowSet object is when you need to set query parameters. In most other cases, you should use the Data Provider to access and manipulate the data. You can lower your learning curve by using the Data Provider API, because the same APIs work no matter what kind of data you are wrapping (that is, which Data Provider implementation you are using).

1. In the Services window, expand the Databases node and check if the TRAVEL database is connected.

   If the jdbc node for the TRAVEL database's badge is broken and you cannot expand the node, the IDE is not connected to the database. To connect to the TRAVEL database, right-click the jdbc node for TRAVEL and choose Connect from the pop-up menu. If the Connect dialog box appears, enter travel for the User and Password, select Remember Password During This Session, and click OK. If you do not see a jdbc node for the TRAVEL database, see the FAQ How do I enable the sample Travel database for MySQL in NetBeans IDE 6.0 for information about making the database available to the IDE.

2. Expand the Travel > Tables node.

   Under Tables, you see nodes for each table in the database, such as CARRENTAL and FLIGHT. The following figure shows the Services window with the Tables node expanded.

   Figure 2 : Services Window

3. Drag PERSON from the Services window and drop it on the Drop Down List.

   The text abc appears in the Drop Down List component. The abc text indicates that the display field is bound to a String object, which, in this case, is a database column of the SQL type varchar. In addition, the IDE adds a nonvisual personDataProvider component for the database table. The personDataProvider component appears in the Navigator window. The IDE also adds a personRowSet property to SessionBean1.

4. Right-click the Drop Down List and choose Bind to Data from the pop-up menu. The Bind to Data dialog box appears, as shown in the following figure.

   Figure 3 : Binding Data to the Drop Down List

    
   When you bind data to a Drop Down List component, you must specify what to display in the list (the Display Field) and you must specify what values to use in the underlying program (the Value Field). Typically, you want to display some meaningful value from the database table, such as a person's name, but you want to use a unique identifier in the underlying program, such as the person's ID. With this application, however, you want to bind both the Value field and the Display field to the same database column, the PERSON.NAME column, as described in the next two steps.
5. In the Bind to Data dialog box, select PERSON.PERSONID in the Value Field list to make the component's getSelected method return the PERSON.PERSONID for the current selection.
6. Select PERSON.NAME in the Display Field list to cause the browser to populate the drop-down list with values from the PERSON.NAME database column.
7. Click OK.

8. Click Run > Run Main Project in the main toolbar.

   The IDE saves all changes and then builds, deploys, and runs the web application. First, the Output window appears at the bottom of the IDE. The IDE writes compilation and deployment preparation information to this window. (So if there are any problems with a build, check the Output window first.) Next, a dialog box displays the status of the deployment. After the deployment is complete, the IDE opens a web browser for the application. When the browser renders the page, it populates the drop-down list with data from the NAME column of the PERSON table.

Adding a Table Component

1. From the Palette, drag a Table component and place it below the Drop Down List component.
2. In the Services window, expand the Travel > Tables node.

3. Drag TRIP from the Services window and drop it on the Table component title bar.

   Note: If you drop the TRIP database onto another part of the table component, the Choose Target dialog box opens. In the Choose Target dialog box, select table1 and click OK.

4. Right-click the Table component and select Table Layout.

   The Selected list in the Table Layout box shows all of the table's columns. You use items from the Selected list to specify which columns should appear in the Table component.
5. Use Ctrl-Click to select all entries in the Selected list except TRIP.DEPDATE, TRIP.DEPCITY, and TRIP.DESTCITY.

6. Click the < button.

   The selected entries are moved to the Available list and the following three entries remain in the Selected list, as shown in the following figure:

   Figure 4: Table Layout Dialog Box

7. Click OK.

   The Visual Designer now displays three columns in the Table component, as shown in the following figure.

   Figure 5: Table Column Display

Modifying the SQL Query

1. In the Navigator window, expand the SessionBean1 node if it is not already expanded.

   Figure 6: SessionBean1 Section in Navigator Window

2. In the SessionBean1 section of the Navigator window, right-click the tripRowSet node and choose Edit SQL Statement.

   The Query Editor appears in the editing area, with a tripRowSet tab.

   Tip: If the Output window is open, close it to give you more room to work with the Query Editor.

3. Drag the Travel > Tables > TRIPTYPE node from the Services window and drop it on the Design View, as shown in Figure 7.

   Another table diagram appears with a link between the two table diagrams. This link represents a join. Notice how the IDE has modified the select statement in the Source Code pane.

4. Clear the checkbox for TRIPTYPEID in the TRIPTYPE table.

   This action removes the column from the result set and from the SQL query that is in the Source Code pane, as shown in the following figure.

   Figure 7: The Query Editor

5. Leave the Query Editor open.
6. Click the Page1 tab in the editing area.

7. In the Visual Designer, right-click the Table component and choose Table Layout.

   The Table Layout dialog box appears. Because you have changed the SQL query for the tripRowSet, there are more columns that you can display.

8. Add the TRIPTYPE.DESCRIPTION column to the Selected list, and click OK.

   A fourth column appears in the Table component.

Controlling the Rows That Are Displayed

When you added a Data Provider for the TRIP table, the IDE created a RowSet object with an SQL query that returns all the rows for all the columns in the table. If you deploy and run the application at this point, the Table component shows all the trip information in the TRIP table.

1. Click the jdbc:derby://localhost:1527/travel tab in the editing area to switch to the Query Editor.
2. In the Design Grid of the Query Editor, right-click any cell in the PERSONID row and choose Add Query Criteria.
3. Set the Comparison drop-down list to =Equals, select the Parameter radio button and click OK.

4. You see =? in the Criteria column for PERSONID, which adds the following WHERE clause in the SQL query.

   Code Sample 1: WHERE Clause in the SQL Query

   WHERE TRAVEL.TRIP.PERSONID = ?

   
   Troubleshooting Hint: At this point, you can verify that your query is properly set. Right-click in the query editor, and choose Run Query. In the Specify Parameters Value dialog box, type 1 for the Value of Parameter TRAVEL.TRIP.PERSONID, and click OK. The output of your query for Person 1 is in the query editor results pane.

5. In the Design Grid of the Query Editor, click the Sort Type cell in the DEPDATE row and choose Ascending from the drop-down list.

   The IDE automatically sets the Sort Order and adds the sort clause to the SQL query.
6. Close the Query Editor.

7. In the Visual Designer, double-click the Drop Down List component.

   The source for the Page1 class opens in the Java Editor, and the cursor is placed inside the body of the personIdDD_processValueChange method. The IDE creates this event handler method stub the first time that you double-click the Drop Down List component.

8. Replace the body of the personIdDD_processValueChange method with the following code shown in bold.

   Code Sample 2: Value Change Event Handling for the Drop Down List Component

   public void personIdDD_processValueChange(ValueChangeEvent event) { try { getSessionBean1().getTripRowSet().setObject( 1, personIdDD.getSelected()); tripDataProvider.refresh(); } catch (Exception e) { error("Cannot switch to person " + personDataProvider.getValue( "PERSON.PERSONID")); log("Cannot switch to person " + personDataProvider.getValue( "PERSON.PERSONID"), e); } }

   
   

   This code binds the value of the PERSONID for the currently selected NAME in the drop-down list to the parameter in the prepared SQL statement for the tripRowSet object, executes the query, and gets the new result set.

   The setObject method replaces the ? in the query with the value of the PERSONID. The refresh method submits the new query and refreshes the result set. To learn more about either method, right-click the method call and choose Show Javadoc from the pop-up menu. View the Data Provider and the RowSet Javadocs by choosing Help > Javadoc References > Data Provider and Help > Javadoc References > RowSet.

   The log method sends a message and the associated stack trace to the application server's log to assist in discovering and diagnosing user problems. You can view the server's log by right-clicking the server's node in the Services window and choosing View Server Log from the pop-up menu.
9. Press Alt-Shift-F to reformat the code.
10. Find the prerender method in the source code.

11. Replace the body of the prerender method with the following code shown in bold.

    Code Sample 3: Synchronizing the Master-Detail Data When the Page Is First Displayed

    public void prerender() { if ( personIdDD.getSelected() == null ) { try { personDataProvider.cursorFirst(); getSessionBean1().getTripRowSet().setObject( 1, personDataProvider.getValue("PERSON.PERSONID")); tripDataProvider.refresh(); } catch (Exception e) { error("Cannot switch to person " + personDataProvider.getValue("PERSON.PERSONID")); log("Cannot switch to person " + personDataProvider.getValue("PERSON.PERSONID"), e); } } }

    
    Code in the prerender method is invoked before a web browser starts to display the page.
12. Press Alt-Shift-F to reformat the code.
13. Click Design in the editing toolbar to return to the Visual Designer.

14. Right-click the Drop Down List component and choose Auto-Submit on Change.

    In the Properties window, the following code appears in the onchange property.

    Code Sample 4: onchange Property Code

    webuijsf.suntheme.common.timeoutSubmitForm(this.form, 'personIdDD');

    
    Now when the user changes the drop-down list selection in the running web application, the web browser automatically submits the page.

15. Click Run Main Project in the main toolbar.

    The IDE saves all changed files, rebuilds the application, and redeploys the application to the server.
16. Select a person from the Drop Down List component to see how the Table component synchronizes the master and detail data. Click the DESTCITY column header to see how the Table component sorts the rows.

Doing More

Try It. Add a Static Text component to the right of the Drop Down List component. Right-click the Static Text component, choose Bind to Data, and bind the component to PERSON.JOBTITLE. Run the program and choose a different name from the drop-down list. Notice that the job title does not change. This is because the application needs to synchronize the personDataProvider with the selected item from the drop-down list. Add the code shown below in bold to the prerender method and run the application again. The job titles should now match the selected name.

  
public void prerender() {
    if ( personIdDD.getSelected() == null ) {
        try {
            personDataProvider.cursorFirst();
            getSessionBean1().getTripRowSet().setObject(
                1, personDataProvider.getValue("PERSON.PERSONID"));
            tripDataProvider.refresh();
        } catch (Exception e) {
            error("Cannot switch to person " +
                    personDataProvider.getValue("PERSON.PERSONID"));
            log("Cannot switch to person " +
                    personDataProvider.getValue("PERSON.PERSONID"), e);
        }
    }
    else {
        try {
            // Synchronize data provider with current selection
            personDataProvider.setCursorRow(
                    personDataProvider.findFirst(
                    "PERSON.PERSONID", personIdDD.getSelected()));
        } catch (Exception e) {
            error("Cannot switch to person " +
                    personIdDD.getSelected());
            log("Cannot switch to person " +
                    personIdDD.getSelected(), e);
        }
    }      
}

Try It. Play with the table's layout options. Right-click the Table component and choose Table Layout from the pop-up menu. Change the Header Text to Departure Date, Departure City, Destination City, and Description. Use the Options table in the dialog box to set the table's title to Trips. Select Enable Pagination and set the Page Size to 3. Run the application and see how your changes affect the way the table is displayed.

Note: If you use the pagination option, add the following code after the tripDataProvider.refresh() statement in the personIdDD_processValueChange method: tableRowGroup1.setFirst(0);. This ensures that the first page is always displayed when a new name is selected from the drop-down list.

Try It. Build a web application with a Drop Down List component and a Table component. Make the Drop Down List component display TRIPTYPE.DESCRIPTION. Make the Table component show all the TRIP records that have the same TRIPTYPEID as the selected TRIPTYPE.

1. constructor
2. prerender
3. constructor
4. personIdDD_processValueChange

When the browser first requests the page, the application creates an instance of Page1 and calls prerender. The server sends the response (the HTML page) and the Page1 instance is destroyed. The application does not call the value change event handler, because the application only generates value change events when a page is submitted (in this case, when a new person is selected).

When you choose a new name from the drop-down list, the browser submits the page. The application creates a new instance of Page1 and restores the values from the previous instance (they are passed in the request). Because this is a post-back (a submission), and because the name has changed, the application generates a value change event. Thus personIdDD_processValueChange gets called and the application refreshes the rowset.

After the value change event handlers are called, the application calls the prerender method. Because the dropdown now has a selected value, the application skips over the if section in the prerender method.

Summary

1. You bind a component to a database table by dropping the database table's node on the component, or by choosing Bind to Data from the pop-up menu and selecting an existing Data Provider from the drop-down list.
2. You use the Bind to Data dialog box to configure the database columns that the component displays and, for a list-type component, what column it returns. You can also use the Table Layout menu action to configure which database table columns the Table component displays.
3. To modify a query for a RowSet object, you open the Query Editor on the RowSet object from the Navigator window.
4. You call the RowSet object's setObject method to set the values for the query parameters. You call the data provider's refresh method to execute the query and refresh the result set.
5. Use the Auto-Submit on Change menu action to cause a page to be submitted automatically whenever a component's value changes.

6. Do the following steps to synchronize a detail component with a master component:

   1. Add code to the Page Bean's prerender method to call the detail RowSet object's setObject method to set the query parameters to some default, such as the first person in a drop-down list. Then call the refresh method to execute the query.
   2. Bind the master component to a processValueChanged method. Have this method call the detail RowSet object's setObject method to set the new query parameters. Then call the refresh method to execute the query.

See Also:

• Help > Javadoc References > Data Provider
• Help> Javadoc References > RowSet