This type of lookup retrieves values from a specified table based on criteria you specify and displays it as part of the current table. In order to create a calculated column, you need to create a new Column object in the StorageDataSet, set its calcType appropriately, and code the calcFields event handler. The lookup values are only visible in the running application. Lookup columns can be defined and viewed in JBuilder, but JBuilder-defined lookup columns are not resolved to or provided from its data source, although they can be exported to a text file.
An example of looking up a field in a different table for display purposes is looking up a part number to display a part description for display in an invoice line item or looking up a zip code for a specified city and state.
This tutorial shows how to use a calculated column to search and retrieve an employee name (from EMPLOYEE) for a given employee number in EMPLOYEE_PROJECT. This type of lookup field is for display purposes only. The data this column contains at run time is not retained because it already exists elsewhere in your database. The physical structure of the table and data underlying the data set is not changed in any way. The lookup column will be read-only by default. This project can be viewed as a completed application by running the sample project Lookup.jpr, located in the /samples/DataExpress/Lookup subdirectory of your JBuilder installation.
Open the connection property editor for the Database component by selecting, then double-clicking the connection property ellipsis in the Inspector. Set the connection properties to the JDataStore sample employee table as follows. The Connection URL points to a specific installation location. If you have installed JBuilder to a different directory, point to the correct location for your installation.
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, which may be different on your system. This sample has been modified to find the file on your system. |
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 in the status area. When the connection is successful, click OK.
Select the query property of the QueryDataSet component in the Inspector, double-click its ellipsis to open the QueryDescriptor dialog, and set the following properties:
Property name | Value |
Database | database1 |
SQL Statement | SELECT * FROM EMPLOYEE |
Click Test Query to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.
To view the data in your application we need to add some UI components and bind them to the data set. To do this,
The JdbNavToolBar will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.
Among other information, the status label displays information about the current record or current operation.
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 designer displays live data at this point.
To add the lookup functionality,
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select * from EMPLOYEE_PROJECT |
Click Test Query. When successful, click OK.
Property name | Value |
calcType | CALC |
caption | EMPLOYEE_NAME |
columnName | EMPLOYEE_NAME |
dataType | STRING |
The new column will display in the list of columns and in the table control. No data will be displayed in the lookup column in the table in the designer. The lookups are only visible when the application is running. The data type of STRING is used here because that is the data type of the LAST_NAME column which is specified later as the lookup column. Calculated columns are read-only, by default.
void queryDataSet2_calcFields(ReadRow changedRow, DataRow calcRow, boolean isPosted) throws DataSetException{ // Define a DataRow to hold the employee number to look for in // queryDataSet1, and another to hold the row of employee data // that we find. DataRow lookupRow = new DataRow(queryDataSet1, "EMP_NO"); DataRow resultRow = new DataRow(queryDataSet1); // The EMP_NO from the current row of queryDataSet2 is our // lookup criteria. // We look for the first match, since EMP_NO is unique. // If the lookup succeeds, concatenate the name fields from the // employee data, and put the result in dataRow; // otherwise, let the column remain blank. lookupRow.setShort("EMP_NO", changedRow.getShort("EMP_NO")); if (queryDataSet1.lookup(lookupRow, resultRow, Locate.FIRST)) calcRow.setString("EMPLOYEE_NAME", resultRow.getString("FIRST_NAME") + " " + resultRow.getString("LAST_NAME")); } }
queryDataSet1.open();
Change a value in the EMP_NO field to a valid employee number. When the application is running, the values in the calculated lookup column will automatically adjust to changes in any columns, in this case the EMP_NO column, referenced in the calculated values. If the EMP_NO field is changed, the lookup will display the value associated with the current value when that value is posted.