How to annotate MYSQL autoincrement field with JPA annotations

Question

Straight to the point, problem is saving the object Operator into MySQL DB. Prior to save, I try to select from this table and it works, so is connection to db.

Here is my Operator object:

@Entity
public class Operator{

   @Id
   @GeneratedValue
   private Long id;

   private String username;

   private String password;


   private Integer active;

   //Getters and setters...
}

To save I use JPA EntityManager’s persist method.

Here is some log:

Hibernate: insert into Operator (active, password, username, id) values (?, ?, ?, ?)
com.mysql.jdbc.JDBC4PreparedStatement@15724a0: insert into Operator (active,password, username, id) values (0, 'pass', 'user', ** NOT SPECIFIED **)

The way I see it, problem is configuration with auto increment but I can't figure out where.

Tried some tricks I've seen here: Hibernate not respecting MySQL auto_increment primary key field But nothing of that worked

If any other configuration files needed I will provide them.

DDL:

CREATE TABLE `operator` ( 
`id` INT(10) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(40) NOT NULL,
`last_name` VARCHAR(40) NOT NULL,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`active` INT(1) NOT NULL,
PRIMARY KEY (`id`)
)

What version of Hibernate are you using? – Steven Benitez Nov 06 '10 at 21:19 — Steven Benitez, Nov 06 '10 at 21:19

Pascal Thivent · Answer 1 · 2010-11-06T21:12:54.790

To use a MySQL AUTO_INCREMENT column, you are supposed to use an IDENTITY strategy:

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

Which is what you'd get when using AUTO with MySQL:

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

Which is actually equivalent to

@Id @GeneratedValue
private Long id;

In other words, your mapping should work. But Hibernate should omit the id column in the SQL insert statement, and it is not. There must be a kind of mismatch somewhere.

