Spring Data JPA with PostgreSQL

JPA (implemented by Hibernate) maps Java classes to tables: an @Entity is a row, fields are columns, and relationships such as @OneToMany/@ManyToOne become foreign keys. Spring Data JPA goes further: you declare a repository interface and Spring writes the implementation, including queries derived from method names like findByForm or findByNameContainingIgnoreCase. This continues the Spring Boot lesson's results-api project; keep its ResultsApplication class.

Let Flyway own the schema: versioned SQL files in db/migration (V1__...sql, V2__...sql) run once each, in order, on every database, and ddl-auto=validate makes Hibernate check that the entities match. Never edit a migration that has already run; add a new one. For queries that don't fit a method name, write JPQL with @Query, which works on entities and fields, and use @EntityGraph or a join fetch to load related rows in one query instead of N+1 queries.

@Transactional service methods run in one transaction: everything commits together or rolls back on an exception. Inside a transaction, changes to loaded entities are saved automatically at commit, so updating a score needs no save call. Tests here are @SpringBootTest + @Transactional against a separate PostgreSQL test database, and each test is rolled back automatically.

TerminalShell

createdb results_jpa          # for running the app
createdb results_jpa_test     # for the tests (kept separate from your data)
mvn test                      # Flyway migrates the test database, then the tests run
mvn spring-boot:run

pom.xmlXML

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>4.1.1</version>
  </parent>
  <groupId>tz.dolese</groupId>
  <artifactId>results-api</artifactId>
  <version>1.0.0</version>

  <properties>
    <java.version>21</java.version>
  </properties>

  <dependencies>
    <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-flyway</artifactId>
    </dependency>
    <dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-database-postgresql</artifactId>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

src/main/resources/application.propertiesText

spring.datasource.url=${DATABASE_URL:jdbc:postgresql://localhost:5432/results_jpa}
spring.datasource.username=${DATABASE_USER:postgres}
spring.datasource.password=${DATABASE_PASSWORD:postgres}

# Flyway creates and upgrades the schema; Hibernate only checks that the entities match it.
spring.jpa.hibernate.ddl-auto=validate
# Don't keep a database connection open while the web response is being written.
spring.jpa.open-in-view=false
# Uncomment to see the SQL Hibernate runs:
# spring.jpa.show-sql=true

src/main/resources/db/migration/V1__create_students_and_results.sqlSQL

CREATE TABLE students (
  id               bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  admission_number text NOT NULL UNIQUE,
  name             text NOT NULL,
  form             integer NOT NULL CHECK (form BETWEEN 1 AND 6)
);

CREATE TABLE exam_results (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  student_id bigint NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  subject    text NOT NULL,
  term       text NOT NULL,
  score      integer NOT NULL CHECK (score BETWEEN 0 AND 100),
  UNIQUE (student_id, subject, term)
);

src/main/java/tz/dolese/results/Student.javaJava

package tz.dolese.results;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "admission_number", nullable = false, unique = true)
    private String admissionNumber;

    @Column(nullable = false)
    private String name;

    private int form;

    // One student has many results; mappedBy names the field that owns the foreign key.
    @OneToMany(mappedBy = "student", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ExamResult> results = new ArrayList<>();

    protected Student() {}                 // JPA needs a no-argument constructor

    public Student(String admissionNumber, String name, int form) {
        this.admissionNumber = admissionNumber;
        this.name = name;
        this.form = form;
    }

    // Keep both sides of the relationship in step.
    public ExamResult addResult(String subject, String term, int score) {
        ExamResult result = new ExamResult(this, subject, term, score);
        results.add(result);
        return result;
    }

    public Long getId() { return id; }
    public String getAdmissionNumber() { return admissionNumber; }
    public String getName() { return name; }
    public int getForm() { return form; }
    public void setForm(int form) { this.form = form; }
    public List<ExamResult> getResults() { return results; }
}

src/main/java/tz/dolese/results/ExamResult.javaJava

package tz.dolese.results;

import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;

@Entity
@Table(name = "exam_results")
public class ExamResult {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)     // loaded only when you use it
    @JoinColumn(name = "student_id")
    private Student student;

    private String subject;
    private String term;
    private int score;

    protected ExamResult() {}

    ExamResult(Student student, String subject, String term, int score) {
        this.student = student;
        this.subject = subject;
        this.term = term;
        this.score = score;
    }

    public Student getStudent() { return student; }
    public String getSubject() { return subject; }
    public String getTerm() { return term; }
    public int getScore() { return score; }
    public void setScore(int score) { this.score = score; }
}

src/main/java/tz/dolese/results/StudentAverage.javaJava

