How to save an Entity object into an initialized h2 table with generated index?

Question

I have a spring boot project with a h2 database. A table debug is initialized from schema.sql:

DROP TABLE IF EXISTS debug;
  
CREATE TABLE debug (
  id BIGINT PRIMARY KEY,
  dummycol VARCHAR(250) NOT NULL
);

data.sql:

INSERT INTO debug (id, dummycol) VALUES
  (0, 'foo'), 
  (1, 'ba');

For that to work I have to put spring.jpa.hibernate.ddl-auto=none in application.properties. According to the documentation this is neccessary for schema.sql but I found that with another value such as create-drop while the schema is generated (I assume from the annotated classes) the loading script data.sql is ignored so the tables are empty after startup.

I define an Entity class:

@Entity
@Table(name = "debug")
@Data
@NoArgsConstructor
public class DebugE {

    @Id
    //@GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="id")
    private Long id;

    @Column(name="dummycol")
    private String dummy;
}

Then, I subclass CrudRepository: public interface DebugRepository extends CrudRepository<DebugE, Long> and add an entity object to the table

@Autowired
DebugRepository cr;
...
    DebugE d = new DebugE();
    d.setId(computeFreeId(cr))
    d.setDummy("foo1");
    cr.save(d);

So far everything works, but now I would like to id to be set automatically. For that I add @GeneratedValue to the class and comment out d.setId because I expect the framework to do that for me. I have no preference towards a GenerationType, so far neither works:
AUTO,SEQUENCE:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 90036, SQLState: 90036
o.h.engine.jdbc.spi.SqlExceptionHelper   : Sequence "HIBERNATE_SEQUENCE" not found; SQL statement:
call next value for hibernate_sequence [90036-200]
o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [call next value for hibernate_sequence]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] with root cause

org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "HIBERNATE_SEQUENCE" not found; SQL statement:
call next value for hibernate_sequence [90036-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:576) ~[h2-1.4.200.jar:1.4.200]

IDENTITY:

o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 23502, SQLState: 23502
o.h.engine.jdbc.spi.SqlExceptionHelper   : NULL not allowed for column "ID"; SQL statement:
insert into debug (id, dummycol) values (null, ?) [23502-200]
o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
insert into debug (id, dummycol) values (null, ?) [23502-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:459) ~[h2-1.4.200.jar:1.4.200]

TABLE:

 o.hibernate.id.enhanced.TableGenerator   : HHH000351: Could not read or init a hi value

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "HIBERNATE_SEQUENCES" not found; SQL statement:
select tbl.next_val from hibernate_sequences tbl where tbl.sequence_name=? for update [42102-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]

Attempts based on other questions:

• https://stackoverflow.com/a/39094773 strategy=GenerationType.IDENTITY and adding hibernate.dialect=org.hibernate.dialect.H2Dialect to application.properties. Gives a warning: 'hibernate.dialect' is an unknown property. and no change in error message
• https://stackoverflow.com/a/63775719/3014199 adding spring.jpa.hibernate.use-new-id-generator-mappings=false
  No change for IDENTITY,SEQUENCE. Similar error for TABLE:

org.hibernate.orm.deprecation            : HHH90000015: Found use of deprecated [org.hibernate.id.MultipleHiLoPerTableGenerator] table-based id generator; use org.hibernate.id.enhanced.TableGenerator instead.  See Hibernate Domain Model Mapping Guide for details.
o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42102, SQLState: 42S02
o.h.engine.jdbc.spi.SqlExceptionHelper   : Table "HIBERNATE_SEQUENCES" not found; SQL statement:
select sequence_next_hi_value from hibernate_sequences where sequence_name = 'debug' for update [42102-200]

• https://stackoverflow.com/a/63127119/3014199
  Setting spring.jpa.hibernate.ddl-auto from none to create-drop works(i.e. ids are generated, rows are added to table) but now the table is no longer initialized from data.sql.

additional details

application.properties:

spring.datasource.url=jdbc:h2:mem:h2db
spring.h2.console.enabled=true
spring.h2.console.path=/h2

#spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.initialization-mode=embedded
#hibernate.dialect=org.hibernate.dialect.H2Dialect
#spring.jpa.hibernate.use-new-id-generator-mappings=false

build.gradle:

plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
    id "io.freefair.lombok" version "5.2.1"
}


group = 'com.my.project'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-actuator'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'com.h2database:h2'
}

Solution

spring.jpa.hibernate.ddl-auto=none in application.properties
@GeneratedValue(strategy=GenerationType.IDENTITY) in the class and id BIGINT PRIMARY KEY AUTO_INCREMENT in schema.sql made it work.

Answer 1

If you want to generate the id and at the same time using *sql files, you need define the auto_increment property to your id field in creation table.

Because when you using generation strategy - Identity, hibernate believes that your columns is auto increments on a table side.