How To Use the H2 Database With Spring Boot

In this tutorial, we’ll review an example application that is written in the Groovy programming language and demonstrate how to use the H2 relational database (H2 DB/H2) with Spring Boot.

The benefit of using the Groovy Programming Language, in this case, is that it allows an example to ship exactly one file which contains everything we need to run the application.

The H2 Database Engine is a powerful open-source relational database that is written in the Java programming language and is used with some frequency in software development projects — especially when it comes to testing applications.

Using H2 with the Spring Framework and with Spring Boot, in particular, is the use case that we’ll demonstrate here.

In the next section, we’ll take a look at the example and dissect, in detail, what happens in each step.

H2 With Spring Boot Example on GitHub

Included here is a link to the GitHub gist pertaining to the example used to demonstrate connecting the H2 Relational Database with Spring Boot.

I’ve also added the full example later in this article, which you should be able to paste into the groovyConsole and run as-is.

Maven Dependencies

The following dependencies are used in this example:

1. Spring Boot
2. Spring Boot AutoConfigure
3. Spring JDBC
4. H2 Database Engine
5. Javax Annotation API
6. SLF4J Simple Provider

The Groovy Grape dependency management system should find these dependencies automatically when the script is executed however for reference purposes I’ve included these here.

An Example Pertaining To Using the H2 Database Engine With Spring Boot

In this section, we’ll take a look at the script in closer detail and go over what’s happening in each step.

Prerequisites

In order to run this example, you will need the following:

• Java version 22.0.1 (required) — see OpenJDK on java.net and Oracle Java SE Development Kit 22.0.1 (JDK 22.0.1)
• Groovy 4.0.17 (required)
• groovyConsole (optional)

This script can be executed using Groovy alone; hence, the groovyConsole is optional.

The script uses the Groovy Adaptable Packaging Engine (Groovy Grape) to pull in dependencies from Maven Central hence a connection to the Internet is required as well.

I’ve included an example of what the output should look like when running this script from the command line here.

H2 DB with Spring Boot Successful CRUD Example Output

The red arrow points to the command used to run the script, and orange arrow points to the log statement that indicates the script is starting, and the blue arrow points to the log statement that indicates that the script has finished running.

In this example, the script runs a Spring Boot application that creates a table in the H2 DB, executes several CRUD (create, read, update, delete) operations on that table, and then drops the table.

The Groovy script runs to completion successfully and then exits.

Step 1: Declare a Package

When we define the Spring Boot Application, we’ll include the scanBasePackages setting, which requires a package name so we set that here.

Step 2: Add the Groovy Grape GrabConfig Annotation

In step two we need to add the Groovy Grape GrabConfig annotation and also set the systemClassLoader property to true — if we do not have this an exception will be thrown when the script is executed.

Step 3: Grab Dependencies and Import Required Classes

In step three we need to grab the dependencies necessary to run this example as well as import required classes — this includes the Spring Boot, H2 Database, and other supporting classes.

Note that we’re using the Hikari database driver in this example.

See the section in this article for complete details.

Step 4: Obtain a Reference to an SLF4J Logger

We’re using the SLF4J log delegation framework in this example and we’ll send messages to console output so we can watch what’s happening as the script executes.

The HikariCP dependency is one other framework that we’re using that also uses SLF4J and we’ve included this high-performance connection pooling implementation in this example.

Step 5: Configure H2 Database Datasource and JdbcTemplate Beans

In the fifth step, we’ll configure the H2 Database datasource which utilizes the HikariCP high-performance connection pool dependency as the datasource type.

Since this example demonstrates some simple CRUD operations executed against the H2 Database from a Spring Boot application, we’ll also configure an instance of JdbcTemplate here which uses this data source.

Note that we’re assigning the HikariDataSource class as the datasource type.

The H2 DB instance configured in this example will reside in memory only — if we want to persist this information to disk, then we need to change the URL.

Step 6: Create a Repository Class

