Spring Boot - Loading Initial Data

<< Back to Spring Boot Tutorial

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

Conclusion

In this short article, we saw how we can leverage schema.sql and data.sql files for setting up an initial schema and populating it with data.

Keep in mind that this approach is more suited for basic and simple scenarios, any advanced database handling would require more advanced and refined tooling like Liquibase or Flyway.

In the next article, you will learn how to develop CRUD RESTFul API using Spring boot 2, Hibernate 5, JPA, Maven, and MySQL database.

Check out all spring boot articles, guides, and tutorials at Top Spring Boot Tutorials

Reference

• https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html
• https://stackoverflow.com/questions/38040572/spring-boot-loading-initial-data

<< Back to Spring Boot Tutorial