How can I populate correctly H2 database with custom script automatically?

Question

Let's say I have:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=truelogging.level.org.hibernate.SQL=DEBUG
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.ddl-auto=create# Show or not log for each sql query
spring.jpa.show-sql = truespring.jpa.generate-ddl=true
spring.jpa.defer-datasource-initialization=true

My scripts are data.sql:

INSERT INTO ITEMS(ITEM_ID, value) VALUES(1, 'EXAMPLE');

and the second script schema.sql:

create table items
(
item_id int not null auto_increment,
value varchar(50) not null,
primary key (item_id)
);

The problem is when I'm using these configurations to populate automatically while running the project I'm getting the issue as in the full stacktrace:

https://gist.github.com/invzbl3/abe68fe95c69b3a81699a2ed08375853#file-stacktrace-L111

Can someone tell me, please, am I missing something here?

Any smart ideas would be helpful here.

If I run it manually I don't have any issues, but while running the project to populate automatically I'm getting the issue as in the stacktrace.

---

UPD:

I've already tried this one, for example:

1. https://stackoverflow.com/a/66333222/8370915

by changing the entity instead of this variant:

@Entity
@Table(name = "ITEMS")
public class Item {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    private String value;
}

to another one as:

@Entity
@Table(name = "`ITEMS`")
public class Item {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    private String value;
}

and as result I have: https://gist.github.com/invzbl3/83f00b9ca8d536052ac3174f7f9ddf47#file-stacktrace-L111

2. And I've tried this: https://stackoverflow.com/a/44267377/8370915

by changing sql script:

instead of:

INSERT INTO ITEMS(item_id, value)
VALUES (1, 'EXAMPLE');

to:

INSERT INTO ITEMS(item_id, value) VALUES (1, '`EXAMPLE`');

and as result I receive: https://gist.github.com/invzbl3/ae873cf7aaeeccfedff2dc5c8f543773#file-stacktrace-2-L111

Answer 1

After a lot of attempts, investigations, reading different articles, I've found finally the way how to do that, so how you can do that using H2 database and scripts using specifically in-memory mode as per requirements in the question.

So to run scripts automatically using H2 database in in-memory mode you need to use this code as:

Item.java

@Entity(name = "ITEM_ENTITY")
@Table(name = "items")
public class Item {

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

    private String designation;
}

MainApplication.java

@SpringBootApplication
public class MainApplication {

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }
}

application.properties

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.session.jdbc.initialize-schema=always
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.defer-datasource-initialization=true
spring.sql.init.continue-on-error=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.sql.init.mode=always

data.sql

insert into ITEMS(id, designation)
values (1, 'EXAMPLE');

schema.sql

create table items
(
    id                    int not null auto_increment,
    designation           varchar(50) not null,
    primary key (id)
);

Useful articles:

1. https://docs.spring.io/spring-boot/docs/2.1.x/reference/html/howto-database-initialization.html
2. https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-initialization.using-basic-sql-scripts
3. https://www.baeldung.com/running-setup-logic-on-startup-in-spring