In this step, we implement a repository that contains the CRUD operations that we can execute on the H2 Database instance via the JdbcTemplate, which is auto-wired in this example by Spring Boot.

Step 7: Implement a Service Bean

In this step, we implement a transactional service bean that has stop-and-start lifecycle methods along with convenience methods that delegate to the repository bean.

The start method creates the example table in H2 when Spring Boot initializes the beans that the container is managing, and the stop method drops the example table before the container stops.

Other methods defined in the ExampleService deliver convenience and hide implementation details.

Using a service aids in reuse and is also helpful when testing our code.

As the image has been truncated, refer to the full example below or see the GitHub Gist.

Step 8: Implement the Spring Boot CommandLineRunner Interface

In this step, we implement the Spring Boot CommandLineRunner specification.

Our implementation includes executing CRUD operations via the service created in step seven against the H2 Database.

We log some information along the way so we can see what happens as each CRUD operation completes.

Step 9: Configure Spring Boot Application for Component Scanning

The code in the snippet defines a Spring Boot application and specifies the base package for component scanning.

Step Ten: Configure and Then Run the Spring Boot Application

The code in this snippet configures and then runs the Spring Boot application with the following configuration:

1. Initialize SpringApplicationBuilder: Creates a builder for the Spring Boot application using H2SpringBootExampleApplication
2. Set profiles and web application type: Configures the application to use the default profile and disables the web environment (this is not a web application so we don’t need this)
3. Set parent context: Specifies the BeanConfiguration, ExampleRepository, ExampleService, and ExampleCommandLineRunner classes as components in the parent context
4. Run the application: Execute the application with the provided arguments
5. Close the context: Closes the application context — this step ensures that the stop lifecycle method in the service (see step six) is called before the Spring Boot example application has exited resulting in the names table in the H2 DB being dropped.

Finally, the script logs a completion message and then exits.

The next section includes the complete Spring Boot with the H2 Database example script.

Spring Boot With The H2 Database Engine Complete Example

Here, I’ve included a copy of the entire script, which you should be able to run either using the groovyConsole or via the command line with the groovy shell.

/*
 * Precondition:
 *
 * - Java version "22.0.1" 2024-04-16
 * - Groovy version 4.0.17
 */
package com.thospfuller.examples.h2.database.spring.boot

@GrabConfig(systemClassLoader=true)

@Grab(group='org.springframework.boot', module='spring-boot', version='3.3.0')
@Grab(group='org.springframework.boot', module='spring-boot-autoconfigure', version='3.3.0')
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.builder.SpringApplicationBuilder
import org.springframework.context.annotation.Configuration
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Repository
import org.springframework.stereotype.Component
import org.springframework.boot.jdbc.DataSourceBuilder

@Grab(group='org.springframework', module='spring-jdbc', version='6.1.8')
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter
import org.springframework.transaction.annotation.Transactional
import org.springframework.context.annotation.Bean
import org.springframework.boot.CommandLineRunner
import org.springframework.boot.WebApplicationType
import org.springframework.stereotype.Service

@Grab(group='com.h2database', module='h2', version='2.2.224')
@Grab(group='com.zaxxer', module='HikariCP', version='5.1.0')
import com.zaxxer.hikari.HikariDataSource
import javax.sql.DataSource
import java.sql.PreparedStatement

@Grab(group='javax.annotation', module='javax.annotation-api', version='1.3.2')
import javax.annotation.PostConstruct
import javax.annotation.PreDestroy

@Grab(group='org.slf4j', module='slf4j-simple', version='2.0.9')
import org.slf4j.LoggerFactory

def log = LoggerFactory.getLogger(this.class)

log.info "H2 Database with Spring Boot example begins; args: $args"

@Configuration
class BeanConfiguration {

  @Bean
  DataSource getDataSource () {

    return DataSourceBuilder
      .create()
      .driverClassName("org.h2.Driver")
      .type(HikariDataSource)
      .url("jdbc:h2:mem:example-db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE")
      .username("sa")
      .password("sa")
    .build()
  }

