Use the databound component to access the database

Directory - Create a page containing the drop-down list Components - Connect components to the database - Add Table component - Modify SQL query - Control the displayed rows - More functions

Before using this tutorial, you need to install netbeans ide 6.0 with Web and Java EE support on the system. Familiarize yourself with the basic sections of the IDE and read the brief introduction to the development environment of netbeans Visual Web JSF.

This tutorial covers the following technologies and resources:

Assumerver faces component Java EE platform	1.2 and Java ee 5 * 1.1 and J2EE 1.4
Travel Database	Required

* By the date of this article, only Sun Java System Application Server supports Java ee 5.

This tutorial applies to the glassfish V2 application server. If you are using different servers, refer to the release notes and FAQs to learn about various problems and solutions. For more information about supported servers and Java EE platforms, see release notes.

Create a page containing the drop-down list Components

In this tutorial, we will build a travel center application, as shown in the application deployed in.

Figure 1: Deployed travel center web application

First, we will add a table component and a drop down list component to the default page 1.jsp page created in the new project.

1. Create a new Web application project namedDataboundComponentsThe application uses the glassfish V2 application server and the visual web JavaServer faces framework.

   IDE creates the first page named page1, which is displayed in visual designer.

2. Expand the basic node in the palette and drag a label component to the left of the page. TypeSelect Name：And press Enter.
3. Drag a drop down list component to the right of the label component.
4. In the Properties window, modify the ID attributepersonIdDD.

5. Press ctrl-shift and drag the drop down list component to the label component to associate the two components.

   Label componentforThe property is now setpersonIdDD.

6. Drag a message group component from the palette window to a certain corner of the page, for example, the upper right corner of the page.

   This component can be used to diagnose programming errors. We can make the Diagnostic message appear in this component by callinginfo(String),error(String),warn(String)Andfatal(String)Method. The message group component is displayed.StringParameter value. In addition, messages about runtime errors, verification errors, and conversion errors are displayed in this component by default.

Connect components to the database

The Services window is located on the left of the IDE workspace and contains a databases node. The databases node displays all the database drivers and connections added to the IDE.

The example of the travel database that comes with netbeans ide appears under the databases node.

When binding a database to a component, we will create two layers between the component and the database table: The rowset layer and the data provider layer. The rowset layer is responsible for connecting to the database, performing queries, and managing result sets. The data provider layer provides a public interface for accessing many types of data, including rowsets, array objects, and Enterprise JavaBeans objects.

Generally, the rowset object is used only when you need to set query parameters. In most cases, we should use the data provider object to access and operate data. We can lower the learning threshold by using the data provider API, because the same API is suitable for encapsulating any type of data (that is, the data provider implementation used ).

In this section, we will use the person table in the travel database to provide a selection for the drop down list component.

1. In the Services window, expand the databases node and verify that it is connected to the travel database.

   If the logo of the JDBC node of the travel database is split and cannot be expanded, the IDE is not connected to the database. To connect to the travel database, right-click the JDBC node of the travel database and select the connect option from the pop-up menu. If the connect dialog box appears, entertravelAs the user name and password, select the Remember password during this session option and click OK. If you do not see the JDBC node of the travel database, see how to enable the MySQL example travel database in netbeans ide 6.0. This article explains how to connect the IDE to the database.

2. Expand the travel> tables node.

   Under the tables node, we can see the nodes of each table in the database, such as carmarshal and flight. Shows the Services window for expanding the tables node.

   Figure 2: Services window

3. Drag the person in the Services window to the drop down list component.

   abcThe text will appear in the drop down list component.abcText indicates that the field is bound toStringObject. In this example, the string object is a column in the SQL database and its type isvarchar. In addition, IDE adds an invisible persondataprovider component to the database table. The persondataprovider component appears in the Navigator window. IDE also addspersonRowSetAttribute to sessionbean1.

