Loading Initial Data with Spring Boot

Loading initial data into your database is a common requirement for many applications, whether it's for setting up a development environment or for ensuring certain data is present in production. Spring Boot provides several ways to load initial data into your database. This guide will cover the most common methods, including using data.sql, schema.sql, and import.sql files, as well as using JPA's @PostConstruct annotation.

Prerequisites

• JDK 17 or later
• Maven or Gradle
• IDE (IntelliJ IDEA, Eclipse, etc.)

Step 1: Set Up a Spring Boot Project

Use Spring Initializr to create a new project with the following dependencies:

• Spring Web
• Spring Data JPA
• H2 Database (or any other database of your choice)

Download and unzip the project, then open it in your IDE.

Step 2: Configure the Database

2.1 Configure application.properties

Set up your database configuration in the src/main/resources/application.properties file. For this guide, we'll use an in-memory H2 database.

# src/main/resources/application.properties

# H2 Database configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.datasource.platform=h2

# Hibernate configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Step 3: Define Your Entity

Create a simple Student entity in the com.example.demo.entity package.

package com.example.demo.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;

    // Getters and setters
    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Step 4: Load Initial Data Using data.sql

4.1 Create data.sql File

Create a data.sql file in the src/main/resources directory. This file will be executed after the schema is created.

-- src/main/resources/data.sql

INSERT INTO student (name, email) VALUES ('Ramesh Fadatare', 'ramesh.fadatare@example.com');
INSERT INTO student (name, email) VALUES ('Ram Jadhav', 'ram.jadhav@example.com');

Explanation:

• The data.sql file contains SQL statements to insert initial data into the student table.

4.2 Verify Data Loading

Run the Spring Boot application and verify that the data has been loaded by querying the database or by creating a simple controller to fetch the data.

package com.example.demo.controller;

import com.example.demo.entity.Student;
import com.example.demo.repository.StudentRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class StudentController {

    @Autowired
    private StudentRepository studentRepository;

    @GetMapping("/students")
    public List<Student> getStudents() {
        return studentRepository.findAll();
    }
}

Step 5: Load Initial Data Using JPA's @PostConstruct

Another way to load initial data is to use the @PostConstruct annotation in a service or component class.

5.1 Create a Service to Load Initial Data

Create a service class named DataLoader in the com.example.demo.service package.

package com.example.demo.service;

import com.example.demo.entity.Student;
import com.example.demo.repository.StudentRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import jakarta.annotation.PostConstruct;

@Service
public class DataLoader {

    @Autowired
    private StudentRepository studentRepository;

    @PostConstruct
    public void loadData() {
        studentRepository.save(new Student(null, "Ramesh Fadatare", "ramesh.fadatare@example.com"));
        studentRepository.save(new Student(null, "Ram Jadhav", "ram.jadhav@example.com"));
    }
}

Explanation:

• @PostConstruct: Annotates a method to be executed after the bean's initialization. This method loads initial data into the database.

Step 6: Using schema.sql for Schema Creation

If you need to create the schema explicitly, you can use the schema.sql file.

6.1 Create schema.sql File

Create a schema.sql file in the src/main/resources directory.

-- src/main/resources/schema.sql

CREATE TABLE IF NOT EXISTS student (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Explanation:

• The schema.sql file contains SQL statements to create the schema.

Step 7: Using import.sql with Hibernate

If you're using Hibernate, you can use the import.sql file to load data. This file will be executed by Hibernate after the schema is created.

7.1 Create import.sql File

Create an import.sql file in the src/main/resources directory.

-- src/main/resources/import.sql

INSERT INTO student (name, email) VALUES ('Ramesh Fadatare', 'ramesh.fadatare@example.com');
INSERT INTO student (name, email) VALUES ('Ram Jadhav', 'ram.jadhav@example.com');

Explanation:

• The import.sql file contains SQL statements to insert initial data into the student table.

Conclusion

In this guide, you have learned several methods to load initial data in a Spring Boot application:

• Using data.sql
• Using JPA's @PostConstruct
• Using schema.sql for schema creation
• Using import.sql with Hibernate

By following these steps, you can ensure that your database is populated with the necessary initial data for development or production environments.