Database ID self-increment

Hibernate uses MySQL's self-growth id attribute to coexist with self-growth ID and manual assignment ID

We know that in MySQL if the table ID is set to the self-growth attribute, if the ID assignment (value is not used) in the INSERT statement, then the ID of the inserted data will be the value set by the user, and the maximum value of the ID of the table will be recalculated to insert the ID of the table after the maximum value from the increment point

But if you use hibernate and want to implement this MySQL feature, how do you do it?

First we look at Hibernate's ID generation strategy:

Hibernate document wrote

1. Automatic Growth identity

For MySQL, DB2, MS SQL Server, a database-generated primary key for generating unique identities for long, short, int types
Using the SQL Server and MySQL self-increment fields, this method cannot be put into Oracle, Oracle does not support self-increment fields, to set sequence (common in MySQL and SQL Server)
The syntax in the database is as follows:
Mysql:create table t_user (ID int auto_increment PRIMARY key, name varchar (20));
SQL server:create table T_user (ID int identity (primary) key, name varchar (20));

< ID name= "id" column= "id" type= "long" >
< generator class= "identity"/>
</id>

2, Sequence

DB2, Oracle-supported sequence for generating unique identities for long, short, or int
The syntax in the database is as follows:
Oracle:create sequence Seq_name increment by 1 start with 1;
You can call Seq_name.nextval or seq_name.curval when you need a primary key value, and the database will help us maintain this sequence sequence, guaranteeing that the value is unique each time it is taken, such as:
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 we do not specify the sequence parameter, hibernate accesses a default sequence, which is hibernate_sequence, and we also need to establish this sequence in the database.
In addition, sequence can also have another parameter is paramters, you can see the Hibernate API to understand its usage, see Org.hibernate.id.SequenceGenerator
Call the sequence of the database to generate the primary key, set the sequence name, otherwise hibernate cannot find:
< param name= "sequence" >NAME_SEQ</param> (very common in Oracle)

3. Hilo

A long, short, or int type identifier generated using a high/low algorithm, given a table and a field as the source of the high value, the default table is Hibernate_unique_key, and the default field is Next_hi. It divides the source of the ID into two parts, db+ the memory, and then generates the ID value together by the algorithm, can produce many records in the few connection times, improves the efficiency

Mysql:create table Hi_value (Next_hi integer 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>

Hibernate is responsible for generating low values when hibernate is persisted. The Hilo identifier generator needs to fetch the current value of Next_hi from the Hi_value table when generating the identifier, and then modify the value, which is done in a separate transaction. The maximum low value is configured in the property Max_lo, but when the low value generated in hibernate memory exceeds this value, it is necessary to read the high value again in the Hi_value table of the database.
Using the Hilo build policy, to create an additional table in the database, the default table name is Hibernate_unique_key, the default field is the integer type, and the name is Next_hi (less)
We can also set our own 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

Depending on the ability of the underlying database, choosing one from identity, sequence, Hilo, is more flexible, but at this point, if you choose sequence or Hilo, all the table's primary keys are taken from Hibernate's default sequence or Hilo table. Also, some databases are not very efficient to support the default primary key generation test
For Oracle in Sequence mode, for MySQL and SQL Server with identity (self-increment primary key generation mechanism), native is the primary key generation work to the database to complete, hibernate regardless (very common)

< ID name= "id" column= "id" >
< generator class= "native"/>
</id>

5, Seqhilo

The combination of sequence and Hilo, Hilo's high position is generated by sequence, so it also requires support from the underlying database
Implemented through the Hilo algorithm, but the primary key history is saved in Sequence and is applicable to databases that support Sequence, such as Oracle (less useful)

< 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 generated by hibernate in the memory of the primary key, each increment of 1, not dependent on the underlying database, so all the database can be used, but the problem also comes, because it is hibernate generated, so only

Can have a hibernate application process to access the database, otherwise there will be a primary key conflict, can not be used in the cluster situation
Hibernate will add a self-incremented primary key to the primary key when inserting the data, but 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

Using a 128-bit UUID algorithm to generate the identifier of the string type, the UUID is encoded into a 32-bit 16-digit string. UUID contains: IP address, JVM start time, System time (up to 1/4 seconds), and a counter value (unique in JVM)
Hibernate calculates a 128-bit unique value to insert

< ID name= "id" column= "id" >
< generator class= "Uuid.hex"/>
</id>


Uuid.string
Hibernate calculates a 16-bit value to insert


8, assigned

It is the responsibility of the application to generate primary key identifiers, often using a primary key that does not have a proxy primary key in the database, for business-related situations, such as:

< ID name= "id" column= "id" type= "string" >
< generator class= "assigned"/>
</id>

This primary key generation method is not recommended, the database table design should use the proxy primary key (surrogate key), should not use the natural primary key (natural key has business meaning), when the <generator> tag is not specified, The default is how the assigned primary key is generated
When inserting data, the primary key is added by the user, and hibernate does not care


9, Foreign
Use fields from external tables as primary keys

10. Select
Use triggers to generate primary keys (primarily for early database primary key generation mechanisms, less)

Ps:

A proxy primary key is a business-independent and uniquely identifiable record in a database, typically a database that is automatically generated, such as MySQL can use auto_increment,sql2000 to generate the identity, and Oracle can use the sequence generation method Natural primary key refers to business-related, user-specified, and uniquely identifies any one record in the database



Brief version:

Increment: The proxy primary key, suitable for all databases, is maintained by hibernate with the primary key self-increment, not related to the underlying database, but not suitable for 2 or more hibernate processes.

Identity: Proxy primary key, suitable for support of the DBMS such as MySQL or MS SQL Server, the primary key value is not maintained by hibernate.

Sequence: Proxy primary key, suitable for DBMS supporting sequences such as Oracle, primary key value not maintained by hibernate, generated by sequence.

Native: Proxy primary key, according to the specific characteristics of the underlying database select the appropriate primary key generation strategy, if it is MySQL or SQL Server, select Identity, if it is Oracle, select sequence.

Hilo: Surrogate primary Key, Hibernate takes a specific table field as a hign value, generates a primary key value

Uuid.hex: Surrogate primary Key, Hibernate uses the UUID 128-bit algorithm to generate a string-based primary key value

Assign: A natural primary key for application maintenance.

To achieve secure self-increment, you can only use Hibernate to maintain the identity of the self-increment attribute, or use the developer to maintain the ID value assign. If you use identify, the test finds that, regardless of whether the developer assigns a value to the object's ID, Hibernate automatically assigns the ID to the object when it is saved (the ID maximum of +1 in the MySQL table), and if you use assign, the user must assign a value to the object's ID before saving the object. The value must be greater than or equal to 0 if it is equal to 0 o'clock, then the data table ID record will use the maximum id+1 maintained by the MySQL database, and if it is greater than 0 o'clock, the table ID is the ID value of the object set by the user.

After experiment, want to implement the SQL sentence sentence pattern and the final effect of MySQL, need to set the hibernate configuration file ID Increment property is assign, if know the ID, then set the ID of the object is a known ID, otherwise the value of the set ID is 0.

If the ID self-increment property is assign, then hibernate saves the object, and the object's ID is still the value set before the save;

If the ID self-increment property is identity, after Hibernate saves the object, the ID of the object is the value of the ID of the record in the database

Database ID self-increment