IT Innovation

Modeling Tables and Components

Maintain your data-centric business services with visual diagrams.

By Steve Muench Oracle Employee ACE

January/February 2007

In previous columns, I've discussed how you can use Oracle JDeveloper to create data-bound Web pages for an application's view layer by dragging and dropping components in a visual editor. In a future column, I'll investigate similar capabilities that Oracle JDeveloper provides for building Swing applications. In this column, I focus on a different aspect of Oracle JDeveloper, examining two visual editors that let you work on "back-end" artifacts such as database tables and business components.

To follow along, make sure you're using the latest maintenance release of Oracle JDeveloper (, which you can download (for free) from As I have done in past columns, I'll be using the familiar EMP and DEPT tables in the SCOTT schema in Oracle Database to work through my examples.

Visualizing Your Database Schema

If you've ever worked with relational databases, chances are that you've used a database schema diagram to give you a bird's-eye view of the structure and relationships of your application's underlying tables. Oracle JDeveloper provides a database diagram capability that lets you design your database schema and synchronize your schema changes with the underlying database. To see how it works, let's create a simple diagram that displays the EMP and DEPT tables.

Using Oracle JDeveloper, start by creating a database connection that maps to the SCOTT schema; name it scott . Then create a new application by using the Web Application [JSF, ADF BC] template, specifying oramag as the application package prefix. Oracle JDeveloper will automatically create Model and ViewController projects for your new application. Next, create a third project, by selecting File -> New from the main menu to bring up the New Gallery wizard. Select the Project category, select Empty Project , and enter Database for the project name in the Create Project dialog box. Next, go to the Application Navigator and double-click the Database project to edit its project properties. Go to the Technology Scope page, shuttle Database to the Selected Technologies list, and click OK . This last step tells Oracle JDeveloper that I will be using this project for database modeling. It simplifies my database design work, by having Oracle JDeveloper filter out choices in various dialog boxes that aren't relevant to the task at hand.

The next step is to create a database diagram. Right-click the Database project in the Application Navigator, and select New . In the New Gallery dialog box, select the Diagrams category and Database Diagram . Name the diagram Database Design . Click OK , and a new diagram appears in the editor. Next, in the Connection Navigator, expand the Database folder, the scott connection, and the corresponding SCOTT schema to show the folders containing the SCOTT schema's objects. Expand the Tables folder, and hold down the Ctrl key while selecting both the DEPT and EMP tables. Drag the selected tables onto the diagram, and release the mouse button. In the Create from Database Object dialog box, click OK to create offline database objects. Your resulting diagram should look similar to Figure 1, showing two tables with a foreign key constraint between them.

Next, select the DEPT table shape in the diagram. If you don't already see the Property Inspector and the Component Palette in your Oracle JDeveloper workspace, use the corresponding options on the View menu to display them. The Property Inspector enables you to configure any of the visual aspects of your selected table, and the Component Palette enables you to create new schema objects by dropping them onto the diagram. The Component Palette supports a variety of schema objects, including tables, views, sequences, and synonyms. Furthermore, you can perform many common tasks by making changes directly to the diagram. For example, to add a column to a table, simply click inside the dotted rectangle within the table shape, enter the new column name information (such as MANAGER_ID:NUMBER(5,2)) , and press Enter. You can also rename a column or change its datatype by clicking the column entry and editing it in place. In addition, you can define one-to-many relationships between two tables by simply clicking the Foreign Key icon in the Component Palette, clicking the source table, and then clicking the target table. And if you double-click a schema object or relationship line in the diagram, the appropriate visual editor appears, allowing you to view and modify the object's full definition.

The tables and other schema objects are known as "offline" objects, because the changes you make in the diagram do not immediately cause updates to the database. Instead, you save your changes to disk, and when you are ready to make database changes, you generate a DDL script that creates or alters your database to match your database design. To generate a DDL script, right-click the database diagram and select Generate -> Data Definition Language for Diagram from the context menu.

Creating Your Business Components

You can use Oracle JDeveloper business components diagrams to visually document and maintain the Oracle Application Development Framework (ADF) objects that implement your business services. To demonstrate, let's create a simple diagram to work with entity objects, view objects, and an application module based on the underlying DEPT and EMP database tables. In the Application Navigator, right-click your Model project and select New to open the New Gallery dialog box. Select the Diagrams category and the Business Components Diagram icon. Name the diagram Business Service , provide oramag.model as the package name, and click OK . Next, go to the Connection Navigator, select the DEPT and EMP tables (as you did in the last section), and drag them onto the diagram. In the Create from Database Object dialog box, select Business Component Entity Objects , and click OK .

