当前页面: 开发资料首页 → Netbeans 专题 → Performing Inserts, Updates, and Deletes

Performing Inserts, Updates, and Deletes

摘要: This tutorial shows you how to use NetBeans Visual Web Pack 5.5 to build a web application that can create, retrieve, update, and delete database rows. The application provides a drop-down list of master data along with a synchronized detail table. Users of the application can add to, update, and delete the records in the detail table and from its associated database.

This tutorial uses concepts introduced in other, more basic tutorials. If you do not have basic knowledge of the IDE and its design components, consider first reading introductory tutorials such as Getting Started With NetBeans Visual Web Pack 5.5 and Using Databound Components to Access a Database.

Contents -  Creating the Project -  Adding an Insert Feature -  Modifying the Page to Save Rowsets -  Adding a Delete Feature -  Adding a Revert Feature -  Summary

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
BluePrints AJAX Component Library	Not 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 Sun Java Application Server PE 9.0 Update Release 1 and with Tomcat 5.5.17. 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 the Project

In this tutorial, you create a single-page web application. You begin by laying out the page, which includes a person and corresponding trips, as shown in the following figure.

Figure 1: Initial Page Layout

1. Create a new visual web application and name it InsertUpdateDelete.
2. From the Basic section of the Palette, drag a Drop Down List component to the upper left corner of the page. In the Properties window, change its id property to personDD.

3. Drag a Message Group component from the Basic Palette and place it to the right of the Drop Down List.

   The Message Group component displays validation and conversion errors, as well as messages written to the Java Server Faces context by the info() and error() methods. These messages can prove helpful if you need to debug your project.
4. Place a Table component below the other two components.

1. Open the Runtime window, expand the Databases node, and verify that 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 Password and click OK. If you do not see a jdbc node for the TRAVEL database, see the NetBeans Visual Web Pack 5.5 Installation Instructions for information about making the database available to the IDE.

2. Expand the jdbc node for the TRAVEL database, then expand the Tables node.

   Note: The jdbc node for the TRAVEL database will be called the Travel node in the rest of this tutorial.

3. Drag the PERSON node onto the Drop Down List in the Visual Designer.

   The Outline window shows a personDataProvider node in the Page1 section and a personRowSet node in the SessionBean1 section.

4. Right-click the Drop Down List and choose Auto-Submit on Change from the pop-up menu.

   This setting causes the page to be sent to the server whenever a new value is chosen from the list.

5. Right-click the Drop Down List and choose Configure Virtual Forms from the pop-up menu.

   In the resulting dialog box, note that personDD is shown in the upper left corner of the window to show that the Drop Down List has been selected.

6. Click New. Enter person in the Name column. Double-click the field under the Participate column and set it to Yes, and then do the same for the Submit column, as shown in the following figure.

   Figure 2: Configuring Virtual Forms

   
   By using a virtual form, the application avoids unnecessary validations for data in the Table.
7. Click OK.

8. Click the Show Virtual Forms button in the Visual Designer toolbar, as shown in Figure 3.

   By viewing virtual forms, you can see the relationship between components in the Visual Designer and any virtual forms that you have configured.

   Figure 3: Showing Virtual Forms

1. Drag the Travel > Tables > TRIP node from the Runtime window and drop it on the Table component in the Visual Designer.

2. Right-click the Table and choose Table Layout from the pop-up menu.

   The dialog box shows the fields that are available from the tripDataProvider that you just created and lets you control which fields to display in the table.

3. Use the < button to remove TRIP.TRIPID, TRIP.PERSONID, and TRIP.LASTUPDATED from the Selected list on the right, as shown in the following figure.

   Figure 4: Binding Table to Data Provider

4. Click the Options tab and change the Title to Trips Summary.

5. Click OK.

   Your Table component in the Visual Designer should now look like the following figure. Note that if your columns are not in the order shown, you can rearrange them by reopening the Table Layout dialog box, clicking the Columns tab, and using the Up and Down buttons.

   Figure 5: Page1 Table Layout

6. In the Outline window, right-click tripRowSet under SessionBean1 and choose Edit SQL Statement from the pop-up menu.

   The SQL Query Editor appears in the editing area.

7. In the grid area near the center of the window, right-click in the PERSONID row and choose Add Query Criteria, as shown in the following figure.

   Figure 6: Adding Query Criteria

8. In the dialog box, set the Comparison drop-down list to =Equals and select the Parameter radio button, and then click OK.

   Figure 7: Add Query Criteria Dialog Box

   
   The characters "=?" appear in PERSONID's Criteria field, and the criteria "WHERE TRAVEL.TRIP.PERSONID=?" appears at the end of the SQL statement in the SQL Pane at the bottom of the editor.
