TP2.2 - H2 Database

Objectives

• Understand the difference between in-memory and persistent databases
• Create a JPA entity and a repository
• Use the 4 basic CRUD methods (save, findById, findAll, deleteById)
• Access the H2 console

---

Part 1: In-Memory H2 (Temporary Data)

1.1 Project Creation

Go to: https://start.spring.io/

Configuration:

• Project: Maven
• Language: Java
• Spring Boot: 3.2.0+
• Group: com.example
• Artifact: h2-memoire
• Java: 17

Dependencies:

• Spring Web
• H2 Database
• Spring Data JPA

Generate and unzip in your workspace.

---

1.2 Create the Employee Entity

File: src/main/java/com/example/h2memoire/model/Employee.java

package com.example.h2memoire.model;

import jakarta.persistence.*;

@Entity
@Table(name = "tbl_employees")
public class Employee {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String firstName;
    private String lastName;
    private String email;
    
    // Constructors
    public Employee() {
    }
    
    public Employee(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }
    
    // Getters and Setters
    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 String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
}

---

1.3 Create the Repository

File: src/main/java/com/example/h2memoire/repository/EmployeeRepository.java

package com.example.h2memoire.repository;

import com.example.h2memoire.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    // Inherited methods: save(), findById(), findAll(), deleteById()
}

---

1.4 Configuration

File: src/main/resources/application.properties

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

# In-MEMORY Database
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# Hibernate
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

---

1.5 Run the Application

mvn clean install -DskipTests spring-boot:run

---

1.6 H2 Console

URL: http://localhost:8080/h2-console/

Connection:

• JDBC URL: jdbc:h2:mem:testdb
• Username: sa
• Password: (empty)

---

1.7 Test the CRUD

CREATE - Insert:

INSERT INTO TBL_EMPLOYEES (FIRST_NAME, LAST_NAME, EMAIL) 
VALUES ('Jean', 'Tremblay', 'jean@example.com');

INSERT INTO TBL_EMPLOYEES (FIRST_NAME, LAST_NAME, EMAIL) 
VALUES ('Marie', 'Gagnon', 'marie@example.com');

READ - Query:

SELECT * FROM TBL_EMPLOYEES;

UPDATE - Modify:

UPDATE TBL_EMPLOYEES 
SET EMAIL = 'nouveau@example.com' 
WHERE FIRST_NAME = 'Jean';

DELETE - Remove:

DELETE FROM TBL_EMPLOYEES WHERE ID = 1;

---

1.8 Persistence Test

Test 1: Disconnect/Reconnect

• Disconnect from H2
• DO NOT stop the application
• Reconnect
• Result: ✅ Data still there

Test 2: Stop/Restart

• Stop the application (Ctrl + C)
• Restart: mvn clean install -DskipTests spring-boot:run
• Reconnect
• Result: ❌ Empty table (data lost)

Why? Memory mode + ddl-auto=create-drop

---

Part 2: Persistent H2 (Disk Data)

2.1 New Project

Option 1: Duplicate the h2-memoire project and rename to h2-persistance

Option 2: Create a new project from Spring Initializr

---

2.2 Copy Entity and Repository

Copy the same Employee.java and EmployeeRepository.java files from Part 1.

---

2.3 Modified Configuration

File: src/main/resources/application.properties

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

# DISK Database (key difference)
spring.datasource.url=jdbc:h2:file:C:/temp/test;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# Hibernate
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Key Changes:

• jdbc:h2:mem: → jdbc:h2:file:C:/temp/test
• ddl-auto=create-drop → ddl-auto=update

---

2.4 Run the Application

mvn clean install -DskipTests spring-boot:run

---

2.5 H2 Console

URL: http://localhost:8080/h2-console/

Connection:

• JDBC URL: jdbc:h2:file:C:/temp/test
• Username: sa
• Password: (empty)

---

2.6 Test the CRUD

INSERT INTO TBL_EMPLOYEES (FIRST_NAME, LAST_NAME, EMAIL) 
VALUES ('Pierre', 'Roy', 'pierre@example.com');

SELECT * FROM TBL_EMPLOYEES;

---

2.7 Persistence Test

Test: Stop/Restart

1. Insert data
2. Stop the application (Ctrl + C)
3. Restart: mvn clean install -DskipTests spring-boot:run
4. Reconnect
5. Result: ✅ Data still there!

File Check:

• Go to C:\temp\
• test.mv.db file created (your database)

---

Comparison: Memory vs File

Aspect	Memory Mode	File Mode
URL	jdbc:h2:mem:testdb	jdbc:h2:file:C:/temp/test
ddl-auto	create-drop	update
Persistence	❌ No	✅ Yes
.mv.db File	Not created	Created on disk
Usage	Tests, development	Development with data

---

Summary

Key Points

• JPA Entity with @Entity and @Table - Hibernate automatically creates the table
• Repository with JpaRepository - 4 free CRUD methods
• In-memory H2 - Fast but temporary
• File H2 - Data persistence on disk
• H2 Console - Interface to test CRUD

4 Basic CRUD Methods

Method	Action
save(entity)	CREATE / UPDATE
findById(id)	READ one
findAll()	READ all
deleteById(id)	DELETE

---

Exercise

Create a new Product entity:

• id, name, price, quantity
• ProductRepository
• Test in memory mode
• Test in file mode

---

Back to Module 2