Cannot add initial data to database with Spring Boot (Hibernate)

Question

I use Hibernate in my Spring Boot app and populate the tables without any problem. However, I am trying to add 2 record to one of the populated table using import.sql on the classpath.

INSERT INTO role(id, role_type) VALUES(1, 'ADMIN');
INSERT INTO role(id, role_type) VALUES(2, 'USER');

I also set the following properties:

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always

When I run the app, tables are created and the data is populated, and tables are created. However, I got "Error executing DDL : alter table user_role drop foreign key" error.

When I update spring.jpa.hibernate.ddl-auto as shown below, there is no error and tables are created. BUT, records are not inserted to the role table:

spring.jpa.hibernate.ddl-auto= update

So, how can I insert data when I run the app without any problem?

Jishnu Prathap · Accepted Answer · 2022-11-06 10:48:50Z

3

import.sql will only be executed provided that spring.jpa.hibernate.ddl-auto is set to create or create-drop.

Alternatively you can use data.sql it will be executed irrespective of hibernate.ddl configuration.

Using spring.jpa.hibernate.ddl-auto= update and data.sql should solve both of your error.

spring.sql.init.mode=always 
spring.jpa.defer-datasource-initialization=true
spring.jpa.hibernate.ddl-auto= update

Use defer-datasource

edited Nov 6, 2022 at 10:48

answered Nov 6, 2022 at 7:39

Jishnu Prathap

2,0633 gold badges22 silver badges44 bronze badges

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

4 Comments

Jack Over a year ago

Actually, I tried to use data.sql with spring.jpa.hibernate.ddl-auto= update, but it does not help to add records. Then I decided to use Flyway.

Jishnu Prathap Over a year ago

@Rosa try adding this property spring.sql.init.mode=always for Spring Boot before 2.5: spring.datasource.initialization-mode=true

Jack Over a year ago

Thanks, but I also tried that way before asking the question and this time I am getting "Table 'student-db.role' doesn't exist" error. I think when data.sql is executed, Hibernate has not created tables. So, is there a solution for that? It is really weird.

Jack Over a year ago

Perfect!... Solved the problem, thanks a lot.

Collectives™ on Stack Overflow

Cannot add initial data to database with Spring Boot (Hibernate)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related