Spring - Using SQL Scripts with Spring JDBC + JPA + HSQLDB

SQL scripts with Spring JDBC and JPA allows developers to automatically initialize and populate the database during application startup. With HSQLDB, an in-memory database, this setup becomes lightweight and ideal for testing and development. Spring simplifies this process by executing schema and data scripts without requiring manual intervention.

Supports automatic execution of schema.sql and data.sql for database creation and data initialization.
Works seamlessly with both Spring JDBC and JPA for consistent database access.
HSQLDB provides a fast, in-memory database environment, making it suitable for testing and rapid development.

Step-by-Step Implementation

This section explains how to configure SQL scripts and integrate Spring JDBC, JPA, and HSQLDB to automatically create and populate the database during application startup.

Step 1: Add Dependencies

Add Spring Boot starters for web and JPA.
Include HSQLDB for in-memory database support.
Optionally add Flyway for versioned database migrations.

XML

<dependencies>
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- HSQLDB -->
    <dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Flyway (Optional) -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
</dependencies>

Step 2: Configure Application Properties

Configure in-memory HSQLDB datasource.
Enable SQL script initialization.
Disable Hibernate auto DDL when using SQL scripts.

# HSQLDB configuration
spring.datasource.url=jdbc:hsqldb:mem:testdb
spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver
spring.datasource.username=sa
spring.datasource.password=
# Enable SQL initialization (Spring Boot 2.5+)
spring.sql.init.mode=always
# JPA settings
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
# Flyway (optional)
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration

Step 3: Create SQL Scripts

schema.sql creates tables.
data.sql inserts initial records.

schema.sql

// -- schema.sql
CREATE TABLE car (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255),
price INT
);

CREATE TABLE book (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(255), price INT );
-- Creating book table
CREATE TABLE book (
id INT IDENTITY PRIMARY KEY,
name VARCHAR(255),
price INT
);

data.sql

-- Insert data into car table
INSERT INTO car (id, name, price) VALUES (DEFAULT, 'Audi', 3000000);
INSERT INTO car (id, name, price) VALUES (DEFAULT, 'BMW', 4000000);
INSERT INTO car (id, name, price) VALUES (DEFAULT, 'Jaguar', 3500000);
-- Insert data into book table
INSERT INTO book (id, name, price) VALUES (DEFAULT, 'Book-1', 600);
INSERT INTO book (id, name, price) VALUES (DEFAULT, 'Book-2', 500);
INSERT INTO book (id, name, price) VALUES (DEFAULT, 'Book-3', 800);

Step 4: Enable Flyway Migration

Place scripts inside src/main/resources/db/migration.
Use versioned naming (V1__, V2__).

V1_create_schema.sql

-- V1__create_schema.sql
CREATE TABLE car (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255),
price INT
);

CREATE TABLE book (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255),
price INT
);

V2_insert_data.sql

-- V2__insert_data.sql
INSERT INTO car (name, price) VALUES ('Audi', 3000000);
INSERT INTO car (name, price) VALUES ('BMW', 4000000);
INSERT INTO car (name, price) VALUES ('Jaguar', 3500000);

INSERT INTO book (name, price) VALUES ('Book-1', 600);
INSERT INTO book (name, price) VALUES ('Book-2', 500);
INSERT INTO book (name, price) VALUES ('Book-3', 800);

Step 5: Run the Application

On startup, Spring Boot executes the schema.sql and data.sql scripts automatically.
If Flyway is enabled, it will run the migration scripts in version order.
The database tables car and book will be created and populated with initial data.

Output:

1. Book Table:

2. Car Table: