Spring Boot H2 Database

What is the in-memory database

In-memory database relies on system memory as oppose to disk space for storage of data. Because memory access is faster than disk access. We use the in-memory database when we do not need to persist the data. The in-memory database is an embedded database. The in-memory databases are volatile, by default, and all stored data loss when we restart the application.

The widely used in-memory databases are H2, HSQLDB (HyperSQL Database), and Apache Derby. It creates the configuration automatically.

Persistence vs. In-memory Database

The persistent database persists the data in physical memory. The data will be available even if the database server is bounced. Some popular persistence databases are Oracle, MySQL, Postgres, etc.

In the case of the in-memory database, data store in the system memory. It lost the data when the program is closed. It is helpful for POCs (Proof of Concepts), not for a production application. The widely used in-memory database is H2.

What is the H2 Database

H2 is an embedded, open-source, and in-memory database. It is a relational database management system written in Java. It is a client/server application. It is generally used in unit testing. It stores data in memory, not persist the data on disk.

Advantages

Zero configuration
It is easy to use.
It is lightweight and fast.
It provides simple Configuration to switch between a real database and in-memory database.
It supports standard SQL and JDBC API.
It provides a web console to maintain in the database.

Configure H2 Database

If we want to use H2 database in an application we need to add the following dependency in pom.xml file:

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

After adding the dependency, we need to configure data source URL, driver class name, username, and password of H2 database. Spring Boot provide an easy way to configure these properties in application.properties file.

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

In the spring.datasource.url property, mem is the name of an in-memory database and testdb is the name of schema that H2 provides, by default. We can also define our own schema and database. The default username is sa and the blank password denotes an empty password. If we want to change the username and password, we can override these values.

Persist the data in H2 Database

If we want to persist the data in the H2 database, we should store data in a file. To achieve the same, we need to change the datasource URL property.

#persist the data
spring.datasource.url=jdbc:h2:file:/data/sampledata
spring.datasource.url=jdbc:h2:C:/data/sampledata

In the above property, the sampledata is a file name.

Create Schema and Populate Data

We can define schema by creating a SQL file in the resource folder (src/main/resource).

schema.sql

DROP TABLE IF EXISTS CITY;
CREATE TABLE CITY (
City_code INT AUTO_INCREMENT  PRIMARY KEY,
city_name VARCHAR(50) NOT NULL,
city_pincode INT(8) NOT NULL
);

We can populate data in the table by creating a SQL file in the resource folder (src/main/resource).

data.sql

INSERT INTO CITY VALUES (11, 'Delhi', 110001);  
INSERT INTO CITY VALUES (12, 'Kanpur', 208001);  
INSERT INTO CITY VALUES (13, 'Lucknow', 226001);  

Spring Boot automatically picks up the data.sql file and run it against the H2 database during the application startup.

H2 Console

By default, the console view of the H2 database is disabled. Before accessing the H2 database, we must enable it by using the following property.

#enabling the H2 console
spring.h2.console.enabled=true

Once we have enabled the H2 console, now we can access the H2 console in the browser by invoking the URL http://localhost:8080/h2-console. The following figure shows the console view of the H2 database.

In the above screenshot, we have defined our own database named javatpoint.

Spring Boot H2 Example

Let's set up a Spring Boot application with the H2 database.

Step 1: Open Spring Initializr http://start.spring.io.

Step 2: Select the Spring Boot version 2.3.0.M1.

Step 2: Provide the Group name. We have provided com.javatpoint.

Step 3: Provide the Artifact Id. We have provided spring-boot-h2-database-example.

Step 5: Add the dependencies Spring Web, Spring Data JPA, and H2 Database.

Step 6: Click on the Generate button. When we click on the Generate button, it wraps the project in a Jar file and downloads it to the local system.

Step 7: Extract the Jar file and paste it into the STS workspace.

Step 8: Import the project folder into STS.

File -> Import -> Existing Maven Projects -> Browse -> Select the folder spring-boot-h2-database-example -> Finish

It takes some time to import.

Step 9: Create a package with the name com.javatpoint.model in the folder src/main/java.

Step 10: Create a model class in the package com.javatpoint.model. We have created model class with the name Student. In the Books class, we have done the following:

Define four variable id, age, name, and
Generate Getters and Setters.
Right-click on the file -> Source -> Generate Getters and Setters.
Mark the class as Entity by using the annotation @Entity.
Mark the class as Table name by using the annotation @Table.
Define each variable as Column by using the annotation @Column.

Student.java

package com.javatpoint.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
//mark class as an Entity 
@Entity
//defining class name as Table name
@Table
public class Student 
{
//mark id as primary key
@Id
//defining id as column name
@Column
private int id;
//defining name as column name
@Column
private String name;
//defining age as column name
@Column
private int age;
//defining email as column name
@Column
private String email;
public int getId() 
{
return id;
}
public void setId(int id) 
{
this.id = id;
}
public String getName() 
{
return name;
}
public void setName(String name) 
{
this.name = name;
}
public int getAge() 
{
return age;
}
public void setAge(int age) 
{
this.age = age;
}
public String getEmail() 
{
return email;
}
public void setEmail(String email) 
{
this.email = email;
}
}

Step 11: Create a package with the name com.javatpoint.controller in the folder src/main/java.

Step 12: Create a Controller class in the package com.javatpoint.controller. We have created controller class with the name StudentController. In the StudentController class, we have done the following:

