HIbernate Annotations @GeneratedValue difference between the strategy type

Question

I am new to Hibernate Framework. I am using Hibernate 5 with MySQL 5.7.14-log.

I am learning about Primary keys @Id @GeneratedValue. I have tried the generation type strategies ie AUTO, SEQUENCE, IDENTITY, TABLE.

The following is my observations about AUTO, SEQUENCE, TABLE The result of the creation is as follows

    @Id @GeneratedValue
Hibernate: drop table if exists hibernate_sequence
Hibernate: drop table if exists USER_DETAILS
Hibernate: create table hibernate_sequence (next_val bigint)
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: create table USER_DETAILS (userId integer not null, address varchar(255), joiningDate date, userName varchar(255), primary key (userId))
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into USER_DETAILS (address, joiningDate, userName, userId) values (?, ?, ?, ?)
Hibernate: insert into USER_DETAILS (address, joiningDate, userName, userId) values (?, ?, ?, ?)
Hibernate: select userdetail0_.userId as userId1_0_0_, userdetail0_.address as address2_0_0_, userdetail0_.joiningDate as joiningD3_0_0_, userdetail0_.userName as userName4_0_0_ from USER_DETAILS userdetail0_ where userdetail0_.userId=?
UserDetails [userId=1, userName=First user, address=Adddress, joiningDate=2016-12-04]

    @Id @GeneratedValue(strategy=GenerationType.AUTO)
Hibernate: drop table if exists hibernate_sequence
Hibernate: drop table if exists USER_DETAILS
Hibernate: create table hibernate_sequence (next_val bigint)
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: create table USER_DETAILS (userId integer not null, address varchar(255), joiningDate date, userName varchar(255), primary key (userId))
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into USER_DETAILS (address, joiningDate, userName, userId) values (?, ?, ?, ?)
Hibernate: insert into USER_DETAILS (address, joiningDate, userName, userId) values (?, ?, ?, ?)
Hibernate: select userdetail0_.userId as userId1_0_0_, userdetail0_.address as address2_0_0_, userdetail0_.joiningDate as joiningD3_0_0_, userdetail0_.userName as userName4_0_0_ from USER_DETAILS userdetail0_ where userdetail0_.userId=?
UserDetails [userId=1, userName=First user, address=Adddress, joiningDate=2016-12-04]

    @Id @GeneratedValue(strategy=GenerationType.SEQUENCE)
Hibernate: drop table if exists hibernate_sequence
Hibernate: drop table if exists USER_DETAILS
Hibernate: create table hibernate_sequence (next_val bigint)
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: create table USER_DETAILS (userId integer not null, address varchar(255), joiningDate date, userName varchar(255), primary key (userId))
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into USER_DETAILS (address, joiningDate, userName, userId) values (?, ?, ?, ?)
Hibernate: insert into USER_DETAILS (address, joiningDate, userName, userId) values (?, ?, ?, ?)
Hibernate: select userdetail0_.userId as userId1_0_0_, userdetail0_.address as address2_0_0_, userdetail0_.joiningDate as joiningD3_0_0_, userdetail0_.userName as userName4_0_0_ from USER_DETAILS userdetail0_ where userdetail0_.userId=?
UserDetails [userId=1, userName=First user, address=Adddress, joiningDate=2016-12-04]

GenerationType.AUTO/SEQUENCE give the same result(ie create a new table hibernate_sequence). Whereas TABLE creates a table hibernate_sequences

I have the following queries.

1. Is there any difference between AUTO and SEQUENCE. IF yes, then whats the difference and which DBs are they specific to?

2. What is the default strategy for MySQL?

3. For GenerationType.TABLE, hibernate_sequences table is created with 2 columns, sequence_name and next_val. What does the sequence_name column indicate. [Current value is default]

Here is my POJO class

@Entity (name="USER_DETAILS")
public class UserDetails
{
    @Id @GeneratedValue
    private int     userId;
    private String  userName;   
    private String address;

    @Temporal (TemporalType.DATE)
    private Date joiningDate;

    public String getAddress()
    {
        return address;
    }

    public void setAddress(String address)
    {
        this.address = address;
    }

    public Date getJoiningDate()
    {
        return joiningDate;
    }

    public void setJoiningDate(Date joiningDate)
    {
        this.joiningDate = joiningDate;
    }

    public int getUserId()
    {
        return userId;
    }

    public void setUserId(int userId)
    {
        this.userId = userId;
    }

    public String getUserName()
    {
        return userName + " from getter";
    }

    public void setUserName(String userName)
    {
        this.userName = userName;
    }
}

I have searched online but I have got confused even more. Links seen

The differences between GeneratedValue strategies

http://docs.oracle.com/javaee/5/api/javax/persistence/GenerationType.html

Answer 1

It is confusing because it is a workaround. The workaround handles the problems that occour while inserting a row (aka new hibernate entity) into the database.

What is the problem about?

The problem is that the insert-statement does not return the new id!

Having this java-code:

Car car = new Car();
car.setName("Hot car");
System.out.println(car.getId()); // returns null
entityManager.persist(car));
System.out.println(car.getId()); // returns 367

The intuitive sql that we expect is:

INSERT INTO car (id, name) VALUES (null, "Hot car");

As you can see, there is no statement like SELECT MAX(ID) FROM car; for the id (367) exactly now inserted row. We can not select the MAX(ID) because the max id may be changed meanwhile by another transaction!

Trust me, there is no database general solution.

Q1

Auto tries to find out the best practice, it looks for the hibernate_sequences-table, if it is not found, it looks for the built-in sequences.

Q2 MySQL

If you are using InnoDB you should use TABLE because you can make a foregin key to the hibernate_sequences table. Otherwise it supports native built-in sequences that should be preferred having the pk of a table bound to this sequence.

Q3

sequence_name is the name of the primary-key (also combined pk) of every table handled with hibernate. If you have three tables, you have three rows in hibernate_sequences. In example the sequence_name for Car is seq_car.

next_val gives the solution for the problem above, it holds the very next prinmary-key that has not yet been used in the table having the pk-sequence in sequence_name. If you have 200 UserDetails in the database the next_val might be 201.