Which primary key generation policy does the Oracle database use?

1. Automatic growth of identity:

Applicable to MySQL, DB2, and ms SQL Server. It uses the primary key generated by the database to generate unique identifiers for long, short, and int types.

Use auto-increment fields of SQL Server and MySQL. This method cannot be placed in Oracle. Oracle does not support auto-increment fields. You need to set sequence (it is often used in MySQL and SQL Server)

The database syntax is as follows:

MySQL: create tablet_user (id int auto_increment primary key, name varchar (20 ));

SQL Server: create tablet_user (id int identity (1, 1) primary key, name varchar (20 ));

<Id name = "id" column = "id" type = "long">

<Generator class = "identity"/>

</Id>

2. sequence:

Sequence supported by DB2 and Oracle, used to generate a unique identifier for long, short, or int

The database syntax is as follows:

Oracle: create sequenceseq_name increment by 1 start with 1;

When you need a primary key value, you can call seq_name.nextval or seq_name.curval to obtain it. The database will help us maintain this sequence and ensure that each retrieved value is unique, for example:

Insert into tbl_name (id, name) values (seq_name.nextval, 'jimliu ');

<Id name = "id" column = "id" type = "long">

<Generator class = "sequence">

<Param name = "sequence"> seq_name </param>

</Generator>

</Id>

If the sequence parameter is not specified, Hibernate will access a default sequence, which is hibernate_sequence. We also need to create this sequence in the database.

In addition, sequence can have another parameter paramters. You can view the Hibernate API to understand its usage. For details, see org. hibernate. id. SequenceGenerator.

Call the database sequence to generate the primary key. You need to set the sequence name. Otherwise, hibernate cannot find it:

NAME_SEQ (common in Oracle)

3. hilo:

Long, short, or int type identifiers generated using a high/low level algorithm. A table and field are given as the source of the high value. The default table is hibernate_unique_key, and the default field is next_hi. It divides the id source into two parts, DB + memory, and then generates the id value together according to the algorithm. Multiple records can be generated within a few connections, improving efficiency.

MySQL: create table hi_value (next_hiinteger not null );

 

Insert into hi_value (next_hi) values (1 );

<Id name = "id" column = "id">

<Generator class = "hilo">

<Param name = "table"> hi_value </param>

<Param name = "column"> next_hi </param>

<Param name = "max_lo"> 100 </param>

</Generator>

</Id>

During hibernate Persistence, hibernate is responsible for generating low-level values. When the hilo identifier generator generates the identifier, it needs to retrieve the current value of next_hi from the hi_value table, and then modify the value. This operation is completed in a separate transaction. The maximum low value is configured in max_lo, but when the low value generated in Hibernate memory exceeds this value, you need to read the high value again in the hi_value table of the database.

To use the hilo generation policy, create an additional table in the database. The default table name is hibernate_unique_key. The default field is of the integer type and the name is next_hi (rarely used)

You can also set the custom table name and field name.

<Id name = "id" type = "integer">

<Column name = "id"/>

<Generator class = "hilo">

<Param name = "my_unique_key"/>

<Param column = "next_hi"/>

</Generator>

</Id>

4. native:

Select one from identity, sequence, and hilo Based on the capabilities of the underlying database, which is more flexible. However, if sequence or hilo is selected, the primary keys of all tables are retrieved from the default sequence or hilo table of Hibernate. In addition, some databases support the test of primary key generation by default, and the efficiency is not very high.

For oracle using Sequence, For MySQL and SQL Server using identity (auto-incrementing primary key generation mechanism), native is to submit the primary key generation work to the database, hibernate (very common)

<Id name = "id" column = "id">

<Generator class = "native"/>

</Id>

5. seqhilo:

With the combination of sequence and hilo, the high level of hilo is generated by sequence, so it also needs the support of the underlying database.

Implemented through the hilo algorithm, but the primary key history is stored in Sequence. It is suitable for databases that support Sequence, such as Oracle (rarely used)

<Id name = "id" column = "id">

<Generator class = "seqhilo">

<Param name = "sequence"> seq_name </param>

<Param name = "max_lo"> 100 </param>

</Generator>

</Id>

6. increment:

This is the primary key generated by Hibernate in the memory. Each increment is 1 and does not depend on the underlying database. Therefore, all databases can be used, but the problem also arises, because it is generated by Hibernate, only

A Hibernate application process can be used to access the database. Otherwise, a primary key conflict occurs. If you cannot use data insertion in a cluster, hibernate will add an auto-incrementing primary key to the primary key, however, a hibernate instance maintains a counter, so this method cannot be used when multiple instances are running.

<Id name = "id" column = "id">

<Generator class = "increment"/>

</Id>

7. uuid. hex:

A 128-bit UUID algorithm is used to generate a string-type identifier. UUID is encoded into a 32-bit hexadecimal number string. UUID includes: IP address, JVM start time, system time (accurate to 1/4 seconds) and

Counter value (unique in JVM)

Hibernate calculates a 128-bit unique value insert.

<Id name = "id" column = "id">

<Generator class = "uuid. hex"/>

</Id>

Uuid. string

Hibernate calculates a 16-Bit Insert value.

8. assigned:

The application is responsible for generating Primary Key Identifiers. The primary key is usually used in the database where there is no proxy primary key. The primary key used is business-related, such:

<Id name = "id" column = "id" type = "string">

<Generator class = "assigned"/>

</Id>

This primary key generation method is not recommended. When designing a database table, you should use a proxy primary key (surrogate key) instead of a natural primary key (natural key has business meaning ), when the <generator> label is not specified,

The default mode is assigned primary key generation.

When inserting data, the primary key is added by the user, and hibernate does not

9. foreign

Use External table fields as primary keys

10. select

Use a trigger to generate a primary key (mainly used to generate a primary key for an earlier database, with less use)

In addition, some tables do not have a proxy primary key and use a joint primary key. Multiple fields are unique. This is not recommended for table design,