Importing Data with spring boot

Question

I have flyway and spring-boot working correctly, but I can't seem to wire up my spring.datasource.data correctly.

If I have a file src/main/resources/db/seeds/one_project.sql. I have tried the following inside my application.properties file.

# fully qualified path
spring.datasource.data=file:///fully/qualified/path/db/seeds/one_project.sql

# classpath specific
spring.datasource.data=classpath:/db/seeds/one_project.sql

# relative path
spring.datasource.data=/db/seeds/one_project.sql

The only thing I can actually get to work is to copy one_project.sql to src/main/resources/schema.sql ( even copying it to src/main/resources/data.sql does not work.

Is there something I am completely missing from the documentation?

I have been following along the documentation here.

Thanks in advance for the help!

Answer 1

Stuck at that quite long. My context: Spring Boot 2.2.6 + Hibernate 5.4 + script.sql in classpath(src/main/resources). To make script executed at application start I was need to add in application.properties:

spring.datasource.initialization-mode=always
spring.jpa.hibernate.ddl-auto=update
spring.datasource.data=classpath:script.sql

And remove all comments BEFORE the actual code and BETWEEN code in script.sql. Or if you need comments, add SELECT 1; on the next line after the line with comment. Because the next line after commented one seems to be ignored. No matter how many line breaks after line with comment you paste.

Answer 2

As i can see it, Spring Boot executes the data scripts if one of the following conditions is true:

• The schema.sql script is present and the initialization is enabled (spring.datasource.initialize=true)
• If JPA and Hibernate is used and autoconfigured with Spring Boot: The property hibernate.hbm2ddl.auto is present (the value doesn't matter, you can give it an empty string or just "validate") and the initialization is enabled (spring.datasource.initialize=true).

Answer 3

The way I got it working was by using the following properties

 spring.datasource.data=classpath:prod.sql
 spring.datasource.url=jdbc:mysql://localhost:3306/DATABASENAME?useSSL=false
 spring.datasource.username=USERNAME
 spring.datasource.password=PASSWORD
 spring.datasource.initialization-mode=always 

spring.datasource.initialization-mode=always seems to do the trick

Answer 4

Try using classpath*, like the following:

spring.datasource.schema=classpath*:db/seeds/your_schema.sql
spring.datasource.data=classpath*:db/seeds/one_project.sql

Answer 5

As of Spring Boot version 2.7 and onwards the provided here solution of

spring.datasource.initialization-mode=always
spring.jpa.hibernate.ddl-auto=update
spring.datasource.data=classpath:script.sql

will stop working since the properties spring.datasource.data and spring.datasource.initialization-mode have been removed from spring boot. The replacements spring.sql.init.data-locations and spring.sql.init.mode should be used instead.

So the solution would be

spring.sql.init.mode=always
spring.jpa.hibernate.ddl-auto=update
spring.sql.init.data-locations=classpath:script.sql

See relevant Spring Boot 2.7 changelog