Oracle TopLink Inheritance

Storing all classes in a single table

In this example all the classes in the Bank Account hierarchy are stored in a single ACCOUNT table. The ACCOUNT table defines the fields for all of the attributes of all the classes in the hierarchy. Each row has only the values for the fields relevant to that class; the other fields are null. This is normally the most efficient way to structure the tables, as this simplifies querying and does not require frequent joins.

To map this type of example in the Oracle TopLink Mapping Workbench, use this procedure:

1. Select File > New Project... from the TopLink Mapping Workbench menu to create a new Mapping Workbench project.

   See the Creating a New Project section in the TopLink Mapping Workbench User's Guide for more information.

2. Import the example classes.

3. Create new tables using the TopLink Mapping Workbench.

4. Create a new ACCOUNT table that contains fields for all of the attributes of all the classes in the hierarchy.

5. Expand the elements in the Navigator pane by clicking on the + symbol (if necessary).

6. In the Editor pane, select the BankAccount Descriptor Info.

7. Map the BankAccount descriptor to the ACCOUNT table as described in the Mapping Classes and Tables section in the TopLink Mapping Workbench User's Guide.

8. Add the Advanced Properties > Inheritance behavior attribute to the BankAccount descriptor from the Selected > Advanced Properties menu.

9. Define the BankAccount as the root class.

10. For each of the subclasses add the Advanced Properties > Inheritance behavior attribute.

11. Select the subclass's parent class in the drop-down.

12. Map the subclass descriptor to the ACCOUNT table.

13. Map each class's attributes.

Storing the common data in a common table and the subclass specific data in a separate table for each subclass

In this example all the classes in the Bank Account hierarchy have their specific data stored in their own table and common data stored in each of their parent's tables. This mirrors the inheritance from the object model into the database schema. For each class, a corresponding table is defined with the same fields as is defined in the class. Each subclass table must also define the same ID primary key to allow the tables to be joined. This is a common method that many projects used in the past to structure their tables as it matches the structure of the object model. Unfortunately this is normally a very inefficient way to structure the database, as it requires excessive joins and frequent multiple table access.

To map this type of example in the TopLink Mapping Workbench, use this procedure:

1. Select File > New Project... from the TopLink Mapping Workbench menu to create a new Mapping Workbench project.

   See the Creating a New Project section in the Oracle TopLink Mapping Workbench User's Guide for more information.

2. Import the example classes.

3. Create new tables using the TopLink Mapping Workbench.

4. Create a table for each class with fields only for the class's local attributes and the ACCOUNT_ID primary key field.

5. Expand the elements in the Navigator pane by clicking on the + symbol (if necessary).

6. In the Editor pane, select the BankAccount Descriptor Info tab.

7. Map the BankAccount descriptor to the ACCOUNT table as described in the Mapping Classes to Tables section in the Oracle TopLink Mapping Workbench User's Guide.

8. Add the Advanced Properties > Inheritance behavior attribute to the BankAccount descriptor from the Selected > Advanced Properties menu.

9. Define the BankAccount as the root class.

10. For each of the subclasses add the Advanced Properties > Inheritance behavior attribute.

11. Select the subclass's parent class in the drop-down.

12. Map the subclass descriptor to the subclasses specific table.

13. Map each class's attributes.

Storing each concrete class in its own independent table

In this example, all of the concrete classes in the Bank Account hierarchy are stored in their own independent tables. For each concrete class a corresponding table is defined with fields for each of the class's attributes and for each of the class's parent class's attributes. This way of structuring the tables is normally not used, as it does not allow easy querying for the root or branch classes. It also requires duplicating a lot of the common structure. This structure is valid when the classes share inheritance for behavior attributes, and the application does not have a need to query for the root or branch classes.

To map this type of example in the TopLink Mapping Workbench, use this procedure:

• Select File > New Project... from the TopLink Mapping Workbench menu to create a new Mapping Workbench project.

  See the Creating a New Project section in the Oracle TopLink Mapping Workbench User's Guide for more information.

