18 Introduction to Relational Projects

This chapter provides an overview of relational projects and focuses on building these projects for relational and object-relational data type databases.

This chapter includes the following sections:

For information on project concepts and features common to more than one type of TopLink projects, see Chapter 15, "Introduction to Projects".

18.1 Building Relational Projects

Use a relational project for transactional persistence of Java objects to a conventional relational database or to an object-relational data type database that supports data types specialized for object storage, both accessed using JDBC.

Note:

If you are using TopLink Workbench, you must add your JDBC driver to the TopLink Workbench classpath. If you are using TopLink Workbench and direct-to-XMLType mappings (see Section 27.4, "Direct-to-XMLType Mapping"), you must add the Oracle Database xdb.jar file to the TopLink Workbench classpath.

For more information, see Section 5.2, "Configuring the TopLink Workbench Environment".

In a relational project, you can make full use of TopLink queries and expressions (see Part XXIV, "Queries").

18.1.1 How to Build Relational Projects for a Relational Database

Oracle JDeveloper TopLink Editor and TopLink Workbench provide complete support for creating relational projects that map Java objects to a conventional relational database accessed using JDBC.

Table 18-1 describes the components of a relational project for a relational database.

Table 18-1 Components of a Relational Project for a Relational Database

Component Supported Types

Data Source

For more information, see the following:

Descriptors

For more information, see Section 21.1, "Relational Descriptors".

Mappings

For more information, see the following:


For more information, see Chapter 19, "Creating a Relational Project".

18.1.2 How to Build Relational Projects for an Object-Relational Data Type Database

Oracle JDeveloper TopLink Editor and TopLink Workbench do not currently support relational projects for an object-relational data type database. You must create such a relational project in Java.

Using Java, you can create a relational project for transactional persistence of Java objects to an object-relational data type database that supports data types specialized for object storage (such as Oracle Database) accessed using JDBC.

When using TopLink to build a relational project for an object-relational data type database, consider the following:

  • You must create a Java class and a TopLink ObjectRelationalDescriptor for each structured type (Struct/object-type).

  • TopLink supports only arrays (Varrays) of basic types or arrays on structured types (Struct/object-type).

    TopLink does not support arrays of Refs or arrays of nested tables.

  • TopLink supports only nested tables of Refs.

    TopLink does not support nested tables of basic types, structured types, or array types.

The general development process for building a relational project for an object-relational data type database is as follows:

  1. Define structured object-types in the database.

  2. Define tables of the structured object-types in the database.

  3. Define the Java classes that will map to the structured object-types.

  4. Create a relational project (see Chapter 116, "Creating a Project").

  5. Create an object-relational data type descriptor for each Java class (see Section 25.2, "Creating an Object-Relational Data Type Descriptor").

  6. Create object-relational data type mappings from each persistent field of each Java class to the corresponding object-types and object-type tables.

    For more information, see the following:

Table 18-2 describes the components of a relational project for an object-relational data type database.

Table 18-2 Components of a Relational Project for an Object-Relational Data Type Database

Component Supported Types

Data Source

For more information, see the following:

Descriptors

For more information, see Section 24.1, "Object-Relational Data Type Descriptors".

Mappings

For more information, see the following:


For more information, see Chapter 19, "Creating a Relational Project".

18.2 Sequencing in Relational Projects

In an relational project, you store persistent objects for your application in database tables that represent the class of instantiated object. As Figure 18-1 shows, each row of the VEHICLE_POOL table represents an instantiated object from that class, and the VEH_ID column holds the primary key for each object.

Figure 18-1 Sequencing Elements in a Class Database Table

Description of Figure 18-1 follows
Description of "Figure 18-1 Sequencing Elements in a Class Database Table"

You configure TopLink sequencing at the project or session level (see Section 20.3, "Configuring Sequencing at the Project Level" or Section 98.4, "Configuring Sequencing at the Session Level") to tell TopLink how to obtain values for the primary key column: that is, what type of sequencing to use (see Section 18.2.2, "Sequencing Types").

You configure TopLink sequencing at the descriptor level (see Section 23.3, "Configuring Sequencing at the Descriptor Level") to tell TopLink into which table and column to write the sequence value when an instance of a descriptor's reference class is created.

Note:

When choosing a column type for a primary key value, ensure that the type provides a suitable precision. For example, if you use a TIMESTAMP type but your database platform's TIMESTAMP is defined only to the second, then identical values may be returned for objects created within the same second.

This section describes the following:

18.2.1 Sequencing Configuration Options

You can configure sequencing using either Oracle JDeveloper TopLink Editor, TopLink Workbench, or Java (but not both).