4. Right-click the drop down list component and select the bind to data option from the pop-up menu. The bind to data dialog box is displayed, as shown in.

   Figure 3: Bind data to the drop down list component

   
   When binding data to the drop down list component, we must specify the content displayed in the list (display field) and the value used by the underlying Program (Value Field. Generally, we want to display meaningful values, such as the name of a person, in a database table, but we also want to use a unique identifier in the underlying program. However, in this application, we want to bind both the value and the display value to the same column in the database, that is, the person. Name column, as shown in the following two steps.

5. In the bind to data dialog box, select the person. personid option in the Value Field List so that the getselected method of the component returns the currently selected person. personid.
6. Select "person. Name" in the display list to allow the browser to fill the drop-down list with the value in the "person. Name" database column.
7. Click OK.

8. Click RUN> run main project in the main toolbar.

   IDE will save all the modifications and then compile, deploy, and run the application. First, the output window appears at the bottom of the IDE. IDE writes the compilation and deployment preparation information to this window. If you encounter any problems during compilation, first check the output window. Next, the dialog box displays the deployment status. After the deployment is complete, the IDE opens the application in a web browser. When rendering the page, the browser fills the drop-down list with data in the name column of the person table.

After adding the table component, we will add a table component in the application and connect the component to a database table.

1. Drag a table component from the palette and place it under the drop down list component.
2. In the Services window, expand the travel> tables node.

3. Drag the trip in the Services window to the title bar of the table component.

   Note:If you place the trip database on other parts of the table component, the choose target dialog box is displayed. In the choose target dialog box, select Table1 and click OK.

4. Right-click the table component and select the table layout option.

   The selected list in the table layout box displays all columns in the table. We will use the items in the selected list to specify which columns should be displayed in the table component.

5. Use ctrl-click to select all entries except trip. depdate, Trip. depcity, and trip. destcity in the selected list.

6. Click<Button.

   All the selected entries have been moved to the available list. Only the following three entries are retained in the selected list, as shown in:

   Figure 4: Table layout dialog box

7. Click OK.

   Visual designer displays three columns of data in the table component, as shown in.

   Figure 5: Show table columns

Modify SQL query next, we will modify the SQL query in the triprowset object so that the query also returns data in the triptype table. We will also modify the table component to display the travel type description.

1. In the Navigator window, expand the sessionbean1 node (if it has not been expanded ).

   Figure 6: Sessionbean1 In the Navigator window

2. In the sessionbean1 section of the Navigator window, right-click the triprowset node and select the edit SQL statement option.

   Query editor will appear in the editing domain, which contains a triprowset tab.

   Tips: If the output window is open, close it and leave more space to query editor.

3. Drag the travel> tables> triptype node in the Services window to design view, as shown in figure 7. </P> another table chart contains a link between the two tables. This link indicates a pair of connections. Note that the IDE selects the statement method on the source code panel.

4. Clear the personid check box in the triptype table.

   This operation removes columns from the result set and SQL query (that is, the source code panel), as shown in:

   Figure 7: Query Editor

5. Let the query editor continue to open.
6. Click the page 1 tab in the editing area.

7. In visual designer, right-click the table component and select the table layout option.

   The table layout dialog box appears. As we have modified the SQL query of triprowset, more columns can be displayed.

8. Add the triptype. Description column to the selected list and click OK.

   The fourth column appears in the table component.

Control the displayed rows

After a data provider is added to the trip table, IDE createsRowSetThe SQL query returns all rows for all columns of the table. If you deploy and run the application at this time, the table component displays all travel information in the trip table.

For the application in this article, the table component only displays travel information corresponding to the person selected in the drop down list component. By editing the SQL query of the triprowset object, you can create a master-slave composite (master-detail) Relationship between the drop down list component and the table component. we limit the information displayed in the database table.

1. ClickJDBC: Derby: // localhost: 1527/travelTab to switch to query editor.
2. In the design grid of query Editor, right-click any table element in the row of personid and select the Add query criteria option.
3. Set the comparison drop-down list=EqualsSelect the parameter radio button and click OK.

4. We can see in the criteria column of the personid table=?It will add the following where statement to the SQL query.

   Code Example 1: Where statement in SQL query

   WHERE TRAVEL.TRIP.PERSONID = ?

   
   Troubleshooting skills:In this case, we can verify that the query has been correctly set. Right-click the query editor and select Run query. In the specify parameters Value dialog box, type 1 as the value of the Travel. Trip. personid parameter, and click OK. The query output of person 1 is located in the query editor result panel.

5. In the design grid of query Editor, click the sort type table element in the depdate row and select the ascending option from the drop-down list.

   IDE automatically sets the sort order and adds the sort statement to the SQL query.

6. Disable query editor.

7. In visual designer, right-click the drop down list component.

   Page1Class source code will open in Java editor, and the cursor will be located inpersonIdDD_processValueChangeMethod body. IDE will create the main program (stub) for this event handler method when double-clicking the drop down list component for the first time ).

8. SetpersonIdDD_processValueChangeThe body of the method is replaced with the following body code.

   Code Example 2: processing of the drop down list component value modification event

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

   The code above binds the personid value of the selected name in the drop-down list to the parameters in the prepared SQL statement of the triprowset object, and executes the query and obtains the new result set.

   setObjectThe method returns the question mark (?) in the query? Replace it with the value of personid.refreshThe method is used to submit a new query and refresh the result set. To learn more about the two methods, right-click a method call and choose show javadoc from the pop-up menu. To view data provider and rowset javadocs, select help> javadoc references> data provider and help> javadoc references> rowset.

   logThe method sends a message and associated stack to track the logs sent to the application server to help you discover and diagnose user problems. To view server logs, right-click the server node in the Services window and select View server log from the pop-up menu.