• Import the example classes.

• Create new tables using the TopLink Mapping Workbench.

• Create a table for each concrete class with fields for all of the class's attributes including the inherited attributes.

• Map each concrete class to its own table.

• For each concrete class select the menu option Map Inherited Attributes - To Superclass.

• Map each concrete class's attributes.

Note: No Inheritance is established in the Storing each concrete class in its own independent table example provided above. This is because in this example TopLink treats each class as independent and does not consider them to have any inheritance relationship, as they are stored in their own independent tables.

Unless you use an interface descriptor, you are not able to query for or define relationships to the root or branch classes in this example. An interface descriptor enables querying and variable relationship mappings to be defined for classes that share some common behavior, but do not share any tables in the database. The example download provides an example of this in the Person, Company, and Client classes.

A combination of the three examples provided above

In real applications the table structure does not normally strictly follow any of the three examples outlined above. More typically, a combination of the three is used. TopLink does not require strict usage of a specific table structure. In fact, the inheritance and multiple table support features are independent features. TopLink also allows for multiple tables to be used in classes not involved in inheritance. For a class involved in inheritance, the class can add no tables, one table, or many tables. How the tables are joined can also be configured. By default they are all assumed to share the same primary key fields. However, they can be joined through a foreign key, either from the subclass table to the parent table, or from the parent table to the subclass table. A multiple table join expression can also be used to define the join when not based solely on a foreign key.

The example download provides an example of mixed inheritance. In the example, BankAccount is mapped to the ACCOUNT table: its subclasses share this table, but the PersonalAccount and the TrustAccount also define their specific data in their own tables. In the Client hierarchy the Person and Student share a table, but the Company is stored in its own table.

Use a class indicator type field

In this example a class indicator field ACCOUNT_TYPE is defined in the ACCOUNT table. This field stores a unique type code for each of the concrete classes in the hierarchy, one of SAV, TRUST, CHEQ, CORP. The class indicator is only stored in the root table, and stores the indicator value for the concrete class; type indicators are not stored in branch tables. This implementation is the most common and the most efficient as it easily allows the type of the class to be determined, and allows easy filtering of a concrete class's instances on queries.

To map this example in the TopLink Mapping Workbench, use this procedure:

1. In the BankAccount descriptor add the Advanced Properties > Inheritance behavior attribute.

2. In the Inheritance tab select Is Root Descriptor.

3. Select Use Class Indicator Dictionary.

4. Define a class indicator type code for each concrete class by checking the Use Class Indicator Field radio button in the Inheritance tab and selecting a class indicator type code from the drop-down menu.

Use the class's name as its type indicator

In this example a class indicator field ACCOUNT_TYPE is defined in the ACCOUNT table. This field stores the full package qualified class name for the instance stored in the row. This implementation is similar to the "Use a class indicator type field" example provided above, except that you are not required to enter a unique type code for each concrete class. In that full package qualified class names tend to be quite large, this can be somewhat inefficient.

To map this example in the TopLink Mapping Workbench, use this procedure:

1. In the BankAccount descriptor add the Advanced Properties > Inheritance behavior attribute.

2. In the Inheritance tab select Is Root Descriptor.

3. Select Use Class Name as Indicator.

Use a class extraction method

In this example there is no class indicator field. The Person and Student class hierarchy shows an example of this in the download example. Both the Person and the Student are stored in the same table: any row that has a null STUDENT_NUMBER is assumed to be a Student; all other rows are assumed to be a Person. This is mapped using a class extraction method. A class extraction method can be used when there is no class indicator field and another method must be used to determine the class that a row corresponds to. The class extraction method allows Java code to be used to decide what class a row corresponds to. The class extraction method must be a static method on the root class that returns a Class and takes a DatabaseRow as an argument. It can use any of the data in the row to determine what the class type is. In this example the method checks the STUDENT_NUMBER field, if it is null it returns Person, otherwise Student.

