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?

Rope

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

Lateen

Thanks. When I change to the strategy=GenerationType.IDENTITY, It works. And the DDL clause now changed to create table Reader (id bigint generated by default as identity, age integer not null, gender varchar(255), name varchar(255) not null, registeredDate timestamp, primary key (id)). So what the diffence between IDENTITY and AUTO here? – Rope

IDENTITY Indicates that the persistence provider must assign primary keys for the entity using a database identity column. AUTO Indicates that the persistence provider should pick an appropriate strategy for the particular database. The<code>AUTO</code> generation strategy may expect a database resource to exist, or it may attempt to create one. A vendor may provide documentation on how to create such resources in the event that it does not support schema generation or cannot create the schema resource at runtime. – Lateen

I was also facing the same problem with the spring boot : 3.1.3, hibernate-core: 6.2.7 and H2 2.1.214 combination with strategy=GenerationType.IDENTITY. For me strategy=GenerationType.AUTO worked as it created sequence in H2 DB and started using it for generating ids. Just keeping this info here if someone faces same challenge :-) – Cotenant

Answer 2

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)

Durrell

Thanks, method 2 indeed worked! So apparently, the H2 “security” update broke its functionality? – Thomas

Thank you! I faced this after upgrading from version 1.4.200 to version 2.1.210. You solution worked for me. – Leaves

Option 2 worked for me too. due to vulnerability in 1.4.200 version, I had to upgrade to latest version and unit test were breaking. – Tearle

Related H2 issue on GitHub "Upgrade H2 version 2.0.204 from 1.4.200": github.com/h2database/h2database/issues/3325 – Hatti

Answer 3

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, ...)

Arcadia

As of Hibernate 6 (I'm on 6.4.x) this no longer works. Spring Boot has deprecated that property with a message that "Hibernate no longer supports disabling the use of new ID generator mappings." – Khalilahkhalin

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.

Hatti