How to Load Initial Data into Spring Boot 3.2.5: Runners, Beans, and SQL Scripts

Environment: Spring Boot 3.2.5

1. Introduction

Loading initial data into a database is a common requirement for many applications. It simplifies setup, ensures consistency, provides default values, and supplies realistic data for development and testing.

Application setup: Pre‑populating data streamlines the initial configuration, allowing the application to start immediately without manual data insertion.

Consistency and integrity: Shared initial data guarantees that all instances of the application start with the same baseline, avoiding inconsistencies.

Default values: Default configuration values give users a ready‑to‑use baseline.

Development data: Developers and testers can work with realistic data sets.

2. Practical examples

2.1 Using CommandLineRunner / ApplicationRunner

<code>@Component
public class PackDataInitializer implements CommandLineRunner {
    private final UserRepository userRepository;
    public PackDataInitializer(UserRepository userRepository) {
        this.userRepository = userRepository;
    }
    @Override
    public void run(String... args) {
        User user1 = new User("Pack", "[email protected]");
        User user2 = new User("Jack", "[email protected]");
        userRepository.saveAll(Arrays.asList(user1, user2));
    }
}
</code>

This component runs automatically during Spring Boot startup to insert the initial users.

2.2 Bean initialization with @PostConstruct or InitializingBean

<code>@Component
public class PackDataLoader {
    private final StringRedisTemplate stringRedisTemplate;
    public PackDataLoader(StringRedisTemplate stringRedisTemplate) {
        this.stringRedisTemplate = stringRedisTemplate;
    }
    @PostConstruct
    public void loadInitialData() {
        ValueOperations<String, String> vo = this.stringRedisTemplate.opsForValue();
        vo.set("a", "...");
        vo.set("b", "...");
        // ...
    }
}
</code>

2.3 SQL scripts

Spring Boot can execute schema.sql and data.sql placed on the classpath.

<code>-- script.sql
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    enabled BOOLEAN NOT NULL
);
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
</code>

<code>-- data.sql
INSERT INTO roles (name) VALUES ('ROLE_USER'), ('ROLE_ADMIN');
INSERT INTO users (username, password, email, enabled) VALUES
('user1','123456','[email protected]',true),
('user2','123456','[email protected]',true),
('admin','admin123','[email protected]',true);
</code>

Configure Spring Boot to run them:

<code>spring:
  sql:
    init:
      mode: always
      schema-locations: classpath:db/script.sql
      data-locations: classpath:db/data.sql
</code>

For non‑embedded databases you may need to set spring.sql.init.platform and name scripts accordingly (e.g., schema-mysql.sql ).

2.4 Test environment with @Sql

<code>@SpringBootTest
@AutoConfigureTestDatabase
@Sql(scripts = {"/data.sql", "/schema.sql"})
public class AppTests {
    @Test
    public void test1() {}
    @Test
    public void test2() {}
}
</code>

The @Sql annotation can also be placed on individual test methods to run specific scripts before that method.

Typically the project uses JPA for basic CRUD operations and MyBatis for more complex queries.