A very important, and frequently misunderstood issue in using a class extraction method, is that queries for the classes in the hierarchy must still be able to filter the results of the classes being queried. This typically requires defining an only instances expression, and a with all subclasses expression to correctly filter the query results. Refer to the querying examples for more information.

To map this example in the TopLink Mapping Workbench, use this procedure:

1. In the Person descriptor add the Advanced Properties > Inheritance behavior attribute.

2. In the Inheritance tab select Is Root Descriptor.

3. Select Use Class Extraction Method.

4. Select the extractClass method from the drop down.

Reading subclasses on queries

By default, queries on all root and branch inheritance classes include their subclasses; by contrast, queries on leaf classes only include the leaf class. If a root or branch class is concrete, it may be a requirement for queries on the class to only return instances of that class. In this case the descriptor must be configured to not read subclasses on queries.

Using a read subclasses view

If queries on a root or branch class include the subclasses of the root or branch, this becomes inefficient if the subclasses span multiple tables since the subclass tables cannot be easily read in a single query. By default, TopLink issues a single query on the root table to retrieve all of the class indicator values that match the query. TopLink then issues a separate query for each class indicator value returned. This can be inefficient and limits the usage of cursors and affects how the results are ordered. A view can be specified on a root or branch class descriptor that outer-joins or unions all of the subclass tables to enable querying on the class in a single query. This view can be specified in the Inheritance tab and must correspond to a view on the database.

In this example a view named ALL_ACCOUNTS can be defined for the ACCOUNT table as:

CREATE VIEW ALL_ACCOUNTS AS SELECT A.*, P.INTEREST_RATE, P.LAST_CHEQUE_NUMBER, T.TRUSTEE_ID, T.TRUSTEE_TYPE FROM ACCOUNT A, PERSONAL_ACCOUNT P, TRUST_ACCOUNT T WHERE (A.ID = P.ID (+)) AND (A.ID = T.ID (+))

Note, this example uses outer-join syntax specific to the Oracle Database; other databases would require different syntax.

Using an only instances and with all subclasses expression

Queries on inheritance classes that share a common table must filter out their sibling instances. When you use a class indicator type field, TopLink automatically generates the required expression and SQL to do this. If you use a class extraction method, you must provide TopLink with an expression to correctly filter sibling instances for all classes that share a common table. Concrete classes are required to define an only instances expression, and any branch classes are required to define a with all subclasses expression.

When TopLink queries for a leaf class it uses the only instances expression to filter out any sibling classes in the query. When querying for a root or branch class whose subclasses do not define their own tables, the with all subclasses expression is used. This is also the case when a subclasses view is used. When querying for a root or branch class that has subclasses that span multiple tables, a query is performed for each concrete class in the inheritance hierarchy using the only instances expression to filter sibling classes.

If a class has its own table for its specific data and does not share this table with any sibling classes, then these expressions are not required. When a class indicator is used, TopLink also uses the indicator values to determine which classes can have concrete instances. When a class extraction method is used the only instances expression is used to determine if a class is concrete. If a class does not require an only instances expression, ensure that its read subclasses on queries is not selected; otherwise it will be assumed to have no instances and be skipped on queries.

In the download example, the Person and Student classes define only instances expressions. The TopLink Mapping Workbench does not currently support defining these expressions so they must be defined through amendment methods.

// Only instances expression for Person
public static void addToPersonDescriptor(Descriptor descriptor) {
	ExpressionBuilder builder = new ExpressionBuilder();
	descriptor.getInheritancePolicy().setOnlyInstancesExpression(
builder.getField("STUDENT_NUMBER").isNull());
}
// Only instances expression for Student
public static void addToStudentDescriptor(Descriptor descriptor) {
	ExpressionBuilder builder = new ExpressionBuilder();
	descriptor.getInheritancePolicy().setOnlyInstancesExpression(
builder.getField("STUDENT_NUMBER").notNull());
}