Spring Boot - Loading Initial Data

In this article, we will discuss how to load initial data (seed data) in Spring boot applications. Typically, we need setup database with initial data while application startup.
Spring Boot can automatically create the schema (DDL scripts) of your DataSource 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 a platform is the value of spring.datasource.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).

The data.sql File

To demonstrate the usage of the data.sql file, let's consider we have a User JPA entity class:
package net.guides.springboot2.springboot2webappjsp.domain;

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

@Entity
@Table(name = "user")
public class User {
    @Id 
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;
    private String name;
 
    public User()
    {
    }

    public User(Integer id, String name)
    {
        this.id = id;
        this.name = name;
    }

    public Integer getId()
    {
        return id;
    }

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

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }
}
If we run our application, Spring Boot will create an empty table for us, but won’t populate it with anything.
An easy way to populate some data in database tables while application startup is to create a file named data.sql:
INSERT INTO `users_database`.`user` (`id`, `name`) VALUES ('1', 'Salman');
INSERT INTO `users_database`.`user` (`id`, `name`) VALUES ('2', 'SRK');
INSERT INTO `users_database`.`user` (`id`, `name`) VALUES ('3', 'AMIR');
INSERT INTO `users_database`.`user` (`id`, `name`) VALUES ('4', 'Tiger');
INSERT INTO `users_database`.`user` (`id`, `name`) VALUES ('5', 'Prabhas');
When we run the project with this file on the classpath, Spring will pick it up and use it for populating the database.

The schema.sql File

Sometimes, we don’t want to rely on the default schema creation mechanism. In such cases, we can create a custom schema.sql file:
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Spring will pick this file up and use it for creating a schema.
It’s also important to remember to turn off automatic schema creation to avoid conflicts:
spring.jpa.hibernate.ddl-auto=none
Check out all spring boot articles, guides, and tutorials at Top Spring Boot Tutorials

Reference

Comments