This is a feature of JBuilder Professional and Enterprise.
The Data Modeler assists you in defining one or more SQL queries that connect to JDBC databases. It can also help you define master-detail relationships between queries. After you build your SQL queries, you store the result as a JBuilder data module in a .java file.
To display the Data Modeler to begin a new query,
If you don't have a project open when you attempt to display the Data Modeler, the Project wizard appears first. When you finish using the Project wizard, the Data Modeler appears.
To open an existing Java data module in the Data Modeler,
To begin building an SQL query, double-click the URL that accesses your data or single-click the plus sign (+). Or select the URL and choose the Data Modeler's Database|Open Connection URL.
If you are asked for a user ID and password, enter the appropriate values and click OK.
Click the Copy button.
The name of the selected column appears in the Selected Columns box and the table name appears in the Queries panel at the top. Continue selecting columns until you have all you want from that table. If you want to select all columns, click the Copy All button.
To add an aggregate function to the query,
Click the column whose data values you want aggregated in the Available Columns list.
Click the function you want to use on that column from the Aggregate Functions list.
If you want the function to operate on only unique values of the selected column, check the Distinct check box.
Choose Add Aggregate to add the function to your query.
As you select columns and add functions, your SQL SELECT statement is being built. To view it, click the SQL tab.
To add the DISTINCT keyword, check the Distinct option on the Columns page.
The Columns list on the left contains the columns of tables in the currently selected query in the Queries panel of the Data Modeler. Use the Columns, Operators, and Functions lists to build the query in the Where Clause box:
To transfer a column as a parameter as in a parameterized query, select a column in the Columns list and click the Paste Parameter button.
Select the operator you need in the Operators drop-down list and click the Paste button. Every Where clause requires at least one operator.
If your query requires a function, select the function you need in the Functions drop-down list and click the Paste button.
By pasting selections, you are building a Where clause. You can also directly edit the text in the Where Clause box to complete your query. For example, suppose you are building a Where clause like this:
WHERE COUNTRY='USA'
You would select and paste the COUNTRY column and the = operator. To complete the query, you would type in the data value directly, which in this case is 'USA'.
When you are satisfied with your Where clause, click the Apply button. The Where clause is added to the entire SQL SELECT statement. To view it, click the SQL tab.
Select the column you want the query sorted by in the Available Columns box and click the button with the > symbol on it to transfer that column to the Order By box.
The Ascending option sorts the specified column from the smallest value to the greatest, while the Descending option sorts the specified column from the greatest value to the smallest. For example, if the sort column is alphabetical, Ascending sorts the column in alphabetical order and Descending sorts it in reverse alphabetical order.
You can sort the query by multiple columns by transferring more than one column to the Order By box. Select the primary sort column first, then select the second, and so on. For example, if your query includes a Country column and a Customer column and you want to see all the customers from one country together in your query, you would first transfer the Country column to the Order By box, then transfer the Customer column.
The Available Columns lists the columns of the currently selected query in the Queries panel of the Data Modeler. The Group By box contains the column names the query will be grouped by. By default, the query is not grouped by any column until you specify one.
To add a Group By clause to your query,
A Group By clause is then added to your SQL SELECT statement. To view it, click the SQL tab.
To view the SELECT statement, click the SQL tab. To edit it, make your changes directly in the SELECT statement.
To see the results of the query you are building,
If your query is a parameterized query, a Specify Parameters dialog box appears so you may enter the values for each parameter. When you choose OK, the query executes and you can see the results. The values you entered are not saved in the data module.
Choose Queries|Link.
In the Queries panel, drag the mouse pointer from the query you want to be the master query to the one you want to be the detail query.
Select a query to be the master query in the Master Query list.
Select a query to be the detail query in the Detail Query list.
If you've displayed the Link Queries dialog box by dragging from the master query to the detail query, the Master Query and Detail Query fields will be filled in appropriately for you. If you used the Link button instead, the fields will also be filled in; if they are not correct, you can make the necessary changes.
Use the grid to visually specify the columns that link the master and detail queries together:
Click the first row under the detail query column of the grid to display a drop-down list of all columns that are of the same data type and size as the currently selected master column. Select the appropriate column, thereby linking the master and the detail tables together.
Choose OK.
When the Link Queries dialog box closes, an arrow is shown between the two queries in the Queries panel showing the relationship between them.
To save the queries you built,
Exit the Data Modeler.
The resulting file appears in your project. Select the file in the project pane to view the code the Data Modeler generated: