How to auto generate primary key ID properly with Hibernate inserting records

Question

I have a Member entity class whose primary key is defined as:

    @Id
    @GeneratedValue
    private Long id;

I pre-loaded two records into database with Hibernate when I starts the application:

insert into Member (id, name, email, phone_number) values (0, 'John Smith', '[email protected]', '2125551212')
insert into Member (id, name, email, phone_number) values (1, 'Mary Smith', '[email protected]', '2025551212') 

Now the MySql database has two records:

select * from Member;
+----+---------------------------+------------+--------------+
| id | email                     | name       | phone_number |
+----+---------------------------+------------+--------------+
|  0 | [email protected] | John Smith | 2125551212   |
|  1 | [email protected] | Mary Smith | 2025551212   |

+----+---------------------------+------------+--------------+

Now, in my member registration page, when I submit a request to register a new member, I received this error message:

Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'

I figured that it because the auto-generated key always starts with '1', but in the database, it already has two records, so the primary key '1' will be duplicate.

My question is, how to create the primary key properly, given a number of existing records in the table? If I don't use the

@GeneratedValue

annotation, then I always have to find out what's the next key from the database table before insertion.

Is there a best way to handle this situation, which seems very common?

EDITED: As suggested, I used the Stragey:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

What's strange is that, if there are two existing records in the table as below:

select * from English;
+----+------------+---------+--------+
| id | sentence   | source  | status |
+----+------------+---------+--------+
|  1 | i love you | unknown |      0 |
|  2 | i like you | unknown |      0 |
+----+------------+---------+--------+

after I register a new record into the table, the new ID starts with 4, not 3, as below.

select * from English;
+----+----------------+---------+--------+
| id | sentence       | source  | status |
+----+----------------+---------+--------+
|  1 | i love you     | unknown |      0 |
|  2 | i like you     | unknown |      0 |
|  4 | I have a book. | Unknown |      0 |
+----+----------------+---------+--------+
3 rows in set (0.00 sec)

What might cause this?

Answer 1

You should use IDENTITY generator. The IDENTITY generator allows an integer and bigint column to be auto-incremented on demand. The increment process is very efficient since it uses a database internal lightweight locking mechanism as opposed to the more heavyweight transactional course-grain locks.

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

And don't use id in the insert statement as it is being generated automatically.
Note that SEQUENCE will not work for you as SEQUENCE does not work with MySQL

Answer 2

For MySQL, you'll be able to successfully persist using any of the below mentioned strategies:

@GeneratedValue(strategy=GenerationType.AUTO)  

=> specify auto_increment attribute for primary key

@GeneratedValue(strategy=GenerationType.IDENTITY)

=>specify auto_increment attribute for primary key

On a further advanced usage, you can make use of TABLE strategy GenerationType.TABLE where you can specify the primary key from a separate table and you can specify this table as @TableGenerator

Hibernate also has a generation strategy: native. It appropriately selects the generation strategy based upon the underlying database's capability.