Configuring Sequencing at the Session Level

You configure TopLink sequencing at the session or project level to tell TopLink how to obtain sequence values: that is, what type of sequences to use.

In a CMP project, you do not configure a session directly: in this case, you must configure sequences at the project level (see "Configuring Sequencing at the Project Level"). In a non-CMP project, you can configure a session directly: in this case, you can use session-level sequence configuration to override project-level sequence configuration, on a session-by-session basis, if required.

Using TopLink Workbench (see "Using TopLink Workbench"), you can configure table sequencing (see "Table Sequencing") and native sequencing ("Native Sequencing With an Oracle Database Platform" and "Native Sequencing With a Non-Oracle Database Platform"), and you can configure a preallocation size that applies to all sequences (see "Sequencing and Preallocation Size").

Using Java (see "Using Java"), you can configure any sequence type that TopLink supports ("Sequencing Types"). You can create any number and combination of sequences. You can create a sequence object explicitly or use the default sequence that the platform creates. You can associate the same sequence with more than one descriptor and you can configure a separate preallocation size for each descriptor's sequence.

If you are migrating a BEA WebLogic CMP application to OC4J and TopLink persistence (see "Migrating BEA WebLogic Persistence to OC4J TopLink Persistence"), the TopLink migration tool does not migrate BEA WebLogic single column sequence tables to TopLink unary sequence tables (see "Unary Table Sequencing"). After migration, you must manually configure your project to use TopLink unary sequence tables if your application originally used single-column sequence tables in BEA WebLogic.

After configuring the sequence type at the session (or project) level, to enable sequencing, you must configure a descriptor with a sequence field and a sequence name (see "Configuring Sequencing at the Descriptor Level").

For more information about sequencing, see "Understanding Sequencing in Relational Projects".

Using TopLink Workbench

To specify the sequencing information for a relational server (or database) session, use this procedure:

Select the session object in the Navigator.
Click the Login tab in the Editor.
Click the Sequencing subtab. The Sequencing subtab appears.

Figure 86-4 Login – Sequencing Tab

Description of "Figure 86-4 Login – Sequencing Tab"

Use the following information to enter data in each field of the Sequencing subtab to configure the persistence type:

Field	Description
Preallocation Size	Select the default preallocation size (see "Sequencing and Preallocation Size"). Default is 50. The preallocation size you configure applies to all sequences.
Default Sequence Table	Select this option to use table sequencing (see "Table Sequencing") with default sequence table name `SEQUENCE`, default sequence name field `SEQ_NAME`, and default sequence count field `SEQ_COUNT`.
Native Sequencing	Select this option to use a sequencing object (see "Native Sequencing With an Oracle Database Platform" or "Native Sequencing With a Non-Oracle Database Platform") created by the database platform. This option applies only to Oracle, Sybase, Microsoft SQL, and IBM Informix database platforms.
Custom Sequence Table	Select this option to use table sequencing (see "Table Sequencing") with a sequence table name, sequence name field, and sequence count field name that you specify.
Name	Select the name of the sequence table.
Name Field	Select the name of the column used to store the sequence name.
Counter Field	Select the name of the column used to store the sequence count.

Using Java

Using Java, you can perform the following sequence configurations:

Using the Platform Default Sequence
Configuring Multiple Sequences
Configuring Query Sequencing

Using the Platform Default Sequence

After you configure your login with a platform (see "Configuring a Relational Database Platform at the Session Level"), you can use the default sequence that the platform provides.

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 "Configuring the Platform Default Sequence".

You can access the default platform sequence directly as Example 86-1 shows. For example, by default, a DatabasePlatform creates a table sequence using the default table and column names (see "Table Sequencing").

Example 86-1 Accessing the Platform Default Sequence

// assume that dbLogin owns a DatabasePlatform
TableSequence tableSeq2 = ((TableSequence)dbLogin.getDefaultSequence()).clone();
tableSeq2.setName("EMP_SEQ");
tableSeq2.setPreallocationSize(75);
dbLogin.addSequence(tableSeq2);

To avoid having to clone the platform default sequence, you can use the DefaultSequence class–a wrapper for the platform default sequence–as Example 86-2 shows. The new sequence named EMP_SEQ will be of the same type as the platform default sequence.

Example 86-2 Using the DefaultSequence Class

login.addSequence(
    new DefaultSequence("EMP_SEQ", 75)
);

You can override the default platform sequence as Example 86-3 shows. In this example, dbLogin owns a DatabasePlatform that provides a default sequence of type TableSequence. After setting the default sequence to type UnaryTableSequence, when you use the DefaultSequence class, it will access the new default sequence type. In this example, the sequence named EMP_SEQ will be of type UnaryTableSequence and have a preallocation size of 75.

Example 86-3 Overriding the Platform Default Sequence

// assume that dbLogin owns a DatabasePlatform
Sequence unaryTableSequence = new UnaryTableSequence();
unaryTableSequence.setPreallocationSize(40);
dbLogin.setDefaultSequence(unaryTableSequence);
dbLogin.addSequence(
    new DefaultSequence("EMP_SEQ", 75) // UnaryTableSequence
);

Configuring Multiple Sequences

In addition to using the platform default sequence (see "Using the Platform Default Sequence"), you can explicitly create sequence instances and configure a Login with any combination of sequence types, each with their own preallocation size as Example 86-4 shows. In this example, the sequence named EMP_SEQ will provide sequence values exclusively for instances of the Employee class and ADD_SEQ will provide sequence values exclusively for instances of the Address class. The sequence named PHONE_SEQ will use the platform default sequence with a preallocation size of 30 to provide sequence values for the Phone class.

Example 86-4 Configuring Multiple Sequences Explicitly

login.addSequence(new TableSequence("EMP_SEQ", 25));
login.addSequence(new DefaultSequence("PHONE_SEQ", 30));
login.addSequence(new UnaryTableSequence("ADD_SEQ", 55));
login.addSequence(new NativeSequence("NAT_SEQ", 10));

If login owned a DatabasePlatform (whose default sequence type is TableSequence), you could configure your sequences using the platform default sequence type as Example 86-5 shows. In this example, sequences EMP_SEQ and PHONE_SEQ share the same TableSequence table: EMP_SEQ and PHONE_SEQ represent rows in this table.

Example 86-5 Configuring Multiple Sequences Using the Default Sequence Type

login.addSequence(new DefaultSequence("EMP_SEQ", 25));
login.addSequence(new DefaultSequence("PHONE_SEQ", 30));
login.addSequence(new UnaryTableSequence("ADD_SEQ", 55));
login.addSequence(new NativeSequence("NAT_SEQ", 10));

Configuring Query Sequencing

You can configure the query that TopLink uses to update or read a sequence value for any sequence type that extends QuerySequence.

In most applications, the queries that TopLink automatically uses are sufficient. However, if your application has special sequencing needs–for example, if you want to use stored procedures for sequencing–then you can configure the update and read queries that the TopLink sequence uses.

illustrates how to specify a stored procedure that updates a sequence and returns the new sequence value with a single SQL select query. In this example, the stored procedure is named UPDATE_SEQ. It contains one input argument–the name of the sequence to update (SEQ_NAME), and one output argument–the value of the sequence after the updated (SEQ_COUNT). The stored procedure increments the sequence value associated with the sequence named SEQ_NAME and returns the new sequence value in the output argument named SEQ_COUNT.

Example 86-6 Using a Stored Procedure for both Sequence Update and Select

DataModifyQuery seqReadQuery = new DataModifyQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqReadQuery.addNamedArgument("SEQ_NAME");
seqReadQuery.addNamedOutputArgument("SEQ_COUNT");
seqReadQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setSelectQuery(seqReadQuery)

and illustrate how to specify separate stored procedures for sequence update and select actions.

In , the stored procedure is named UPDATE_SEQ and it contains one input argument: the name of the sequence to update (SEQ_NAME). The stored procedure increments the sequence value associated with the sequence named SEQ_NAME.

Example 86-7 Using a Stored Procedure for Sequence Updates Only

DataModifyQuery seqUpdateQuery = new DataModifyQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqUpdateQuery.addNamedArgument("SEQ_NAME");
seqUpdateQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setUpdateQuery(seqUpdateQuery)

In , the stored procedure is named SELECT_SEQ and it takes one argument: the name of the sequence to select from (SEQ_NAME). The stored procedure reads one data value: the current sequence value associated with the sequence name SEQ_NAME.

Example 86-8 Using a Stored Procedure for Sequence Selects Only

ValueReadQuery seqReadQuery = new ValueReadQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("SELECT_SEQ");
seqReadQuery.addArgument("SEQ_NAME");
seqReadQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setSelectQuery(seqReadQuery)

You can also create a QuerySequence directly and add it to your login, as Example 86-9 shows.

Example 86-9 Using the QuerySequence Class

// Use the two-argument constructor: pass in sequence name and preallocation size
// Alternatively, you can use zero- or one-argument (sequence name) constructor
login.addSequence(new QuerySequence("SEQ1", 75));