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.
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,
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.
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.
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.
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.
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 |
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));
To view and manipulate the data in your application,
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.
You'll notice that the table in the designer displays live data.
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(); } }
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.