Creating a calculated aggregated column is simpler than creating a calculated column, because no event method is necessary (unless you are creating a custom aggregation component). The aggregate can be computed for the entire data set, or you can group by one or more columns in the data set and compute an aggregate value for each group. The calculated aggregated column is defined in the data set being summarized, so every row in a group will have the same value in the calculated column (the aggregated value for that group). The column is hidden by default. You can choose to show the column or show its value in another control, which is what we do in the following tutorial. In this example, we will query the SALES table and create a JdbTextField component to display the sum of the TOTAL_VALUE field for the current CUST_NO field. To do this, we first create a new column called GROUP_TOTAL. Then set the calcType property of the column to aggregated and create an expression that summarizes the TOTAL_VALUE field from the SALES table by customer number and places the resulting value in the GROUP_TOTAL column. The completed project is available in the /samples/DataExpress/Aggregating 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 aggregating functionality,
SELECT CUST_NO, PO_NUMBER, SHIP_DATE, TOTAL_VALUE from SALES
Click the Test Query button to test the query and ensure its validity. When successful, click OK.
Property name | Value |
caption | GROUP_TOTAL |
columnName | GROUP_TOTAL |
currency | True |
dataType | BIGDECIMAL |
calcType | aggregated |
visible | Yes |
A new column is instantiated and the following code is add to the jbInit() method. To view the code, select the Source tab to view, select the Design tab to continue.
column1.setCurrency(true); column1.setCalcType(com.borland.dx.dataset.CalcType.AGGREGATE); column1.setCaption("GROUP_TOTAL"); column1.setColumnName("GROUP_TOTAL"); column1.setDataType(com.borland.dx.dataset.Variant.BIGDECIMAL);
No data will be displayed in the JdbTextField in the designer. The calculations are only visible when the application is running. The data type of BIGDECIMAL is used here because that is the data type of the TOTAL_VALUE column which will be used in the calculation expression. Aggregated columns are always read-only.
This step ensures the columns that will display in the table are persistent. Persistent columns are enclosed in brackets in the Structure pane.
In the agg property editor,
Based on above selections, you will have a sum of all sales to a given customer.
Click OK.
This step generates the following source code in the jbInit() method:
column1.setAgg(new com.borland.dx.dataset.AggDescriptor(new String[] {"CUST_NO"}, "TOTAL_VALUE", new com.borland.dx.dataset.SumAggOperator()));
Modify a value in the TOTAL_VALUE column. When the application is running, the values in the aggregated field (GROUP_TOTAL) will automatically adjust to changes in the TOTAL_VALUE field. Also, the value that displays in the JdbTextField is the sum of the TOTAL_VALUE entries for the customer in the currently selected row.