When you do the Using Databound Components to Access a Database tutorial, you learn how to use the Query Editor on the rowset to build the SQL statement to retrieve the desired set of data. You also learn that for every ? (parameter) in your query, you need to call setObject() on the rowset before the query is executed. Take, for example, the following query.

SELECT ALL TRAVEL.TRIP.TRIPID, 
           TRAVEL.TRIP.PERSONID, 
           TRAVEL.TRIP.DEPDATE 
FROM TRAVEL.TRIP
WHERE TRAVEL.TRIP.PERSONID = ?
   AND TRAVEL.TRIP.TRIPTYPEID = ? 

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

If you forget to set the parameters before the rendering phase, you usually get the following error.

SqlException: At least one parameter to the current statement is uninitialized.

You will also get an error like this if you do not set all the parameters. Such as only calling setObject(1, somevalue) when you have two parameters in the query.

Another common error is to forget to edit the query to add the parameters. If you call setObject(1, somevalue), but your query has no parameters (no ?), you might get an error like the following.

java.lang.NullPointerException at org.apache.derby.client.am.PreparedStatement.checkForValidParameterIndex(Unknown Source)

Similarly, if you call setObject(2, somevalue) when your query has only one parameter, the server will emit something like the following message.

SqlException: The parameter position '2' is out of range. The number of parameters for this prepared statement is '1'

A couple of months ago, JB blogged about how to use the Sun Java Studio Creator tutorial to learn about using Hibernate with the Visual Web Pack. We are happy to announce that there is now a Using Hibernate With the Visual Web Pack tutorial. Not only does this show how to use the Hibernate framework in a Visual Web application, it also shows how to use ObjectListDataProvider objects and how to fill an Options array for list-type components.

A forum poster recently asked for the following information:

