Spring Boot with H2 Database

Learn to configure H2 database with Spring boot. H2 database is an in-memory database and is generally used for unit testing or POC purposes.

Remember that an in-memory database is created/initialized when an application starts up; and destroyed when the application shuts down.

1. What is H2 Database?

H2 is one of the popular in-memory databases written in Java. H2 can be embedded in Java applications or run in the client-server mode.

Spring Boot provides excellent integration support for H2 using simple properties configuration.

To make itself even more helpful, H2 also provides a console view to maintain and interact with the database tables and data using the GUI interface.

2. Maven Dependency

To use H2 in the Spring boot application, we need to add the H2 runtime jar into dependencies. The best way to add is through maven.

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

3. H2 Configuration Options

3.1. In-Memory Database Configuration

Spring provides effortless configuration options to connect to any database using properties. Below are the configuration properties, we shall have in application.properties file for the most straightforward H2 configuration.

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

Please note by default, spring boot configures the in-memory database connection with the username 'sa' and an empty password. If you wish to change the username and password, override them in the above properties options.

3.2. Persistent Database Configuration

By default, the in-memory databases are volatile, and all stored data will be lost when we restart the application. In this case, data is written in temporary memory, and as soon as JVM is stopped, data is flushed.

To have a persistent data store capable of storing data between application start/stop, we should hold the data in files. For this change the spring.datasource.url property.

# temporary data storage
spring.datasource.url = jdbc:h2:mem:testdb

# temporary data storage
spring.datasource.url = jdbc:h2:file:/data/sample
spring.datasource.url = jdbc:h2:file:C:/data/sample

4. Creating Schema and Inserting Data on Initialization

We may want to initialize the database with some fixed schema (DDL) and insert default data (DML) into tables before the application is ready for business usecases.

We can achieve this by putting SQL files into the resources folder (/src/main/resources/).

  • schema.sql – To initialize the schema ie.create tables and dependencies.
  • data.sql – To insert default data rows.
DROP TABLE IF EXISTS TBL_EMPLOYEES;

CREATE TABLE TBL_EMPLOYEES (
  id INT AUTO_INCREMENT  PRIMARY KEY,
  first_name VARCHAR(250) NOT NULL,
  last_name VARCHAR(250) NOT NULL,
  email VARCHAR(250) DEFAULT NULL
);
INSERT INTO TBL_EMPLOYEES (first_name, last_name, email) VALUES
  ('Lokesh', 'Gupta', 'abc@gmail.com'),
  ('Deja', 'Vu', 'xyz@email.com'),
  ('Caption', 'America', 'cap@marvel.com');

5. H2 Console

5.1. Enabling H2 Console

By default, the console view of the H2 database is disabled. We must enable it to view and access it in the browser. Note that we can customize the URL of the H2 console, which, by default, is '/h2'.

# Enabling H2 Console
spring.h2.console.enabled=true

# Custom H2 Console URL
spring.h2.console.path=/h2

5.2. Accessing H2 Console

Start the spring boot application and access the console in the browser with this URL : http://localhost:8080/h2.

We can see the console like this.

H2 Database Console Login Window
H2 Database Console Login Window

Now enter the configured username and password. We can verify the table structure and default data inserted through SQL files.

H2 Console View
H2 Console View

5.3. Other Configuration Options

Spring boot provides two more properties to customize the behavior of the H2 console further. We can enable/disable the database trace logs, and we can enable/disable the remote access of H2 console.

By default, both properties are false.

# Whether to enable trace output.
spring.h2.console.settings.trace=false 

# Whether to enable remote access.
spring.h2.console.settings.web-allow-others=false

6. Conclusion

In this Spring boot with H2 database tutorial, we learned to configure, initialize and access the H2 database through a spring boot application using simple properties configuration options.

Happy Learning !!

References:

H2 DB Tutorials

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

20 thoughts on “Spring Boot with H2 Database”

    • I am still not able to see it . Please guide if there could be any other problem . Have even added @Entity and @Id at the pojo class.

      Reply
  1. I have the same error. I am confused, do we have to make our own database. What should we put for JDBC url? Please help

    “Database “C:/Users/sethy/test” not found, either pre-create it or allow remote database creation”

    Reply
  2. Hi,

    This solution works very fine but how do we avoid schema from re-creating itself again and again?
    As the data.sql and schema.sql run every time the app started.

    Reply
  3. i am getting this error
    Database “C:/Users/sudhanshu/test” not found, either pre-create it or allow remote database creation (not recommended in secure environments) [90149–200] 90149/90149

    Reply
  4. I am getting this error: The method findById(Long) is undefined for the type EmployeeRepository

    public EmployeeEntity getEmployeeById(Long id) throws RecordNotFoundException
        {
            Optional employee = repository.findById(id);
             
            if(employee.isPresent()) {
                return employee.get();
            } else {
                throw new RecordNotFoundException("No employee record exist for given id");
            }
        }
    Reply
  5. using postman iam unable to insert data…saying status”:500,”error”:”Internal Server Error”,”message”:”The given id must not be null!

    Reply
    • To fix this, change the controller method as below

      public ResponseEntity createOrUpdateEmployee(@RequestBody EmployeeEntity employee) throws RecordNotFoundException

      Reply
  6. Hi,
    I hope this can help you, [EmployeePageController]

    @Controller
    @RequestMapping("/employeePage")
    public class EmployeePageController {
    	...
    	@GetMapping(value = "/list")
    	public String list(Model model) {
    		model.addAttribute("employees", employeeService.getAllEmployees());
    		return "list-employees";
    	}
    	...
    }
    

    Best regards

    Reply

Leave a Comment

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.