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 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 in the toolbar. The Mapping Workbench logs into the database. The database object in the Project Tree pane changes to  .

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

• Fields - Add or modify the table's fields, and specify the field's properties

• References - Specify references between tables

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

• getTables( )

• getTableTypes( )

• getImportedKeys( )

• getCatalogs( )

• getPrimaryKeys( )

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

• Fields - Add or modify the table's fields, and specify the field's properties

• References - Specify references between tables

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

• SQL scripts

• Descriptors and classes

• EJB entities

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

• Create a class definition and a descriptor definition.

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

• Automatically generate access methods, if specified.

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

• Create 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 may edit the class properties to change their names.

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 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 the illustration choose.gif

6. Select a Potential Relationship and click on 1:1 Mapping or 1:M Mapping 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:

• 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 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 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 or 1:M Mapping 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.