package tz.dolese.results;

public record StudentAverage(String name, double average) {}

src/main/java/tz/dolese/results/StudentRepository.javaJava

package tz.dolese.results;

import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

// Spring writes the implementation: save, findById, findAll, delete, count ... plus these.
public interface StudentRepository extends JpaRepository<Student, Long> {

    // Derived query: Spring builds the SQL from the method name.
    List<Student> findByForm(int form, Sort sort);

    Optional<Student> findByAdmissionNumber(String admissionNumber);

    List<Student> findByNameContainingIgnoreCase(String text);

    // Load students AND their results in one query, avoiding the "N+1 queries" problem.
    @EntityGraph(attributePaths = "results")
    List<Student> findWithResultsByForm(int form);

    // JPQL works on entities and fields, not tables and columns.
    @Query("""
            select new tz.dolese.results.StudentAverage(s.name, avg(r.score))
            from Student s join s.results r
            where s.form = :form and r.term = :term
            group by s.id, s.name
            order by avg(r.score) desc
            """)
    List<StudentAverage> averages(int form, String term);
}

src/main/java/tz/dolese/results/ResultService.javaJava

package tz.dolese.results;

import java.util.NoSuchElementException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ResultService {
    private final StudentRepository students;

    public ResultService(StudentRepository students) {
        this.students = students;
    }

    // One transaction: everything below commits together, or rolls back on an exception.
    // Changes to loaded entities are saved automatically at commit ("dirty checking").
    @Transactional
    public ExamResult record(String admissionNumber, String subject, String term, int score) {
        Student student = students.findByAdmissionNumber(admissionNumber)
                .orElseThrow(() -> new NoSuchElementException("No student " + admissionNumber));
        for (ExamResult existing : student.getResults()) {
            if (existing.getSubject().equals(subject) && existing.getTerm().equals(term)) {
                existing.setScore(score);           // correction: UPDATE, no save() call needed
                return existing;
            }
        }
        return student.addResult(subject, term, score);   // new row, saved through the cascade
    }
}

src/test/resources/application.propertiesText