9. Close the SQL Editor for tripRowSet.

Changing the Column Components

1. Right-click the Table component and choose Table Layout.

   The Table Layout dialog box opens.

2. In the Columns tab, select TRIP.DEPDATE from the Selected list on the right. In the Column Details area at the bottom of the dialog box, change the Component Type from Static Text to Text Field, as shown in the following figure.

   Figure 8: Changing the Appearance of a Column

3. Perform the same action for TRIP.DEPCITY and TRIP.DESTCITY.
4. Select TRIP.TRIPTYPEID in the Selected list and change the Component Type from Static Text to Drop Down List.

5. Click OK.

   Note: If the table columns are too wide after performing the above steps, you can resize them by selecting the first component in each column and dragging its selection handles.

6. Drag the Travel > Tables > TRIPTYPE node onto the Drop Down List in the Table component.

   This action creates the triptypeDataProvider.

Configuring the Virtual Form for the Table

1. In the Visual Designer, use Ctrl-Click to select the three Text Field components and the Drop Down List component in the Table component. Right-click one of the selected components and choose Configure Virtual Forms from the pop-up menu.
2. In the Configure Virtual Forms dialog box, make sure the three Text Fields and the Drop Down List are listed in the upper left corner of the window. If they are not all listed as shown in the following figure, close the dialog box, reselect them, and try again. When they are correctly listed, click New.

3. Change the name of the new virtual form to save and the Participate setting to Yes, as shown in the following figure, and then click OK to close the window.

   Figure 9: Configuring Virtual Forms for Table Elements

Adding Event and Initialization Code

1. Double-click the personDD Drop Down List to create a value change event method and to open the Java Editor on that method.

2. In the value change event method, note the comment line // TODO: Replace with your code. Replace this line with the bold text in Code Sample 1.

   Code Sample 1: Drop Down List Event Handler

   public void personDD_processValueChange(ValueChangeEvent event) { Object selectedPersonId = personDD.getSelected(); try { personDataProvider.setCursorRow( personDataProvider.findFirst("PERSON.PERSONID", selectedPersonId)); getSessionBean1().getTripRowSet().setObject(1, selectedPersonId); tripDataProvider.refresh(); form1.discardSubmittedValues("save"); } catch (Exception e) { error("Cannot switch to person " + selectedPersonId); log("Cannot switch to person " + selectedPersonId, e); } }

   
   At the end of the try clause, the statement form1.discardSubmittedValues("save") ensures that whenever the user selects a new person from the drop-down list, current trip information will be replaced by new information related to the chosen person. Recall that the user interface elements that display the trip information all participate in the virtual form named save. Note that the event handler does not throw exceptions. Instead, it logs them in the server.log file. The event handler also calls an error method that, in the event of an error, displays a message in the Message Group component.

3. Scroll in the Java source to the prerender() method (or, if you prefer, type Ctrl-F to open the Find dialog box and search for prerender). Add the following code in bold to the method.

   Code Sample 2: Prerender Method

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

4. Right-click in the Java Editor and choose Reformat Code to make the code align correctly.

Testing Your Application – Part 1

Build, deploy, and run the project by clicking the Run Main Project button on the main toolbar. When the page loads into your web browser, the drop-down list is populated with names, and the table is filled with data. When you select a different name from the list, the trips associated with that name should appear in the table.

Figure 10: Deployed Application, Test 1

Adding an Insert Feature

1. View Page1 in the Visual Designer.
2. From the Basic section of the Palette, drag a Message component onto the topmost Text Field in the first three columns of the Table.
3. Select the the first Message component. In the Properties window, scroll to the for property and choose textField1 from the drop down list. When the Message is correctly associated with the Text Field, the Message text changes to show the association, as shown in Figure 11.
4. Select the second Message component and set its for property to textField2.

5. Set the for property of the third Message component to textField3.

   Figure 11: Associating Message Components With Text Fields

6. From the Basic section of the Palette, drag a Button component onto Page1 and place it above the Table component near the top of the second column, as shown in Figure 12.
7. Change the text from Button to Add Trip.
8. In the Properties window, change the button's id property to add.
9. In the Visual Designer, double-click the button to open the Java Editor with the insertion point in the event handler for the button.