Oracle recommends using Oracle JDeveloper to configure sequencing. Using Oracle JDeveloper, you can easily configure the sequencing options applicable to most applications. For more information, see Section 20.3, "Configuring Sequencing at the Project Level" or Section 98.4, "Configuring Sequencing at the Session Level".

Using TopLink Workbench, create one sequence with a single preallocation size that applies to all descriptors that require sequencing. You can configure table sequencing (see Section 18.2.2.1, "Table Sequencing") or native sequencing (see Section 18.2.2.5, "Native Sequencing with an Oracle Database Platform"). If you choose table sequencing, you can either use default table and column names or specify your own (see Section 18.2.2.1.1, "Default Versus Custom Sequence Table").

Using Java, you can configure any sequence type that TopLink supports (see Section 18.2.2, "Sequencing Types"). You can create any number and combination of sequences per project. You can create a sequence object explicitly or use the platform default sequence (see Section 18.2.2.4, "Default Sequencing"). You can associate the same sequence with more than one descriptor or associate different sequences (and different sequence types) to various descriptors. You can configure a separate preallocation size for each descriptor's sequence. For more information, see Section 98.4.2, "How to Configure Sequencing at the Session Level Using Java".

18.2.2 Sequencing Types

TopLink supports the following sequence types:

18.2.2.1 Table Sequencing

With table sequencing, you create a single database table that includes sequencing information for one or more sequenced objects in the project. TopLink maintains this table to track sequence numbers for these object types.

As Figure 18-2 shows, the table may contain sequencing information for more than one class that uses sequencing. The default table is called SEQUENCE and contains two columns:

  • SEQ_NAME, which specifies the class type to which the selected row refers

  • SEQ_COUNT, which specifies the highest sequence number currently allocated for the object represented in the selected row

Figure 18-2 TopLink Table Sequence Table

Description of Figure 18-2 follows
Description of "Figure 18-2 TopLink Table Sequence Table"

The rows of the SEQUENCE table represent each sequence object: one for each class that participates in sequencing or a single sequence object across several classes so that they can benefit from the same preallocation pool. When you configure sequencing at the descriptor level (see Section 23.3, "Configuring Sequencing at the Descriptor Level"), you specify the SEQ_NAME for the class. Add a row with that name to the SEQUENCE table and initialize the SEQ_COUNT column to the value 0.

Each time a new instance of a class is created, TopLink obtains the required sequence value. For efficiency, TopLink uses preallocation to reduce the number of table accesses required to obtain sequence values (see Section 18.2.3, "Sequencing and Preallocation Size").

You can create the SEQUENCE table on the database in one of two ways:

You can configure table sequencing using Oracle JDeveloper, TopLink Workbench, or Java. For more information about configuring table sequencing, see Section 20.3, "Configuring Sequencing at the Project Level" or Section 98.4, "Configuring Sequencing at the Session Level".

18.2.2.1.1 Default Versus Custom Sequence Table

In most cases, you implement table sequencing using the default table and column names. However, you may want to specify your own table and column names if the following holds true:

  • You want to use an existing sequence table for sequencing.

  • You do not want to use the default naming convention for the table and its columns.

18.2.2.2 Unary Table Sequencing

Although similar to table sequencing (see Section 18.2.2.1, "Table Sequencing"), with unary table sequencing, you create a separate sequence table for each sequenced object in the project.

As Figure 18-3 shows, sequencing information appears in the table for a single class that uses sequencing. You can name the table anything you want but it must contain only one column named (by default) SEQUENCE.

Figure 18-3 TopLink Unary Table Sequence Table

Description of Figure 18-3 follows
Description of "Figure 18-3 TopLink Unary Table Sequence Table"

When you configure sequencing at the descriptor level, you specify the sequence name for the class: this is the name of the unary table sequence table. Figure 18-3 shows a unary table sequence for the Employee class. The Employee class descriptor is configured (see Section 23.3, "Configuring Sequencing at the Descriptor Level") with a sequence name of EMP_SEQ to match the unary table sequence table name. TopLink adds a row to this table and initializes the SEQUENCE column to the value 1.

Each time a new class is created, TopLink obtains the required sequence value from the single row of the unary sequence table corresponding to the class. For efficiency, TopLink uses preallocation to reduce the number of table accesses required to obtain sequence values (see Section 18.2.3, "Sequencing and Preallocation Size").

You can create the unary table sequence table on the database in one of two ways:

Currently, you can only configure unary table sequencing in Java using the UnaryTableSequence class (for more information, see Section 98.4.2, "How to Configure Sequencing at the Session Level Using Java").

18.2.2.3 Query Sequencing

