Working With Databases

In relational projects, when you expand the database object in the Navigator, TopLink Workbench displays the database tables associated with the project. You can associate tables by importing them from the database, or by creating them within TopLink Workbench.

Figure 5-15 Sample Database Tables

Description of "Figure 5-15 Sample Database Tables"

Figure 5-15 numbered callouts identify the following database icons.

1. Project

2. Database

3. Database table

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

• Columns – Add or modify the table's fields, and specify each field's properties.

• References – Specify references between tables.

This section includes information on the following topics:

• Working With Database Tables in the Navigator Window

• Working With Database Tables in the Editor Window

• Generating Data From Database Tables

Working With Database Tables in the Navigator Window

This section describes the following options:

• Logging In and Out of a Database

• Creating New Tables

• Importing Tables from a Database

• Removing Tables

• Renaming Tables

• Refreshing Tables from the Database

See "Working With Database Tables in the Editor Window" for more information.

Logging In and Out of a Database

To log in or out of a relational database:

1. Create a database login (see "Database Login Configuration Overview").

2. To log in a relational database, right-click the database object in the Navigator, and choose Log In to Database from the context menu or choose Selected > Log In to Database from the menu.

3. To log out of a relational database, right-click the database object in the Navigator and choose Log Out of Database from the context menu or choose Selected > Log Out of Database from the menu.

Creating New Tables

To create a new database table within TopLink Workbench, use the following procedure:

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

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

   Figure 5-16 New Table Dialog Box

   
   

2. Complete each field on the New Table dialog box and click OK.

Field	Description
Catalog	Use to identify specific database information for the table. Consult your database administrator for more information.
Table Name	Specify the name of this database table.
Schema	Use to identify specific database information for the table. Consult your database administrator for more information.

TopLink Workbench adds the database table to the project.

Although the database table has been added to the project, it has not been written to the actual database. See "Generating Tables on the Database" for more information on creating the table in the database.

Continue with "Working With Database Tables in the Editor Window" to use these tables in your project.

Importing Tables from a Database

TopLink Workbench can automatically read the schema for a relational database and import the table data into the project as long as your JDBC driver supports the following JDBC methods:

• getTables

• getTableTypes

• getImportedKeys

• getCatalogs

• getPrimaryKeys

The JDBC driver must be on the TopLink Workbench classpath (see "Configuring the TopLink Workbench Environment").

To import tables from the database, use the following procedure:

1. 
   Select the database object in the Navigator, and click Add/Update Existing Tables from Database. 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 context menu or choose Selected > Add/Update Existing Tables from Database from the menu.

   Figure 5-17 Import Tables from Database Dialog Box

   
   Description of "Figure 5-17 Import Tables from Database Dialog Box"
   
   

   Figure 5-17 numbered callouts identify the following user interface components:

   1. Filters

   2. Database tables that match the filters

2. Complete each field on the Import Tables from Database dialog box and click OK.

Field	Description
Table Name Pattern	Specify the name of database table(s) to import. Use percent character ( %) as a wildcard. Tables that match the Table Name Pattern can be imported.
Catalog	Specify the catalog of database table(s) to import.
Schema Pattern	Specify the schema of database table(s) to import.
Table Type	Specify the type of database table(s) to import.
Available Tables	Click Get Table Names to make TopLink display tables that match Table Name Pattern, Catalog, Schema Pattern, and Table Type settings.
Selected Tables	Select the tables in the Available Tables area to import, and click the right-arrow button. TopLink adds the table to the Selected Tables field. Click OK to import the tables from the database into the TopLink Workbench project.
Import Fully Qualified Names	Specify whether or not the tables' names are fully qualified against the schema and catalog.

Examine each table's properties to verify that the imported tables contain the correct information. See "Working With Database Tables in the Editor Window" for more information.

Removing Tables

To remove a database table from the project, use the following procedure:

1. 
   Select a database table in the Navigator, and click Remove Table on the toolbar. TopLink Workbench prompts for confirmation.

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

2. Click OK. TopLink Workbench removes the table from the project.

   
   

   Note: Although you have removed the table from the TopLink Workbench project, the table remains in the database.

   
   

