3
Understanding Databases

When you create a descriptor for a class, the Oracle Application Server TopLink Mapping Workbench retrieves the table information from the database. This section includes information on:

• Working with Databases

• Working with Database Tables in the Navigator Pane

• Working with Database Tables in the Editor Pane

• Generating Data from Database Tables

Working with Databases

Each OracleAS TopLink Mapping Workbench project contains a database. You can create multiple logins for each database. This section describes how to specify database properties and how to log into the database.

Database Properties

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

To Specify the Database Properties:

1. Choose the database object in the Navigator pane. The Database property sheet appears in the Editor pane.

   Figure 3-1 Database Property Sheet

   

   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 Change to select a new database.
   Defined Logins	Login used to access the database. Click 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 OracleAS TopLink 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. Note: When exporting deployment XML and Java source (see "Exporting Project Information") the OracleAS TopLink Mapping Workbench writes out this password. Use the OracleAS TopLink Sessions Editor (see Chapter 8, "Understanding the OracleAS TopLink Sessions Editor") to specify password encryption options.
   Save Password	Specify whether the OracleAS TopLink Mapping Workbench saves the Password for this Defined Login.
   Development Login Deployment Login	The OracleAS TopLink 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:

Select the database object in the Navigator pane and click the Login button on the toolbar. The OracleAS TopLink Mapping Workbench logs into the database. The database object in the Navigator pane changes to  .

You can also right-click on the database object and choose Log In from the pop-up menu or choose Selected > Log In from the menu.

Note: If 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 Navigator Pane

When you expand the database object in the Navigator pane, the OracleAS TopLink 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 OracleAS TopLink Mapping Workbench.

Figure 3-2 Sample Database Tables

Text description of the illustration dbtable.gif

Figure 3-2 calls out the following database icons.

1. Project

2. Database

3. Database table

Each database table property sheet contains the following tabs in the Editor pane:

• Fields - Add or modify the table's fields, and specify the field's properties (see Figure 3-5).

• References - Specify references between tables (see Figure 3-8).

Creating New Tables

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

To Create a New Table:

1. Select the database object in the Navigator pane and click the Add New Table button . The New Table dialog box appears.

   You can also right-click the database object and choose Add New Table from the pop-up menu or choose Selected > Add New Table from the menu.

   Figure 3-3 New Table

   

   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 and click OK. The OracleAS TopLink 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 Editor Pane" to use these tables in your project.

Importing Tables from Database

The OracleAS TopLink Mapping Workbench can automatically read the schema for a database and import the table data into the project. This section describes the driver requirements and how to import, remove, and rename tables.

JDBC Driver Requirements

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

• getTables( )

• getTableTypes( )

• getImportedKeys( )

• getCatalogs( )

• getPrimaryKeys( )

To Import Tables from the Database:

1. Select the database object in the Navigator pane and click on Add/Update Existing Tables from Database button . The Import tables from database dialog box appears.

   You can also right-click on the database object in the Navigator and choose Add/Update Existing Tables from Database from the pop-up menu or choose Selected > Add/Update Existing Tables from Database from the menu.

   Figure 3-4 Import tables from Database

   

   Text description of the illustration importdb.gif

Figure 3-4 calls out the following user-interface components:

1. Filters

2. Database tables that match the filters

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

   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 whether the tables' names are fully qualified against the schema and catalog.

3. Enter the filter information and click Get Table Names. OracleAS 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 . OracleAS TopLink adds the table to the Selected Tables field.

5. Select all the tables to import, then click OK. OracleAS TopLink imports the tables from the database into the OracleAS TopLink 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 Editor Pane".

Removing Tables

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

To Remove a Table:

1. Select on a database table in the Navigator pane and click the Remove Table button on the toolbar. The OracleAS TopLink Mapping Workbench prompts for confirmation.

   You can also right-click on the database object and choose Remove from the pop-up menu or choose Selected > Remove Table from the menu.

2. Click OK. The OracleAS TopLink 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 OracleAS TopLink Mapping Workbench project.

To Rename the Table:

1. Right-click the table in the Navigator pane and choose Rename from the pop-up menu. The Rename dialog box appears

   You can also select the table and choose Selected > Rename from the menu.

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

   Note: The original table name remains in the database.

Working with Database Tables in the Editor Pane

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

• Fields - Add or modify the table's fields, and specify the field's properties (see Figure 3-5).

• References - Specify references between tables (see Figure 3-8).

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 Navigator pane. The table's property sheet displays in the Editor pane.

2. Click the Fields tab.

   Figure 3-5 Fields Properties

   

   Text description of the illustration fields.gif

