user697911 user697911 - 3 months ago 16
MySQL Question

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

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', 'john.smith@mailinator.com', '2125551212')
insert into Member (id, name, email, phone_number) values (1, 'Mary Smith', 'mary.smith@mailinator.com', '2025551212')


Now the MySql database has two records:

select * from Member;
+----+---------------------------+------------+--------------+
| id | email | name | phone_number |
+----+---------------------------+------------+--------------+
| 0 | john.smith@mailinator.com | John Smith | 2125551212 |
| 1 | mary.smith@mailinator.com | 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

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