10. Modify the button's event code (the add_action() method) to read as follows:

    Code Sample 3: Add Trip Action Code

    public String add_action() { try { RowKey rk = tripDataProvider.appendRow(); tripDataProvider.setCursorRow(rk); tripDataProvider.setValue("TRIP.TRIPID", new Integer(0)); tripDataProvider.setValue("TRIP.PERSONID", personDD.getSelected()); tripDataProvider.setValue("TRIP.TRIPTYPEID", new Integer(1)); } catch (Exception ex) { log("Error Description", ex); error(ex.getMessage()); } return null; }

11. Right-click in the Java Editor and choose Fix Imports to resolve the RowKey not found error.

    The IDE adds the following package to the Page1.java block of import statements:

    import com.sun.data.provider.RowKey;

Testing Your Application – Part 2

Build, deploy, and run the project. The page loads into your web browser, and the Add Trip button appears, as shown in the following figure. Each time you click the button, a new empty row is appended to the bottom of the table. You are able to edit the information in the row, but because you have not yet provided a mechanism for saving the rowset, your changes will be lost when you choose a different name from the drop-down list.

Figure 12: Deployed Application, Test 2

Modifying the Page to Save Rowsets

1. Click Design in the editor window to return to Page1 in the Visual Designer.

2. From the Runtime window, select the Databases > Travel > Tables > TRIP table and drag it onto the SessionBean1 node in the Outline window.

   Figure 13: Dragging TRIP Table Onto SessionBean1

   
   This action opens the Add New Data Provider dialog box.

3. Click the Create SessionBean1/tripRowSet1 radio button, change the data provider name to maxTripRowSet, and click OK.

   Note: Rowsets appear twice in the dialog box. This is a known issue and should be ignored. It does not affect the application in this tutorial.

   Figure 14: Adding New Data Provider to SessionBean1

   
   This action creates maxTripDataProvider and maxTripRowSet in SessionBean1.
4. In the Outline window, double-click SessionBean1 > maxTripRowSet to open the Query Editor. Click in the Source Code pane (third from the top). Delete the existing SQL query you find there and enter the following query:

    SELECT MAX(TRAVEL.TRIP.TRIPID)+1 AS MAXTRIPID FROM TRAVEL.TRIP

   You will use the MAXTRIPID value in the action handler for the Save button, which you will add next.
5. Close the Query Editor. Note that this query is not supported by the Query Editor's graphical editor. If you see an alert dialog box complaining of a lexical error, you may safely dismiss it by clicking Continue.

1. Drop a Button component above the first column in the Table.
2. Change the Button text from Button to Save Changes.
3. In the Properties window, change the id property to save.
4. Right-click the Save Changes Button and choose Configure Virtual Forms from the pop-up menu.
5. In the Configure Virtual Forms dialog box, make sure save is shown in the list in the upper left corner so that your changes in this window apply to the Save Changes Button. Then, select the save virtual form, change the Submit value to Yes, and click OK.
6. In the Visual Designer, double-click the Save Changes Button to open the Java Editor. In the Java Editor, the insertion point is located in the event handler for the button.