With query sequencing, you can access a sequence resource using custom read (ValueReadQuery) and update (DataModifyQuery) queries and a preallocation size that you specify. This allows you to perform sequencing using stored procedures and allows you to access sequence resources that are not supported by the other sequencing types that TopLink provides.

Currently, you can only configure query sequencing in Java using the QuerySequence class (for more information, see Section 98.4.2.3, "Configuring Query Sequencing").

18.2.2.4 Default Sequencing

The platform owned by a login is responsible for providing a default sequence instance appropriate for the platform type. For example, by default, a DatabasePlatform provides a table sequence using the default table and column names (see Section 18.2.2.1, "Table Sequencing").

You can access this default sequence directly using DatasourceLogin method getDefaultSequence, or indirectly by using the DefaultSequence class, a wrapper for the platform default sequence.

If you associate a descriptor with a nonexistent sequence, the TopLink runtime will create an instance of DefaultSequence to provide sequencing for that descriptor. For more information, see Section 23.3.2.3, "Configuring the Platform Default Sequence".

The main purpose of the DefaultSequence is to allow a sequence to use a different pre-allocation size than the project default.

Currently, you can only make use of default sequencing in Java (for more information, see Section 98.4.2.1, "Using the Platform Default Sequence").

18.2.2.5 Native Sequencing with an Oracle Database Platform

TopLink support for native sequencing with Oracle Databases is similar to table sequencing (see Section 18.2.2.1, "Table Sequencing"), except that TopLink does not maintain a table in the database. Instead, the database contains a sequence object that stores the current maximum number and preallocation size for sequenced objects. The sequence name configured at the descriptor level identifies the sequence object responsible for providing sequencing values for the descriptor's reference class.

You can configure native sequencing using Oracle JDeveloper, TopLink Workbench, or Java. For more information about configuring table sequencing, see Section 20.3, "Configuring Sequencing at the Project Level" or Section 98.4, "Configuring Sequencing at the Session Level".

18.2.2.5.1 Understanding the Oracle SEQUENCE Object

The Oracle SEQUENCE object implements a strategy that closely resembles TopLink sequencing: it implements an INCREMENT construct that parallels the TopLink preallocation size, and a sequence.nextval construct that parallels the SEQ_COUNT field in the TopLink SEQUENCE table in table sequencing. This implementation enables TopLink to use the Oracle SEQUENCE object as if it were a TopLink SEQUENCE table, but eliminates the need for TopLink to create and maintain the table.

As with table sequencing, TopLink creates a pool of available numbers by requesting that the Oracle SEQUENCE object increment the sequence.nextval and return the result. Oracle adds the value, INCREMENT, to the sequence.nextval, and TopLink uses the result to build the sequencing pool.

The key difference between this process and the process involved in table sequencing is that TopLink is unaware of the INCREMENT construct on the SEQUENCE object. TopLink sequencing and the Oracle SEQUENCE object operate in isolation. To avoid sequencing errors in the application, set the TopLink preallocation size and the Oracle SEQUENCE object INCREMENT to the same value. Note that the Oracle sequence object must have a starting value equal to the preallocation size because when TopLink gets the next sequence value, it assume it has the previous preallocation size of values.

18.2.2.5.2 Using SEQUENCE Objects

Your database administrator (DBA) must create a SEQUENCE object on the database for every sequencing series your application requires. If every class in your application requires its own sequence, the DBA creates a SEQUENCE object for every class; if you design several classes to share a sequence, the DBA need create only one SEQUENCE object for those classes.

For example, in Figure 18-4, consider the case of a sporting goods manufacturer that manufactures three styles of tennis racquet. The data for these styles of racquet are stored in the database as follows:

  • Each style of racquet has its own class table.

  • Each manufactured racquet is an object represented by a line in the class table.

  • The system assigns serial numbers to the racquets that use sequencing.

Figure 18-4 Example of Database Tables–Racquet Information

Description of Figure 18-4 follows
Description of "Figure 18-4 Example of Database Tables–Racquet Information"

The manufacturer can do either of the following:

  • Use separate sequencing for each racquet style. The DBA builds three separate SEQUENCE objects, perhaps called ATTACK_SEQ, VOLLEY_SEQ, and PROX_SEQ. Each different racquet line has its own serial number series, and there may be duplication of serial numbers between the lines (for example: all three styles may include a racquet with serial number 1234).

  • Use a single sequencing series for all racquets. The DBA builds a single SEQUENCE object (perhaps called RACQUET_SEQ). The manufacturer assigns serial numbers to racquets as they are produced, without regard for the style of racquet.

18.2.2.6 Native Sequencing with a Non-Oracle Database Platform

Several databases support a type of native sequencing in which the database management system generates the sequence numbers.

