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', '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?

You'll need to provide the table schema and the code you're using for the save. Generally, you just *don't do anything* and leave the ID null before saving; that's the point of having it as a wrapper type. — chrylis -cautiouslyoptimistic-, Aug 17 '16 at 05:35

amit · Accepted Answer · 2016-08-17T06:28:02.490

13

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

edited Aug 17 '16 at 06:28

answered Aug 17 '16 at 06:08

amit

807
6
14

I will try. Thanks. – user697911 Aug 17 '16 at 17:56
1

"auto-incremented on demand" means that a new primary key "3" instead of 1 will be generated, if the table already has two records, "1" and "2"? – user697911 Aug 17 '16 at 17:59

score 1 · Answer 2 · answered Aug 17 '16 at 08:03

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.

score 1 · Answer 3 · answered Sep 06 '16 at 09:51

1

<id name="id" column="id" type="integer"> 
    <generator class="increment"></generator> 
</id>

In case if you are using mapping classes then just use above code to generate primary key automatically.

answered Sep 06 '16 at 09:51

Priyanka Patil

17
7

score 0 · Answer 4 · answered Aug 17 '16 at 05:45

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

and you leave it null (0) when persisting.

In some cases the AUTO strategy is resolved to SEQUENCE rather than to IDENTITY or TABLE, so you might want to manually set it to IDENTITY or TABLE (depending on the underlying database).

It seems SEQUENCE + specifying the sequence name worked for you.

score 0 · Answer 5 · answered Jun 17 '17 at 13:51

(postgresql) In the script where you populate the db add this at the end :

ALTER SEQUENCE RESTART WITH 13;

look at the db to find the seq name, example :

ALTER SEQUENCE role_role_id_seq RESTART WITH 13;

you should be able to name the generated sequence name as well with the anotation on the model...

hope it helps !

score 0 · Answer 6 · answered Sep 24 '19 at 07:53

Now in Hibernate 5.2 you just need check @GeneratedValue(strategy = IDENTITY) at Variable level OR method level.

@Id
@GeneratedValue(strategy = IDENTITY)
private Integer id;

or

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "Id", unique = true, nullable = false)

public Integer getId() {
    return this.id;
}

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

6 Answers6

Linked