Load H2 source data from custom sql with Spring

Question

Having a problem with H2 set up in Spring. I placed all my sql data into one file named data.sql, however when I change it to anything else - it cannot be identified. Any idea how to set up multiple separate files?

Let's say i have a table User and some data inserted, but aiming to have 2 separate files, e.g. user-schema and user-data, and so on - multiple schema files with same number of insert data files.

My current spring.properties looks as follows:

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.h2.console.path=/h2

My current data.sql looks as follows:

DROP TABLE IF EXISTS User;

CREATE TABLE User
(
    id      INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(20) NOT NULL,
    surname VARCHAR(20) NOT NULL,
    role    VARCHAR(20) NOT NULL,
    email   VARCHAR(30) NOT NULL
);

INSERT INTO User (name, surname, role, email)
VALUES ('Thor', '', 'admin', '[email protected]'),
       ('Hulk', '', 'user', '[email protected]'),
       ('Venom', '', 'user', '[email protected]'),
       ('Spider', 'Man', 'user', '[email protected]'),
       ('Super', 'Man', 'user', '[email protected]');

Piotr Solarski · Accepted Answer · 2020-05-31 11:26:38Z

3

If you want to separate your data input to many files you should provide information to Spring about files location:

spring.datasource.data=classpath*:sql/mock-*.sql
spring.datasource.initialization-mode=always

In my project we keep our .sql files in resource/sql folder and every file name mock-*.sql, like mock-user.sql, mock-role-.sql that why I have wildcard in path. Anyway in spring.datasource.data you have to provide path to file with sql inserts.

spring.datasource.initialization-mode=always tells Spring to always initialize DB from files. You should configure that property since you "create-drop" database each time your tests starts.

Spring documentation about data and schema initialization: https://docs.spring.io/spring-boot/docs/2.1.x/reference/html/howto-database-initialization.html

answered May 31, 2020 at 11:26

Piotr Solarski

3162 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Load H2 source data from custom sql with Spring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related