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 runtime. 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 topic "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:

"Using parameters"
"Re-executing the parameterized query with new parameters"
"Binding parameters"
"Parameterized queries in master-detail relationships"

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 runtime. When the values in the ParameterRow are changed, the grid will automatically refresh its display to reflect only the records that meet the criteria specified with the parameters.

A sample project ParamQuery.jpr, located in the samples\borland\samples\tutorial\dataset\ParamQuery directory of your JBuilder installation, contains the completed tutorial.

To create the application,

  1. Create a new application.

    Select File|Close All. Select File|New. Double-click Application to start the Application Wizard and create a new application. Accept all defaults.

  2. Add a Database component and connect it to the Local InterBase sample employee table.

    To do this, select the Frame file in the Navigation pane. Select the Design tab in the UI Designer. Add a Database component from the Data Express tab to the Component tree. Open the connection property editor for the Database component by selecting the connection property ellipsis in the Inspector. Set the connection properties as follows:
    Property name Value
    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    Click the Test Connection button to check that the connection properties have been correctly set. Results of the connection attempt are displayed in the gray area below the button. When the gray area beneath the button indicates Success, click OK to close the dialog.

  3. Add a ParameterRow component from the Data Express tab to the Component tree.

    The following code is generated in the class definition:

    ParameterRow parameterRow1 = new ParameterRow();
    

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

  1. Select the ParameterRow in the Component tree. Double-click the component to display the column editor. 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

    The following code is generated in the class definition:

    Column column1 = new Column();
    

    The following code is generated in jbInit():

    column1.setColumnName("low_no");
    column1.setDataType(borland.jbcl.util.Variant.INT);
    column1.setDefault("15");
    parameterRow1.setColumns(new Column[] {column1});
    

  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 following code is generated in the class definition:

    Column column2 = new Column();
    

    The following code is generated in jbInit():

    column2.setColumnName("high_no");
    column2.setDataType(borland.jbcl.util.Variant.INT);
    column2.setDefault("50");
    

    The column definition for the parameterRow object is modified as follows:

    parameterRow1.setColumns(new Column[] {column1, column2});
    

  3. Add a QueryDataSet component from the Data Express tab. Set the query properties of the QueryDataSet component from the Inspector as follows:
    Property name Value
    Database database1
    SQL Statement select * from employee where emp_no >= :low_no and emp_no <= :high_no
    Place SQL Text In Resource Bundle uncheck

    On the Parameters page of the query dialog. Select parameterRow1 in the drop-down list box to bind the data set to the ParameterRow.

    On the Query page, click the Test Query button to ensure that the query is runnable. When the gray area beneath the button indicates Success, click OK to close the dialog.

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

    queryDataSet1.setQuery(new borland.sql.dataset.QueryDescriptor(database1,
      "select * from employee where emp_no <= :low_no and emp_no >= :high_no",
      parameterRow1, true, Load.ALL));
    

To view and manipulate the data in your application,

  1. Add a GridControl component from the JBCL tab to the UI Designer. Set its dataSet property to queryDataSet1. You'll notice that the grid fills with data.

  2. Add a NavigatorControl component from the JBCL tab to the UI Designer. Set its dataSet property to queryDataSet1. When the application is running, the navigator will enable you to browse and edit the data in the table.

To add the controls to make the parameterized query variable at runtime,

  1. Add a TextField component from the AWT tab to the UI Designer. This control holds the minimum value. Click the Events tab of the Inspector and triple-click on the keyPressed field. The Source pane will display and the cursor will be located at the correct spot for adding the following code:

    //void textField1_keyPressed(KeyEvent e) {
       //when the Enter key is pressed
       if (e.getKeyCode() == KeyEvent.VK_ENTER) {
         try {
           //change the value in the parameter row and refresh the display
           parameterRow1.setInt("low_no", Integer.parseInt(textField1.getText()));
           queryDataSet1.refresh();
         }
         catch (Exception ex) {
           ex.printStackTrace();
         }
        }
    // }
    
    

    The following code is automatically entered in jbInit():

        textField1.addKeyListener(new java.awt.event.KeyAdapter() {
    
    A new class with the same name is added to the Frame file as well.

    You can add a LabelControl to identify this field as the minimum field.

  2. Select File|Save All to save your work so far.

  3. Select the Design tab of the UI Designer. Add another TextField component from the AWT tab to the UI Designer. This control holds the maximum value. Select the Events tab of the Inspector and triple-click on the keyPressed field. The Source pane will display and the cursor will be located at the correct spot for adding the following code:

    //void textField2_keyPressed(KeyEvent e) {
       //when the Enter key is pressed
       if (e.getKeyCode() == KeyEvent.VK_ENTER) {
         try {
           //change the value in the parameter row and refresh the display
           parameterRow1.setInt("high_no", Integer.parseInt(textField2.getText()));
           queryDataSet1.refresh();
         }
         catch (Exception ex) {
           ex.printStackTrace();
         }
        }
     //}
    
    

    The following code is automatically entered in jbInit():

        textField2.addKeyListener(new java.awt.event.KeyAdapter() {
    
    A new class with the same name is added to the Frame file as well.

    You can add a LabelControl to identify this field as the maximum field.

  4. Select Run|Run to compile and run the application and test the example using different parameter values in the two TextFields.

To save changes back to the data source, click the Save Changes button on the Navigator.

To test the example, enter a new value in the TextField that holds the minimum value, then press Enter. The grid displays only those values above the new minimum value. Enter a new value in the TextField that holds the maximum value, then press Enter. The grid displays only those values below the new maximum value. To enter new values for both the minimum and maximum fields, enter one, then press Tab to move to the other field. Enter the new value in the other field, then press Enter to refresh the display for both new values.

Using parameters

To assign parameter values in a parameterized query, you must first create a ParameterRow and add named columns that will be the placeholders for the values to be passed to the query.

ParameterRow is a class that can be instantiated. It contains one row of storage, and is primarily used for parameterized queries and procedures. Unlike DataRow, it gets its columns from add/setColumns() methods. The reason that DataRow cannot be used for query/procedure parameters is that there may be multiple parameters for the same column. For example, if there is a WHERE clause that compares a column value to a start and end parameter, two parameter columns are needed for one data column.

The Row classes are used extensively in the DataExpress APIs. The ReadRow and ReadWriteRow are used much like interfaces that indicate the usage intent. By using a class hierarchy, implementation is shared, and there is a slight performance advantage over using interfaces.

The Row classes provide access to column values by ordinal and column name. Specifying columns by name is a more robust and readable way to write your code. Accessing columns by name is not quite as quick as by ordinal, but it is still quite fast if the number of columns in your DataSet is less than twenty, due to some patented high-speed name/ordinal matching algorithms. It is also a good practice to use the same strings for all access to the same column. This saves memory and is easier to enter if there are many references to the same column. The ParameterRow is passed in the queryDescriptor. The query property editor allows you to select a parameter row. Editing of ParameterRow, such as adding a column and changing its properties, can be done in the Inspector or in code.

For example, you create a ParameterRow with two fields, low_no and high_no. You can refer to low_no and high_no in your parameterized query, and compare them to any field in the table. See the examples below for how to use these values in different ways.

In JBuilder, parameterized queries can be run with named parameters, with parameter markers, or with a master-detail relationship. The following sections give a brief explanation of each.

Re-executing the parameterized query with new parameters

To re-execute the query with new parameters, set new values in the ParameterRow and then call QueryDataSet.refresh() to cause the query to be executed again with new parameter values. For example, to use a UI control to set the value of a parameter, you can use a SQL statement such as:

	SELECT * FROM phonelist WHERE lastname LIKE :searchname

In this example, the :searchname parameter's value could be supplied from a UI control. To do this, your code would have to:

  1. obtain the value from the control each time it changes
  2. place it into the ParameterRow object
  3. supply that object to the QueryDataSet
  4. call refresh() on the QueryDataSet

See "Tutorial: Parameterizing a query" for an example of how to do this with JBuilder sample files.

Binding parameters

Binding parameters means allocating resources for the statement and its parameters both locally and on the server in order to improve execution of the query.

Binding parameters to a data set can be done by constructing a QueryDescriptor with a ParameterRow (containing values). In a parameterized query, parameters from the ParameterRow are used to set the parameters of the query. There is a getParameterRow method on the QueryDataSet that you can use to obtain the current ParameterRow. You can add an execute method that takes a ReadRow to set the parameters.

To bind the values of a parameter to a data set,

  1. Create a ParameterRow with column names explicitly defined for named parameters (such as :fld1, for example).

  2. Get values for the columns (from a visual control, for example).

  3. Put the values in the ParameterRow.

  4. Execute or refresh the query (with executeQuery(), or refresh(), for example).

An example of binding parameters is included in "Tutorial: Parameterizing a query".

Binding may also be done by invoking refresh() to re-execute the query with the new value in the parameter as discussed in "Re-executing the parameterized query with new parameters".

Parameterized queries in master-detail relationships

In a master-detail relationship with DelayedDetailFetch set to true (to fetch details when needed), you can specify a SQL statement such as:

SELECT * FROM employee WHERE country = :job_country

In this example, :job_country would be the field that this detail data set is using to link to a master data set. You can specify as many parameters and master link fields as is necessary. In a master-detail relationship, the parameter must always be assigned a name that matches the name of the column. For more information about master-detail relationships and the DelayedDetailFetch parameter, see "Establishing a master-detail relationship".

In a master-detail descriptor, binding is done implicitly. Implicit binding means that the data values are not actually supplied by the programmer, they are retrieved from the master row and implicitly bound when the detail query is executed. See "Binding parameters" for more information on binding parameters.