Spring Boot H2 Database CRUD

5 min readMar 27, 2021

Source code is available for this article on github.

Overview

In this example, we are creating CRUD (Create, Read, Update, Delete) operations using Spring Boot and H2 in-memory database. Like other databases, there’s full intrinsic support for it in the Spring Boot ecosystem. Here we are using Spring Data JPA for built-in methods to do CRUD operations.

@EnableJpaRepositories annotation is used on main class to Enable H2 DB related configuration, which will read properties from application.properties file.

Deployed this application on heroku server, all endpoints are available on below host

https://spring-boot-h2-crud.herokuapp.com/

Prerequisites

Tools

Eclipse or IntelliJ IDEA (or any preferred IDE) with embedded Maven
Maven (version >= 3.6.0)
Postman (or any RESTful API testing tool)

Build and Run application

cd ~/absolute-path-to-directory/spring-boot-h2-crud
and try below command in terminal

mvn spring-boot:run it will run application as spring boot application

mvn clean install it will build application and create jar file under target directory

Run jar file from below path with given command

java -jar ~/path-to-spring-boot-h2-crud/target/spring-boot-h2-crud-0.0.1-SNAPSHOT.jar

run main method from SpringBootH2CRUDApplication.java as spring boot application.

Note : In SpringBootH2CRUDApplication.java class we have autowired SuperHero, Student and Employee repositories.
If there is no record present in DB for any one of that module class (SuperHero, Student and Employee), static data is getting inserted in DB from HelperUtil.java class when we are starting the app for the first time.

Code Snippets

Maven Dependencies

Need to add below dependencies to enable H2 DB related config in pom.xml. Lombok’s dependency is to get rid of boiler-plate code(Getters, Setters, Parameterized Constructors, etc.). Openapi-ui for API documentations.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

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

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency><dependency>
    <groupId>org.springdoc</groupId>
    <artifactId>springdoc-openapi-ui</artifactId>
    <version>1.4.4</version>
</dependency>

Properties file

Reading H2 DB related properties from application.properties file and configuring JPA connection factory for H2 database.

After starting the application, we can navigate to http://localhost:8088/h2-console, which will present us with a login page. Provide details to login to h2 database from below code snippet.

src/main/resources/application.properties

server.port=8088

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

spring.h2.console.enabled=true

#spring.data.rest.base-path=/phone
spring.data.rest.base-default-page-size=10
spring.data.rest.base-max-page-size=20

springdoc.version=1.0.0
springdoc.swagger-ui.path=/swagger-ui-custom.html

Model classes

Below are the model classes which we will store in H2 DB and perform CRUD operations.
SuperHero.java

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table
public class SuperHero implements Serializable {

    @Id
    @GeneratedValue
    private int id;

    private String name;
    private String superName;
    private String profession;
    private int age;
    private boolean canFly;

}

Student.java

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table
public class Student implements Serializable {

	@Id
	@GeneratedValue
	private int id;

	private int rollNo;
	private String firstName;
	private String lastName;
	private float marks;
	
}

Employee.java

One-to-One relationship - (Employee to Address)

One-to-Many relationship - (Employee to Phone number)

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table
public class Employee implements Serializable {

    @Id
    @GeneratedValue
    private int id;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    private int age;

    @Column(name = "no_of_childrens")
    private int noOfChildrens;
    private boolean spouse;

    @JsonManagedReference
    @OneToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="address")
    private Address address;


    @JsonManagedReference
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "employee", cascade = { CascadeType.ALL})
    private List<PhoneNumber> phoneNumbers;



    @ElementCollection
    @CollectionTable(name="hobbies", joinColumns=@JoinColumn(name="id"))
    @Column(name="hobby")
    private List<String> hobbies = new ArrayList<>();

}

Address.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table
public class Address implements Serializable {

    @Id
    @GeneratedValue
    private int id;

    @Column(name = "street_address")
    private String streetAddress;
    private String city;
    private String state;
    private String country;

    @Column(name = "postal_address")
    private String postalCode;

    @JsonBackReference
    @OneToOne(mappedBy="address", cascade=CascadeType.ALL)
    private Employee employee;
}

PhoneNumber.java

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table
public class PhoneNumber implements Serializable {

    @Id
    @GeneratedValue
    private int id;
    private String type;
    private String number;


    @JsonBackReference
    @ManyToOne(cascade= { CascadeType.ALL})
    @JoinColumn(name="employee_id")
    private Employee employee;

}

CRUD operation for Super Heroes

In SuperHeroController.java class, we have exposed 5 endpoints for basic CRUD operations

