CODEWITHSUNDEEP
javaspringspring-boot
Susanne
Susanne

Reputation: 53

Spring Boot - Loading Initial Data to Database

I have a little problem. I have a Spring Boot Application and I will fill my H2 database with data. But I can not load the initial database data from the data-h2.sql file.

Model:

@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name = "mood")
public class Mood {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "mood_id")
    private int id;

    @Column(name = "name")
    private String name;

    public Mood(String name) {
        this.name = name;
    }

    public Mood(int id, String name) {
        this.id = id;
        this.name = name;
    }
}

data-h2.sql file:

INSERT INTO mood (name) VALUES ('Good');

application.properties file:

spring.datasource.url=jdbc:h2:mem:mooddb;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=a
spring.datasource.password=a
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create

spring.h2.console.enabled=true

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

spring.jpa.defer-datasource-initialization=true

spring.h2.console.path=/h2-console
spring.h2.console.settings.trace=false
spring.h2.console.settings.web-allow-others=false

Upvotes: 4

Views: 9742

Answers (2)

Panagiotis Bougioukos
Panagiotis Bougioukos

Reputation: 18979

The problem is that Spring Boot expects to find a file with either the name schema.sql or data.sql in the classpath to load.

But your file is named data-h2.sql so spring will not consider it, even if it is correctly placed in resources location (i.e either src/main/resources/ or src/test/resources).

It could search for a file with the name data-h2.sql and load it if you had configured the property spring.datasource.platform = h2.

Without using the aforementioned property, there are 2 solutions:

  1. Rename the file into data.sql and be sure it is inside the resources folder

  2. Do not rename the file and use the following application property to inform spring about the name of the file that it should load spring.sql.init.data-locations=classpath:data-h2.sql

Upvotes: 9

xerx593
xerx593

Reputation: 13261

The How-To guide is this.

And the property is named:

spring.sql.init.platform=h2

Spring Boot can automatically create the schema (DDL scripts) of your JDBC DataSource or R2DBC ConnectionFactory and initialize it (DML scripts). It loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively. In addition, Spring Boot processes the schema-${platform}.sql and data-${platform}.sql files (if present), where platform is the value of spring.sql.init.platform. This allows you to switch to database-specific scripts if necessary. For example, you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql, and so on). By default, SQL database initialization is only performed when using an embedded in-memory database. ...

 spring.jpa.defer-datasource-initialization=true

... on the other hand:

This will defer data source initialization until after any EntityManagerFactory beans have been created and initialized. schema.sql can then be used to make additions to any schema creation performed by Hibernate and data.sql can be used to populate it.

(Manually with EnitityManager, as I understand)

Please also consider:

It is recommended to use a single mechanism for schema generation.

(So, not hibernate + scripts (+ migration tool)!)

And:

Spring Boot supports two higher-level migration tools: Flyway and Liquibase.

A common pattern (for DDL) is:

  1. To generate/log DDL scripts with hibernate/jpa (e.g. when new entities evolve)
  2. To use these in flyway/liquibase.

Upvotes: 3

Related Questions