Mark the class as RestController by using the annotation @RestController.
Autowire the StudentService class by using the annotation @Autowired.
Define the following methods:
- getAllStudent(): It returns a List of all Students.
- getStudent(): It returns a student detail that we have specified in the path variable. We have passed id as an argument by using the annotation @PathVariable. The annotation indicates that a method parameter should be bound to a URI template variable.
- deleteStudent(): It deletes a specific student that we have specified in the path variable.
- saveStudent(): It saves the student detail. The annotation @RequestBody indicates that a method parameter should be bound to the body of the web request.

StudentController.java

package com.javatpoint.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.javatpoint.model.Student;
import com.javatpoint.service.StudentService;
//creating RestController
@RestController
public class StudentController 
{
//autowired the StudentService class
@Autowired
StudentService studentService;
//creating a get mapping that retrieves all the students detail from the database 
@GetMapping("/student")
private List<Student> getAllStudent() 
{
return studentService.getAllStudent();
}
//creating a get mapping that retrieves the detail of a specific student
@GetMapping("/student/{id}")
private Student getStudent(@PathVariable("id") int id) 
{
return studentService.getStudentById(id);
}
//creating a delete mapping that deletes a specific student
@DeleteMapping("/student/{id}")
private void deleteStudent(@PathVariable("id") int id) 
{
studentService.delete(id);
}
//creating post mapping that post the student detail in the database
@PostMapping("/student")
private int saveStudent(@RequestBody Student student) 
{
studentService.saveOrUpdate(student);
return student.getId();
}
}

Step 13: Create a package with the name com.javatpoint.service in the folder src/main/java.

Step 14: Create a Service class. We have created a service class with the name StudentService in the package com.javatpoint.service.

StudentService.java

package com.javatpoint.service;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.javatpoint.model.Student;
import com.javatpoint.repository.StudentRepository;
@Service
public class StudentService 
{
@Autowired
StudentRepository studentRepository;
//getting all student records
public List<Student> getAllStudent() 
{
List<Student> students = new ArrayList<Student>();
studentRepository.findAll().forEach(student -> students.add(student));
return students;
}
//getting a specific record
public Student getStudentById(int id) 
{
return studentRepository.findById(id).get();
}
public void saveOrUpdate(Student student) 
{
studentRepository.save(student);
}
//deleting a specific record
public void delete(int id) 
{
studentRepository.deleteById(id);
}
}

Step 15: Create a package with the name com.javatpoint.repository in the folder src/main/java.

Step 16: Create a Repository interface. We have created a repository interface with the name StudentRepository in the package com.javatpoint.repository. It extends the Crud Repository interface.

StudentRepository.java

package com.javatpoint.repository;
import org.springframework.data.repository.CrudRepository;
import com.javatpoint.model.Student;
public interface StudentRepository extends CrudRepository<Student, Integer>
{
}

Now we will configure the datasource URL, driver class name, username, and password, in the application.properties file.

Step 17: Open the application.properties file and configure the following properties.

application.properties

spring.datasource.url=jdbc:h2:mem:javatpoint
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
#enabling the H2 console
spring.h2.console.enabled=true

Note: Do not forget to enable the H2 console.

After creating all the classes and packages, the project directory looks like the following.

Now we will run the application.

Step 18: Open SpringBootH2DatabaseExampleApplication.java file and run it as Java Application.

SpringBootH2DatabaseExampleApplication.java

package com.javatpoint;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringBootH2DatabaseExampleApplication 
{
public static void main(String[] args) 
{
SpringApplication.run(SpringBootH2DatabaseExampleApplication.class, args);
}
}

In the next step, we will use rest client Postman for sending the POST and GET request. If the Postman is not installed in your system, follow the steps below:

Download the Postman from https://www.getpostman.com/downloads/ or add Google Chrome extension in the browser https://bit.ly/1HCOCwF.
Launch the Postman and Signup. Create a user name. We have created user with the name javatpointand clicked on Submit

Step 19: Open the Postman and do the following:

Select the POST
Invoke the URL http://localhost:8080/student.
Select the Body
Select he Content-Type JSON (application/json).
Insert the data. We have inserted the following data in the body:

{
    "id": "001",
    "age": "23",
    "name": "Amit",
    "email": "amit@yahoo.co.in"
} 

Click on the Send

When the request is successfully executed, it shows the Status:200 OK. It means the record has been successfully inserted in the database.

Similarly, we have inserted the following data.

{
    "id": "002",
    "age": "24",
    "name": "Vadik",
    "email": "vadik@yahoo.co.in"
} 
{
    "id": "003",
    "age": "21",
    "name": "Prateek",
    "email": "prateek@yahoo.co.in"
} 
{
    "id": "004",
    "age": "25",
    "name": "Harsh",
    "email": "harsh@yahoo.co.in"
} 
{
    "id": "005",
    "age": "24",
    "name": "Swarit",
    "email": "Swarit@yahoo.co.in"
} 

Let's access the H2 console to see the data.

Step 20: Open the browser and invoke the URL http://localhost:8080/h2-console. Click on the Connect button, as shown below.

After clicking on the Connect button, we see the Student table in the database, as shown below.

Step 21: Click on the Student table and then click on the Run button. The table shows the data that we have inserted in the body.

Step 22: Open the Postman and send a GET request. It returns the data that we have inserted in the database.

Let's send a GET request with the URL http://localhost:8080/student/{id}. We have invoked the URL http://localhost:8080/student/3. It returns the detail of the student whose id is 3.

Similarly, we can also send a DELETE request. Suppose we want to delete a student record whose id is 2.

To delete a student record, send a DELETE request with the URL http://localhost:8080/student/2. We see that the student whose id is 2 has been deleted from the database.