In Spring Boot, we can load the initial data into the database while startup the application. It is a powerful feature while we are working with different environments. This tutorial will see how to load initial data on startup.

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

Spring Boot JPA Entities:

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

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.

Based on your database, you have to configure the below property in the .properties file.

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

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

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.

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 below.

spring.datasource.initialization-mode=always

Done!

References:

• Spring Boot in-memory DB example
• Spring Boot database auto initialization

Happy Learning 🙂