In Spring Boot we can load the initial data into the database while startup the application. It is an extreme feature while we working with different environments. In this tutorial, we are going to see the same how to load initial data on startup.

Some time back I have written an article on spring boot + h2 database integration,  where I have loaded my SQL script including DDL and DML into the H2 DB while starting up the application, however for a complete example you check this.

Spring Boot JPA Entities:

As we know that we can easily create the database tables with Spring Boot JPA entities like below.

Items.java
package com.onlinetutorialspoint.model;

import javax.persistence.*;

@Entity
@Table(name = "items")
public class Item {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(name="item_name")
    private String itemName;
    @Column(name="item_category")
    private String category;

    public Item() {
    }

    public Item(String itemName, String category) {
        this.itemName = itemName;
        this.category = category;
    }
    public long getId() {
        return id;
    }

    public String getItemName() {
        return itemName;
    }

    public String getCategory() {
        return category;
    }
}

Using the above entity class we can create a item table in any database, but ultimately it’s an empty table now let’s populate some data init.

Spring Boot How to load initial data on startup:

So coming to the loading of initial data while startup, we need to separate our DDL (create) and DML (inserts/updates) scripts in two different files that are schema.sql and data.sql respectively. That way Spring Boot can differentiate the scripts.

If you are working with multiple database vendors, for example, MySQL and PostgreSQL you can make these files like schema-mysql.sql or schema-postgresql.sqlsimilar for data.sql files.

In this case, we have to configure the below property in the .properties file.

application.properties
spring.datasource.platform=mysql   #specify vendor name here

Spring Boot always looks these files into the classpath resources – Hence we have to create these files in the src/main/resources folder so that these will be available automatically by the Spring context on startup.

Let’s create the startup files:

schema.sql

src/main/resources/schema.sql
CREATE TABLE `item` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(50) NULL DEFAULT NULL,
  `category` VARCHAR(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

data.sql

src/main/resources/data.sql
INSERT INTO `item` (`id`, `name`, `category`) VALUES (1, 'IPhone 6S', 'Mobile');
INSERT INTO `item` (`id`, `name`, `category`) VALUES (2, 'Samsung Galaxy', 'Mobile');
INSERT INTO `item` (`id`, `name`, `category`) VALUES (3, 'Lenovo', 'Laptop');
INSERT INTO `item` (`id`, `name`, `category`) VALUES (4, 'LG', 'Telivision');

I am not sure, whether you noticed this or not? do we really need schema.sqlfile? because JPA entities create database tables while startup right? So does it make any sense to you?

For me, it doesn’t make any sense, but still, there are some scenarios that we have to create tables from the startup scripts while having the entities in the source, in such cases, we have to tell the spring to remove the ambiguity by defining the below property in application.properties file.

application.properties
spring.jpa.hibernate.ddl-auto=none

If you are using Spring Boot 2or later versions, this database initialization works only for in-memory databases like H2 and HSQLDB etc. So if we want to make this for other DBS we need to configure the spring.datasource.initialization-mode property in the .properties file like below.

application.properties
spring.datasource.initialization-mode=always

Done!

References:

Happy Learning 🙂

About the Author:

Founder of onlinetutorialspoint.com Love Java, Python, Shell and opensource frameworks. Follow him on twitter and facebook for latest updates.

Leave A Comment