data.sql with spring boot JPA Postresql is not loading

Question

I have written a spring boot application. I want to setup initial database data with a data.sql file.

src/main/resources/application.properties

spring.jpa.hibernate.ddl-auto=create
spring.datasource.url=${JDBC_DATABASE_URL}
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.initialization-mode=always
spring.jpa.show-sql=true

src/main/java/package_name/model/my_entity

@Entity
@Table(name = "user_entry")
public class User {
    @Id
    @Column(nullable = false, length = 50)
    private String userId;
    @Column(nullable = false, length = 50)
    private String firstName;
    @Column(nullable = false, length = 50)
    private String lastName;
    @Column(nullable = false, length = 50)
    private String password;
    @OneToMany(mappedBy = "owner", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set songLists;

    public User() {
    }
    ...
}

src/main/resources/data.sql

insert into user_entry (user_id, first_name, last_name, password) values
('MaMu', 'Maxime', 'Muster', 'pass1234');

However the data appears not to be loaded into the database during deployment.

edit:

adding spring.datasource.data= classpath:/data.sql in my src/main/resources/application.properties solved the problem.

user_10961238 · Answer

We have these properties to create and insert the records into a table. As you only need to insert the records you can avoid spring.datasource.schema

spring.datasource.schema= # Schema (DDL) script resource references.
spring.datasource.data= # Data (DML) script resource references.

Set SQL file

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

Notes:

For schema generation and insertion in the same file do not use spring.datasource.data, we have to use spring.datasource.schema
Keep all files in src/main/resources
set spring.datasource.initialization-mode=always
Also make sure you don't have used spring.jpa.generate-ddl=true as it will set hibernate.hbm2ddl.auto=update behind scenes and will invalidate spring.jpa.hibernate.ddl-auto=create

user_2443208 · Answer

In case your Entity not getting considered add below annotation on your Springboot main class just below your @SpringBootApplication annotation,

@EntityScan(basePackages = {"package_name.model.my_entity"}

Also, after removing @Id tag from Entity and it was working for me (Not sure it can be the reason).

data.sql with spring boot JPA Postresql is not loading

2 Answers