I have a table with 5 columns, (week_no, objective, date1, date2 and add report). How do I get the date for date1( example, today's date)? How do I set the week number as auto increment by 1( example, row 1, the week_no is 1, row 2, the week_no is 2 and so on)? Lastly, how to I set the add report button to link to another JavaServer Pages file?

This scenario gives me an excellent opportunity to show how to use an Object Array Data Provider (OADP). I usually use the Object List Data Provider (OLDP), but OLDPs take a bit more work. (See the Using Hibernate tutorial for an OLDP example.) If you have a simple array of data, the OADP might be the better choice, as in this case. The following steps create a table similar to the one requested in the forum.

1. Create a web application named ArrayTableExample.
   
   (If using the Visual Web Pack, be sure to create a Visual Web Application.)
   
   
2. Create a JavaBeans object for the array. In this example, I chose to put the week number, start date, and end date in the bean. In the Projects window, right-click Source Packages > arraytableexample, choose New > Java Class, name the class WeekBean, and click Finish.

3. Replace the class definition with the following code shown in bold.

   /*
    * WeekBean.java
    *
    */
   
   package arraytableexample;
   
   public class WeekBean 
           extends Object implements Serializable {
       
       private int weekNumber;
       private Calendar startDate;
       private Calendar endDate;
       private int dayOfWeek; 
       
       public WeekBean(int weekNumber) {
           this.weekNumber = weekNumber;        
           Calendar workingDate = Calendar.getInstance();
           workingDate.set(java.util.Calendar.HOUR_OF_DAY, 0);
           int offset = weekNumber - 1; 
           workingDate.add(
               Calendar.DAY_OF_YEAR, 
               + (offset * 7));
           dayOfWeek = workingDate.get(Calendar.DAY_OF_WEEK);
           startDate = (Calendar) workingDate.clone();   
           startDate.add(
           Calendar.DAY_OF_YEAR, - (dayOfWeek - Calendar.SUNDAY));
           endDate = (Calendar) workingDate.clone();
           endDate.add(
               Calendar.DAY_OF_YEAR,
               + (Calendar.SATURDAY - dayOfWeek));
       }
       
       public WeekBean() {
           this(1);
       }
           
       public int getWeekNumber() {
           return this.weekNumber;
       }
       
       public void setWeekNumber(int weekNumber) {
           this.weekNumber = weekNumber;
       }
       
       public Calendar getStartDate() {
           return this.startDate;
       }
       
       public void setStartDate(Calendar startDate) {
           this.startDate = startDate;
       }
       
       public Calendar getEndDate() {
           return endDate;
       }
       
       public void setEndDate(Calendar endDate) {
           this.endDate = endDate;
       }
   }
   

4. Right-click in the code and choose Fix Imports. Make sure the Fully Qualified Name for Calendar is java.util.Calendar, and click OK.
   
   
5. Close and save the file.
   
   
6. In this example, we want the table to show the same data throughout the session, so put the array property in the session bean. In the Projects window, double-click the Session Bean node to open SessionBean1.java in the source editor.
   
   
7. Add the following code to the bottom of the class defintion, just before the ending brace. This code also adds properties for passing the selected start and end dates to other pages.

   
       /**
        * Holds value of property weeks.
        */
           WeekBean[] weeks = {
               new WeekBean(1),
               new WeekBean(2),
               new WeekBean(3),
               new WeekBean(4)
           };
   
       /**
        * Getter for property weeks.
        * @return Value of property weeks.
        */
       public WeekBean[] getWeeks() {
           return this.weeks;
       }
       
       /**
        * Holds value of property reportStartDate.
        */
       private Calendar reportStartDate;
   
       /**
        * Getter for property reportStartDate.
        * @return Value of property reportStartDate.
        */
       public Calendar getReportStartDate() {
   
           return this.reportStartDate;
       }
   
       /**
        * Setter for property reportStartDate.
        * @param reportStartDate New value of property reportStartDate.
        */
       public void setReportStartDate(Calendar reportStartDate) {
   
           this.reportStartDate = reportStartDate;
       }
   
       /**
        * Holds value of property reportEndDate.
        */
       private Calendar reportEndDate;
   
       /**
        * Getter for property reportEndDate.
        * @return Value of property reportEndDate.
        */
       public Calendar getReportEndDate() {
   
           return this.reportEndDate;
       }
   
       /**
        * Setter for property reportEndDate.
        * @param reportEndDate New value of property reportEndDate.
        */
       public void setReportEndDate(Calendar reportEndDate) {
   
           this.reportEndDate = reportEndDate;
       }
   

8. Right-click in the code and choose Fix Imports. Make sure the Fully Qualified Name for Calendar is java.util.Calendar, and click OK.
   
   
9. Close and save SessionBean1.java.
   
   
10. View Page1 in the Visual Designer.
    
    
11. In the Palette, expand the Data Providers section.
    
    
12. Drag the Object Array Data Provider node and drop it on Page1.
    
    
13. If the properties for the OADP do not appear in the Properties window, select Page1 > objectArrayDataProvider1 in the Outline window to make its properties appear.
    
    
14. In the Properties window, change the id to weekDataProvider.
    
    
15. Here is where it starts getting kludgy. You should be able to choose weeks from the array drop-down list, but it propably is not appearing at this point. If weeks is not in the drop-down list, choose Clean and Build Main Project from the main menu. After the build completes, close and reopen the project.
    
    
16. In the Outline window, select Page1 > weekDataProvider.
    
    
17. In the Properties window, select weeks (SessionBean1) from the array drop-down list.
    
    
18. Drag a Table component from the Palette and drop it on Page1.
    
    
19. Right-click the Table component and choose Table Layout.
    
    
20. In the Table Layout dialog box, choose weekDataProvider from the Get Table From drop-down list.
    
    
21. Use the Up and Down buttons to put the values in the Selected list in the following order:

    weekNumber
    startDate
    endDate
    

22. (Optional) Set the Header Text values for the columns to Week, Start Date, and End Date.
    
    
23. Click New to add another column.
    
    
24. In the new column, set the Header Text to blank, select Button from the Component Type drop-down list, and set the Value Expression to Show Me.
    
    
25. Click OK to dismiss the dialog box.
    
    Again, it gets kludgy. Only one row appears, which says "No items found", and there is no button. Don't worry, it will work out in the end.
    
    
26. In the Outline window, expand table1 > tableRowGroup1 > tableColumn4, right-click button1, and choose Edit Action Event Handler.
    
    Page1.java opens in the source editor and scrolls to the newly added button1_action method.
    
    
27. Add the code shown in bold to the method.

      public String button1_action() {
            getSessionBean1().setReportStartDate(
              (Calendar)getValue("#{currentRow.value['startDate']}"));
            getSessionBean1().setReportEndDate(
              (Calendar)getValue("#{currentRow.value['endDate']}"));        
            return null;
      }
    

28. Right-click in the code and choose Fix Imports. Make sure the Fully Qualified Name for Calendar is java.util.Calendar, and click OK.
    
    
29. Typically, you would set up page navigation to go to another page to display the report. For simplicity, click Design and drop a Static Text component on the page to display the selected start date.
    
    
30. Right-click the Static Text component and choose Bind to Data.
    
    
31. Click Bind to an Object, select SessionBean1 > reportStartDate, and click OK.
    
    
32. Click Run Main Project. When the page appears, click the button in each row to verify that the action method returns the right value.

Winston has many blog entries about the dataprovider. A good place to start is his blog titled What is this Data Provider in Sun Java Studio Creator anyway?. Another good resource is Joel Brown's Weblog.

Last night we posted our newest tutorial, Using Hibernate With the Sun Java Studio Creator IDE. This tutorial shows the essential steps necessary for adding a Hibernate framework to your project, as well as shows now to display the query results in a DropDown List component and a Table component.

This tutorial is not only helpful for Hibernate users, it is also helpful for anyone who is using a database for which compliant JDBC drivers are not available. You can use the concepts shown here to learn how to use the ObjectListDataProvider and Option arrays to feed the data from your result sets to the web application.

Note: NetBeans has a plug-in to generate the Hibernate persistence classes. You can read about it here and here.

Let's say that you want to put a footer in a column to show the column's total. To do so, you could code like the following in a property's getter method and bind the column's footer to the getter method.

        int total = 0;
        try {
            employeeDataProvider.cursorFirst();
            do {
                ... 
                total += somevalue;
                }
            } while (employeeDataProvider.cursorNext());
        } catch (Exception ex) {
            error("put your message here" +
                    ex.getMessage());
            log("put your message here: " +
                    ex.getMessage(), ex);
        }
        return total;

