CODEWITHSUNDEEP
hibernateh2
Guisong He
Guisong He

Reputation: 1986

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

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?

Upvotes: 44

Views: 66681

Answers (4)

Markus Pscheidt
Markus Pscheidt

Reputation: 7351

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.

Upvotes: 7

Ramon Pacheco
Ramon Pacheco

Reputation: 407

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)

Upvotes: 26

Grigory Kislin
Grigory Kislin

Reputation: 18030

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

Upvotes: 16

StanislavL
StanislavL

Reputation: 57421

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

Upvotes: 51

Related Questions