Renaming Tables

To rename a database table in the TopLink Workbench project, use the following procedure:

1. Right-click the table in the Navigator and choose Rename from the context 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. TopLink Workbench renames the table.

   
   

   Note: Although you have renamed the table in the TopLink Workbench project, the original table name remains in the database.

   
   

Refreshing Tables from the Database

To refresh (that is, reload) the database tables in the TopLink Workbench project, use this procedure:

Select a database table in the Navigator, and click Refresh from Database on the toolbar.

You can also select the table and choose Selected > Refresh from Database from the menu, or click Refresh. TopLink Workbench reloads the database table.

When refreshing tables from the database, if there are multiple database tables with similar names, the Duplicate Tables dialog box appears.

Figure 5-18 Duplicate Table Dialog Box

Description of "Figure 5-18 Duplicate Table Dialog Box"

Select the specific database table to update, and then click OK.

Working With Database Tables in the Editor Window

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

• Columns–Add or modify the table fields, and specify each field properties.

• References–Specify references between tables.

This section describes how to use these tabs to configure the following:

• Working With Column Properties

• Setting a Primary Key for Database Tables

• Creating Table References

• Creating Field Associations

Working With Column Properties

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

To specify a table's column properties, use this procedure:

1. Select a database table in the Navigator. The table's property sheet displays in the Editor.

2. Click the Columns tab.

   Figure 5-19 Fields Properties

   
   Description of "Figure 5-19 Fields Properties"
   
   

3. Enter data in each field on the Columns tab. Use the scroll bar to display the additional field.

Field	Description
Name	Specify the 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	Specify the size of the field.
Sub-Size	Specify the sub-size of the field.
Allows Null	Specify if this field can be null.
Unique	Specify whether the value must be unique within the table.
Primary Key	Specify whether or not this field is a primary key for the table (see "Setting a Primary Key for Database Tables").
Identity	Use to indicate a Sybase, SQL Server or Informix identity field.

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

To add a new field, click Add.

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

To rename a field, select the field and click Rename.

Setting a Primary Key for Database Tables

To set a primary key(s) for a database table, use this procedure:

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

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

2. Click the Fields tab.

3. Figure 5-20 Setting Primary Key for a Database Table

   
   Description of "Figure 5-20 Setting Primary Key for a Database Table"
   
   

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

Creating Table References

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

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

To create a new table reference, use this procedure:

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

2. Click the References tab.

   Figure 5-21 References Tab

   
   Description of "Figure 5-21 References Tab"
   
   

   Figure 5-23 numbered callouts identify the following user interface components:

   1. Table References area

   2. Key Pairs area

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

   Figure 5-22 New Reference Dialog Box

   
   

4. Complete each field on the New Reference dialog box and click OK.

Field	Description
Enter Name of New Reference	Specify the name of the reference table. If you leave this field blank, TopLink Workbench automatically creates a name based on the format: SOURCETABLE_TARGETTABLE.
Select the Source Table	Specify the name of the source database table (the currently selected table in the Navigator).
Select the Target Table	Use the 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 Associations".

Creating Field Associations

For each table reference, you can specify one or more field associations that define how fields in the source table relate to fields in the target table. See "Creating Table References".

To create new field references, use this procedure:

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

2. Click the References tab.

   Figure 5-23 References Tab

   
   Description of "Figure 5-23 References Tab"
   
   

   Figure 5-23 numbered callouts identify the following user interface components:

   1. Table references area

   2. Key pairs area

3. Select a table reference from the references area.

4. To create a new key pair, click Add in the key pairs area and complete each field in the key pairs area using the following information:

Field	Description
Table References Area
Reference Name	Specify the name of this table reference
Target Table	Specify the database table that is the target of this reference.
On Table	Specify if the reference exists on the database.
Key Pairs Area
Source Field	Select the database field from the source table.
Target Field	Select the database field from the target table.

Generating Data From Database Tables

TopLink Workbench can automatically generate a variety of information from the database tables. This section describes the following:

• Generating SQL Creation Scripts

• Generating Classes and Descriptors From Database Tables

