< Java Web Application & MySQL 2 | Java & Friends | Java Web App: Insert, Delete Update - Derby 1 >

 


 

 

Java Web Application:

Using Databound Components to Access a Database (Derby)

 

 

 

Contents:

  1. Machine specifications used for this tutorial

  2. Creating a Page That Includes a Drop Down List Component

  3. Connecting the Component to a Database

  4. Adding a Table Component

  5. Modifying the SQL Query

  6. Controlling the Rows That Are Displayed

  7. Doing More

  8. Try On your Own

  9. Summary

 

In this tutorial, you use the NetBeans IDE to create and deploy a web application that displays master-detail data from a database (Java database Derby) that is bundled with the IDE. In the application, you select a person from a JSF 1.2 (Woodstock) drop-down list, and the application displays a table that shows all the trip records for that person. The original tutorial can be found at netbeans.org. All credits must go to the original authors. In this tutorial we try to refine all the steps to more details using screen shots.

 

Machine specifications used for this tutorial

  • Intel Pentium Core 2 Duo, 2.2 GHz,

  • Windows XP Pro SP2 + periodical patches + periodical updates…

  • 2 GB DDR2 RAM

  • 160 GB SATA HDD

  • 17” SyncMaster 713N monitor.

 

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.

 

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

You begin by adding a Table and a Drop Down List component to the default Page1.jsp created with the new project.

 

  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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

  1. Drag a Drop Down List component to the right of the Label component.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

  1. In the Properties window, change the id to personIdDD.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

The NetBeans IDE comes with a sample Travel database that appears under the sun’s databases node and the database location is under your login profile.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

You can check the Derby database location from Tools > Java DB Database > Settings. The following Figure shows the installation path and location for default NetBeans installation

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

By default, three database samples (Sample, Travel and Vir) have been configured when you open NetBeans.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

To enable this Travel database, select the Travel database node > right click mouse > select Connect.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

By expanding the Tables node you can see the tables available in Travel database.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Differentiate between the connected and not connected icons.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

In this section of the tutorial, you use the PERSON table from the Travel database to supply the choices for the Drop Down List component.

 

  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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

  1. 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 non-visual personDataProvider component for the database table. The personDataProvider component appears in the Navigator window. The IDE also adds a personRowSet property to SessionBean1.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

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

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

  3. Click OK.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Next, a dialog box displays the status of the deployment.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Adding a Table Component

 

Next, you add a Table component to your application and connect the component to a database table.

 

  1. From the Palette, drag a Table component and place it below the Drop Down List component.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

  1. In the Services window, expand the Travel > Tables node.

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

  1. Use Ctrl-Click to select all entries in the Selected list except TRIP.DEPDATE, TRIP.DEPCITY, and TRIP.DESTCITY.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

Modifying the SQL Query

 

Next, you modify the SQL query in the tripRowSet object so that the query also returns data from the TRIPTYPE table. You also modify the Table component to display the trip type description.

 

1.      In the Navigator window, expand the SessionBean1 node if it is not already expanded. (If the Navigator window is empty, you need to close the NetBeans IDE and re-open it. I’m not sure this is a bug or what).

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

3.      Drag the Travel > Tables > TRIPTYPE node from the Services window and drop it on the Design View, as shown in the following Figure. 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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

8.      Add the TRIPTYPE.DESCRIPTION column to the Selected list, and click OK. A fourth column appears in the Table component.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

For this application, the Table component must display only the trip information for the person whose name is selected from the Drop Down List component. You constrain the information that is displayed in the table by editing the query for the tripRowSet object to create a master-detail relationship between the Drop Down List component and the Table component.

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

3.      Set the Comparison drop-down list to =Equals, select the Parameter radio button and click OK.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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 = ?

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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);

    }

}

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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);

        }

    }

}

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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

 

Code Sample 4: onchange Property Code

 

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

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

-----------------------------------------------------------------------------------------------------------------

 

-----------------------------------------------------------------------------------------------------------------

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Doing More

 

Try It. Add a Static Text component to the right of the Drop Down List component.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Right-click the Static Text component, choose Bind to Data, and bind the component to PERSON.JOBTITLE.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Run the program and choose a different name from the drop-down list.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Code Sample 5: Synchronizing personDataProvider With Selected Person

 

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);

        }

    }

}

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

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.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Note: If you use the pagination option, add the following code after the tripDataProvider.refresh() statement in the personIdDD_processValueChange method:

 

tableRowGroup1.setFirst(0);

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

This ensures that the first page is always displayed when a new name is selected from the drop-down list. Run the application and see how your changes affect the way the table is displayed.

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Step-by-step on Java web application using Data bound component to access Java DB, Derby database screenshots

 

Try On your Own

 

  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

 

The steps for binding a component to a database table are as follows:

  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.

 

 

 

 

 

 

 

 


 

< Java Web Application & MySQL 2 | Java & Friends | Java Web App: Insert, Delete Update - Derby 1 >