  @Bean  
  JdbcTemplate getJdbcTemplate (DataSource dataSource) {
    return new JdbcTemplate (dataSource)
  }
}

@Repository
class ExampleRepository {

  private static final def log = LoggerFactory.getLogger(ExampleRepository)

  static final def TABLE_NAME = "NAMES"

  @Autowired
  private JdbcTemplate jdbcTemplate

  void createExampleTable () {

    log.info "createExampleTable: method begins."

    jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(255));")

    log.info "createExampleTable: method ends."
  }

  def deleteExampleTable () {

    log.info "deleteExampleTable: method begins."

    jdbcTemplate.execute("DROP TABLE IF EXISTS ${TABLE_NAME};")

    log.info "deleteExampleTable: method ends."
  }

  def addNames (String... names) {
    return addNames (names as List<String>)
  }

  def addNames (List<String> nameList) {

    return jdbcTemplate.batchUpdate(
      "INSERT INTO ${TABLE_NAME} (name) VALUES (?);",
      nameList,
      nameList.size (),
      { PreparedStatement preparedStatement, String name ->
        preparedStatement.setString(1, name)
      } as ParameterizedPreparedStatementSetter<String>
    )
  }

  def updateName (int id, String newName) {

    return jdbcTemplate.update(
      "UPDATE ${TABLE_NAME} SET NAME = ? WHERE ID = ?;",
      newName,
      id
    )
  }

  def deleteName (int id) {

    return jdbcTemplate.update(
      "DELETE FROM ${TABLE_NAME} WHERE ID = ?;",
      id
    )
  }

  def readNames () {
    return jdbcTemplate.queryForList ("select name from ${TABLE_NAME}")
  }
}

@Service
@Transactional
class ExampleService {

  private static final def log = LoggerFactory.getLogger(ExampleService)

  @Autowired
  def exampleRepository

  @PostConstruct
  def start () {

    log.info "start: method begins."

    exampleRepository.createExampleTable ()

    log.info "start: method ends."
  }

  @PreDestroy
  def stop () {

    log.info "stop: method begins."

    exampleRepository.deleteExampleTable ()

    log.info "stop: method ends."
  }

  def addNames (String... nameList) {
    return exampleRepository.addNames (nameList)
  }

  def updateName (int id, String newName) {
    return exampleRepository.updateName (id, newName)
  }

  def deleteName (int id) {
    return exampleRepository.deleteName (id)
  }

  def readNames () {
    return exampleRepository.readNames ()
  }
}

@Component
class ExampleCommandLineRunner implements CommandLineRunner {

  private static final def log = LoggerFactory.getLogger(H2SpringBootExampleApplication)

  @Autowired
  private def exampleService

  @Override
  public void run (String... args) {

    log.info "run: method begins; args: $args"

    def namesAdded = exampleService.addNames ("aaa", "bbb", "ccc", "ddd")

    log.info "namesAdded: $namesAdded"

    def updateResult = exampleService.updateName (2, "ZZZ")

    def names = exampleService.readNames ()

    log.info "updateResult: $updateResult, names after update: $names"

    def deletedNames = exampleService.deleteName (2)

    names = exampleService.readNames ()

    log.info "deletedNames: $deletedNames, names after deletion: $names"

    log.info "run: method ends."
  }
}

@SpringBootApplication(scanBasePackages = ["com.thospfuller.examples.h2.database.spring.boot"])
class H2SpringBootExampleApplication {}

def springApplicationBuilder = new SpringApplicationBuilder(H2SpringBootExampleApplication)

def context = springApplicationBuilder
  .profiles("default")
  .web(WebApplicationType.NONE)
  .parent (
    BeanConfiguration,
    ExampleRepository,
    ExampleService,
    ExampleCommandLineRunner
  )
  .run(args)

context.close ()

log.info "...done!"

return

Conclusion

I hope that this tutorial has provided adequate guidance as well as a useful example regarding how to use the H2 Database with Spring Boot.

If you encounter any problems or have questions about this tutorial, please leave a comment and I’ll try to help.