Introduction to Relational Projects (ELUG)

For the latest EclipseLink documentation, please see http://www.eclipse.org/eclipselink/documentation/

---

Related Topics

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

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

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 Workbench, you must add your JDBC driver to the Workbench classpath. For more information, see Configuring the Workbench Environment.

In a relational project, you can make full use of EclipseLink queries and expressions (see Queries).

How to Build Relational Projects for a Relational Database

The Workbench provides complete support for creating relational projects that map Java objects to a conventional relational database accessed using JDBC.

Components of a Relational Project for a Relational Database

Component	Supported Types
Data Source	DatabaseLogin or Database Platforms
Descriptors	Relational Descriptors
Mappings	Relational Mappings

For more information, see Creating a Relational Project.

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

The Workbench does 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 EclipseLink to build a relational project for an object-relational data type database, consider the following:

• You must create a Java class and an EclipseLink ObjectRelationalDescriptor for each structured type (Struct/object-type).
• EclipseLink supports only arrays (Varrays) of basic types or arrays on structured types (Struct/object-type).
  EclipseLink does not support arrays of Refs or arrays of nested tables.
• EclipseLink supports only nested tables of Refs.
  EclipseLink 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.
5. Create an object-relational data type descriptor for each Java class.
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:
   • Creating a Mapping
   • Configuring an Object-Relational Data Type Mapping

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

Component	Supported Types
Data Source	DatabaseLogin or Database Platforms
Descriptors	Object-Relational Data Type Descriptors
Mappings	Object-Relational Data Type Mappings

For more information, see Creating a Relational Project.

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 the Sequencing Elements in a Class Database Table figure 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.

Sequencing Elements in a Class Database Table

You configure EclipseLink sequencing at the Project Level or Session Level to tell EclipseLink how to obtain values for the primary key column: that is, what type of sequencing to use.

You configure EclipseLink configure Sequencing at the Descriptor Level to tell EclipseLink 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:

• Sequencing Configuration Options
• Sequencing Types
• Sequencing and Preallocation Size

Sequencing Configuration Options

You can configure sequencing using either the Workbench or Java (but not both).

Using the Workbench, create one sequence with a single preallocation size that applies to all descriptors that require sequencing. You can configure Table Sequencing or native sequencing (see 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 Default Versus Custom Sequence Table).

Using Java, you can configure any sequence type that EclipseLink supports (see 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 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 How to Configure Sequencing at the Session Level Using Java.

Sequencing Types

EclipseLink supports the following sequence types:

• Table Sequencing
• Unary Table Sequencing
• Query Sequencing
• Default Sequencing
• Native Sequencing with an Oracle Database Platform
• Native Sequencing with a Non-Oracle Database Platform

Table Sequencing

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

As the EclipseLink Table Sequence Table figure 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

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

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

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

• Use the Workbench to create the table. See Generating Tables on the Database for more information.
• Use the EclipseLink table creator to create and update the table manually. See Generating SQL Creation Scripts for more information.

You can configure table sequencing using the Workbench or Java. For more information about configuring table sequencing, see Configuring Sequencing at the Project Level or Configuring Sequencing at the Session Level.

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.

Unary Table Sequencing

Although similar to Table Sequencing, with unary table sequencing, you create a separate sequence table for each sequenced object in the project.

As this figure 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.

EclipseLink 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. The EclipseLink Unary Table Sequence Table figure shows a unary table sequence for the Employee class. The Employee class descriptor is configured (see Configuring Sequencing at the Descriptor Level) with a sequence name of EMP_SEQ to match the unary table sequence table name. EclipseLink adds a row to this table and initializes the SEQUENCE column to the value 1.

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

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

• Use the Workbench to create the table. See Generating Tables on the Database for more information.
• Use the EclipseLink table creator to create and update the table manually. See Generating SQL Creation Scripts for more information.

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

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

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

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 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 EclipseLink runtime will create an instance of DefaultSequence to provide sequencing for that descriptor. For more information, see 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 Using the Platform Default Sequence).

Native Sequencing with an Oracle Database Platform

EclipseLink support for native sequencing with Oracle Databases is similar to table sequencing (see Table Sequencing), except that EclipseLink 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 Workbench or Java. For more information about configuring table sequencing, see Configuring Sequencing at the Project Level or Configuring Sequencing at the Session Level.

Understanding the Oracle SEQUENCE Object

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

As with table sequencing, EclipseLink 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 EclipseLink uses the result to build the sequencing pool.

The key difference between this process and the process involved in table sequencing is that EclipseLink is unaware of the INCREMENT construct on the SEQUENCE object. EclipseLink sequencing and the Oracle SEQUENCE object operate in isolation. To avoid sequencing errors in the application, set the EclipseLink 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 EclipseLink gets the next sequence value, it assume it has the previous preallocation size of values.

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 the Example of Database Tables–Racquet Information figure, 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.

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.

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, EclipseLink 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, EclipseLink reads back the primary key for the new object so that the object has an identity in the EclipseLink 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, we recommend that you use table sequencing on these databases instead of native sequencing.

If your database provides native sequencing, but EclipseLink 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 Query Sequencing.

You can configure native sequencing using Workbench or Java. We recommend that you use the Workbench. For more information about configuring table sequencing, see Configuring Sequencing at the Project Level or Configuring Sequencing at the Session Level.

Sequencing and Preallocation Size

To improve sequencing efficiency, EclipseLink lets you preallocate sequence numbers. Preallocation enables EclipseLink to build a pool of available sequence numbers that are assigned to new objects as they are created and inserted into the database. EclipseLink 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, EclipseLink sets preallocation size to 50. You can specify preallocation size either in the 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. We recommend that you use table sequencing in non-Oracle databases to allow preallocation.

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

1. EclipseLink 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 the EclipseLink Table Sequence Table figure. If you create a new purchase order and EclipseLink has exhausted its pool of sequence numbers, then EclipseLink executes a SQL statement to increment SEQ_COUNT for SEQ_PURCH_ORDER by the preallocation size (in this case, the EclipseLink default of 50). The database increments SEQ_COUNT for SEQ_PURCH_ORDER to 1600 and returns this number to EclipseLink.
2. EclipseLink calculates a maximum and a minimum value for the new sequence number pool, and creates the pool of values.
3. EclipseLink 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, EclipseLink continues to assign values from the pool until it exhausts the pool. When the pool is exhausted, EclipseLink again requests new values from the table.

Using the 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 Configuring Sequencing at the Project Level or Configuring Sequencing at the Session Level.

---

Copyright Statement