Skip Headers

Oracle9iAS TopLink Mapping Workbench Reference Guide
Release 2 (9.0.3)

Part Number B10063-01
Go To Documentation Library
Home
Go To Solution Area
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

3
Understanding Databases

When you create a descriptor for a class, the Mapping Workbench retrieves the table information from the database.

Working with Databases

Each Mapping Workbench project contains a database. You can create multiple logins for each database.

Database Properties

Use the Database properties to specify information about the database and login(s).

To specify the database properties:
  1. Click on the database object in the Project Tree pane. The database properties appear in the Properties pane.

    Figure 3-1 Database Properties

    Text description of dbprop.gif follows.

    Text description of the illustration dbprop.gif

  2. Use this table to enter data in each field:

    Field Description

    Database Platform

    Database platform for the project. Click on Change to select a new database.

    Defined Logins

    Login used to access the database. Click on Add to add a new login or Remove to delete an existing login.

    Login Fields:

    To edit these fields, first select a Defined Login.

      Driver Class

      URL

    The Mapping Workbench connects to databases through JDBC. Contact your database administrator for information on installing and configuring your driver.

      Username

    Name required to log into the database.

      Password

    Password required to log into the database.

      Save Password

    Specify if the Mapping Workbench saves the Password for this Defined Login.

      Development Login

      Deployment Login

    The Mapping Workbench supports multiple logins. Select a Defined Login to use for development and/or deployment.

  3. After entering the information, continue with "Logging into the Database".

Logging into the Database

You must log into the database before importing or exporting table information.

To log into the database:

Click on the database object in the Project Tree pane and click on the Login button Login button. in the toolbar. The Mapping Workbench logs into the database. The database object in the Project Tree pane changes to  Login to database icon. .

You can also log into the database by right-clicking on the database object and selecting Log In from the pop-up menu or Selected > Log in from the menu.


Note:

f you have not defined a login, the system displays a warning message. See "Database Properties" for more information on creating a database login.


Working with Database Tables in the Project Tree Pane

When you expand the database object in the Project Tree pane, the Mapping Workbench displays the database tables associated with the project. You can associate tables by importing them from the database or by creating them within the Mapping Workbench.

Figure 3-2 Sample Database Tables

Text description of dbtable.gif follows.

Text description of the illustration dbtable.gif

Database pane icons called out in Figure 3-2:

  1. Project

  2. Database

  3. Database table

Each database table contains the following tabs in the Properties pane:

Creating New Tables

Use this procedure to create a new database table within the Mapping Workbench.

To create a new table:
  1. Select the database object in the Project Tree pane and click on the Add New Table button Add New Table button. . The New Table window appears.

    You can also create a new table by right-clicking on the database object and selecting Add New Table from the pop-up menu or Selected > Add New Table from the menu.

    Figure 3-3 New Table

    Text description of newtable.gif follows.

    Text description of the illustration newtable.gif

  2. Use this table to enter data in each field.

    Field Description

    Catalog

    Schema

    Use these fields to identify specific database information for the table. Consult your database administrator for more information.

    Table Name

    Name of this database table.

  3. Enter the necessary information click on OK. The Mapping Workbench adds the database table to the project.


    Note:

    Refer to "Generating Tables on the Database" to add the table information to the database.


Continue with "Working with Database Tables in the Properties Pane" to use these tables in your project.

Importing Tables from Database

The Mapping Workbench can automatically read the schema for a database and import the table data into the project.

JDBC Driver Requirements

To retrieve table information from the database, the database driver must support the following JDBC methods:

To import tables from the database:
  1. Select the database object in the Project Tree pane and click on the Add/Update Existing Tables from Database button Add/update tables from database button. . The Import tables from database window appears.

    You can also import tables from the database by right-clicking on the database object in the Project Tree and selecting Add/Update Existing Tables from Database from the pop-up menu or Selected > Add/Update Existing Tables from Database from the menu.

    Figure 3-4 Import tables from Database

    Text description of importdb.gif follows.

    Text description of the illustration importdb.gif

    User-interface components called out in Figure 3-4:

    1. Filters

    2. Database tables that match the filters

  2. Use this table to enter data in each filter field on the window:

    Field Description

    Table Name Pattern

    Name of database table(s) to import. Use % (percent character) as a wildcard. Tables that match the Table Name Pattern can be imported.

    Catalog

    Catalog of database table(s) to import.

    Schema Pattern

    Schema of database table(s) to import.

    Table Type

    Type of database table(s) to import.

    Import Fully Qualified Names

    Specify if the tables names are fully qualified against the schema and catalog.

  3. Enter the filter information and click on Get Table Names. TopLink examines the database and displays the tables that match the filters in the Available Tables field.

  4. Select the table(s) in the Available Tables area to import and click on Arrow. . TopLink adds the table to the Selected Tables field.

  5. Select all the tables to import, click on OK. TopLink imports the tables from the database into the Mapping Workbench project.

  6. Examine each table's properties to verify that the imported tables contain the correct information. See "Working with Database Tables in the Properties Pane".