GET All Super Heroes
GET by ID
POST to store Super Hero in DB
PUT to update Super Hero
DELETE by ID

@RestController
@RequestMapping("/super-hero")
public class SuperHeroController {
    
    @GetMapping
    public ResponseEntity<List<?>> findAll();

    @GetMapping("/{id}")
    public ResponseEntity<?> findById(@PathVariable String id);

    @PostMapping
    public ResponseEntity<?> save(@RequestBody SuperHero superHero);

    @PutMapping("/{id}")
    public ResponseEntity<?> update(@PathVariable int id, @RequestBody SuperHero superHero);

    @DeleteMapping("/{id}")
    public ResponseEntity<?> delete(@PathVariable String id);
}

In SuperHeroRepository.java, we are extending JpaRepository<Class, ID> interface which enables CRUD related methods.

public interface SuperHeroRepository extends JpaRepository<SuperHero, String> {
}

In SuperHeroServiceImpl.java, we are autowiring above interface using @Autowired annotation and doing CRUD operation.

In EmployeeController.java class, we have exposed 5 endpoints for basic CRUD operations

GET All Employee
GET by ID
POST to store Employee in DB
PUT to update Employee
DELETE by ID

@RestController
@RequestMapping("/employees")
public class EmployeeController {
     
    @GetMapping
    public ResponseEntity<List<?>> findAll();
 
    @GetMapping("/{id}")
    public ResponseEntity<?> findById(@PathVariable int id);
 
    @PostMapping
    public ResponseEntity<?> save(@RequestBody Employee employee);
 
    @PutMapping("/{id}")
    public ResponseEntity<?> update(@PathVariable int id, @RequestBody Employee employee);
 
    @DeleteMapping("/{id}")
    public ResponseEntity<?> delete(@PathVariable int id);
}

In EmployeeRepository.java, we are extending JpaRepository<Class, ID> interface which enables CRUD related methods.

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
}

In SuperHeroServiceImpl.java, we are autowiring above interface using @Autowired annotation and doing CRUD operation.

JPA And Query operation for Employee

API Endpoints

Super Hero CRUD Operations

GET Mapping http://localhost:8088/super-hero - Get all Super Heroes
GET Mapping http://localhost:8088/super-hero/1 - Get Super Hero by Id
POST Mapping http://localhost:8088/super-hero - Add new Super Hero in DB

Request Body:

{
      "name": "Tony",
      "superName": "Iron Man",
      "profession": "Business",
      "age": 50,
      "canFly": true
 }

PUT Mapping http://localhost:8088/super-hero/3 - Update existing Super Hero for given ID

Request Body:

{
      "id": "3"
      "name": "Tony",
      "superName": "Iron Man",
      "profession": "Business",
      "age": 50,
      "canFly": true
 }

DELETE Mapping http://localhost:8088/super-hero/3 - Delete Super Hero by ID

Employee CRUD Operations

GET Mapping http://localhost:8088/employees - Get all Employees
GET Mapping http://localhost:8088/employees/1 - Get Employee by Id
POST Mapping http://localhost:8088/employees - Add new Employee in DB

Request Body:

{
    "firstName": "Rahul",
    "lastName": "Ghadage",
    "age": 28,
    "noOfChildrens": 0,
    "spouse": true,
    "address": {
      "id": 16,
      "streetAddress": "RS road",
      "city": "Pune",
      "state": "Maharashtra",
      "country": "India",
      "postalCode": "411018"
    },
    "phoneNumbers": [
      {
        "id": 18,
        "type": "Mobile",
        "number": "1234567890"
      }
    ],
    "hobbies": [
      "Coding",
      "Reading"
    ]
  }

PUT Mapping http://localhost:8088/employees/1 - Update existing Employee for given ID

Request Body:

{
    "id": 1,
    "firstName": "Rahul",
    "lastName": "Ghadage",
    "age": 28,
    "noOfChildrens": 0,
    "spouse": true,
    "address": {
      "id": 16,
      "streetAddress": "RS road",
      "city": "Pune",
      "state": "Maharashtra",
      "country": "India",
      "postalCode": "411018"
    },
    "phoneNumbers": [
      {
        "id": 18,
        "type": "Mobile",
        "number": "1234567890"
      }
    ],
    "hobbies": [
      "Coding",
      "Reading"
    ]
  }

DELETE Mapping http://localhost:8088/employees/2 - Delete Employee by ID

Source code is available for this article on github.

Spring Boot H2 Database CRUD

Overview

Prerequisites

Tools

Build and Run application

Code Snippets

API Endpoints

Written by Rahul Ghadge