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

When developing applications that use databases, it's often useful to initialize the database with certain schema structures or data on startup. Spring provides easy ways to execute SQL scripts during the initialization of a DataSource. The same mechanism can be applied whether you're using Spring JDBC, JPA, or any other database-related Spring module.

In this example, we'll use the Spring JDBC, JPA, and an in-memory database, HSQLDB. Here's how you can initialize your database using SQL scripts:

1. Maven Dependencies:

Add the necessary dependencies for Spring JDBC, JPA, and HSQLDB:

<!-- Spring JDBC -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${spring.version}</version>
</dependency>

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

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>${hibernate.version}</version>
</dependency>

<!-- HSQLDB -->
<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>${hsqldb.version}</version>
</dependency>

2. SQL Scripts:

Create your SQL scripts:

src/main/resources/schema.sql:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

src/main/resources/data.sql:

INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com');
INSERT INTO users (id, name, email) VALUES (2, 'Jane', 'jane@example.com');

3. Spring Configuration:

Assuming you're using Spring Boot, the auto-configuration will pick up these files and run them on startup. If not, you can configure a data source initializer:

@Bean
public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
    DataSourceInitializer initializer = new DataSourceInitializer();
    initializer.setDataSource(dataSource);

    // Populator for schema and data
    ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.addScript(schemaScript);
    populator.addScript(dataScript);
    initializer.setDatabasePopulator(populator);

    return initializer;
}

With this setup, when your application starts, the schema.sql script will create the necessary tables, and the data.sql script will populate them with initial data.

4. Application Properties (for Spring Boot):

In your application.properties or application.yml, add the following to configure HSQLDB and JPA:

spring.datasource.url=jdbc:hsqldb:mem:testdb
spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

# Load schema and data scripts
spring.datasource.initialization-mode=always

The above properties will ensure the embedded HSQLDB database is used, and the scripts schema.sql and data.sql will be picked up and executed on startup.

By following these steps, you can easily integrate SQL scripts with your Spring applications, allowing you to define schema and initial data for your databases. This setup is particularly useful in development environments and for integration testing.