Spring Using SQL Scripts with Spring JDBC + JPA + HSQLDB (original) (raw)

Last Updated : 4 May, 2026

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 `

org.springframework.boot spring-boot-starter-web

<!-- 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>

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:

Book-table

Book table

**2. Car Table:

Car-table

Car table