Setting up the H2 Database

In this lesson, we will set up the in-memory H2 database. Let’s have a look the required steps:

1. Configuring database connection

• The in-memory database H2 has automatically been configured in our application. The URL can be found from the console log. This value is randomly generated each time the server is restarted. To make the database URL a constant, we need to configure this in application.properties as follows:

  spring.datasource.url=jdbc:h2:mem:testdb
  

• The next task is connecting to the H2 database. One of the reasons for using Spring Boot is that its autoconfiguration feature looks at the H2 dependency and automatically configures a connection to the H2 database. The H2 console can be enabled in the application.properties file as follows:

  spring.h2.console.enabled=true
  

• The database can be viewed in the web browser by typing localhost:8080/h2-console or http://127.0.0.1:8080/h2-console. In the login page that shows up, make sure that the JDBC URL is the same as the one that we provided in the applications.properties file (jdbc:h2:mem:testdb). If not, change it to jdbc:h2:mem:testdb and click “Connect” to go to the database console.

  This will open up the interface of the database.

2. Creating a table

• Now that we have connected to our database, it is time to create a table. We can do that in the H2 web console by using a CREATE TABLE query, but since this is an in-memory database, all changes will be lost when the application is terminated. A better way is to define the query in a SQL file.

  For this purpose, create an SQL file in src/main/resources called schema.sql. This file will be called whenever the application is launched and will initialize the database. It contains all the DDL (Data Definition Language) queries. We will create a table called Player in this file: