H2 database: NULL not allowed for column "ID" when inserting record using jdbcTemplate

Question

I use hibernate's hbm2ddl to generate schema automatically. Here is my domain:

@Entity
public class Reader {

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

  @Column(nullable=false,unique=true)
  String name;

  @Enumerated(EnumType.STRING)
  Gender gender;

  int age;

  Date registeredDate = new Date();

// getter and setter ...
}

When I using hibernate to save a reader, it works fine as expected as it generats a id to the reader . However when I use jdbcTemplate to insert a record with pure SQL, it report an error:

org.springframework.dao.DataIntegrityViolationException: StatementCallback; 
SQL [insert into reader(name,gender,age) values('Lily','FEMALE',21)]; 
NULL not allowed for column "ID"; 
    SQL statement:insert into reader(name,gender,age) values('Lily','FEMALE',21) [23502-192]; 
nested exception is org.h2.jdbc.JdbcSQLException: NULL not allowed for column "ID"; 
    SQL statement:  insert into reader(name,gender,age) values('Lily','FEMALE',21) [23502-192]

How to solve this?

1. I debug to find that the DDL of hb2ddl generated is create table Book (id bigint not null, author varchar(255), name varchar(255), price double not null, type varchar(255), primary key (id)). It seems that the hiberate handle the id stratege in its own way but how?
2. The @GeneratedValue(strategy=GenerationType.AUTO) should generate auto increment in the statement of the DDL but I didn't find that. Did I miss it?

Answer 1

Try to use strategy=GenerationType.IDENTITY instead of the strategy=GenerationType.AUTO

Also could be wrong hibernate.dialect Try the

hibernate.dialect=org.hibernate.dialect.H2Dialect

Answer 2

Hibernate 5.2.x (Spring Boot 2.x) change default strategy for sequences, if DB supported one. So, with strategy=GenerationType.AUTO, hibernate_sequence is created, but id is not autoincremented, based on this sequence, as must be:

create table users (id integer not null, ...) 

instead of

create table table_name(id int default hibernate_sequence.nextval primary key, ...);

(see HHH-13268). There are several solutions:

• change @GeneratedValue to strategy = GenerationType.IDENTITY
• set spring.jpa.properties.hibernate.id.new_generator_mappings=false (spring-boot alias spring.jpa.hibernate.use-new-id-generator-mappings)
• insert with nextval: INSERT INTO TABLE(ID, ...) VALUES (hibernate_sequence.nextval, ...)

Answer 3

If you're using H2 dependency version: "2.0.202" or higher, those other 2 aproaches might work.

1: Use H2 version: "1.4.200" ('com.h2database:h2:1.4.200')

2: Append ";MODE=LEGACY" to the JDBC url (test case -> jdbc:h2:mem:test;MODE=LEGACY)

Answer 4

This has been resolved in Hibernate 5.6.5 (Spring Boot 2.6.4), so that H2 version 2.0.202 (or higher) works again.

See https://github.com/hibernate/hibernate-orm/pull/4524 for reference.