3. Use this table to enter information in each field. Use the scroll bar to display all fields on the tab.

   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.
   Sub-Size	Sub-size of the field.
   Allows Null	Specify if this field can be null.
   Primary Key	Specify whether this field is a primary key for the table.
   Identity	Indicates a Sybase, SQL Server or Informix identity field.
   Unique	Specify whether 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 Add Field to add a new field.

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

Setting a Primary Key for Database Tables

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

Note: The OracleAS TopLink Mapping Workbench can automatically import primary key information if supported by the JDBC driver.

To Set a Primary Key:

1. Select a database table in the Navigator pane. Its property sheet appear in the Editor pane.

2. Click the Fields tab.

   Figure 3-6 Setting Primary Key for a Database Table

   

   Text description of the illustration fieldspk.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 OracleAS TopLink 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 OracleAS TopLink Mapping Workbench can automatically create references (if the driver supports this), or you can define references from the workbench.

Creating Table References

Use this procedure to create a new table reference.

To Create a New Table Reference:

1. Select a database table in the Navigator pane. The table's properties display in the Editor pane.

2. Click the Table Reference tab (see Figure 3-8).

3. In the Table References area, click the Add button. The New Reference dialog box appears.

   Figure 3-7 New Reference

   

   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 OracleAS TopLink 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

Use this procedure to create a new field reference.

To Specify Table Reference Properties:

1. Select a database table in the Navigator pane. The table's properties display in the Editor pane.

2. Click on Table Reference tab.

   Figure 3-8 References Properties

   

   Text description of the illustration reftab.gif

   Figure 3-8 calls out the following user-interface components:

   1. Table References area

   2. Key Pairs area

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

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

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

Generating Data from Database Tables

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

• Generating SQL Creation Scripts

• Generating Descriptors and Classes from Database Tables

• Generating EJB Entities from 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 Navigator pane.

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

   You can also choose Selected > Generate Creation Script for > Selected Table or All Tables from the menu.

   Figure 3-9 SQL Creation Script

   

   Text description of the illustration sqlscrip.gif

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

   Note: If OracleAS 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 OracleAS TopLink 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 OracleAS TopLink Mapping Workbench:

• Creates a class definition and a descriptor definition.

• Adds attributes to the class for each column in the table.

• Automatically generates access methods, if specified.

• Creates direct-to-field mappings for all direct (non-foreign key) fields in the table.

• Creates relationship mappings (one-to-one and one-to-many) if there is sufficient foreign key information. You may be required to determine the exact mapping type.

  Note: Class and attribute names are generated based on the table and column names. You can edit the class properties to change their names.

To Generate Descriptors and Classes from Database Tables:

1. Select the database table(s) in the Navigator pane.

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

   You can also choose Selected > Generate Descriptors and Classes from > Selected Table or All Tables from the menu.

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

   Figure 3-10 Generate Classes and Descriptors

   

   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 OracleAS TopLink Mapping Workbench generates accessing methods for each class and descriptor

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

   Figure 3-11 Choose Relationships to Generate

   

   Text description of the illustration choose.gif

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

   You can also specify whether the relationships are bidirectional.

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

The newly created descriptors appear in the Navigator pane of the OracleAS TopLink Mapping Workbench.

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:

• One EJB descriptor that implements the <javax.ejb.EntityBean> interface and four EJB 1.1 classes for each table

• Bean relation attributes (CMP or BMP)

• Java source fore each class

• EJB-compliant method stubs

  Note: This option is available only for projects with CMP or BMP persistence.

To Generate EJB Entities:

1. Select the database table(s) in the Navigator pane.

2. Right-click the table(s) and choose 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 choose Selected > Generate EJB Entities and Descriptors from > Selected Table or All Tables from the menu.

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

   Figure 3-12 Generate EJB Entity Classes and Descriptors

   

   Text description of the illustration genejb.gif

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

5. If the table contains foreign key fields that may represent relationship mappings, then the Choose Relationships To Generate dialog box appears (see Figure 3-11). Select a potential relationship and click the 1:1 Mapping or 1:M Mapping button.

   You can also specify whether the relationships are bidirectional.

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

7. Click Create to generate the relationship mappings (or click 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.

Generating Tables on the Database

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

To Create a Table on the Database:

1. Select the database table(s) in the Navigator pane.

2. Right-click the table(s) and choose 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 OracleAS TopLink Mapping Workbench creates the tables on the database.

The newly created descriptor(s) appear in the Navigator pane of the OracleAS TopLink Mapping Workbench. Use the EJB Info tab (see Figure 4-16) to modify the EJB information.