'Spring Boot running data.sql before creating entities in presence of schema.sql

Note : I have opened an issue on the Spring boot repository. Here's is the link

https://github.com/spring-projects/spring-boot/issues/9048

I'm trying to insert some rows in my entities for testing on developer machine using H2 Database. I'm using data.sql for this.

It works fine, entities are created and then data.sql is run to insert data in the tables produced by the entities.

However I need to create some other tables for which there are no entity classes, so I'm using schema.sql for those. Here's the issue, as soon as I add schema.sql to the project, Spring Boot runs data.sql before creating entities, which ends in Table not found exception.

How can I get data.sql working with the schema.sql and entity classes at the same time ?

Here's sample code for the project.

Git link for functional maven project to reproduce the issue.

https://github.com/ConsciousObserver/SpringBootSchemaSqlIssue.git

package com.test;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class TestDataSqlApplication {

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

@Entity
@Table(name="USER_DETAILS")
class UserDetails {
    @Id
    @GeneratedValue
    private Long id;

    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

schema.sql

create table test(id int(10), name varchar(10));

data.sql

insert into USER_DETAILS VALUES(1, 'user1');
insert into USER_DETAILS VALUES(2, 'user2');
insert into USER_DETAILS VALUES(3, 'user3');

Edit *

As suggested by @abaghel renaming data.sql to import.sql works, however import.sql runs unconditionally. That's not what I need.

For the testing, I have a maven profile which activates a specific spring.datasource.platform = h2, which in turn forces spring to load schema-h2.sql and data-h2.sql. Unfortunately platform has no effect on the import.sql so renaming it to import-h2.sql stops Spring from loading it.

Here's the branch with the platform changes to reproduce this issue.

https://github.com/ConsciousObserver/SpringBootSchemaSqlIssue/tree/platform-h2

javaspringspring-bootspring-data-jpa


Solution 1:[1]

Rename your data.sql to import.sql and your application will start without any error. Please see related document at here.

Solution 2:[2]

I know this is an old question but I was facing the same issue in 2022. It seems that the data.sql file by default is executed before the schema creation by Hibernate. To avoid this, you need to set the property: spring.jpa.defer-datasource-initialization=true

More information here: https://www.baeldung.com/spring-boot-h2-database#2-hibernate-and-datasql

Solution 3:[3]

I was facing the same problem. I have the data.sql and schema.sql files in my project. My solution was to add in the schema.sql file the table creation DDL used by the commands in the data.sql file.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Priyantha
Solution 2 Marco
Solution 3 Durval Printes

Related Questions