7. Modify the Button's event code (the save_action() method) to read as follows:

   Code Sample 4: Save Action Code

   public String save_action() { try { // Get the next key, using result of query on MaxTrip data provider CachedRowSetDataProvider maxTrip = getSessionBean1().getMaxTripDataProvider(); maxTrip.refresh(); maxTrip.cursorFirst(); int newTripId = ((Integer) maxTrip.getValue("MAXTRIPID")).intValue(); // Navigate through rows with data provider if (tripDataProvider.getRowCount() > 0) { tripDataProvider.cursorFirst(); do { if (tripDataProvider.getValue("TRIP.TRIPID").equals (new Integer(0))) { tripDataProvider.setValue("TRIP.TRIPID", new Integer(newTripId)); newTripId++; } } while (tripDataProvider.cursorNext()); }; tripDataProvider.commitChanges(); } catch (Exception ex) { log("Error Description", ex); error("Error :"+ex.getMessage()); } return null; }

Testing Your Application – Part 3

Build, deploy, and run the project by clicking the Run Main Project button. The application should work as follows:

• You can add a trip and save it. The trip will then appear in the table and persist if you choose a different person and then return.
• You can edit existing trip information and save your changes.
• If you enter something other than a date in the Date field, the application will provide an error message.
• You can click Add Trip more than once before saving, as an easy way to add multiple rows at once.
• If you switch to a different person before saving, all of your updates (including filling out added rows) will be lost.
• If you modify some values and then click one of the column headers (to sort by that column), the Table component will remember the pending changes, which can then be saved.

Adding a Delete Feature

Now, add a delete feature to the Table. Using this feature, users will be able to delete a trip by removing a row from the database. As implemented in this tutorial, the action of the Delete button is immediate and does not require the Save Changes button to delete the row from the database. In fact, because the Delete button event handler uses the commitChanges() method, it also saves all pending changes just as the Save Changes button does.

1. In the Visual Designer, right-click the Table and choose Table Layout from the pop-up menu.

   The Table Layout dialog box opens.
2. Click the Columns tab, then click New to add a new column to the table.

3. With the new column name selected in the Selected list, make the following changes in the Column Details area:

   1. Header and Footer Text: Delete any default text from the Header and Footer text fields and leave them blank.
   2. Component type: Button
   3. Value Expression: Delete
   4. Width: Delete any default value and leave blank.
   5. Horizontal Align: Center
   6. Vertical Align: Middle
   7. Click OK.
4. Select the topmost Delete button in the table, and in the Properties window, set the id property to delete.

1. Double-click the first button in the Delete column to open the Java Editor in the delete_action() event handler.

2. Change the delete_action() method to read as follows:

   Code Sample 5: Delete Action Code

   public String delete_action() { form1.discardSubmittedValues("save"); try { RowKey rk = tableRowGroup1.getRowKey(); if (rk != null) { tripDataProvider.removeRow(rk); tripDataProvider.commitChanges(); tripDataProvider.refresh();} } catch (Exception ex) { log("ErrorDescription", ex); error(ex.getMessage()); } return null; }

Testing Your Application – Part 4

Build, deploy, and run the project by clicking the Run Main Project button. The following figure shows the running application.

When deployed, you should be able to delete a row from the table to remove it from the database. The delete action will also commit all pending changes to the database.

Figure 15: Testing Your Application, Part 4

Adding a Revert Feature

Now, add a revert feature to the page. Using this feature, users will be able to abandon their edits and revert to the previously saved data. Note that the revert feature will not bring back saved or deleted rows; both the Save Changes and Delete buttons commit changes to the database.

1. In the Visual Designer, drag a Button component from the Palette onto Page1. Place the new Button to the right of the Add Trip Button.
2. Change the button text to Revert Changes.
3. In the Properties window, change the Button component's id property to revert.
4. Double-click the Revert Changes Button to open the Java Editor in the revert_action() method.

5. Add the code in bold in the following code sample to the revert_action() method.

   Code Sample 6: Revert Action Code

   public String revert_action() { form1.discardSubmittedValues("save"); try { tripDataProvider.refresh(); } catch (Exception ex) { log("Error Description", ex); error(ex.getMessage()); } return null; }

Configuring a Virtual Form

The application as presently configured exhibits some undesirable behavior. For example, if the user enters an invalid date in the first column of an existing row and then clicks the Add button, the operation fails. No new row is added to the table because a conversion error on the date rejects the form submission. The desired behavior is to forego processing the input fields in the table so that a new row can be added regardless of pending edits to existing rows.

Similarly, when the user clicks the Delete button to delete a row, the row should be deleted no matter what edits have been made to that row or to other existing rows. And when the user clicks the Revert button, the intention is to abandon all edits, so edits should be ignored in that case also.

1. In the Visual Designer, select the Add, Delete, and Revert buttons, right-click, and choose Configure Virtual Forms from the pop-up menu.

   In the Configure Virtual Forms window, add, delete, and revert should appear in the upper left corner to show that those buttons have been selected.

2. In the Configure Virtual Forms window, click New, name the new virtual form add/delete/revert, and set Submit to Yes. Click OK.

   Figure 16: Configuring Virtual Form for Add, Revert, and Delete Buttons

Testing Your Application—Part 5

Build, deploy, and run the project by clicking the Run Main Project button. Figure 17 below shows the running application.

When deployed, you should be able to perform the following functions:

• Choose a name from the drop-down list and display that person's trips summary.
• Edit existing trip information and save your changes to the database.
• Add a row to the table, fill out the fields for the trip, and save your changes to the database.
• Delete a row from the table (and from the database).

• Abandon your edits and revert to the most recently saved data from the database.

Figure 17: Testing Your Application, Part 5

Summary

In this tutorial, you associated a Table component, Text Field components, and Drop Down List components with information in a database. You set properties on components and added prerender and event code to insert, update, and delete data from the database and revert changes. You used virtual forms, which allowed your application to use just a single page and allowed submitted data to bypass validation checks.

See Also:

• Using Databound Components to Access a Database
• Getting Started With NetBeans Visual Web Pack 5.5