️ Spring Boot: Multiple SQL File Initialization

Project Overview

In many enterprise applications, you cannot rely on Hibernate to automatically manage your database schema (ddl-auto). This project demonstrates a custom Database Initialization Service that scans specific folders for SQL scripts and executes them in a controlled order (Schema first, then Data) using the ResourceDatabasePopulator.

---

Technical Concepts

1. Programmatic Initialization

Instead of using the default schema.sql and data.sql (which are limited to one file each), this project uses a custom @Service to handle multiple files.

• ResourceDatabasePopulator: A Spring utility that can take any SQL resource and execute it against a DataSource.
• @PostConstruct: Ensures that the scripts are run exactly once, immediately after the service is initialized but before the application starts accepting requests.

2. Wildcard Resource Loading

Using Spring's @Value with wildcards allows the application to be flexible: @Value("classpath:db/migration/schema/*.sql") This automatically detects and loads every .sql file in the directory, allowing you to split your schema into manageable pieces (e.g., users.sql, products.sql).

3. Turning off Hibernate DDL

To avoid conflicts, we set spring.jpa.hibernate.ddl-auto=none.

• Why?: We want our manual SQL scripts to have "full authority" over the database structure. This prevents Hibernate from trying to change what we have manually defined.

---

Component Reference

DatabaseInitializationService.java

The core logic of the project. It handles:

1. Injection: Receives the DataSource.
2. Discovery: Finds all SQL files in the db/migration path.
3. Execution: Loops through schema scripts first, then data scripts to maintain referential integrity.

resources/db/migration/

The project structure is organized for clarity:

• /schema/: Contains CREATE TABLE statements (e.g., schema1.sql, schema2.sql).
• /data/: Contains INSERT statements for seeding (e.g., data1.sql, data2.sql).

---

The Database Initialization Service is a custom solution used to manage the state of your database schema and initial data manually using SQL scripts. This is particularly useful when you need more control over the order and execution of multiple SQL files than what Spring Boot's default schema.sql provides.

---

Purpose of the Service

The primary goal of this class is to automate the creation of database tables and the insertion of sample data every time the application starts.

• Decoupling: It separates the database structure (schema) from the initial records (data).
• Organization: It allows you to break down massive SQL files into smaller, manageable scripts (e.g., schema1.sql, schema2.sql).
• Automation: It uses the Spring lifecycle to ensure the database is ready before any user requests are handled.

---

How the Components Work Together

1. Resource Loading (@Value)

The service uses Spring's Resource abstraction to "scan" your project.

• classpath:db/migration/schema/*.sql: The asterisk (*) is a wildcard. It tells Spring to find every SQL file inside that specific folder.
• Resource[]: These files are injected as an array of objects, allowing the service to iterate through them.

2. The Connection (DataSource)

The DataSource is the bridge to your database (MySQL, H2, etc.). Spring Boot creates this bean automatically using the settings you provided in application.properties. By using Constructor Injection, you ensure that the service has a valid connection before it tries to run any scripts.

3. Execution Logic (ResourceDatabasePopulator)

This is a utility class provided by Spring JDBC.

• It takes a Resource (your SQL file) and an active DataSource.
• It reads the file, parses the SQL statements (usually looking for ;), and executes them one by one against the database.

4. The Trigger (@PostConstruct)

This is the "magic" part of the service.

• When Spring Boot starts, it creates the DatabaseInitializationService bean.
• Once the bean is fully initialized, the @PostConstruct annotation triggers the initializeDatabase() method.
• This ensures the tables exist and the data is inserted before the rest of the application (like your Controllers or Repositories) starts running.

---

The Execution Flow

Step	Action	Description
1	Scan	Spring finds all files in db/migration/schema and db/migration/data.
2	Inject	The file paths are converted into Resource objects and injected into the class.
3	Startup	The bean is created and the DataSource is connected.
4	Schema Run	The service loops through schemaScripts to build the tables.
5	Data Run	The service loops through dataScripts to populate those tables.

---

Key Technical Details

• Arrays.stream(scripts): This converts your array of files into a stream so it can process each script individually.
• execute(dataSource): This method handles the heavy lifting of opening a database connection, executing the script, and handling the transaction.

---

Execution Flow

1. Spring Context Starts: The DataSource bean is created using the Aiven MySQL credentials.
2. Service Init: DatabaseInitializationService is instantiated.
3. PostConstruct:
   • schema1.sql and schema2.sql are executed (Tables created).
   • data1.sql and data2.sql are executed (Users and Products inserted).
4. API Ready: The SampleController can now fetch the data that was just seeded.

---

How to Test

1. Database Configuration:
   • Update application.properties with your MySQL host, username, and password.
   • Note: The current config is set for a secure Aiven MySQL instance.
2. Run Application: Execute MultipleSqlFileDemoApplication.java.
3. Verify via API:
   • Users: GET http://localhost:8080/api/users
   • Products: GET http://localhost:8080/api/products
4. Verify via Console: Check your MySQL terminal. You will see the users and products tables populated with the data from the .sql files.