9. Press Alt-shift-F to reformat the code.
10. FindprerenderMethod.

11. SetprerenderThe body of the method is replaced with the following body code.

    Sample Code 3: Synchronize Master/Slave Data when the page is displayed for the first time

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

    
    The code in the prerender method will be called before the web browser starts displaying the page.

12. Press Alt-shift-F to reformat the code.
13. Click the design button in the editing toolbar to return to visual designer.

14. Right-click the drop down list component and choose auto-submit on change.

    In the projects window, the following code appears in the onchange attribute.

    Code example 4: onchange Property Code

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

    
    Now, when you modify the drop-down list in a running web application, the Web browser automatically submits the page.

15. Click RUN> run main project in the main toolbar.

    IDE will save all the modified files and recompile the application to redeploy it to the server.

16. Select a character from the drop down list component to view how the table component synchronizes Master/Slave Data. Click the destcity column header to view how the table component sorts rows.

More functions

Try it.Add a static text component to the right of the drop down list component. Right-click the static text component, select the bind to data option from the pop-up menu, and then bind the component to person. jobtitle. Run the program and select different names from the drop-down list. Note that the position name has not changed. This is because the application needs to synchronize the persondataprovider with the selected project in the drop-down list. Add the following simhei codeprerenderMethod and re-run the application. Now, the job name should be the same as the selected name.

Sample Code 5: Synchronize the persondataprovider with the selected person 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.Try to operate the table layout options. Right-click the table component and select the table layout option from the pop-up menu. Modify the header text to departure date, departure city, destination city, and description. Use the options table in the dialog box to set the table question to "trips. Select enable pagination and set the page size to 3. Run the application and view how these modifications affect the table display mode.

Note:To use the paging option, add the following codepersonIdDD_processValueChangeMethodtripDataProvider.refresh()Statement: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 an application containing the drop down list component and table component. Let the drop down list component display triptype. description. The table component displays all trip records with the same triptypeid as the selected triptype.

Try it.You may want prerenderAnd personIdDD_processValueChangeWhether repeated code in the method causes the detailed rowset to be refreshed twice. The answer is no. To demonstrate this, you can add log( Method-name )Statements to constructors, prerenderMethod and personIdDD_processValueChange. In the Services window, right-click a server node and select View server log from the pop-up menu. Run the program and select a new name. In the server log (in the output window), we can see that the method will be called in the following order:

1. Constructor
2. Prerender
3. Constructor
4. Personiddd_processvaluechange

When the browser requests the page for the first time, the application creates a page1 instance and callsprerenderMethod. The server will send a response (HTML page) and the page1 instance will be destroyed. The application does not call the value modification event handler, because the application generates a value modification event only when it submits the page.

When a new name is selected from the drop-down list, the browser submits the page. IDE will create a new page1 instance and restore the values of the previous instance (they are all passed in the request ). Because this is a submission event, and because the name has been modified, all applications will generate a value modification event. This will callpersonIdDD_processValueChangeAnd the application refreshes the rowset.

After the value is called to modify the event handler, the application will callprerenderMethod. Because the drop-down list already has a selected value, the application will ignoreprerenderMethodif.

To bind a component to a database table, follow these steps:

1. Bind a component to the database table by placing the node of the database table on the component, alternatively, select the bind to data option from the pop-up menu and select an existing data provider from the drop-down list.
2. Use the bind to data dialog box to configure the database columns displayed by the component. For list-type components, you also need to configure the Returned Database columns. You can also use the table layout menu to configure which database columns will be displayed in the table component.
3. To modify the SQL query in the rowset object, you can use query editor to open the rowset object in the Navigator window.
4. ThesetObjectMethod To set the value of the query parameter. Call the data provider'srefreshMethod to execute the query and refresh the result set.
5. Use the auto-submit on change menu to automatically submit the page when the component value changes.

6. Follow these steps to synchronize the master and slave components:

   1. InprerenderMethod To CallsetObjectMethod To set query parameters to some default values, such as the first character in a drop-down list. Then, callrefreshMethod.
   2. Bind the master (master) componentProcessvaluechangedMethod. Let this method callsetObjectMethod to set new query parameters. Then, callrefreshMethod.

This article from http://developers.sun.com.cn/Java/databoundcomponents.html