# Tests use their own database, so they never touch (or depend on) development data.
spring.datasource.url=${TEST_DATABASE_URL:jdbc:postgresql://localhost:5432/results_jpa_test}
spring.datasource.username=${DATABASE_USER:postgres}
spring.datasource.password=${DATABASE_PASSWORD:postgres}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false

src/test/java/tz/dolese/results/StudentRepositoryTest.javaJava

package tz.dolese.results;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

import java.util.NoSuchElementException;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.annotation.Transactional;

// Runs against a real PostgreSQL database; @Transactional rolls each test back afterwards.
@SpringBootTest
@Transactional
class StudentRepositoryTest {
    @Autowired StudentRepository students;
    @Autowired ResultService results;

    @BeforeEach
    void seed() {
        Student amina = new Student("ADM-2026-0001", "Amina Hassan", 4);
        amina.addResult("Maths", "2026-T1", 88);
        amina.addResult("Biology", "2026-T1", 79);
        Student juma = new Student("ADM-2026-0002", "Juma Said", 4);
        juma.addResult("Maths", "2026-T1", 29);
        students.save(amina);
        students.save(juma);
        students.save(new Student("ADM-2026-0003", "Neema Kimaro", 3));
    }

    @Test
    void derivedQueries() {
        assertThat(students.findByForm(4, Sort.by("name")))
                .extracting(Student::getName).containsExactly("Amina Hassan", "Juma Said");
        assertThat(students.findByAdmissionNumber("ADM-2026-0003")).get()
                .extracting(Student::getForm).isEqualTo(3);
        assertThat(students.findByNameContainingIgnoreCase("SAID")).hasSize(1);
    }

    @Test
    void averagesWithJpql() {
        assertThat(students.averages(4, "2026-T1")).containsExactly(
                new StudentAverage("Amina Hassan", 83.5),
                new StudentAverage("Juma Said", 29.0));
    }

    @Test
    void recordingTwiceUpdatesInsteadOfDuplicating() {
        results.record("ADM-2026-0002", "Maths", "2026-T1", 35);
        results.record("ADM-2026-0002", "Biology", "2026-T1", 48);
        students.flush();
        Student juma = students.findByAdmissionNumber("ADM-2026-0002").orElseThrow();
        assertThat(juma.getResults()).extracting(ExamResult::getSubject, ExamResult::getScore)
                .containsExactlyInAnyOrder(org.assertj.core.groups.Tuple.tuple("Maths", 35),
                        org.assertj.core.groups.Tuple.tuple("Biology", 48));
        assertThatThrownBy(() -> results.record("ADM-9999", "Maths", "2026-T1", 50))
                .isInstanceOf(NoSuchElementException.class);
    }
}

Key points

Entities map to tables; repositories get CRUD plus derived queries for free.
Flyway migrations own the schema; Hibernate only validates it. Add migrations, never edit them.
@Transactional services commit or roll back as a unit, and loaded entities save automatically.

Exercise

Add a V2 migration with a guardian_phone column and a CHECK on its format, map it in Student, and add repository methods to page through a form (Page<Student> findByForm(int, Pageable)), find students with no guardian phone, and promote a whole form with one @Modifying UPDATE. Test each one, including the database rejecting a bad phone number.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The new column arrives through a new migration, never by editing V1, and the CHECK constraint means the database rejects a bad phone even if a bug skips validation in Java. Page<Student> findByForm(int, Pageable) makes Spring add LIMIT/OFFSET and a count query. @Modifying runs a single UPDATE for the whole form; clearAutomatically discards cached entities so later reads see the new forms.

src/main/resources/db/migration/V2__add_guardian_phone.sqlSQL

-- Never edit a migration that has already run: add a new one.
ALTER TABLE students ADD COLUMN guardian_phone text;
ALTER TABLE students ADD CONSTRAINT guardian_phone_format CHECK (guardian_phone ~ '^0[67][0-9]{8}$');

Student.java (addition)Java

// In Student.java: a nullable column added by V2
@Column(name = "guardian_phone")
private String guardianPhone;

public String getGuardianPhone() { return guardianPhone; }
public void setGuardianPhone(String guardianPhone) { this.guardianPhone = guardianPhone; }

src/main/java/tz/dolese/results/StudentRepository.javaJava

package tz.dolese.results;

import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

public interface StudentRepository extends JpaRepository<Student, Long> {

    List<Student> findByForm(int form, Sort sort);

    // Paging: Spring adds LIMIT/OFFSET and runs a count query for the total.
    Page<Student> findByForm(int form, Pageable pageable);

    Optional<Student> findByAdmissionNumber(String admissionNumber);

    List<Student> findByNameContainingIgnoreCase(String text);

    List<Student> findByGuardianPhoneIsNull();

    @EntityGraph(attributePaths = "results")
    List<Student> findWithResultsByForm(int form);

    @Query("""
            select new tz.dolese.results.StudentAverage(s.name, avg(r.score))
            from Student s join s.results r
            where s.form = :form and r.term = :term
            group by s.id, s.name
            order by avg(r.score) desc
            """)
    List<StudentAverage> averages(int form, String term);

    // One UPDATE statement for many rows, instead of loading and saving each entity.
    // clearAutomatically: forget cached entities, which would otherwise show the old form.
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("update Student s set s.form = s.form + 1 where s.form = :form")
    int promote(int form);
}

src/test/java/tz/dolese/results/PagingAndPromotionTest.javaJava

package tz.dolese.results;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest
@Transactional
class PagingAndPromotionTest {
    @Autowired StudentRepository students;

    @BeforeEach
    void seed() {
        for (int i = 1; i <= 5; i++) {
            Student s = new Student("ADM-2026-000" + i, "Student " + i, 4);
            if (i % 2 == 0) s.setGuardianPhone("071234567" + i);
            students.save(s);
        }
        students.save(new Student("ADM-2026-0100", "Neema Kimaro", 3));
    }

    @Test
    void pagesThroughAForm() {
        Page<Student> page = students.findByForm(4, PageRequest.of(1, 2, Sort.by("name")));
        assertThat(page.getTotalElements()).isEqualTo(5);
        assertThat(page.getTotalPages()).isEqualTo(3);
        assertThat(page.getContent()).extracting(Student::getName).containsExactly("Student 3", "Student 4");
    }

    @Test
    void findsMissingGuardianPhones() {
        assertThat(students.findByGuardianPhoneIsNull()).hasSize(4);   // students 1, 3, 5 and Neema
    }

    @Test
    void promotesAWholeFormInOneStatement() {
        assertThat(students.promote(4)).isEqualTo(5);
        assertThat(students.findByForm(5, Sort.unsorted())).hasSize(5);
        assertThat(students.findByAdmissionNumber("ADM-2026-0001").orElseThrow().getForm()).isEqualTo(5);
    }

    @Test
    void databaseRejectsABadPhone() {
        Student s = students.findByAdmissionNumber("ADM-2026-0100").orElseThrow();
        s.setGuardianPhone("12345");
        assertThatThrownBy(students::flush).isInstanceOf(DataIntegrityViolationException.class);
    }
}

Key points

Exercise

Check your understanding