Removing Tables

Use this procedure to remove a database table from the project.

To remove a table:
  1. Click on a database table in the Project Tree pane and click on the Remove Table button Remove Table button. in the toolbar. The Mapping Workbench prompts for confirmation.

    You can also remove a database table from the project by right-clicking on the database object and selecting Remove from the pop-up menu or Selected > Remove Table from the menu.

  2. Click on OK. The Mapping Workbench removes the table from the project.


    Note:

    The table remains in the database.


Renaming Tables

Use this procedure to rename a database table in the Mapping Workbench project.

To rename the table:
  1. Right-click on the table in the Project Tree pane and select Rename from the pop-up menu. The Rename window appears

    You can also rename the table by choosing the table and selecting Selected > Rename from the menu.

  2. Enter a new name and click OK. The Mapping Workbench renames the table.


    Note:

    The original table name remains in the database.


Working with Database Tables in the Properties Pane

When you select a database table in the Project Tree pane, its properties appear in the Properties pane. Each database table contains the following property tabs:

Working with Field Properties

Use the database table's Field tab to specify properties for the database table's fields.


Note:

Some properties may be unavailable, depending on your database type.


To specify table field properties:
  1. Select a database table in the Project Tree pane. The table's properties display in the Properties pane.

  2. Click on the Fields tab.

    Figure 3-5 Field Properties

    Text description of fields.gif follows.

    Text description of the illustration fields.gif

  3. Use this table to enter information in each field.

    Field Description

    Name

    Name of the field.

    Type

    Use the drop-down list to select the field's type.

    Note: The valid values will vary, depending on the database.

    Size

    Size of the field.

    Subsize

    Sub-size of the field.

    Allows Null

    Specify if this field can be null.

    Primary Key

    Specifies if this field is a primary key for the table.

    Identity

    Indicates a Sybase, SQL Server or Informix identity field.

    Unique

    Specifies if the value must be unique within the table.


    Note:

    Use the scroll bar to display the additional fields.


  4. Enter the necessary information for the existing fields or click on Add Field to add a new field.

    To remove a field, select the field and click on Remove.

Setting a Primary Key for Database Tables

Use this procedure to set primary key(s) for a database table.


Note:

The Mapping Workbench can automatically import primary key information, if supported by the JDBC driver.


To set a primary key:
  1. Choose a database table in the Project Tree pane. Its properties appear in the Properties pane.

  2. Click on the Fields tab.

    Figure 3-6 Setting Primary Key for a Database Table

    Text description of fields.gif follows.

    Text description of the illustration fields.gif

  3. Select the Primary Key field(s) for the table.

Working with Reference Properties

References are table properties that contain the foreign key - they may or may not correspond to an actual constraint that exists on the database. The Mapping Workbench uses these references when you define relationship mappings and multiple table associations.

When importing tables from the database (see "Importing Tables from Database"), the Mapping Workbench can automatically create references (if the driver supports this) or you can define references from the workbench.

Creating table references

To create a new table reference:
  1. Select a database table in the Project Tree pane. The table's properties displays in the Properties pane.

  2. Click on the Table Reference tab.

  3. In the Table References area, click on the Add button. The New Reference window appears.

    Figure 3-7 New Reference Window

    Text description of newref.gif follows.

    Text description of the illustration newref.gif

  4. Use this table to enter information in each field.

    Field Description

    Name of New Reference

    Name of the reference table. If you leave this field blank, the Mapping Workbench automatically creates a name based on the format: SOURCETABLE_TARGETTABLE.

    Select the Source Table

    Name of the database table. This field is for display only.

    Select the Target Table

    Use the drop-down list to specify the target table for this reference.

    On Database

    Specify if you want to create the reference on the database when you create the table. Not all database drivers support this option.

Continue with Creating Field References.

Creating Field References

To specify table reference properties:
  1. Select a database table in the Project Tree pane. The table's properties display in the Properties pane.

  2. Click on the Table Reference tab.

    Figure 3-8 References properties

    Text description of reftab.gif follows.

    Text description of the illustration reftab.gif

  3. In the Table references area, select a Table Reference (see "Creating table references").

  4. In the Key Pairs area, click on the Add button. The Source and Target fields appear on the tab.

  5. Use the Source Field and Target Field drop-down lists to select the key pair for this reference.

Generating Data from Database Tables

The Mapping Workbench can automatically generate the following information from the database tables.

You can also generate database tables from descriptors in your project.

