2

I've got this Spring Boot application that is working with a PostgreSQL database. The application-dev.properties file is as follows:

spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=root


spring.datasource.data=classpath:/sql/dev-data.sql


spring.jpa.properties.hibernate.default_schema=myschema

spring.jpa.show-sql=true
spring.jpa.database=POSTGRESQL

The database is installed in my development machine, and already initialized. The dev-data.sql file is in src/main/resources/sql and right now only deletes everything on a specific table (lets call it myschema.example_table) and inserts some registries. (This is intended to check the functionality works than for an actual use):

delete from myschema.example_table where id > 0;

insert into myschema.example_table (id, val1, val2) values (1, "Hello", "Petecander");
insert into myschema.example_table (id, val1, val2) values (2, "Goodbye", "Gromenauer");

My issue is that... nothing happens. Is as if there wasn't anything related to the dev-data.sql file at all. So, I'm completely at a loss here. I've been browsing for a while, and reading about a lot of different switches that can be enabled on the application.properties file, but nothing. Any idea?

EDIT: Just to provide a bit more of info that I've been asked down there: The application loads fine and can perform some basic CRUD stuff against the database (Just read stuff at the moment), so the application-dev.properties file seems that is being loaded right.

Improve this question
3
  • 1
    Why you named it as dev-data.sql? Why not data.sql? Commented Jun 10, 2020 at 10:34
  • I don't think spring.datasource.platform=postgres is necessary since spring will determine the db from the URL where possible. Presumably if you're running this against localhost you've mapped the port from a docker container? Is 5432 the right port? You've specified DB as postgres but schema as myschema for hibernate - could this be an issue? Presumably when you're spring boot app it sets the profile to dev so that application-dev.properties is the property source in use? I can think of ways to test but there's questions that need answering before you can have a plan to debug this Commented Jun 10, 2020 at 10:46
  • Answering @RobEvans: The application runs the basic CRUD operations "just fine" and the database is not in a docker container, but installed in my dev machine (We are in really early stages of development, the idea is going with docker, just not yet). And yeah, the application-dev.properties file is being load (it has all the connection info to the database, so either that or magic). Commented Jun 10, 2020 at 10:58

8 Answers 8

Reset to default
5

Hibernate will only execute data.sql for either create or create-drop

Add any of the following property to your application properties file:

  • spring.jpa.hibernate.ddl-auto=create
  • spring.jpa.hibernate.ddl-auto=create-drop

This is required along with the property spring.datasource.initialization-mode=always for external (not embedded) databases.

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

2 Comments

Yeah, that did it! I used spring.jpa.hibernate.ddl-auto=update, as the database is created and "stable" at the moment (Although my idea for the future is to use a ddl on develop to create the database). Had to fix some minor problems with my SQL and now is working. Thanks!
This answer is useful, but the "Hibernate will only execute data.sql for either create or create-drop" sentence seems confuse against spring.jpa.hibernate.ddl-auto
4

Looks like some of the suggestions are deprecated now.

spring.datasource.initialization-mode=always

Needs to be replaced with:

spring.sql.init.mode=always

Here is a picture of my application.properties settings for a local H2 database if this helps anybody: Spring H2 Settings

Improve this answer

Comments

1

Did you try to name file data.sql? I think it could help https://www.baeldung.com/spring-boot-data-sql-and-schema-sql

Improve this answer

Comments

0

I've faced a similar issue and found my answer here. If you add:

spring.datasource.initialization-mode=always

To your properties it should work.

Improve this answer

3 Comments

Didn't work. It was one of the "lot of different switches" I mentioned on my original post (I've tried already a lot of combinations to no avail).
are you sure the properties are being loaded?
Yeah, the application.properties file only has spring.profiles.active=dev, application-dev.properties has all the connection data for the local database (and I can perform basic CRUD operations) and the console is telling me that is running with the "dev" profile.
0

For embedded database like h2 , adding below entries in application.properties worked for me.

spring.datasource.data=classpath:mysql.sql

Improve this answer

Comments

0

For spring boot version 2.7, adding spring.sql.init.mode=always to application properties will work.

Improve this answer

Comments

0

I've tried many things, and in the end i found that the combination of

spring.jpa.hibernate.ddl-auto=none

and

spring.sql.init.mode=always

did the trick for me.

Improve this answer

Comments

0

For me it was that I did not add the project as maven project so it was not able to find proper folder structure even after adding the data.sql file to /resources -> click add as maven project In Intellij

Improve this answer

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.