「primarykey」[JavaEE] JPA中的Primary Key的几种生成方式

primarykey

本文发表在https://softwarecave.org/2014/08/02/primary-key-generators-in-jpa/，详实、易懂的介绍了JPA 的Key Generator

Generation of primary key values is a very important functionality of relational database management systems. The main idea is to let RDBMS automatically calculate and assign primary key value to the row being inserted into the database table. This not only simplifies the source code of the APPlication using database but also makes the application more robust. There are two widely known methods to perform the generation of primary key values on the database level: auto-increment/identity columns or sequences.

Due to these differences JPA provide three different ways to automatically generate primary key values. Let’s explore them below.

1 Using auto-increment/identity columns

my sql and Microsoft SQL Server provide a functionality to automatically generate a unique number for the primary key when a new row is added into a table. For example, in MySQL it is done by marking primary key column with AUTO_INCREMENT keyword:

CREATE TABLE JPAGEN_ADDRESS
(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  CITY VARCHAR(255) NOT NULL,
  STREET VARCHAR(255) NOT NULL
);

and in Microsoft SQL Server with IDENTITY keyword:

CREATE TABLE JPAGEN_ADDRESS
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CITY VARCHAR(255) NOT NULL,
    STREET VARCHAR(255) NOT NULL
);

JPA must be informed to use auto-increment/identity for primary key by specifying IDENTITY generation strategy on the ID column:

@Entity
@Table(name = "JPAGEN_ADDRESS")
public class Address implements serializable {

    @Id
    @generatedValue(strategy = GenerationType.IDENTITY)
    private long id;

    // other fields and methods are omitted 
}

Now, when a new Address entity is persisted, the value of the primary key column will be automatically generated by the database.

2 Using sequences

oracle Database and PostgreSQL use explicit sequence type to generate unique primary key numbers. For example, in Oracle Database the table and its sequence can be defined like this:

CREATE TABLE JPAGEN_ADDRESS
(
    ID NUMBER PRIMARY KEY,
    CITY VARCHAR(255) NOT NULL,
    STREET VARCHAR(255) NOT NULL
);
CREATE SEQUENCE JPAGEN_ADDRESS_SEQ START WITH 100;

When using sequence for given entity, the SEQUENCE strategy must be defined on its ID column and additionally the name of the sequence must be specified using @SequenceGenerator annotation:

@Entity
@Table(name = "JPAGEN_ADDRESS")
public class Address implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
            generator = "addressGen")
    @SequenceGenerator(name = "addressGen",
            sequenceName = "JPAGEN_ADDRESS_SEQ")
    private long id;

    // other fields and methods are omitted 
}

When a new Address entity is persisted, JPA implementation will obtain the next value of the sequence and use it to insert a new row into the database table.

3 Using table

The third method to generate primary keys is to have a separate table which stores in a single row the sequence name along with the next number to use for the primary key. For performance reasons the next value is not increased by one, whenever JPA implementation needs a new value for the primary key but by a much higher number (e.g. 50). Once the range of values becomes reserved, JPA implementation can assign primary keys without accessing the database. When every value in the range becomes used, JPA implementation reserves a new range and the cycle continues.

Additionally, in such table we can have multiple rows with each row serving different entity. The only requirement is to use unique sequence names for each entity.

The table strategy is the most complicated one but it is the only strategy that is really portable across different databases. If you are developing an application which can use multiple RDBMS or there may be a need to port to a new RDBMS in the future, using table strategy is the most viable option.

The table to store the Person entity and the table to generate sequences can be created like this:

CREATE TABLE JPAGEN_PERSON
(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    ADDRESS_ID NUMBER
);
CREATE TABLE JPAGEN_GENERATORS
(
    NAME VARCHAR(255) PRIMARY KEY,
    VALUE NUMBER
);

The table with sequences must have two columns. The first one should contain the name of the sequence and the second one should be of numeric type. JPA implementation will automatically update the second column when reserving a new range of values.

Additionally, JPA must be instructed to use TABLE generation strategy:

@Entity
@Table(name = "JPAGEN_PERSON")
public class Person implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE,
            generator = "personGen")
    @TableGenerator(name = "personGen",
            table = "JPAGEN_GENERATORS",
            pkColumnName = "NAME",
            pkColumnValue = "JPAGEN_PERSON_GEN",
            valueColumnName = "VALUE")
    private long id;

    // other fields and methods are omitted 
}

We also have to add @TableGenerator annotation to inform JPA about the name of the generator table (table element), the names of its both columns (pkColumnName and valueColumnName elements)and also the name of the sequence (pkColumnValue element).

4 Conclusion

The choice between IDENTITY, SEQUENCE and TABLE strategies should be pretty obvious. If you application is going to use only one RDBMS, the first or the second option should be fine (depending on your RDBMS vendor). In other cases, TABLE strategy is preferred, if not the only possible.

5 Reference

[1] https://softwarecave.org/2014/08/02/primary-key-generators-in-jpa/

[JavaEE] JPA中的Primary Key的几种生成方式

primarykey

1 Using auto-increment/identity columns

2 Using sequences

3 Using table

4 Conclusion

5 Reference

相关阅读

栏目导航

推荐阅读

热门阅读