How to generate a primary key using @GeneratedValue

In JPA, you can use @GeneratedValue to automatically generate a unique value for the primary key and apply it to a filled with @ Id. There are four ways to generate the value of this primary key. ・ GenerationType.IDENTITY ・ GenerationType.SEQUENCE ・ GenerationType.TABLE ・ GenerationType.AUTO I examined the difference between each DBMS.

Verification environment

• Spring Boot 1.4.3.RELEASE
• PostgreSQL 9.3.15
• MySQL Community Server 5.7.17
• Oracle Database Express Edition 11g Release 2

GenerationType.IDENTITY Use the identity column of the table to generate the primary key value.

@Entity
@Table(name="users")
public class User implements Serializable {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name="userid")
	private long userId;
	
	@Column(name="username")
	private String userName;
	
	@Column(name="password")
	private String password;
	
	@Column(name="email")
	private String email;
        
	...
}

For PostgreSQL, the column type of the associated table must be SERIAL.

CREATE TABLE users
(
    userid serial NOT NULL,
    username character varying,
    password character varying,
    email character varying,
    CONSTRAINT "PK" PRIMARY KEY (userid)
)

For MySQL, you need to specify AUTO_INCREMENT for the column attribute.

Create TABLE users (
    userid bigint not null AUTO_INCREMENT PRIMARY KEY,
    username varchar(20),
    password varchar(20),
    email varchar(20)
);

Oracle does not support GenerationType.IDENTITY.

GenerationType.SEQUENCE Use the database sequence object to generate the primary key value.

@Entity
@Table(name="users")
public class User implements Serializable {
	
	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "userid_seq")
	@SequenceGenerator(name = "userid_seq", sequenceName = "userid_seq", allocationSize = 1)
	@Column(name="userid")
	private long userId;
	
	@Column(name="username")
	private String userName;
	
	@Column(name="password")
	private String password;
	
	@Column(name="email")
	private String email;
        
	...
}

For PostgreSQL, the sequence object specified in sequenceName is required.

CREATE SEQUENCE userid_seq
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 10000
    START 1
    CACHE 1
    CYCLE;

You cannot use GenerationType.SEQUENCE because MySQL does not support sequences.

Like PostgreSQL, Oracle requires a specified sequence object.

create sequence "USERID_SEQ"
    start with 1
    increment by 1
    maxvalue 10000
    minvalue 1
    nocache
    cycle
    noorder

GenerationType.TABLE Generates a primary key value using a table that holds the primary key value.

@Entity
@Table(name="users")
public class User implements Serializable {
	
	@Id
	@TableGenerator( name = "seqTable", table = "seq_table", pkColumnName = "seq_name", pkColumnValue = "user_seq", valueColumnName = "seq_value", initialValue = 1, allocationSize = 1 )
	@GeneratedValue(strategy = GenerationType.TABLE, generator="seqTable")
	@Column(name="userid")
	private long userId;
	
	@Column(name="username")
	private String userName;
	
	@Column(name="password")
	private String password;
	
	@Column(name="email")
	private String email;
        
	...
}

PostgreSQL, MySQL and Oracle are all compatible and you need to create the specified table object.

--postgresql
CREATE TABLE seq_table
(
    seq_name character varying NOT NULL,
    seq_value bigint,
    CONSTRAINT seq_pk PRIMARY KEY (seq_name)
)

GenerationType.AUTO Select a different method for each database to generate a primary key value.

@Entity
@Table(name="users")
public class User implements Serializable {
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name="userid")
	private long userId;
	
	@Column(name="username")
	private String userName;
	
	@Column(name="password")
	private String password;
	
	@Column(name="email")
	private String email;
        
	...
}

For PostgreSQL, use the sequence object hibernate_sequence. If this sequence does not exist, you will receive the error message ʻorg.postgresql.util.PSQLException: ERROR: Relation "hibernate_sequence" does not exist`.

For MySQL, it works the same as GenerationType.IDENTITY. If AUTO_INCREMENT is not specified for the column attribute, the error message java.sql.SQLException: Field'userid' doesn't have a default value is displayed.

For Oracle, as with PostgreSQL, the sequence object hibernate_sequence is used.