Did you specify a MySQL dialect in your Hibernate configuration (probably MySQL5InnoDBDialect or MySQL5Dialect depending on the engine you're using)?

Also, who created the table? Can you show the corresponding DDL?

Follow-up: I can't reproduce your problem. Using the code of your entity and your DDL, Hibernate generates the following (expected) SQL with MySQL:

insert 
into
    Operator
    (active, password, username) 
values
    (?, ?, ?)

Note that the id column is absent from the above statement, as expected.

To sum up, your code, the table definition and the dialect are correct and coherent, it should work. If it doesn't for you, maybe something is out of sync (do a clean build, double check the build directory, etc) or something else is just wrong (check the logs for anything suspicious).

Regarding the dialect, the only difference between MySQL5Dialect or MySQL5InnoDBDialect is that the later adds ENGINE=InnoDB to the table objects when generating the DDL. Using one or the other doesn't change the generated SQL.

Pascal, you are right about that but something in my config files is just not right. Any other suggetions maybe, now that I have put new information and config files? — trivunm, Nov 16 '10 at 22:17
@Pascal - I've found that I needed @GeneratedValue(strategy=GenerationType.IDENTITY) to get AUTO_INCREMENT to work properly when using JPA 2 / Hibernate 4.0.0.CR2 / JBoss AS 7. http://bit.ly/tdNyOJ — Joshua Davis, Oct 26 '11 at 18:35
Note that is true for **MySQL**. With **PostgreSQL** you don't get `@GeneratedValue(strategy=GenerationType.IDENTITY)` when you write `@GeneratedValue(strategy=GenerationType.AUTO)` and `@GeneratedValue`. So be careful and try to be verbose. — sajjadG, Nov 08 '13 at 11:00
note: with more recent versions of Hibernate and/or MySQL/MariaDB GenerationType.AUTO will default to GenerationType.TABLE . Which when upgrading can lead to nasty problems with out of sync sequences. — jwenting, Sep 03 '19 at 10:26
don't forget to recreate table: spring.jpa.hibernate.ddl-auto = create. With update it will not work — Glasnhost, May 12 '20 at 17:48

score 53 · Answer 2 · edited Apr 14 '16 at 04:17

53

Using MySQL, only this approach was working for me:

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

The other 2 approaches stated by Pascal in his answer were not working for me.

edited Apr 14 '16 at 04:17

Sнаđошƒаӽ

16,753
12
73
90

answered Apr 13 '16 at 19:30

Jelle den Burger

1,428
17
31

Yeah, I refereed this in my question/answer. http://stackoverflow.com/questions/39675714/schema-validation-missing-table-hibernate-sequences/39675802#39675802 – Yan Khonski Sep 24 '16 at 11:29
Thanks , it work for me , I'm using `JPA` with `spring-boot` and `MySQL`. – Osama Al-Banna May 07 '18 at 15:55
Agree with this answer, `@GeneratedValue(strategy=GenerationType.AUTO)` with MySQL will produce an extra table `create table hibernate_sequence (next_val bigint) engine=InnoDB` to produce the next id. – Lebecca Nov 04 '20 at 12:42

score 13 · Answer 3 · answered May 03 '12 at 21:39

For anyone reading this who is using EclipseLink for JPA 2.0, here are the two annotations I had to use to get JPA to persist data, where "MySequenceGenerator" is whatever name you want to give the generator, "myschema" is the name of the schema in your database that contains the sequence object, and "mysequence" is the name of the sequence object in the database.

@GeneratedValue(strategy= GenerationType.SEQUENCE, generator="MySequenceGenerator")
@SequenceGenerator(allocationSize=1, schema="myschema",  name="MySequenceGenerator", sequenceName = "mysequence")

For those using EclipseLink (and possibly other JPA providers), it is CRITICAL that you set the allocationSize attribute to match the INCREMENT value defined for your sequence in the database. If you don't, you'll get a generic persistence failure, and waste a good deal of time trying to track it down, like I did. Here is the reference page that helped me overcome this challenge:

http://wiki.eclipse.org/EclipseLink/Examples/JPA/PrimaryKey#Using_Sequence_Objects

Also, to give context, here is what we're using:

Java 7 Glassfish 3.1 PostgreSQL 9.1 PrimeFaces 3.2/JSF 2.1

Also, for laziness' sake, I built this in Netbeans with the wizards for generating Entities from DB, Controllers from Entities, and JSF from Entities, and the wizards (obviously) do not know how to deal with sequence-based ID columns, so you'll have to manually add these annotations.

score 9 · Answer 4 · answered Mar 27 '19 at 21:03

If you are using Mysql with Hibernate v3 it's ok to use GenerationType.AUTO because internally it will use GenerationType.IDENTITY, which is the most optimal in for MySQL.

However in Hibernate v5, It has changed. GenerationType.AUTO will use GenerationType.TABLE which generates to much queries for the insertion.

You can avoid that using GenerationType.IDENTITY (if MySQL is the only database you are using) or with these notations (if you have multiple databases):

@GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
@GenericGenerator(name = "native", strategy = "native")

yes, tricky cause for some nasty regression errors when upgrading your Spring application which under the hood also upgrades Hibernate. — jwenting, Sep 03 '19 at 10:28

score 7 · Answer 5 · answered Nov 15 '17 at 09:55

Please make sure that id datatype is Long instead of String, if that will be string then @GeneratedValue annotation will not work and the sql generating for

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

create table VMS_AUDIT_RECORDS (id **varchar(255)** not null auto_increment primary key (id))

that needs to be

create table VMS_AUDIT_RECORDS (id **bigint** not null auto_increment primary key (id))

score 7 · Answer 6 · answered Aug 11 '20 at 19:28

If you are using MariaDB this will work

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", updatable = false, nullable = false)
private Long id;

For more, you can check https://thorben-janssen.com/hibernate-tips-use-auto-incremented-column-primary-key/

score 1 · Answer 7 · answered Mar 17 '18 at 16:55

I tried every thing, but still I was unable to do that, I am using mysql, jpa with hibernate, I resolved my issue by assigning value of id 0 in constructor Following is my id declaration code

@Id
@Column(name="id",updatable=false,nullable=false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

score 1 · Answer 8 · edited Jan 17 '19 at 12:18

As you have define the id in int type at the database creation, you have to use the same data type in the model class too. And as you have defined the id to auto increment in the database, you have to mention it in the model class by passing value 'GenerationType.AUTO' into the attribute 'strategy' within the annotation @GeneratedValue. Then the code becomes as below.

@Entity
public class Operator{

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

  private String username;

  private String password;

  private Integer active;

  //Getters and setters...
}

score 1 · Answer 9 · answered Mar 25 '20 at 18:18

1

same as pascal answered, just if you need to use .AUTO for some reason you just need to add in your application properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.ddl-auto = update

answered Mar 25 '20 at 18:18

Jorge

11
1

score 0 · Answer 10 · answered Sep 12 '18 at 11:57

can you check whether you connected to the correct database. as i was faced same issue, but finally i found that i connected to different database.

identity supports identity columns in DB2, MySQL, MS SQL Server, Sybase and HypersonicSQL. The returned identifier is of type long, short or int.

More Info : http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/mapping.html#mapping-declaration-id

How to annotate MYSQL autoincrement field with JPA annotations

10 Answers10

Linked