In this tutorial you review an existing database schema by creating a schema diagram. You also create a plan of the application by creating a page flow diagram.
Time to Complete
Approximately 30 minutes
This tutorial covers the following topics:
Place the cursor over this icon or hit Alt-a (Alt-Shift-a on Firefox) to load and view all the screenshots for this tutorial. Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
As a Forms or Designer developer, before you begin to build your application it is helpful to have an idea of the tables or views the application will use, the screens that you will need to build, and how they will work together. JDeveloper's modeling and page flow tools enable you to visualize these elements of the application as you build it.
In this tutorial you quickly create a diagram of the tables you use in this workshop. You review the tables and generate the DDL. You then sketch out the flow of the application in the form of a page flow diagram that is used to control the navigation and behavior of the application that you develop.
You have been asked to create a small Order Entry application based on tables in a schema that already exists. You know there are query pages, update and data capture pages and you have been given the rough sketches of the desired layout for the completed screens. You first want to verify that the schema is appropriate for the task and as such would like to review the full schema before proceeding. You also want to capture the flow of the application in a high-level diagram from which you can build the various components of the application.
Before starting this tutorial, you should:
Have access to or have installed Oracle JDeveloper version 11.1.1.
Have access to or install an Oracle database 11g.
Have access to or install the OE sample schema. If you have dropped the sample schemas from Oracle 10g, or if you did not install them in the first place, you should install them following the instructions in Oracle Database Sample Schemas 10g Release 1 (10.1).
Warning: For security reasons, it may not be advisable to install the sample schemas into a production database. If you do install them, you should use passwords other than default passwords, although default passwords are used in the examples shown in tutorials provided by Oracle. When you are finished using the sample schemas for tutorial and demo purposes, you may drop them by issuing the following SQL*Plus command for each installed sample schema:
DROP USER <schema_name> CASCADE;
If you are using the sample schemas for the first time, you may find that you must unlock the OE sample schema user, and then grant CONNECT and RESOURCE roles to it. You can do this by using Oracle Enterprise Manager, which is part of Oracle.
Alternatively, you can issue the following SQL*Plus commands:
ALTER USER oe IDENTIFIED BY OE ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE to OE;
Java Database Connectivity (JDBC) is a standard application programming interface (API) that is used for connecting a Java application to relational databases. To create a JDBC connection to the OE schema, perform the following steps:
Choose View | Database Navigator.
Right-click the IDE Connections node and choose New Connection from the context menu.
The Create Database Connection dialog:
In the Connection Name field type a name for the connection. Name the connection oeconn .
In the Username and Password fields, type oe and oe .
Select Deploy password.
In the Host name field, type the name (or IP address) of the computer where the database is located. If the database is on your PC, the value is the default: localhost.
In the JDBC Port and SID fields, enter the information for the connection to the database. If you do not know these values, check with your database administrator. The default values are 1521 and ORCL.
Click Test Connection.
If the database is available and the connection details are correct, you will see Success! in the Status window.
If an error occurs, verify the settings with your database administrator, make any changes necessary, and then retest the connection.
Click OK. The connection now appears below the Connections node in the Database Navigator.
You can create a web application without modeling the database schema. In some instances you may know the schema structure and so this step is unnecessary. However, you may have no tables at all and so need to model the schema and generate the DDL, or you may simply want a visual picture of the schema.
In this tutorial, you work with an existing schema, and you use a database diagram to generate the DDL for all the tables. To model the existing schema tables and generate the DDL script, perform the following steps:
If you want to create a web application using JDeveloper, you start by creating an application. An application is the highest level in the control structure, and is made up of one or more projects. JDeveloper projects are organizational structures used to logically group related files. You can use projects to organize your source code. JDeveloper uses templates to expose only those technology choices required for the particular aspect of work you are interested in. By applying the Web Application[JSF, ADF BC] template, two projects, Model and ViewController, are created for you. In this example you want to review the tables in the database schema and so for that purpose, you create an additional project.
To do this, perform the following steps:
In the File menu chose New... In the New gallery choose Applications under the General node and then Fusion Web Application (ADF)
In the Create Application dialog, enter the Application Name OrderEntry. Notice that as you enter the application name, the directory name changes automatically.
Accept the defaults for the rest of the steps. Click Finish.
The Applications Navigator shows the OrderEntry application and two default projects: Model and ViewController.
Next we'll create an extra project, used for the diagrams.
Click the New icon In the New Gallery, expand General and select Projects from the Categories list. Select Generic Project from the Items list and click OK.
In the Create Project dialog, enter the Project Name OESchema.
Select Database (Offline) from the Available Technologies list. Click Add to shuttle it to the Selected list.
By completing this action, as you work with the OESchema project, you will only be presented with database related information. You can change this setting at any stage to display all available options.
The Applications Navigator shows that the OrderEntry application now has three projects: Model, ViewController, and OESchema.
Click Save All or select File | Save All to save your work.
In this section you display a selection of tables from the OE schemas on a diagram. To display objects from online schemas on a single diagram, perform the following steps:
In the Applications Navigator, right-click the OESchema project and select New from the context menu
In the Categories list of the New Gallery, expand General and select Diagrams.
Because of the technology scope that you set for the project, the items available in the Items list are limited. Select Database Diagram and click OK.
In the Create Database Diagram dialog, enter the name OESchema and give the package the name OE.
The database diagram opens in the visual editor with the Database Component Palette to the right.
Switch to the Connections tab to display the Connections Navigator. (If it is not available, use the menu choice View | Database Navigator.
Expand the oeconn connection, the OE schema, and the Tables node.
Select the tables CUSTOMERS, ORDERS, ORDER_ITEMS and PRODUCT_INFORMATION and drag them onto the page.
In the Specify Location dialog accept the defaults.
Click Save All or select File | Save All to save your work.
Note: In this section, you review the tables and some of the features available in JDeveloper for enhancing your diagram visually. If you do not have time, you can do some of this section or move directly to the section 2.4.
Note: Dragging the tables from the online connection onto the diagram causes two things to happen: 1) all the tables were created as offline tables in the project and 2) they were drawn on the diagram.
You can review the structures in a number of ways. To review the structures, do the following:
In the Applications Navigator, expand the OESchema project, the Offline Database Sources node, and the OE package.
Select any one of the tables and note the columns, constraints and indexes displayed in the Structure window below the Applications Navigator.
Hint: If the Structure window is not visible, select View | Structure from the menu.
Click the Thumbnail view, or select View | Thumbnail from the menu if the Thumbnail is not visible.
Drag the little black square around the Thumbnail to focus on different areas of the main database diagram. If your mouse has a wheel, rolling the wheel will zoom the diagram in and out.
To make the diagram appear less complicated and easier to work with, select any number of objects in the diagram and select Diagram | View As | Compact from the main menu. This eliminates the column- and constraint names from the display.
You can reverse this action at any time by right-clicking the diagram and selecting Diagram | View As | Symbolic from the main menu.
If you change the shapes from Standard to Compact, then you may need to redo the layout. Right-click a blank space in the diagram and select Lay Out Shapes | Hierarchical (Bottom to Top) from the context menu.
You can use the Zoom controls at the top left of the diagram editor to display a magnified or compacted diagram.
You can use color to visually distinguish the tables on a diagram. As an example isolate CUSTOMER visually.
Click the CUSTOMER table in the diagram.Right-Click and choose Visual properties in the context menu. Choose a new fill color. Its also possible to do the same thing in the Properties pane, under Graphical Options, as seen in the screenshot.
Lay out the tables on the diagram to suit your preference. An illustration is offered as a guideline. Right-click on a single table, a collection of tables, or anywhere on the diagram to see the various drawing capabilities offered.
For example, to display the Foreign Key names on the diagram, select all objects [ctrl A] and invoke the Properties Inspector from the View menu.
Use the drop down list to set Show Names to True.
You can also drag tables, lines and labels to different positions on the diagram.
Click Save All Save All or select File | Save All to save your work.
NOTE: If you are extending existing applications, you may need to modify the schema by adding tables or by adding columns and regenerating the structures. In this tutorial you don't modify any of the tables, instead just review the details and create a CREATE DDL script. If you do not have time, you can do some of this section or move directly to the Section 3.
There are a number of ways to invoke the table definitions. To review a table and generate DDL, do the following:
Double-click the CUSTOMERS table either in the database diagram or in the Applications Navigator (expand the OESchema project, and the OE package).
Double-clicking the table invokes the Edit Table dialog. Review the various options, specifically the primary key and foreign key categories.
You can also review the columns visually. Right-click the CUSTOMERS table in the database diagram and select View As | Standard to see the columns displayed. (If all the columns are not visible, you may have to expand the height of the table and of the column display compartment of the table in the diagram).
It is often useful to have a full CREATE TABLE DDL script to hand. To do this select all the tables in the diagram, right-click and select Synchronize with Database | Generate To | SQL Script from the context menu.
If the Welcome page of the Generate SQL from Offline Database Objects wizard displays, click Next.
The Object Picker page of the wizard should show the four tables already in the Selected list. Click Next.
In the Choose Operation page of the wizard, select CREATE and click Next.
In the SQL Scripts Options page enter a SQL file name of create_tables.sql.
In the Finish page, review what you have done and click Finish.
While you will not run this script in the tutorial, you can run a SQL script by right-clicking it in the code editor and select Run in SQL*Plus and then select the desired connection from the context menu. Do Not Run the Script against oeconn.
Note: The first time you run a script from JDeveloper, in a new setup, you will be prompted for the path to the SQL*Plus executable.
As a Forms developer, you probably have some sort of plan in mind when you begin to build forms. In addition to knowing what tables the forms will use, you have an idea of screens that the user needs to see and how the user should navigate from one to another. Perhaps you draw a rough sketch of these ideas similar to the following:
Many developers do not build a page flow at this early stage of the development cycle; instead they go ahead and build the business service first. In this tutorial we take a slightly alternative approach, believing that it is not only good to know what each independent page looks like by having a rough sketch, but also by looking at the possible flow between pages. JDeveloper provides a visual tool to diagram the page flow of the application. As subsequent workshop sessions demonstrate, there is much that occurs behind the scenes of this visual tool, which provides far more functionality than simply drawing a diagram. However, for now, you use it to capture an overview of the application you plan to build.
To create a page flow diagram of the planned application, perform the following steps:
In the Applications Navigator, select the ViewController project. Expand the project node and the Web Content and the WEB-INF nodes. Find the adfc-config.xml and double-click on it.
The page flow diagram opens in the editor.
In the Component Palette to the right of the visual page flow editor, ensure that ADF Task Flow is selected from the dropdown list at the top and that Common Components accordion is open. Drag the View item out to an empty part of the diagram. Type over the name of the new page: browseCustomers .
Note that the page icon for /browseCustomers shows a yellow alert because there is no corresponding page built for it yet.
Create two additional pages, editCustomers and browseCustomerOrders, on the diagram:
Next you create JSF Navigation Cases between the pages. The significance of these becomes clear later; for now you can think of them as visual indicators of the flow of the application.
To add the navigation, click Control Flow Case in the Component Palette, then click the source, browseCustomers, and then the destination, browseCustomerOrders.
To modify the navigation properties, select the Control Flow Case you have just created and change the From Outcome property to customerOrders using the Property Inspector. (If you don't see the Property Inspector, choose it from the View menu or type Ctrl-Shift-I). You'll notice the navigation flow name changes on the diagram to customerOrders. If you want square corners like in the diagram below click on the green Orthogonal icon highlighted in the screenshot below.
Create a return navigation case from browseCustomerOrders to browseCustomers. Again, change the name of the navigation flow. This time edit it directly on the diagram by selecting the flow name. Call the navigation case browseCustomers.
Now create three JSF Navigation Cases between the pages browseCustomers and editCustomers, the first from browseCustomers to editCustomers, setting the From Outcome property to editCustomers.
Create the next two from the editCustomers back to the browseCustomers, setting the From Outcome property to cancel and commit, respectively.
An alternative approach to setting this property is to double-click the flow to invoke the Property Inspector and edit the From Outcome property.
Click Save All Save All or select File | Save All to save your work.
In this tutorial you created a diagram of the existing schema available to you for this workshop. You reviewed the relationships and generated the DDL. You will use these tables in future workshop sessions.
In addition, you created a page flow diagram for a few pages.