Why is my import.sql file not automatically running?

Question

Working on java spring boot app. Our postgres database is containerized. I can get hibernate to automatically create the tables, but I can't get it to automatically run the import.sql file. Can you please help me figure out what is going on?

Here is the build.gradle file:

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath('org.springframework.boot:spring-boot-gradle-plugin:2.1.6.RELEASE')
    }
}


apply plugin: 'application'
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

mainClassName = 'idealab.IdeaLabMain'

bootJar {
    baseName = 'idealab'
    excludeDevtools = false //TODO(e-carlin): In production this should be removed
}

repositories {
    mavenCentral()
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
    compile('org.springframework.boot:spring-boot-devtools') // TODO(e-carlin): Make sure this isn't pulled in in the production jar
    implementation('org.springframework.boot:spring-boot-starter-data-jpa')
    implementation('org.springframework.boot:spring-boot-starter-web')
    implementation('org.postgresql:postgresql')
    testCompile('junit:junit')
}

Here is the application.properties file:

logging.level.org.hibernate=DEBUG
debug=true

spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=docker
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
hibernate.hbm2ddl.auto=create # TODO(e-carlin): This will wipe away the
                                     # database data. Good for dev not for prod
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

Here is the import.sql file located in src/main/resources:

INSERT INTO color_type (color) VALUES ('blue'),('green'),('purple'),('red');

And here is an example of this model:

package idealab.api.model;

import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;



@Entity
@Table(name = "color_type")
public class ColorType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @OneToMany(targetEntity=PrintModel.class, mappedBy="colorTypeId")   
    private Set<PrintModel> printModel;

    @Column(name = "color", nullable = false)
    private String color;

    public ColorType(Integer id, String color) {

        this.color = color;
    }

    //getters and setters
    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }
}

Thanks for any help you can provide!

Answer 1

import.sql is the Hibernate native feature which will only be executed provided that hibernate.hbm2ddl.auto is set to create or create-drop.

But now you are setting :

spring.jpa.generate-ddl=true
hibernate.hbm2ddl.auto=create 

• spring.jpa.generate-ddl=true will set hibernate.hbm2ddl.auto=update behind scene .
• hibernate.hbm2ddl.auto=create is invalid and does not have any effect as all the valid springboot JPA properties should start with spring.jpa

So at the end , the hibernate.hbm2ddl.auto will be set to update and hence import.sql will not be executed.

You can simply fix it by changing hibernate.hbm2ddl.auto=create to :

spring.jpa.properties.hibernate.hbm2ddl.auto=create

Note : spring.jpa.generate-ddl will be overrided by spring.jpa.properties.hibernate.hbm2ddl.auto and hence you can simply remove it.

Answer 2

Try after adding the below property,

spring.datasource.initialization-mode=always

If you're using Spring boot 2, database initialization only works for embedded databases (H2, HSQLDB, ...). If you want to use it for other databases as well, you need to change the spring.datasource.initialization-mode property:

if it doesnt work change the sql file to data.sql and try it.

ref please check the spring-doc