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?

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

score 48 · Accepted Answer · answered Aug 23 '16 at 07:21

48

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

answered Aug 23 '16 at 07:21

StanislavL

56,971
9
68
98

3

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? – Guisong He Aug 23 '16 at 07:40
4

**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. TheAUTO 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. – StanislavL Aug 23 '16 at 07:56

score 20 · Answer 2 · edited Jan 30 '22 at 13:59

20

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)

edited Jan 30 '22 at 13:59

ouflak

2,458
10
44
49

answered Jan 29 '22 at 15:27

Ramon Pacheco

301
2
2

3

Thanks, [method 2](https://github.com/tarent/timestamp-utc/commit/a42b21e0665e7c7d86d3f4b341b78c7497570cce) indeed worked! So apparently, the H2 “security” update broke its functionality? – mirabilos Feb 07 '22 at 19:47
Thank you! I faced this after upgrading from version **1.4.200** to version **2.1.210**. You solution worked for me. – Flavio Oliva Feb 07 '22 at 21:04
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. – jayant Feb 09 '22 at 20:38
Related H2 issue on GitHub "Upgrade H2 version 2.0.204 from 1.4.200": https://github.com/h2database/h2database/issues/3325 – Markus Pscheidt Mar 05 '22 at 16:39

Grigory Kislin · Answer 3 · 2019-02-17T19:24:14.127

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

score 6 · Answer 4 · answered Mar 05 '22 at 20:07

6

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.

answered Mar 05 '22 at 20:07

Markus Pscheidt

6,853
5
55
76

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

4 Answers4

Linked