Tutorial: Learn How to Use the h2 Database With Spring Boot! 🤔

by Thomas P. FullerAugust 20th, 2024

Too Long; Didn't Read

In this instructional we’ll review an example application which is written in the Groovy Programming Language and which demonstrates how to use the H2 relational database (H2 DB / H2) with Spring Boot.

featured image - Tutorial: Learn How to Use the h2 Database With Spring Boot! 🤔

In this instructional we’ll review an example application which is written in the Groovy Programming Language (Groovy) and which demonstrates how to use the H2 relational database (H2 DB / H2) with Spring Boot.

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

The H2 Database Engine is a powerful open-source relational database which is written in the Java Programming Language (Java) and which 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 a common use case, one which 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 -- you should be able to paste this script into the groovyConsole and run as-is.

Spring Boot With H2 DB Example Maven Dependencies

The following dependencies are used in this example:

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.

Preconditions

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

Java version 22.0.1 (required)
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.

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 One: 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.

package com.thospfuller.examples.h2.database.spring.boot

Step Two: 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.

@GrabConfig(systemClassLoader=true)

Step Three: 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.

@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

See the Maven Dependencies section in this article for complete details.

Step Four: 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.

def log = LoggerFactory.getLogger(this.class)

Step Five: 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.

@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)
  }
}

Step Six: 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 autowired in this example by Spring Boot.

@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}")
  }
}

Step Seven: Implement a service bean.

In this step we implement a transactional service bean which 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.

@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 ()
  }
}

Step Eight: 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.

@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."
  }
}

Step Nine: 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.

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

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:

Initialize SpringApplicationBuilder: Creates a builder for the Spring Boot application using H2SpringBootExampleApplication.
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).
Set Parent Context: Specifies the BeanConfiguration, ExampleRepository, ExampleService, and ExampleCommandLineRunner classes as components in the parent context.
Run the Application: Execute the application with the provided arguments.
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.

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

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