Run Queries From a File in H2 Database

1. Overview

In this tutorial, we’ll explore how to run a script from a file for H2 databases. H2 is an open-source Java database that can be embedded in Java applications or run as a separate server. It’s often used as an in-memory database for testing purposes.

Sometimes, we may need to run a script file to create tables, insert data, or update data in the database before or while running an application.

2. Setup

For our code example, we’ll create a small Spring Boot application. We’ll include an embedded H2 database in the application. Then we’ll try different ways of running a script file that modifies the database.

2.1. Dependencies

Let’s start by adding the spring-boot-starter-data-jpa dependency to our pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

This dependency creates a basic setup for a Spring Boot application with JPA support.

To use H2 in our project, we need to add the H2 database dependency:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

2.2. Properties

Next, we need to configure a connection to the H2 database in our application.properties file:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=none

This configuration sets up the URL, driver class, username, and password for the H2 database. It also sets the database platform and disables the automatic schema generation. Disabling the automatic schema generation is important because we want to run a script file to create the schema later.

3. Running a Script in Spring Boot Application

Now that we’ve set up our project, let’s explore the different ways to run the script file in a Spring Boot application. Sometimes adding data to the database may be required when the application starts. For example, if we want to run a test with predefined data, we can use a script file to insert data into the database.

3.1. Using Default Files

By default, Spring Boot looks for a file named schema.sql in the src/main/resources directory to create the schema. It also looks for a file named data.sql in the same directory and runs its commands when the application starts.

Therefore, let’s create a schema.sql file. This file contains the SQL script to create a table in the H2 database:

CREATE TABLE IF NOT EXISTS employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

Similarly, let’s create a data.sql file that contains the SQL script to insert records into the employee table and update one record:

INSERT INTO employee (name) VALUES ('John');
INSERT INTO employee (name) VALUES ('Jane');
UPDATE EMPLOYEE SET NAME = 'Jane Doe' WHERE ID = 2;

When we run the application, Spring Boot automatically runs these scripts to create a table and insert/update data.

3.2. Files From a Different Directory

We can change the default file location by setting the spring.sql.init.schema-locations and spring.sql.init.data-locations properties in the application.properties file. This is useful when we want to use different files in different environments or if we want to keep our scripts in a different directory.

Let’s move the schema.sql and data.sql files to a different directory – src/main/resources/db and update the properties:

spring.sql.init.data-locations=classpath:db/data.sql
spring.sql.init.schema-locations=classpath:db/schema.sql

Now, when we run the application, Spring Boot looks for the schema.sql and data.sql files in the db directory.

3.3. Using Code

We can also run a script file using code. This is useful when we want to run a script file conditionally or when we want to run a script file based on some logic. For this example, let’s create a script file named script.sql in the src/main/resources directory.

This file contains the SQL script to update and read data from the employee table:

UPDATE employee SET NAME = 'John Doe' WHERE ID = 1;
SELECT * FROM employee;

The H2 driver provides the RunScript.execute() method to help us run a script. Let’s use this method to run the script file in our Spring Boot application.

We’ll create a @PostConstruct method in the main class to run the script file:

@PostConstruct
public void init() throws SQLException, IOException {
    Connection connection = DriverManager.getConnection(url, user, password);
    ResultSet rs = RunScript.execute(connection, new FileReader(new ClassPathResource("db/script.sql").getFile()));
    log.info("Reading Data from the employee table");
    while (rs.next()) {
        log.info("ID: {}, Name: {}", rs.getInt("id"), rs.getString("name"));
    }
}

The @PostConstruct annotation tells the application to run the init() method after the application context is initialized. In this method, we create a connection to the H2 database and run the script.sql file using the RunScript.execute() method.

When we run the application, Spring Boot runs the script to update and read data from the employee table. We see the output of the script in the console:

Data from the employee table:
John Doe
Jane Doe

4. Running a Script Through the Command Line

Another option is to run a script file through the command line. This is useful when we want to run a script on a live database. We can use the RunScript tool provided by the H2 database to run a script file. This tool is available in the H2 jar file.

To use this tool, we need to run the following command:

java -cp /path/to/h2/jar/h2-version.jar org.h2.tools.RunScript -url jdbc:h2:db/server/url -user sa -password password -script script.sql -showResults

Here we provide the path to the H2 jar file in the classpath(-cp) option, the database URL, user, and password. Finally, we provide the script.sql file that we want to run. The -showResults option is required if we want to display the results of running the script.

It’s important to note that in-memory databases cannot be shared between different processes. If we use an in-memory database URL here, it creates a new database for the RunScript tool instead of using the database created by the Spring Boot application.

5. Conclusion

In this article, we explored how to run a script file for H2 databases. We learned how to run a script file in a Spring Boot application using default resource files, files at custom locations, and through code. We also learned how to run a script file through the command line using the RunScript tool provided by the H2 database.