Using parameterized queries to obtain data from your database

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

A parameterized SQL statement contains variables, also known as parameters, the values of which can vary at run time. A parameterized query uses these variables to replace literal data values, such as those used in a WHERE clause for comparisons that appear in a SQL statement. These variables are called parameters. Ordinarily, parameters stand in for data values passed to the statement. You provide the values for the parameters before running the query. By providing different sets of values and running the query for each set, you cause one query to return different data sets.

An understanding of how data is provided to a DataSet is essential to further understanding of parameterized queries, so read the topics "Understanding JBuilder database applications" and "Querying a database" if you have not already done so. This topic is specific to parameterized queries.

In addition to the "Tutorial: Parameterizing a query", the following parameterized query topics are discussed:

Tutorial: Parameterizing a query

The following tutorial shows how to provide data to an application using a QueryDataSet component. This example adds a ParameterRow with low and high values that can be changed at run time. When the values in the ParameterRow are changed, the table will automatically refresh its display to reflect only the records that meet the criteria specified with the parameters.

Note: We strongly recommended that before starting this tutorial you take the beginning database tutorial, called "Tutorial: An introduction to JBuilder database applications", to become familiar with using the visual design tools.

A completed version of this tutorial is available in the sample project ParameterizedQuery.jpr, located in the /samples/DataExpress/ParameterizedQuery directory of your JBuilder installation.

Creating the application

To create the application,

  1. Select File|Close All.
  2. Select File|New and double-click the Application icon

  3. Accept all defaults to create a new application.

  4. Select the Design tab to activate the UI designer.

  5. Click the Database component on the Data Express tab of the component palette, then click anywhere in the UI designer to add the component to the application.

    Open the Connection property editor for the Database component by clicking the ellipsis in the connection property value in the Inspector.

  6. Set the connection properties to the JDataStore sample EMPLOYEE table, as follows:
    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL Browse to /jbuilder/samples/JDataStore/datastores/employee.jds in the local URL field.
    Username Enter your name
    Password not required

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed beside the button. When the connection is successful, click OK.

    If you want to see the code that was generated, click on the Source tab and look for the ConnectionDescriptor code. Click the Design tab to continue.

    For more information on connecting to databases, see "Connecting to a database".

Adding a Parameter Row

Next, you will add a ParameterRow with two columns: low_no and high_no. After you bind the ParameterRow to a QueryDataSet, you can use JdbTextField components to change the value in the ParameterRow so that the query can be refreshed using these new values.

  1. Add a ParameterRow component to the application from the Data Express tab.
  2. Click the expand icon to the left of parameterRow1 in the component tree to display the columns contained in the ParameterRow.
  3. Select <new column>, and set the following properties for the new column in the Inspector:
    Property name Value
    columnName low_no
    dataType INT
    default 15

    To see the code generated by the designer for this step, click the Source tab and look at the jbInit() method. Click the Design tab to continue.

  4. Select <new column> again to add the second column to the ParameterRow, and set the following properties for it:
    Property name Value
    columnName high_no
    dataType INT
    default 50

Adding a QueryDataSet

  1. Add a QueryDataSet component from the Data Express tab to the application.
  2. Click the ellipsis button for the query property to open the Query property editor.
  3. Set the query property for queryDataSet1 as follows:
    Property name Value
    Database database1
    SQL Statement select emp_no, first_name, last_name from employee where emp_no >= :low_no and emp_no <= :high_no
  4. Click the Parameters tab in the Query property editor.

  5. Select parameterRow1 in the drop-down list box to bind the data set to the ParameterRow.

  6. Click the Query tab. Click the Test Query button to ensure that the query is runnable. When the area beneath the button indicates Success, click OK to close the dialog.

    The following code for the queryDescriptor is added to the jbInit() method:

    queryDataSet1.setQuery(new com.borland.dx.sql.dataset.QueryDescriptor(database1,
      "select emp_no, first_name, last_name from employee where emp_no <= :low_no and emp_no >= :high_no",
      parameterRow1, true, Load.ALL));
    
  7. Add a DBDisposeMonitor component from the More dbSwing tab. The DBDisposeMonitor will close the JDataStore when the window is closed.

  8. Set the dataAwareComponentContainer property for the DBDisposeMonitor to 'this'.

Add the UI components

The instructions below assume you have taken the beginning database tutorial and are already familiar with adding UI components to the designer.

To add the components for viewing and manipulating the data in your application,

  1. Click the TableScrollPane component on the dbSwing tab of the component palette. Drop it into the center of the panel in the UI designer.

    Make sure its constraints property is set to CENTER.

  2. Drop a JdbTable component from the dbSwing tab into the center of tableScrollPane1 component. Set its dataSet property to queryDataSet1.

    You'll notice that the table in the designer displays live data.

  3. Select Run|Run Project to run the application and browse the data set.
  4. Close the running application.

To add the components that make the parameterized query variable at run time,

  1. Select the JPanel component on the Swing Containers tab.
  2. Drop it into the component tree, directly on the icon to the left of contentPane(BorderLayout). This ensures that the JPanel (jPanel1) will be added to the main UI, rather than to tableScrollPane1 which is currently occupying the entire UI panel.

  3. Make sure its constraints property is set to NORTH. (If tableScrollPane1 suddenly shrinks, check that its constraints property is still set to CENTER.)

  4. Select jPanel1 and set its preferredSize property to 200,100. This will make it big enough to contain the rest of the components for the UI.

  5. Drop a JdbTextField component from the dbSwing tab into jPanel1. This component holds the minimum value.

  6. Notice that jdbTextField1 is placed in the center of the panel at the top. This is because the default layout for a JPanel component is FlowLayout. If you try to drag the component to a different location, it won't stay there, but will return to it's initial location.

    To take control of the placement of the UI components in this panel, change the layout property for jPanel1 to 'null'. Then, drag jdbTextField1 to the left side of the panel.

  7. Set the columns property for jdbTextField1 to 10 to give it a fixed width. Set its text property to 10 to match the default minimum parameter value you entered earlier.

  8. Add a JLabel from the Swing tab to jPanel1. This label will identify jdbTextField1 as the minimum field.

  9. Click on jLabel1 in the UI designer and drag it to just above jdbTextField1.

  10. Set the text property for jLabel1 to Minimum value. Grab the middle black sizing nib on the right edge and expand the width of the label until all of the text is visible.

  11. Add another JdbTextField and JLabel to jPanel1 for the maximum value. Drag this pair of components to the right side of the panel.

  12. Set the columns property for jdbTextField2 to 10, and its text property to 50.

  13. Set the text property for jLabel2 to Maximum value, and expand its width to show all the text.

  14. Align all four components as follows:

    Hold the control key down and click on both jLabel1 and jdbTextField1. Right-click and choose Align Left so their left edges will be aligned. (When you are using null layout for prototyping a UI, you have alignment options available from the context menu.)

    Left align jLabel2 and jdbTextField2. Top align the two text fields, and top align the two labels.

  15. Add a JButton from the Swing tab to jPanel1. Put this button in the middle, midway between the two text fields. Set its text property to Update.

    Clicking this button will update the results of the parameterized query with the values entered into the minimum and maximum value entry fields.

  16. Select the Events tab of the Inspector, select the actionPerformed field, and double-click the value field to create an actionPerformed() event in the source code. The Source pane will display and the cursor will be located between the opening and closing braces for the new actionPerformed() event.

    Add the following code so the event looks like this:

    void jButton1_actionPerformed(ActionEvent e) {
    	try {
    		// change the values in the parameter row
    		// and refresh the display
    	   parameterRow1.setInt("low_no",
    	   		Integer.parseInt(jdbTextField1.getText()));
    		parameterRow1.setInt("high_no",
    			Integer.parseInt(jdbTextField2.getText()));
           queryDataSet1.refresh();
         }
         catch (Exception ex) {
           ex.printStackTrace();
         }
        }
    }
    
  17. Save your work, and run the application. It should look like similar to this:

    Parameterized query tutorial - running application

    To test the example, enter a new value in the minimum value entry field, then press the Update button. The table displays only those values above the new minimum value. Enter a new value in the maximum value entry field, then press the Update button. The table displays only those values below the new maximum value.

    To save changes back to the data source, you will need to add a QueryResolver. See "Saving changes from a QueryDataSet" to learn how to add a button with resolving code, or add a JdbNavToolbar component to the content pane and use its Save Changes button as a default query resolver.