8

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

4
  • 1
    import.sql isn't run by spring it is detected by hibernate and loaded as such. So it doesn't adhere to the rules that Spring Boot has for the data.sql and schema.sql. Also combining different ways of managing your database is probably not a good thing (imho) and can be confusing. I would suggest disabling hibernate from creating the schema and you put all the schema creation logic in the schema.sql.] Commented Apr 29, 2017 at 9:43
  • @M.Deinum thanks for information, are you saying it can't be done ? I can't figure out what could be the reasoning behind invoking data.sql before entities are created only in presence of schema.sql when all it does is cause issues. Project requires content from some existing tables, which I'm creating in the schema-h2.sql only for testing. New tables are to be generated by entities, so I can't get away with using just the one. Commented Apr 29, 2017 at 19:22
  • Why not, you can put the whole schema in schema.sql imho you shouldn't rely on hibernate creating the schema (at least not in your production environment you want more control over that). Combining the schema.sql, data.sql together with hibernate is tricky. Commented Apr 30, 2017 at 9:30
  • I believe this to be an issue with Spring Boot, have created bug on the github and added link to it in the post above. Thanks for your response. I'm going to try to do it with Java code instead of data.sql. Commented May 2, 2017 at 12:56

3 Answers 3

14

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

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

1 Comment

Thanks, I haven't tried it, I will update the Git repository in question if this works.
2

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

1 Comment

Thanks for the response, it does work with the import.sql, however I have to make it work with the an active spring.datasource.platform value. I have edited the question with the changes.
0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.