• Generating EJB Entities and Descriptors From Database Tables

• Generating Tables on the Database

Generating SQL Creation Scripts

Using the TopLink Workbench, you can generate SQL scripts that you can use to create tables in a relational database.

To automatically generate SQL scripts to create the tables in a project, use this procedure:

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

2. Right-click the table(s) and choose Generate Creation Script for > Selected Table or All Tables from the context 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 5-24 SQL Creation Script Dialog Box

   
   

Copy the script and paste it into a file. You may need to edit the file to include additional SQL information that TopLink 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 Classes and Descriptors From Database Tables

TopLink 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, TopLink Workbench does the following:

• 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 (nonforeign 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 classes and descriptors from database tables, use the following procedure:

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

2. Right-click the table(s) and choose Generate Classes and Descriptors from > Selected Table or All Tables from the context menu.

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

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

   Figure 5-25 Generate Classes and Descriptors Dialog Box

   
   

4. Complete each field on the Generate Classes and Descriptors dialog box and click OK.

   Use the following information to enter data in each field:

5. Field	Description
   Package Name	Specify the name of package to generate. The package name must comply with Java naming standards.
   Generate Accessing Methods	Specify if TopLink Workbench generates accessing methods for each class and descriptor.

   
   

   If the table contains foreign key fields that may represent relationship mappings, then the Choose Relationships to Generate dialog box appears.

   If the table contains foreign key fields that may represent relationship mappings, then the Choose Relationships to Generate dialog box appears.

   Figure 5-26 Choose Relationships to Generate Dialog Box

   
   

6. 
   
   Select an entry from Potential Relationships and click the 1:1 Mapping or 1:M Mapping button, located between the Potential Relationships and Selected Relationships windows. See "Understanding Relational Mappings" for more information on mappings.

   You can also specify whether the relationships are bidirectional. See "Configuring Bidirectional Relationship" for more information.

7. Click OK to automatically create the relationships.

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

Generating EJB Entities and Descriptors From Database Tables

.Using TopLink Workbench, you can automatically generate EJB entities and descriptors for each database table, including the following:

• 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 for each class

• EJB-compliant method stubs

  
  

  Note: This option is available only for projects with CMP or BMP persistence. See "Configuring Persistence Type" for more information.

  
  

To automatically generate EJB entities and descriptors for each database table, use this procedure:

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

2. Right-click the table(s) and choose Generate EJB Entities and Descriptors from > Selected Table or All Tables from the context menu. TopLink Workbench prompts you to save your project.

   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 EJB Entity Classes and Descriptors dialog box appears.

   Figure 5-27 Generate EJB Entity Classes and Descriptors Dialog Box

   
   Description of "Figure 5-27 Generate EJB Entity Classes and Descriptors Dialog Box"
   
   

4. Complete each field on the Generate EJB Entity Classes and Descriptors dialog box and click OK.

Field	Description
Package Name	Name of the package to contain the generated entities and descriptors.
Generate Local InterfacesFoot 1	Specify if TopLink creates local interfaces for the EJB entities.
Generate Remote InterfacesFootref 1	Specify if TopLink creates remote interfaces for the EJB entities.

^Footnote 1 For CMP 2.0 and BMP projects only. See "Configuring a Descriptor With EJB Information" for more information.

If the table contains foreign key fields that may represent relationship mappings, then the Choose Relationships to Generate dialog box appears. Select a potential relationship and click the 1:1 Mapping or 1:M Mapping button, located between the Potential Relationships and Selected Relationships windows.

You can also specify if the relationships are bidirectional. See "Configuring Bidirectional Relationship" for more information.

Repeat for all appropriate sets of tables.

Click OK to generate the relationship mappings.

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

To create a table in the database, based on the information in TopLink Workbench, use this procedure:

Note: You must log in the database before creating tables. See "Logging in to the Database" for more information.

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

2. Right-click the table(s) and choose Create on Database > Selected Table or All Tables from the context menu.

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

TopLink Workbench creates the tables on the database.

Alternatively, you can generate tables at run time by exporting the information in TopLink Workbench to a TableCreator class (see "Understanding the Schema Manager").