As a result, Oracle JDeveloper creates new Dept and Emp entity objects and adds them to the diagram. It also creates an association between the objects based on the foreign key constraint specified in the table definition. Note that you can now modify these components in much the same way you did in the previous section. You can use the Property Inspector to change a component's visual aspects, the Component Palette to create new components, and onscreen editing functions to perform common inline tasks such as adding or modifying attributes. Furthermore, you can double-click any component to bring up the respective component editor for in-depth editing.

Now drag a view object component from the Component Palette, and drop it onto the diagram. Give it the name EmployeeList , by typing that name directly into the new view object shape. Next, select the data the View Object query will reference. To do so, hold down the Ctrl key while selecting the Empno , Ename , and Sal attributes from the Emp entity on the diagram. Then drag the selected attributes to the new view object and release the mouse button. As a result, the Emp entity now becomes the primary entity for the new view object and the selected attributes are added to its attribute list.

You can also add related join information to the view object. For example, to add the department name of each employee, just select the Dname attribute from the Dept entity and drop it onto the view object. Note that Oracle JDeveloper automatically adds the primary key attribute (in this case, Dept.Deptno ) from a secondary entity and shows a message alert indicating that it has done so.

Next, drop an Application Module component from the Component Palette onto the diagram. Give it the name EmpModule , by typing that name directly into the new application module shape. Add an instance of the EmployeeList view object to the EmpModule's data model, by dragging the view object and dropping it onto the application module. By default, Oracle JDeveloper gives the view instance the name EmployeeList1, but you can change it to something more meaningful (such as AllEmployees ), by clicking the view instance shape and typing a new name. Finally, you can test the application module directly from the diagram by selecting it and selecting Test from the context menu.

Your diagram will automatically update to reflect changes you make to any component, regardless of how you make them. For example, I have used familiar business components wizards in past articles to create view objects and view links. In this example, you could create a Departments view object based on the Dept entity and a view link between Departments and EmployeeList. Then you could use the Application Module editor to add master/detail linked view instances named Departments and EmployeeList to its data model. After you do so, the diagram will update to automatically show the changes to the data model.

If you drop the new Departments view object and view link from the Application Navigator onto the diagram, fiddle a bit with the colors and layout of the shapes, and set a few options as described in the next section, you'll end up with a diagram similar to the one shown in Figure 2.

Perfecting, Printing, and Publishing the Diagram

Oracle JDeveloper provides a lot of display options to help you get your diagrams to look exactly the way you want. By right-clicking the diagram and selecting Visual Properties from the context menu, you can control the grid spacing, page breaks, and snap-to-grid behavior of your diagram. Furthermore, by using the Property Inspector, you can control the exact display definition of each shape in your diagram. In this example, you can turn off the display of package names (as shown in Figure 2), by simply right-clicking each shape and unchecking the Show Package check box in the Visual Properties dialog box. Furthermore, you can specify the display mode, by selecting the View As menu from the shape's context menu. Every shape can be shown in a compact or standard display mode, and container shapes can also be shown in an expanded mode. For example, in Figure 2, the Departments view object is shown in compact mode whereas the EmployeeList view object is shown in standard mode.

figure 1
Figure 1: Oracle JDeveloper database diagram showing the EMP and DEPT tables
figure 2
Figure 2: Business components diagram with entity objects, view objects, and an application module

You can also multiple-select items, and you can use the Property Inspector with multiple selections to set properties on multiple items in a single pass. One handy option is to use the Select All This Type option from a shape's context menu. This option selects all of the shapes on the diagram of the same kind as the one initially selected. You can use this option to easily color-code different kinds of components with just a few clicks.

When you're happy with your diagram, you can print it (by using File -> Print ). If you need to include the diagram as a graphic in documentation or an e-mail, you can export the diagram to an external file. To do this, select Model -> Publish Diagram from the Oracle JDeveloper main menu. In the Publish Diagram dialog box, select one of the four supported export formats— PNG , JPEG , SVG , or SVGZ (compressed)—from the File Type list.

This whirlwind tour is meant to help you boost your own visual development skills up a notch. Hopefully, you'll use these and other diagramming tools provided in the Oracle JDeveloper environment on your next project.

Next Steps

READ more about Oracle JDeveloper 10g and Oracle ADF

 Oracle JDeveloper 10g


Photography byLuana Azevedo,Unsplash