Using parameterized queries to obtain data from your database

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.

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.

To create the application,

  1. Select File|Close All.

  2. Select File|New. Double-click the Application icon and accept all defaults to create a new application.

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

  4. Click the Database component on the Data Express tab of the component palette, then click anywhere in the Design pane to add the component to the application.

    Open the connection property editor for the Database component by double-clicking the connection property ellipsis in the Inspector. Set the connection properties to the JDataStore sample EMPLOYEE table, as follows:
    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL jdbc:borland:dslocal:/usr/local/jbuilder/samples/JDataStore/datastores/employee.jds (the employee.jds database is located under the samples directory of your JBuilder installation directory, which may be different on your system. You can browse to this file to reduce the chance of making a typing error, click the Browse button)
    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.

    The code generated by the designer for this step can be viewed by selecting the Source tab and looking for the ConnectionDescriptor code. Click the Design tab to continue.

  5. Add a ParameterRow component from the Data Express tab to the Structure pane.

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

  1. Select the expand glyph to the left of parameterRow1 to display the columns contained in the ParameterRow. Select <new column>, and set the following properties for the new column in the Inspector:
    Property name Value
    columnName low_no
    dataType INT
    default 10

    The code generated by the designer for this step can be viewed by selecting the Source tab and looking in the jbInit() method. Click the Design tab to continue.

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

    The code generated by the designer for this step can be viewed by selecting the Source tab and looking in the jbInit() method. Click the Design tab to continue.

  3. Add a QueryDataSet component from the Data Express tab to the Structure pane. Set the query property of the QueryDataSet component from the Inspector 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. Select parameterRow1 in the drop-down list box to bind the data set to the ParameterRow.

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

  6. Select a DBDisposeMonitor component from the "More dbSwing" tab. Click in the Structure pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

  7. Set the DBDisposeMonitor's dataAwareComponentContainer property to this.

To view and manipulate the data in your application,

  1. Add a TableScrollPane component. To do this,

    1. Select a TableScrollPane component from the dbSwing tab of the component palette.
    2. Click and drag the TableScrollPane to the center of the Design pane.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  2. Drop a JdbTable component from the dbSwing tab into the center of the TableScrollPane 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 to vary the parameterized query at run time,

  1. Select a JPanel component from the Swing Containers tab, click and drag in the top of the Design pane to add this panel to the Design pane.

  2. Set the preferred size of the JPanel big enough to contain components for the UI. Select preferredSize in the Inspector. Enter '200, 100' for the value of the preferredSize property.

  3. Add a JLabel from the Swing tab to jPanel1 to label the minimum value entry field. In the Inspector, set its text property to Minimum value.

  4. Add a JdbTextField component from the dbSwing tab to jPanel1. This component holds the minimum value. Set its colunms property to 10 to give it a fixed width. Set its text property to 10 to match the default minimum parameter value you entered earlier.

  5. Add another JLabel from the Swing tab to jPanel1 to identify the maximum value entry field. In the Inspector, set its text property to Maximum value.

  6. Add another JdbTextField component from the dbSwing tab to jPanel1. This component holds the maximum value. Set its colunms property to 10 to give it a fixed width. Set its text property to 50 to match the default maximum parameter value you entered earlier.

  7. Add a JButton from the Swing tab to jPanel1. Clicking this button will update the results of the parameterized query with the values entered into the minimum and maximum value entry fields. Set its text property to Update. Select the Events tab of the Inspector, select the actionPerformed field, and double-click it. The Source pane will display and the cursor will be located at the correct spot for adding the following code:

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

  8. Select File|Save All to save your work.
  9. Select Run|Run Project to compile and run the 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 your 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.