Data Modeler

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,

  1. Choose File|New.
  2. Double-click the Data Module icon.

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,

  1. Right-click the module in the project pane.
  2. Choose the Open with Data Modeler command.

Building SQL queries with 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.

Adding a URL

If the dataset you want to access is not available from the Data Modeler, add the appropriate URL:

  1. Choose Database|Add URL to display the New URL dialog box.
  2. Select an installed driver in the drop-down Driver list.
  3. Type in the URL or use the Browse button to select the URL of the data you want to access.

Beginning a query

Begin building a query by selecting columns you want to add to the query, or by selecting an aggregate function that operates on a specific column:

As you select columns and add functions, your SQL SELECT statement is being built. To view it, click the SQL tab.

Selecting rows with unique column values

You might want to see only those rows that contain unique column values. If you add the DISTINCT keyword to the SELECT statement, only rows with unique values are returned. DISTINCT affects all columns in the SELECT statement.

To add the DISTINCT keyword, check the Distinct option on the Columns page.

Adding a Where clause

To add a Where clause to your SQL query, click the Where tab to display the Where 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:

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.

Adding an Order By clause

To specify how rows of a query are sorted,

  1. Select the query you want sorted in the Queries panel.

  2. Click the Order By tab in the Current Query panel.

  3. 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.

  4. Select the sort order direction from the Selected Column Sort Direction options.

    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.

Adding a Group By clause

To add a Group By clause to your query, click the Group By tab to display the Group By page.

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,

  1. Select the column you want the query grouped by.

  2. Click the > button to transfer the column name to the Group By box.

A Group By clause is then added to your SQL SELECT statement. To view it, click the SQL tab.

Editing the query directly

At any time while you are using the Data Modeler to create your query, you can view the SQL SELECT statement and edit it directly.

To view the SELECT statement, click the SQL tab. To edit it, make your changes directly in the SELECT statement.

Testing your query

You can view the results of your query in the Data Modeler.

To see the results of the query you are building,

  1. Click the Test tab.
  2. Click the Execute Query button.

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.

Building multiple queries

To build multiple queries, choose Queries|Add, and the Data Modeler is ready to begin building a new query. As you select columns in one or more tables, the table names appear in place of the <new query> field.

Specifying a master-detail relationship

To set up a master-detail relationship between two queries,

  1. Display the Link Queries dialog box in one of two ways: .

  2. Select a query to be the master query in the Master Query list.

  3. 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.

  4. Use the grid to visually specify the columns that link the master and detail queries together:

    1. Click the first row under the master query column of the grid to display a drop-down list of all the specified columns in the master table. Select the column you want the detail data to be grouped under.
    2. 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.

    3. 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.

Saving your queries

To save the queries you built,

  1. Choose File|Save in the Data Modeler and specify a name with an a .java extension.
  2. 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: