Not able to import sql insert script data from data.sql in a Spring Boot application

Question

I have a very basic Spring Boot application that has schema.sql and data.sql which creates an H2 DB schema and imports data respectively.

My schema is getting generated fine in the H2 DB but for some reason data is not getting imported. I see 0 records in the table.

I do not see any errors either.

Looks like a config issue. Please guide.

pom.xml

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/>
    </parent>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-rest</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>

application.properties

hibernate.jdbc.batch_size=500
hibernate.show_sql=true
hibernate.format_sql=true

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:tcp://localhost/~/h2-dbs/demo-db
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

src/main/resources/schema.sql

drop table env if exists;
create table env (
  id        int             auto_increment      primary key,
  name      varchar(250)    not null            default 'DEV',
  category  varchar(250)    not null            default 'LOWER',
  enabled   char(1)         not null            default 'Y'
);

src/main/resources/data.sql

insert into env (name, category) values
  ('DEV','LOWER'),
  ('QA','LOWER'),
  ('PERF','HIGHER'),
  ('CT','HIGHER'),
  ('UAT','HIGHER'),
  ('PROD','HIGHER');

domain/Env.java

@Entity
public class Env {
    private int id;
    private String name;
    private String category;
    private String enabled;

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "name")
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Basic
    @Column(name = "category")
    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    @Basic
    @Column(name = "enabled")
    public String getEnabled() {
        return enabled;
    }

    public void setEnabled(String enabled) {
        this.enabled = enabled;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Env env = (Env) o;
        return id == env.id &&
                Objects.equals(name, env.name) &&
                Objects.equals(category, env.category) &&
                Objects.equals(enabled, env.enabled);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, category, enabled);
    }
}

EnvRepository.java

@RepositoryRestResource
public interface EnvRepository extends PagingAndSortingRepository<Env, Integer> {
}

DemoRestApp.java

@SpringBootApplication
public class DemoRestApp {

    public static void main(String[] args) {
        SpringApplication.run(DemoRestApp.class, args);
    }

}

Can't you handle insert operations within schema.sql as well, after creation of schema? At least; to check if that type of property setting making insert or not ? — Hayra, Oct 08 '19 at 20:28
And here, there are more properties suggested https://stackoverflow.com/questions/38040572/spring-boot-loading-initial-data spring.jpa.hibernate.ddl-auto=none spring.datasource.initialization-mode=always — Hayra, Oct 08 '19 at 20:30

score 4 · Accepted Answer · answered Oct 08 '19 at 20:35

You need to have this in properties file

spring.jpa.hibernate.ddl-auto=none

here is more information on that. As you can see, there is note in the documentation which says

In a JPA-based app, you can choose to let Hibernate create the schema or use schema.sql, but you cannot do both. Make sure to disable spring.jpa.hibernate.ddl-auto if you use schema.sql.

Not able to import sql insert script data from data.sql in a Spring Boot application

1 Answers1