CODEWITHSUNDEEP
spring-boot
Alex
Alex

Reputation: 2013

Importing Data with spring boot

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!

Upvotes: 9

Views: 22600

Answers (6)

Panagiotis Bougioukos
Panagiotis Bougioukos

Reputation: 18929

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

Upvotes: 2

Zergleb
Zergleb

Reputation: 2302

TL;DR

Create a blank schema.sql if you want your data.sql to run.
Also as stated in a comment it must execute one line such as `select 1` or `select 1 from dual`

You said

The only thing I can actually get to work is to copy one_project.sql to src/main/resources/schema.sql

Which makes me think it's evident you don't have a schema.sql

So just create a blank schema.sql and then it will run one_project.sql

Source Code -https://github.com/spring-projects/spring-boot/blob/master/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializer.java

As you can see it gathers the schema locations and if schema resources are empty then it doesn't continue to run the data.sql (this is at the top of the runSchemaScripts() method)

Upvotes: 4

chill appreciator
chill appreciator

Reputation: 422

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.

Upvotes: 6

Xiangming Hu
Xiangming Hu

Reputation: 355

Try using classpath*, like the following:

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

Upvotes: 3

patriques
patriques

Reputation: 5201

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

Upvotes: 4

dunni
dunni

Reputation: 44535

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).

Upvotes: 5

Related Questions