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:

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

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

VALUE is a reserved word is SQL and is a keyword in H2, you need to quote it as "VALUE" or "value" (or MySQL-style `value`). — Evgenij Ryazanov
– Evgenij Ryazanov, Commented Nov 23, 2022 at 4:35
@EvgenijRyazanov, thank you a lot for pointing out! I'll share here new investigations & updates a bit later. I appreciate your time for checking it. It helps me a lot. — invzbl3
– invzbl3, Commented Nov 23, 2022 at 11:12

invzbl3 · Accepted Answer · 2022-12-20 21:18:45Z

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:

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related