Generating SQL Creation Scripts

Use this procedure to automatically generate SQL scripts to create the tables in a project.

To generate SQL scripts from database tables:
  1. Select the database table(s) in the Project Tree pane.

  2. Right-click on the table(s) and select Generate Creation Script for > Selected Table or All Tables from the pop-up menu. The SQL Creation Script window appears.

    You can also generate SQL scripts by selecting Selected > Generate Creation Script for > Selected Table or All Tables from the menu.

    Figure 3-9 SQL Creation Script

    Text description of sqlscrip.gif follows.

    Text description of the illustration sqlscrip.gif

  3. Copy the script from the window and paste it into a file. You may need to edit the file to include additional SQL information that the Mapping Workbench could not generate.


    Note:

    If TopLink cannot determine how a particular table feature should be implemented in SQL, it generates a descriptive message in the script.


Generating Descriptors and Classes from Database Tables

The Mapping Workbench can automatically generate Java class definitions, descriptor definitions, and associated mappings from the information in database tables. You can later edit the generated information, if necessary.

For each table, the Mapping Workbench will:

To generate descriptors and classes from database tables:
  1. Select the database table(s) in the Project Tree pane.

  2. Right-click on the table(s) and select Generate Descriptors and Classes from > Selected Table or All Tables from the pop-up menu. The Save Project dialog box appears.

    You can also generate SQL scripts by selecting Selected > Generate Descriptors and Classes from > Selected Table or All Tables from the menu.

  3. Click on Yes. The Generate Classes and Descriptors dialog box appears.

    Figure 3-10 Generate Classes and Descriptors

    Text description of genclass.gif follows.

    Text description of the illustration genclass.gif

  4. Use this table to enter data in each field:

    Field Description

    Package Name

    Name of package to generate

    Generate Accessing Methods

    Specify if the Mapping Workbench generates accessing methods for each class and descriptor

  5. Enter the information and click on OK. If the table contains foreign key fields that may represent relationship mappings, the Choose Relationships to Generate window appears.

    Figure 3-11 Choose Relationships to Generate

    Text description of choose.gif follows.

    Text description of the illustration choose.gif

  6. Select a Potential Relationship and click on 1:1 Mapping One-to-one Mapping button. or 1:M Mapping One-to-many Mapping button. button. See Chapter 6, "Understanding Relationship Mappings" for more information on mappings.

    You can also specify if the relationships are bidirectional.

  7. Click on Create to automatically create the relationships (or Skip to generate the descriptors without creating these relationships.).

The newly created descriptors appear in the Project Tree pane of the Mapping Workbench.

Generating Tables on the Database

Use this procedure to create a table on the database, based on the information in the Mapping Workbench.

To create a table on the database:
  1. Select the database table(s) in the Project Tree pane.

  2. Right-click on the table(s) and select Create on Database > Selected Table or All Tables from the pop-up menu. The Save Project dialog box appears.

    You can also create tables by selecting Selected > Create on Database > Selected Table or All Tables from the menu.


    Note:

    You must log into the database before creating tables. See "Logging into the Database" for more information.


The Mapping Workbench creates the tables on the database.

Generating EJB Entities from Database Tables

Use this procedure to automatically generate EJB classes and descriptors for each database table. Generating EJB entities allows you to create:

To generate EJB entities:
  1. Select the database table(s) in the Project Tree pane.

  2. Right-click on the table(s) and select Generate EJB Entities and Descriptors from > Selected Table or All Tables from the pop-up menu. The Save Project dialog box appears.

    You can also create entities by selecting Selected > Generate EJB Entities and Descriptors from > Selected Table or All Tables from the menu.

  3. Click on Yes to save your project before generating EJB entities. The Generate Enterprise Java Beans window appears.

    Figure 3-12 Generate Enterprise Java Beans

    Text description of genejb.gif follows.

    Text description of the illustration genejb.gif

  4. Enter a package name, select any persistence type options, and click on OK.


    Note:

    The Generate Local/Remote Interfaces options appear for 2.0 CMP and BMP projects only.


  5. If the table contains foreign key fields that may represent relationship mappings, the Choose Relationships To Generate window appears. Select a potential relationship and click on the 1:1 Mapping One-to-one Mapping button. or 1:M Mapping One-to-many Mapping button. button.

    You can also specify if the relationships are bi-directional.

  6. Repeat step 5 for all appropriate sets of tables.

  7. Click Create to generate the relationship mappings (or Skip to generate the EJB descriptors without creating these relationships.).

The system creates the remote primary key, home, and bean classes for each bean and adds this information to the project. The newly created descriptor(s) appear in the Project Tree pane of the Mapping Workbench. Use the EJB Info tab to modify the EJB information.


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Solution Area
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index