When you create a database table for a class that uses native sequencing, include a primary key column, and set the column type as follows:

  • For Sybase and Microsoft SQL Server databases, set the primary key field to the type IDENTITY.

  • For IBM Informix databases, set the primary key field to the type SERIAL.

  • For IBM DB2 databases, set the primary key field to the type IDENTITY.

When you insert a new object into the table, TopLink populates the object before insertion into the table, but does not include the sequence number. As the database inserts the object into its table, the database automatically populates the primary key field with a value equal to the primary key of the previous object plus 1.

At this point, and before the transaction closes, TopLink reads back the primary key for the new object so that the object has an identity in the TopLink cache.

Note:

This type of sequencing does not support preallocation, so the preallocation size must be set to 1. To take advantage of sequence preallocation, Oracle recommends that you use table sequencing on these databases instead of native sequencing.

If your database provides native sequencing, but TopLink does not directly support it, you may be able to access the native sequence object using a query sequence and stored procedures. For more information, see Section 18.2.2.3, "Query Sequencing".

You can configure native sequencing using Oracle JDeveloper, TopLink Workbench, or Java. For more information about configuring table sequencing, see Section 20.3, "Configuring Sequencing at the Project Level" or Section 98.4, "Configuring Sequencing at the Session Level".

18.2.3 Sequencing and Preallocation Size

To improve sequencing efficiency, TopLink lets you preallocate sequence numbers. Preallocation enables TopLink to build a pool of available sequence numbers that are assigned to new objects as they are created and inserted into the database. TopLink assigns numbers from the sequence pool until the pool is empty.

The preallocation size specifies the size of the pool of available numbers. Preallocation improves sequencing efficiency by substantially reducing the number of database accesses required by sequencing. By default, TopLink sets preallocation size to 50. You can specify preallocation size either in Oracle JDeveloper TopLink Editor, TopLink Workbench, or as part of the session login.

Preallocation size configuration applies to table sequencing and Oracle native sequencing. In Oracle native sequencing, the sequence preallocation size must match the Oracle sequence object increment size. Preallocation is not available for native sequencing in other databases as they use an auto-assigned sequence column. Oracle recommends that you use table sequencing in non-Oracle databases to allow preallocation.

For table sequencing, TopLink maintains a pool of preallocated values for each sequenced class. When TopLink exhausts this pool of values, it acquires a new pool of values, as follows:

  1. TopLink accesses the database, requesting that the SEQ_COUNT for the given class (identified by the SEQ_NAME) be incremented by the preallocation size and the result returned.

    For example, consider the SEQUENCE table in Figure 18-2. If you create a new purchase order and TopLink has exhausted its pool of sequence numbers, then TopLink executes a SQL statement to increment SEQ_COUNT for SEQ_PURCH_ORDER by the preallocation size (in this case, the TopLink default of 50). The database increments SEQ_COUNT for SEQ_PURCH_ORDER to 1600 and returns this number to TopLink.

  2. TopLink calculates a maximum and a minimum value for the new sequence number pool, and creates the pool of values.

  3. TopLink populates the object sequence attribute with the first number in the pool and writes the object to the class table.

As you add new objects to the class table, TopLink continues to assign values from the pool until it exhausts the pool. When the pool is exhausted, TopLink again requests new values from the table.

Using Oracle JDeveloper TopLink Editor and TopLink Workbench, you specify a preallocation size when you choose a sequencing type at the project or session level. That preallocation size applies to all descriptors.

Using Java, you can specify a different preallocation size for each sequence that you create.

For more information about configuring preallocation size, see Section 20.3, "Configuring Sequencing at the Project Level" or Section 98.4, "Configuring Sequencing at the Session Level".

18.2.4 Sequencing with EJB 2.n Entity Beans with Container-Managed Persistence

To implement sequencing for entity beans with container-managed persistence, use a sequencing strategy that implements preallocation, such as table sequencing or Oracle native sequencing. Preallocation ensures that the entity bean primary key is available at the ejbPostCreate method. If you use non-Oracle native sequencing (for example, Sybase, Microsoft SQL Server, or Informix database native sequencing), be aware of the following:

  • Non-Oracle native sequencing does not strictly conform to any EJB specification, because it does not initialize the primary key for a created object until you commit the transaction that creates the object. EJB specifications prior to 3.0 expect that the primary key is available at ejbPostCreate method.

  • OC4J supports native sequencing; however, this type of native sequencing does not assign or return a primary key for a created object until you commit the transaction in which the object is created. Because of this, if you use native sequencing, commit a transaction immediately after calling the ejbCreate method to avoid problems with object identity in the TopLink cache and the container.