Creating data modules using the Data Modeler

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

The JBuilder IDE provides tools that can help you quickly create applications that query a database. The Data Modeler can build data modules that encapsulate a connection to a database and the queries to be run against the database. The Data Module Application wizard can then use that data module to create a client-server database application.

Creating queries with the Data Modeler

JBuilder can greatly simplify the task of viewing and updating your data in a database. The JBuilder Data Modeler lets you visually create SQL queries and save them in JBuilder Java data modules.

To begin a new project,

  1. Choose File|New Project to start the Project wizard.
  2. Choose a location and name for the project.
  3. Click the Finish button.

For more specific information about creating projects, see the online help topic "Creating and managing projects."

To display the Data Modeler,

  1. Choose File|New.
  2. Double-click the Data Module icon.
  3. Enter the package and class name for the data module you are creating, and check the Invoke Data Modeler option.
  4. Click OK. The Data Modeler displays.

To open an existing Java data module in the Data Modeler,

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

Opening a URL

To begin building an SQL query, you must first open a connection URL. There are several ways you can do this:

If the database you want to access is not listed under Database URLs in the Data Modeler, you can add it.

  1. Choose Database|Add Connection URL to display the New URL dialog box.
  2. Select an installed driver in the drop-down Driver list or type in the driver you want. For the samples, you can select com.borland.datastore.jdbc.DataStoreDriver.
  3. Type in the URL or use the Browse button to select the URL of the data you want to access. For the samples, you can select /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. You can use the Browse button to browse to this file to reduce the chance of making a typing error.

Beginning a query

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

To view the tables, double-click the Tables node or choose the Tables expand icon.

From the list of tables, select the table you want to query and double-click it. Double-click the Columns node to view all the columns in the selected table.

The SELECT statement is the data retrieval statement that returns a variable number of rows of a fixed number of columns. The Data Modeler helps you build the SELECT statement.

As you select columns and add functions, your SQL SELECT statement is being built. When you aggregate data, you must include a GROUP BY clause. For information on GROUP BY clauses, see "Adding a GROUP BY clause". To view it, click the SQL tab.

SQL

Adding a Group By clause

The GROUP BY clause is used to group data returned by a select statement and is often used in conjunction with aggregate functions. When used with aggregate functions, the following process is followed:

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

The Available Columns box 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.

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

Adding a WHERE clause to a select statement specifies the search condition that has to be satisfied for rows to be included in the result table. To add a Where clause to your SQL query, click the Where tab.

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 clause of the query in the Where Clause box.

To transfer a column as a column name to the Where Clause box, select a column in the Columns list and click the Paste Column button.

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.

Adding an Order By clause

An ORDER BY clause is used to sort or rearrange the order of the data in the result table. To specify how rows of a table 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 Sort Order 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.

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. The query created in this topic will not execute, the topics were presented in a way that made them most understandable, but not in a way that enabled the query to run property.

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

    Link Queries dialog box

  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.

    The Master Query and Detail Query fields are filled with suggested fields. If they are not the ones you want, make the necessary changes.

  4. Use the table 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 table 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 table 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.

For more information about master-detail relationships, see "Establishing a master-detail relationship".

Saving your queries

To save the data module you built,

  1. Choose File|Save in the Data Modeler and specify a name with a .java extension.
  2. Exit the Data Modeler.

    The resulting file appears in your project.

  3. Compile the data module.

Double-click the file in the project pane to open it in the content pane to view the code the Data Modeler generated.


Generating database applications

From your compiled data module, JBuilder can generate two-tier client-server applications with its Data Module Application wizard.

To display the Data Moduler Application wizard, select the Data Module Application wizard icon in the object gallery:

  1. Choose File|New and select the Applications tab.

  2. Double-click the Data Module 2-Tier Application icon.

    Data Module 2-Tier Application

  3. Specify the data module file you want to generate an application from in the dialog box that appears. You can select any data module that you have or you can select one that was created by the Data Modeler.
  4. Choose OK.

The wizard creates a database application for you. The wizard generates several JAVA files and an HTML file.

Using a generated data module in your code

Once you've created a data module with the Data Modeler, you can use it in applications that you write. Follow these steps:

  1. Run the Use DataModule Wizard. In the source code of the frame for your application, it adds a setModule() method that identifies the data module. The setModule() method the wizard creates calls the frame's jbInit() method. The wizard also removes the call to jbInit() from the frame's constructor.
  2. In the source code of your application file, call the frame's setModule() method, passing it the data module class.

For example, suppose you have used the Data Modeler to create a data module called CountryDataModelModule. To access the logic stored in that data module in an application you write, you must add a setModule() method to your frame class.

To add the setModule() method and remove the jbInit() method from the frame's constructor,

  1. Add the data module to the list of required libraries (in Project|Project Properties dialog).
  2. Choose Wizards|Use Data Module while the frame's source code is visible in the editor.
  3. Specify the data module you want to use with the wizard.
  4. Select the Application Sets The Instance By Calling setModule() option.
  5. Choose OK.

The resulting code of the frame would look like this:

package com.borland.samples.dx.myapplication;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
//imports package where data module is
import com.borland.samples.dx.datamodule.*;

public class Frame1 extends JFrame {
  BorderLayout borderLayout1 = new BorderLayout();
  CountryDataModelModule countryDataModelModule1;

  //Construct the frame without calling jbInit()
  public Frame1() {
    enableEvents(AWTEvent.WINDOW_EVENT_MASK);
  }

  //Component initialization
  private void jbInit() throws Exception  {
    this.getContentPane().setLayout(borderLayout1);
    this.setSize(new Dimension(400, 300));
    this.setTitle("Frame Title");
  }

  //Overridden so we can exit on System Close
  protected void processWindowEvent(WindowEvent e) {
    super.processWindowEvent(e);
    if(e.getID() == WindowEvent.WINDOW_CLOSING) {
      System.exit(0);
    }
  }

  // The Use Data Module wizard added this code
  public void setModule(CountryDataModelModule countryDataModelModule1) {
    this.countryDataModelModule1 = countryDataModelModule1;
    try  {
      jbInit();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Note that the frame's jbInit() method is now called after the module is set and not in the frame's constructor.

Next you must call the new setModule() method from the main source code of your application. In the constructor of the application, call setModule(), passing it the data module class. The code of the main application would look like this:

package com.borland.samples.dx.myapplication;

import javax.swing.UIManager;

public class Application1 {
  boolean packFrame = false;

  //Construct the application
  public Application1() {
    Frame1 frame = new Frame1();
	
    

// This is the line of code that you add frame.setModule(new untitled3.CountryDataModelModule()); //Validate frames that have preset sizes //Pack frames that have useful preferred size info, e.g. from their layout if (packFrame) frame.pack(); else frame.validate(); frame.setVisible(true); } //Main method public static void main(String[] args) { try { UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName()); } catch(Exception e) { } new Application1(); } }