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:
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.
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.
Select File|New and double-click the Application icon
Select the Design tab to activate the UI designer.
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.
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".
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.
ParameterRow
component to the application from the Data Express tab.
parameterRow1
in the component tree to display the columns contained in the ParameterRow
.
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.
ParameterRow
, and set the following properties for it:
Property name | Value |
columnName | high_no |
dataType | INT |
default | 50 |
QueryDataSet
component from the Data Express tab to the application.
query
property to open the Query property editor.
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 |
Click the Parameters tab in the Query property editor.
Select parameterRow1
in the drop-down list box to bind the data set to the ParameterRow
.
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));
Add a DBDisposeMonitor
component from the More dbSwing tab. The DBDisposeMonitor
will close the JDataStore when the window is closed.
Set the dataAwareComponentContainer
property for the DBDisposeMonitor
to 'this'.
To add the components for viewing and manipulating the data in your application,
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.
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.
Close the running application.
To add the components that make the parameterized query variable at run time,
JPanel
component on the Swing Containers tab.
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.
Make sure its constraints
property is set to NORTH
. (If tableScrollPane1
suddenly shrinks, check that its constraints
property is still set to CENTER.)
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.
Drop a JdbTextField
component from the dbSwing tab into jPanel1
. This component holds the minimum value.
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.
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.
Add a JLabel
from the Swing tab to jPanel1
. This label will identify jdbTextField1
as the minimum field.
Click on jLabel1
in the UI designer and drag it to just above jdbTextField1
.
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.
Add another JdbTextField
and JLabel
to jPanel1
for the maximum value. Drag this pair of components to the right side of the panel.
Set the columns
property for jdbTextField2
to 10
, and its text
property to 50
.
Set the text property for jLabel2
to Maximum value
, and expand its width to show all the text.
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.
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.
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(); } } }
Save your work, and run the application. It should look like similar to this:
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.