H2 Database and a Complete CRUD Application

The fastest way to learn Spring Data JPA end to end is to build a complete CRUD (Create, Read, Update, Delete) application against an in-memory H2 database — no installation, no Docker, just a dependency. H2 even ships with a web console to browse your tables.

In this lesson you will build a full "Student" REST API from scratch: entity, repository, service, DTOs with validation, controller with every HTTP method, global error handling, seed data, and the H2 console — then switch the same code to a file-based H2 database and finally to PostgreSQL by changing only configuration.

What H2 Is Good For

H2 is a small Java SQL database that runs inside your application. In in-memory mode (jdbc:h2:mem:) data disappears when the application stops — perfect for learning, demos and prototypes. In file mode (jdbc:h2:file:./data/app) data persists on disk. H2 is not meant for production workloads, and it differs from PostgreSQL/MySQL in SQL details, so real integration tests should use your production database via Testcontainers.

Setup and the H2 Console

Add spring-boot-starter-data-jpa and the com.h2database:h2 runtime dependency. With no spring.datasource.url, Spring Boot creates an in-memory database with a generated name (printed at startup); set spring.datasource.url=jdbc:h2:mem:studentsdb for a fixed name. Enable the browser console with spring.h2.console.enabled=true and open /h2-console. In Spring Boot 4 the console auto-configuration lives in its own module — add spring-boot-h2console if the console does not appear. If Spring Security is present, permit /h2-console/** and allow frames from the same origin for that path, in development only.

Seeding Data

For embedded databases Spring Boot runs schema.sql and data.sql from the classpath automatically. When Hibernate generates the schema (ddl-auto=create-drop), set spring.jpa.defer-datasource-initialization=true so data.sql runs after the tables exist. A CommandLineRunner that saves entities through the repository is an alternative that stays database-independent.

CRUD Endpoint Design

A conventional REST mapping: GET /api/students (list, paginated), GET /api/students/{id} (one, 404 if missing), POST /api/students (create, 201 + Location), PUT /api/students/{id} (replace), PATCH /api/students/{id} (partial update), and DELETE /api/students/{id} (204). Keep entities internal and use request/response DTOs.

Examples

Dependencies and configuration

Java

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webmvc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

# application.properties
spring.datasource.url=jdbc:h2:mem:studentsdb
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.defer-datasource-initialization=true
spring.h2.console.enabled=true

Output

H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:studentsdb'
Hibernate: create table student (id bigint generated by default as identity, course varchar(255), email varchar(255) not null unique, name varchar(255) not null, primary key (id))

Entity, repository, DTOs and seed data

Java

@Entity
public class Student {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false)
    private String name;
    @Column(nullable = false, unique = true)
    private String email;
    private String course;

    protected Student() {}
    public Student(String name, String email, String course) {
        this.name = name; this.email = email; this.course = course;
    }
    // getters and setters
}

public interface StudentRepository extends JpaRepository<Student, Long> {
    boolean existsByEmailIgnoreCase(String email);
    Page<Student> findByCourseIgnoreCase(String course, Pageable pageable);
}

public record StudentRequest(@NotBlank String name, @Email @NotBlank String email, String course) {}
public record StudentResponse(Long id, String name, String email, String course) {
    static StudentResponse from(Student s) {
        return new StudentResponse(s.getId(), s.getName(), s.getEmail(), s.getCourse());
    }
}

-- src/main/resources/data.sql
insert into student (name, email, course) values ('Asha Rao', 'asha@webnest.in', 'Spring Boot');
insert into student (name, email, course) values ('Ravi Kumar', 'ravi@webnest.in', 'Java Core');

Output

Hibernate: insert ... (2 rows from data.sql)

Service and controller with every CRUD operation

Java

@Service
@Transactional
public class StudentService {

    private final StudentRepository repo;

    public StudentService(StudentRepository repo) {
        this.repo = repo;
    }

    @Transactional(readOnly = true)
    public Page<StudentResponse> list(String course, Pageable pageable) {
        Page<Student> page = course == null ? repo.findAll(pageable) : repo.findByCourseIgnoreCase(course, pageable);
        return page.map(StudentResponse::from);
    }

    @Transactional(readOnly = true)
    public StudentResponse get(Long id) {
        return StudentResponse.from(find(id));
    }

    public StudentResponse create(StudentRequest req) {
        if (repo.existsByEmailIgnoreCase(req.email())) {
            throw new ResponseStatusException(HttpStatus.CONFLICT, "Email already exists");
        }
        return StudentResponse.from(repo.save(new Student(req.name(), req.email(), req.course())));
    }

    public StudentResponse replace(Long id, StudentRequest req) {
        Student s = find(id);
        s.setName(req.name());
        s.setEmail(req.email());
        s.setCourse(req.course());
        return StudentResponse.from(s);          // dirty checking saves on commit
    }

    public StudentResponse changeCourse(Long id, String course) {
        Student s = find(id);
        s.setCourse(course);
        return StudentResponse.from(s);
    }

    public void delete(Long id) {
        repo.delete(find(id));
    }

    private Student find(Long id) {
        return repo.findById(id).orElseThrow(() ->
            new ResponseStatusException(HttpStatus.NOT_FOUND, "Student " + id + " not found"));
    }
}

@RestController
@RequestMapping("/api/students")
public class StudentController {

    private final StudentService service;

    public StudentController(StudentService service) {
        this.service = service;
    }

    @GetMapping
    public Page<StudentResponse> list(@RequestParam(required = false) String course,
                                      @PageableDefault(size = 20, sort = "name") Pageable pageable) {
        return service.list(course, pageable);
    }

    @GetMapping("/{id}")
    public StudentResponse get(@PathVariable Long id) {
        return service.get(id);
    }

    @PostMapping
    public ResponseEntity<StudentResponse> create(@Valid @RequestBody StudentRequest req) {
        StudentResponse created = service.create(req);
        return ResponseEntity.created(URI.create("/api/students/" + created.id())).body(created);
    }

    @PutMapping("/{id}")
    public StudentResponse replace(@PathVariable Long id, @Valid @RequestBody StudentRequest req) {
        return service.replace(id, req);
    }

    @PatchMapping("/{id}/course")
    public StudentResponse changeCourse(@PathVariable Long id, @RequestBody Map<String, String> body) {
        return service.changeCourse(id, body.get("course"));
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> delete(@PathVariable Long id) {
        service.delete(id);
        return ResponseEntity.noContent().build();
    }
}

Output

GET    /api/students                  -> 200 {"content":[{"id":1,"name":"Asha Rao",...},{"id":2,"name":"Ravi Kumar",...}],"page":{"size":20,"number":0,"totalElements":2,"totalPages":1}}
POST   /api/students {"name":"Meera","email":"meera@webnest.in","course":"SQL"} -> 201, Location: /api/students/3
POST   /api/students (same email)     -> 409 {"detail":"Email already exists"}
GET    /api/students/99               -> 404 {"detail":"Student 99 not found"}
PUT    /api/students/3 {...}          -> 200 updated student
PATCH  /api/students/3/course {"course":"PostgreSQL"} -> 200
DELETE /api/students/3                -> 204 No Content

H2 console, file mode, and switching to PostgreSQL

Java

# Browse data: http://localhost:8080/h2-console
#   JDBC URL: jdbc:h2:mem:studentsdb   User: sa   Password: (empty)
#   SELECT * FROM STUDENT;

# Keep data between restarts: file mode
spring.datasource.url=jdbc:h2:file:./data/studentsdb
spring.jpa.hibernate.ddl-auto=update

# Production: same code, different configuration only
# (replace the h2 dependency with org.postgresql:postgresql)
spring.datasource.url=jdbc:postgresql://localhost:5432/students
spring.datasource.username=webnest
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate
spring.h2.console.enabled=false

Output

H2 console query result:
ID | NAME       | EMAIL            | COURSE
1  | Asha Rao   | asha@webnest.in  | Spring Boot
2  | Ravi Kumar | ravi@webnest.in  | Java Core

Common Mistakes

data.sql running before Hibernate creates tables; set spring.jpa.defer-datasource-initialization=true.
Enabling the H2 console in production or on a publicly reachable server.
Relying on H2 tests to prove PostgreSQL/MySQL-specific SQL works.
Using a random generated in-memory database name and then being unable to connect from the console; set spring.datasource.url.
Returning entities directly from CRUD controllers instead of DTOs.

Key Points to Remember

H2 runs in-process in memory or file mode — ideal for learning and prototypes.
spring.h2.console.enabled=true exposes a browser console at /h2-console (development only).
data.sql seeds embedded databases; defer initialization when Hibernate creates the schema.
A complete CRUD API maps GET, POST, PUT, PATCH and DELETE with proper status codes.
The same JPA code runs on PostgreSQL by changing the driver and datasource properties.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Use your local JDK or project IDE for these examples. Codelab currently runs Python and HTML/CSS/JavaScript; framework examples may need project dependencies.