Oracle sequence and primary key mappings

Objective

Each table has a primary key, which can be a simple primary key and a combined primary key, a simple primary key is a column in the table, and the combined primary key is a few columns in the table. There are many types of primary key generation strategies, where the sequence is one of the common primary key generation strategies for Oracle. This article mainly explains the technical details of the JPA mapping primary key. Where the primary key generation strategy is the Oracle sequence, the JPA implementation is hibernate.

Oracle Sequence Creation sequence

Drop sequence testsequence; Create sequence testsequence Increment by 1 Start with 1 nomaxvalue nominvalue nocycle nocache;

Sequence testsequence, incremented from 1, incremented by 1 at a time. Do not specify a maximum, minimum, no loop, no cache.

Create a table

CREATE TABLE test (  ID number not   null,  name VARCHAR2); ALTER TABLE test  add constraint pk_id primary k EY (ID); CREATE TABLE TEST2 (  ID number not   null,  name VARCHAR2 ()); ALTER TABLE TEST2  add constraint pk_ TEST2_ID primary KEY (ID);

Two tables were created, TEST, TEST2. Table structure is exactly the same, simple primary key ID, name.

Create a Trigger

CREATE OR REPLACE TRIGGER "Tg_test" before INSERT on TEST for each ROW if  (New.id is null) Beginselect testsequence. Nextval into:new.id from Dual;end;/alter TRIGGER "Tg_test" ENABLE; CREATE OR REPLACE TRIGGER "Tg_test2" before INSERT on TEST2 for each ROW if  (New.id is null) Beginselect Testsequenc E.nextval into:new.id from Dual;end;/alter TRIGGER "Tg_test" ENABLE;

The primary key generation strategy for defining two tables test and TEST2 is sequence testsequence.

JPA Primary Key mapping annotations use the JPA mapping primary key, and if the primary key generation strategy is a sequence, four annotations are required, namely @id, @Column, @SequenceGenerator, @GeneratedValue. The annotation @id represents the primary key field of the database for the Entity property, the annotation @column the name of the field that represents the data for the entity attribute, @SequenceGenerator defines a sequence, @GeneratedValue applies a sequence.

private long ID;    @Id    @GeneratedValue (strategy = generationtype.sequence, generator = "SEQUENCE")    @SequenceGenerator (name= " Sequence ", sequencename=" Testsequence ", Initialvalue=1, allocationsize=1)    @Column (name =" ID ") public    long GetId ()    {        return this.id;    }    public void SetId (long id)    {        this.id = ID;    }

@SequenceGenerator, property name, which represents the name of the sequence definition, property sequencename, which represents the name of the sequence. The interpretation of these two properties is quite similar, but not the same. The property name refers to the name of the sequence definition itself, and the property Sequencename represents the name of the sequence that the definition involves. The InitialValue property, which represents the sequence initial value, the property allocationsize, indicates the amplitude of the sequence increment or decrement. @GeneratedValue, the property strategy, which represents the primary key generation policy, generationtype.sequence indicates that the primary key generation policy is a sequence, a property generator that represents the name of the application sequence definition. The properties of the @GeneratedValue generator and @sequencegenerator property name are consistent. Now, perform the persistence action.

Test test = new test (); Test.setname ("John Wiley & Sons"); Manager.persist (test);

To query the database, the data is as follows:

According to the source code, the annotation @sequencegenerator's properties InitialValue, allocationsize are optional, and the default values are 1, 50, respectively.

/**      * (Optional) The value from which the sequence object      * are to start generating.     *    /int initialvalue () default 1;    /**     * (Optional) The amount to increment is allocating * sequence numbers from the      sequence.     *    /int allocationsize () default 50;

However, depending on the test, the results are not as shown in the source code. The above sequence re-establishes, clears the table test data, the @sequencegenerator attribute initialvalue, allocationsize removed, remains the default. Executes the action of the persisted entity. To query the database, the data is as follows:
If you re-establish the above sequence again, empty the table test data, restart the Web server, and perform the entity persistence entity's work, the database ID does not necessarily start at 100. That is to say, the value of Initiavalue is random, and allocationsize is not 50. Moreover, it appears that the primary key values in the inserted table are out of sequence operation. As you can see, the last_number of the sequence testsequence is 3, but the value of the primary key inserted by the program is 100,101. Therefore, it is recommended that when writing annotation @sequencegenerator, although the attribute InitialValue, allocationsize is optional, specify numeric values for both properties and maintain exactly the same definition as the database for the sequence. The scope of application is based on the source code, within the persistence unit, the sequence definition is global. A sequence definition defined within an entity that can be applied to other entities within a persisted unit.

/** * Defines a primary key generator that could be referenced by name when * A generator element was specified for the {@lin K Generatedvalue} * annotation. A sequence generator is specified on the entity * class or in the primary key field or property. The scope of the * Generator name is global to the persistence unit (across all * generator types). * * <pre> *   Example: * *   @SequenceGenerator (name= "Emp_seq", allocationsize=25) * </pre> * * @since Jav A Persistence 1.0 */

However, according to the test, within the persistence unit, the sequence definition is valid only for the entity that defines it. A sequence definition is defined within the entity test with the name sequence. This sequence definition is now applied in the entity TEST2.

  private long ID;    @Id    @GeneratedValue (strategy = generationtype.sequence, generator = "SEQUENCE")    @Column (name = "Id")    Public long getId ()    {        return this.id;    }    public void SetId (long id)    {        this.id = ID;    }

Start Web server and report the error unknown Id.generator:sequence.

private long ID;    @Id    @GeneratedValue (strategy = generationtype.sequence, generator = "SEQUENCE")    @SequenceGenerator (name= " Sequence ", sequencename=" Testsequence ", Initialvalue=1, allocationsize=1)    @Column (name =" ID ") public    long GetId ()    {        return this.id;    }    public void SetId (long id)    {        this.id = ID;    }

The above code, in the entity TEST2, for the sequence testsequence, redefined the definition of the sequence, persistent all normal. Therefore, the sequence definition is valid only for the entity that defines it. In a production environment, different tables should use a different sequence. This ensures that the values generated by the sequence are in a particular table and that their primary key values are continuous. For example, a sequence produces values like 1, 2, 3, 4, 5 ... when the sequence is applied to the table test,test2, it is possible that the values 1, 2 are assigned to test, and 3, 4, and 5 are assigned to TEST2, so that the primary key values in the table are often not contiguous. Of course, the sequence is assigned only to a table, and there is no guarantee that the table's primary key values will be contiguous. When a transaction rollback is encountered, the values of the sequence are also not inserted into the table's primary key, causing the primary key value to be discontinuous.

Oracle sequence and primary key mappings