Part 3 - Spring Boot with H2 Database

Table of Contents

Spring Boot with H2 Database

What have we learned so far,

Part 1 – Spring Rest Advance : Input / Bean Validations
In this post, we discussed the steps to validate the input bean in Spring Rest.

Part 2 – Spring Rest Advance : Rest Documentation with Swagger 2
In this post, we discussed the steps to configure swagger to do the documentation for Spring Rest services

What is an in memory database and its need?

An in memory database is created when our Application starts and it gets destroyed when an application is stopped.

You will need to install the normal databases to use it in your application.

Lets consider below situations

1. Suppose you want to do a quick poc on database side so for this requirement you will need to install the database on your system which is time consuming

2. Consider writing your integration testing’s, where you might need to insert or retrieve the records from the database and for this use case we will not be able to run it on production site or on our local database as it may change the state of the database.

To overcome this problem we have H2 in memory database.

Advantages of H2 Database

– No setup is required on your infrastructure
– Easy to use and setup
– Zero configuration
– Spring boot provides very simple configuration to switch the database from H2 to another database.

Lets see how to configure the H2 Database in Spring Boot
Step 1 – Add dependency in pom.xml

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

Step 2 – Add datasource properties
Add below properties datasource related properties in application.properties file as below

spring.h2.console.enabled=true
spring.h2.console.path=/h2
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop

spring.datasource.url=jdbc:h2:~/test
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Step 3 – Start Spring boot App
When you start the application, you can launch up H2 Console at http://localhost:8080/h2 can be seen as below

When we connect to H2 database, we can see that Employee table is already created with some sample data in it. How does this happened ?

Creating schema and adding sample data on application startup in H2 DB with Spring Boot
Spring Boot gives the support to H2 DB to create schema or add the sample data into table on application startup.
If we put schema.sql and data.sql into resources folder then Spring Boot will automatically create the schema from schema.sql file and perform the DML(insert/update data) into tables.

/src/main/resources/schema.sql
/src/main/resources/data.sql
data.sql file :

INSERT INTO employee_data VALUES (1, 'saurabh', 'oza', 1000);
INSERT INTO employee_data VALUES (2, 'only', 'fullstack', 2000);
INSERT INTO employee_data VALUES (3, 'abc', 'xyz', 1000);

Here I am not creating the tables from schema.sql file but I am using the @Enity annotation on Employee class to create the table for us. For this auto creation we need to add below property into application.properties

spring.jpa.hibernate.ddl-auto=create-drop

EmployeeDo.java – Model

package com.onlyfullstack.springrestadvance.domainobject;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "employee_data")
@SequenceGenerator(name="employee_id_seq", initialValue=5, allocationSize=100)
public class EmployeeDO {

 @Id
 @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="employee_id_seq")
 private Long id;
 
 @Column(name="first_name")
 private String firstName;

 @Column(name="last_name")
 private String lastName;
 
 @Column(name="salary")
 private Long salary;

 @Override
 public String toString() {
  return "EmployeeDO [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", salary=" + salary
    + "]";
 }

 public Long getId() {
  return id;
 }

 public void setId(Long id) {
  this.id = id;
 }

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 public Long getSalary() {
  return salary;
 }

 public void setSalary(Long salary) {
  this.salary = salary;
 }
 
}

EmployeeController – Controller

package com.onlyfullstack.springrestadvance.controller;

import java.util.List;
import java.util.stream.Collectors;

import javax.validation.Valid;

import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
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.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.onlyfullstack.springrestadvance.datatransferobject.EmployeeDTO;
import com.onlyfullstack.springrestadvance.domainobject.EmployeeDO;
import com.onlyfullstack.springrestadvance.exception.EntityNotFoundException;
import com.onlyfullstack.springrestadvance.service.EmployeeService;

import io.swagger.annotations.Api;

@RestController
@RequestMapping("employees")
@Api(value="employees", description="This resource handles the employee related operations")
public class EmployeeController {
 
 @Autowired
 private EmployeeService service;
 
 @Autowired
 private ModelMapper mapper;

  @GetMapping
   public List<EmployeeDTO> getAllEmployees() {
   List<EmployeeDO> employeeDOList = service.getAllEmployees();
   List<EmployeeDTO> employeeDTOList = employeeDOList.stream()
     .map(employee -> mapper.map(employee, EmployeeDTO.class))
     .collect(Collectors.toList());
   
   return employeeDTOList;
   }
  
  @GetMapping(value="{employeeId}")
   public EmployeeDO getEmployee(@PathVariable(name="employeeId") Long empId ) throws EntityNotFoundException {
    return service.getEmployee(empId);
   }
  
  @PostMapping
  public ResponseEntity<Object> addEmployee(@Valid @RequestBody EmployeeDTO employeeDTO) {
   System.out.println(employeeDTO);
   EmployeeDO employeeDO = mapper.map(employeeDTO, EmployeeDO.class);
   service.addEmployee(employeeDO);
   return new ResponseEntity<>(HttpStatus.CREATED);
  }
  
    @DeleteMapping
    public void deleteEmployee(@PathVariable Long employeeId) throws EntityNotFoundException
    {
     service.deleteEmployee(employeeId);
    }
}

EmployeeService – Service layer

package com.onlyfullstack.springrestadvance.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.onlyfullstack.springrestadvance.domainobject.EmployeeDO;
import com.onlyfullstack.springrestadvance.exception.EntityNotFoundException;
import com.onlyfullstack.springrestadvance.repositoy.EmployeeRepository;

@Service
public class EmployeeServiceImpl implements EmployeeService{

 @Autowired
 private EmployeeRepository repository;
 
 @Override
 public void addEmployee(EmployeeDO employee) {
  repository.save(employee);
 }

 @Override
 public List<EmployeeDO> getAllEmployees() {
  return (List<EmployeeDO>) repository.findAll();
 }
 
 @Override
 public EmployeeDO getEmployee(Long employeeId) throws EntityNotFoundException{
  return repository.findById(employeeId).orElseThrow(() 
    -> new EntityNotFoundException("Employee", "empId", employeeId.toString()));
 }

 @Override
 public void deleteEmployee(Long employeeId) throws EntityNotFoundException {
  EmployeeDO employee = repository.findById(employeeId).orElseThrow(() 
    -> new EntityNotFoundException("Employee", "empId", employeeId.toString()));
  repository.delete(employee);
 }
}

EmployeeRepository – Repository Layer

package com.onlyfullstack.springrestadvance.repositoy;

import org.springframework.data.repository.CrudRepository;

import com.onlyfullstack.springrestadvance.domainobject.EmployeeDO;

public interface EmployeeRepository extends CrudRepository<EmployeeDO, Long> {

}

You can find the postman collection of this request at – CRUD Rest Example Postman Collection

Source Code

Download source code of Spring Rest Advance Topics from below git repository : spring-rest-advance-topics

Spring Rest Advanced Tutorial

https://www.onlyfullstack.com/spring-rest-advanced-tutorial/
Lets go to our next tutorial where we will discuss below point

Only Fullstack

Learn Java, Spring and automation

Part 3 – Spring Boot with H2 Database