Running SQL scripts on startup with spring boot and h2 memory database

Question

I have a spring-boot application where i'm running a h2 memory database, however I can't seem to run SQL scripts when starting the app. I want to run a script to create a couple of tables. This script works fine when I run it manually, but I want to run it automatically on startup.

I have understood that the script should be ran automatically when put in the src/main/resources directory, which is also the classpath.

I found this Stackoverflow question which seems to contain all sorts of different answers, however none of them seem to work for me.

This is my pom.xml :

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>...</groupId>
    <artifactId>...</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>...</name>
    <description>...</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.3.6</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.193</version>
        </dependency>

        <!-- Switch back from Spring Boot 2.x standard HikariCP to Tomcat JDBC,
        configured later in Heroku (see https://stackoverflow.com/a/49970142/4964553) -->
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <excludes>
                    <exclude>app/**</exclude>
                </excludes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>com.heroku.sdk</groupId>
                <artifactId>heroku-maven-plugin</artifactId>
                <version>2.0.8</version>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

This is how my application.properties looks like :

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=admin
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=none

I tried to configure my application.properties in a couple of ways that were listed:

//Configuration 1
spring.datasource.schema=classpath:data.sql

//Configuration 2
spring.sql.init.schema-locations=classpath:data.sql 

//Configuration 3
spring.datasource.data=classpath:data.sql

//Configuration 4
spring.sql.init.data-locations=classpath:data.sql

None of them worked, I also tried to configure a DataSource Bean, which seemed to have done something different, since the logs didn't show h2 memory database starting up before, now the logs show this:

2022-06-25 18:46:25.826  INFO 5303 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2022-06-25 18:46:25.840  INFO 5303 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 8 ms. Found 0 JPA repository interfaces.
2022-06-25 18:46:26.413  INFO 5303 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8081 (http)
2022-06-25 18:46:26.419  INFO 5303 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2022-06-25 18:46:26.419  INFO 5303 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.56]
2022-06-25 18:46:26.467  INFO 5303 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2022-06-25 18:46:26.467  INFO 5303 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1335 ms
2022-06-25 18:46:26.568  INFO 5303 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:testdb'
2022-06-25 18:46:26.725  INFO 5303 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2022-06-25 18:46:26.747  INFO 5303 --- [  restartedMain] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.6.4.Final
2022-06-25 18:46:26.857  INFO 5303 --- [  restartedMain] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2022-06-25 18:46:26.906  INFO 5303 --- [  restartedMain] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2022-06-25 18:46:26.993  INFO 5303 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]

The DataSource Bean :

@Bean
public DriverManagerDataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    dataSource.setUrl("jdbc:h2:mem:testdb");
    dataSource.setUsername("admin");
    dataSource.setPassword("admin");

    // schema init
    Resource initSchema = new ClassPathResource("data.sql");
    DatabasePopulator databasePopulator = new ResourceDatabasePopulator(initSchema);
    DatabasePopulatorUtils.execute(databasePopulator, dataSource);

    return dataSource;
}

What am I missing here? Thanks!

EDIT I fixed the issue: I found this post on github and it seemed relevant since my hikariPool wasn't starting up, I checked one of the answers where you configure the hikaripool as a bean, like this :

@Bean
public HikariDataSource createHikariConfig() {
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setDriverClassName("org.h2.Driver");
    hikariConfig.setJdbcUrl("jdbc:h2:mem:testdb");
    hikariConfig.setUsername("admin");
    hikariConfig.setPassword("admin");
    return new HikariDataSource(hikariConfig);
}

This made the hikariPool startup and execute the schema.sql script file under my src/main/resources, and now it is working.

Docs says the file name must be schema.sql or data.sql, is this be the problem? — Enfield Li
– Enfield Li, Commented Jun 25, 2022 at 21:27
I tried to name it both data.sql and schema.sql, sadly didn't work — Ruthless
– Ruthless, Commented Jun 25, 2022 at 22:37

Davide D'Alto · Accepted Answer · 2022-06-26 09:13:26Z

3

You've disabled schema generation with

spring.jpa.hibernate.ddl-auto=none

Set it to create and it should work with the default schema.sql or import.sql

For reference, these are the possible values you can set it to:

none: No action will be performed
create-only: Database creation will be generated
drop: Database dropping will be generated
create: Database dropping will be generated followed by database creation.
create-drop: Drop the schema and recreate it on SessionFactory startup. Additionally, drop the schema on SessionFactory shutdown
validate: Validate the database schema
update: Update the database schema

edited Jun 26, 2022 at 9:13

answered Jun 26, 2022 at 1:09

Davide D'Alto

8,4212 gold badges20 silver badges34 bronze badges

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

6 Comments

Ruthless Over a year ago

I changed the schema generation to create and changed the sql file name to import.sql, still nothing happens

Davide D'Alto Over a year ago

Have you remove all the configuration where you set it to data.sql? Did you check that the *.sql is actually in the build or target folder?

Davide D'Alto Over a year ago

Otherwise, you can generate an example using start.spring.io and compare what's different in your project.

Davide D'Alto Over a year ago

As a last resort, if you can push the project on GitHub, I will have a look.

Ruthless Over a year ago

I managed to fix it by configuring the hikaripool, see my edited answer, thanks for the help!

|

Collectives™ on Stack Overflow

Running SQL scripts on startup with spring boot and h2 memory database

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related