Sometimes, you might have some columns that are bound to the underlying data provider and some that are not. So, how do you iterate through the columns that are not bound to the data provider's fields. For those columns, you can create a hash map in the session bean that is keyed on the row id. Then you can create a getter and setter like the following and bind the column to the property (nbrServings in this example).

    public Integer getNbrServings() {
        TableRowDataProvider rowData = 
           (TableRowDataProvider) getBean("currentRow");
        if (rowData == null) {
            return new Integer(1);
        } else {
            String rowId = rowData.getTableRow().getRowId();
            Integer nbrServingsValue = 
              (Integer) this.getNbrServingsMap().get(rowId);
            if (nbrServingsValue == null) {
                return new Integer(1);
            } else {
                return  nbrServingsValue;
            }
        }
    }
    
    public void setNbrServings(Integer nbrServings) {
        TableRowDataProvider rowData = 
            (TableRowDataProvider) getBean("currentRow");
        if (rowData != null) {
            String rowId = rowData.getTableRow().getRowId();
            this.getNbrServingsMap().put(rowId, nbrServings);
        }
    }

Then add something like the following to the session bean's init method.

        nbrServingsMap = new HashMap();

You can then iterate through the has map's set of values.

Here is a very basic tutorial on creating a simple input form to add a record to a database. It leaves a lot to the reader. For example, you would want to have nicer messages for duplicate ids and you wouldn't want your error messages to display internal database information such as table names and column names. If the record was added sucessfully, you would probably want the page to navigate elsewhere.

1. Drop three Label components on the left side of the page, one under the other. Set the label text to ID:, Name:, and Description: respectively.

2. Drop three Text Field components on the page, one next to each label. Set the ids for the components to idTF, nameTF, and descriptionTF respectively.

4. Set the required property to true for each Text Field component.

6. Drop three Message components on the page, one next to each text field.

7. Ctrl-Shift-Drag from each Text Field component to its associated label.

8. Ctrl-Shift-Drag from each Text Field component to its associated message.

9. Drop a Button component on the page, under the text fields. Set its text to Add. Set its id to addButton.

10. Drop a Message Group component on the page to the right of the button. Set its showGlobalOnly property to true.

11. Access the Servers window. Drag Data Sources > Travel > Tables > TRIPTYPE and drop it on a blank spot on the page.

11. Double-click the button to access its addButton_action() method. Replace the method's body with the following code shown in bold.

    public String addButton_action() {
        if ( triptypeDataProvider.canAppendRow() ) {
            try {
                RowKey rowKey = triptypeDataProvider.appendRow();
                triptypeDataProvider.setCursorRow(rowKey);
                triptypeDataProvider.setValue("TRIPTYPE.TRIPTYPEID",
                        rowKey,  idTF.getText());
                triptypeDataProvider.setValue("TRIPTYPE.NAME",
                        rowKey, nameTF.getText());
                triptypeDataProvider.setValue("TRIPTYPE.DESCRIPTION",
                        rowKey, descriptionTF.getText());               
                triptypeDataProvider.commitChanges();

            } catch (Exception e) {
                error("Cannot append new trip type: " + e);
                triptypeDataProvider.refresh();
                return null;
            }
        } else {
            error("Cannot append trip types");
            return null;
        }
        //clear fields
        idTF.setText(null);
        nameTF.setText(null);  
        descriptionTF.setText(null);        
        return null;

12. Fix imports.

13. Run and test. It will reject existing IDs (1 through 10).

14. To see the database updates, right-click on the TRIPTYPE node in the Servers window and choose View Data.