Spring Data JPA with Spring Boot

Real-world domain example

We will use an employee-management application to connect the main JPA ideas. An employee belongs to a department, may have one profile, and can work on many projects. Customers place orders containing products. These are common relationship patterns in business systems.

Required dependencies

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

<!-- Choose one relational database driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- Or use MySQL instead of PostgreSQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

The application normally uses one relational driver for a deployment. Do not add PostgreSQL and MySQL as competing runtime databases unless you have deliberately configured separate data sources.

Entity and Repository

@Entity
class Product {
    @Id @GeneratedValue
    private Long id;
    private String name;
    private BigDecimal price;
}

interface ProductRepository extends JpaRepository<Product, Long> {
    List<Product> findByNameContainingIgnoreCase(String name);
}

JPA maps the entity to a table. A repository provides common operations such as save, findById, findAll, and delete without writing basic SQL. Spring Data also parses method names like findByNameContainingIgnoreCase into a query automatically.

Employee, Department, Customer, and Product

Entities represent relational tables. Keep entity classes focused on persistence and use DTOs at the API boundary. The following simplified model uses com.javacodeex.orders as the application package.

@Entity
public class Department {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
    private List<Employee> employees = new ArrayList<>();
}

@Entity
public class Employee {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "department_id")
    private Department department;
}

@Entity
public class Customer {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
}

@Entity
public class Product {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private BigDecimal price;
    private int stockQuantity;
}

Entity Relationships

Real schemas are rarely a single table. A common pattern is a parent entity with many child rows — for example, an Order that has many OrderItem rows. Model this with @OneToMany on the parent and @ManyToOne on the child.

@Entity
class Order {
    @Id @GeneratedValue
    private Long id;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<OrderItem> items = new ArrayList<>();
}

@Entity
class OrderItem {
    @Id @GeneratedValue
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id")
    private Order order;

    private String productName;
    private int quantity;
}

The @ManyToOne side is the owning side of the relationship because it holds the foreign key column (order_id). The @OneToMany side uses mappedBy to say “the other entity already manages this column, I am just the inverse view.” Only the owning side’s changes are written to the foreign key.

cascade = CascadeType.ALL means operations on the Order (persist, remove, merge) propagate to its OrderItem rows — convenient for a true parent/child relationship, but dangerous on relationships that are merely a reference (you would not want deleting a Product to cascade-delete every OrderItem that ever referenced it). fetch = FetchType.LAZY defers loading the collection until it is actually accessed; @ManyToOne defaults to EAGER in plain JPA, so it is common to set it to LAZY explicitly to avoid loading data you do not need.

Relationship end-to-end examples

The examples below use the employee-management domain. In a real application, expose DTOs instead of directly accepting entities so clients cannot accidentally change relationships or create recursive JSON responses.

One-to-many: Department to Employees

One department can have many employees. The foreign key is stored in the employee table, so the employee side owns the relationship.

@Entity
class Department {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "department",
               cascade = CascadeType.ALL,
               orphanRemoval = true)
    private List<Employee> employees = new ArrayList<>();

    public void addEmployee(Employee employee) {
        employees.add(employee);
        employee.setDepartment(this);
    }
}

@RestController
@RequestMapping("/api/departments")
class DepartmentController {
    private final DepartmentService service;

    @PostMapping
    DepartmentResponse create(
            @Valid @RequestBody CreateDepartmentRequest request) {
        return service.create(request);
    }

    @GetMapping("/{id}/employees")
    List<EmployeeResponse> employees(@PathVariable Long id) {
        return service.findEmployees(id);
    }
}

POST /api/departments
{ "name": "Engineering" }

POST /api/employees
{ "name": "Anand", "email": "anand@example.com", "departmentId": 1 }

GET /api/departments/1/employees

Many-to-one: Employees to Department

Many employees can reference one department. This is the owning side because it contains the department_id foreign key.

@Entity
class Employee {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "department_id", nullable = false)
    private Department department;
}

@Service
class EmployeeService {
    private final EmployeeRepository employees;
    private final DepartmentRepository departments;

    @Transactional
    EmployeeResponse create(CreateEmployeeRequest request) {
        Department department = departments.findById(request.departmentId())
                .orElseThrow(DepartmentNotFoundException::new);
        Employee employee = new Employee();
        employee.setName(request.name());
        employee.setDepartment(department);
        return EmployeeResponse.from(employees.save(employee));
    }
}

Keep this association lazy and fetch the department only for queries that need it. Use JOIN FETCH or @EntityGraph for an employee list that must include department names.

One-to-one: Employee to Employee Profile

Store a unique foreign key on the dependent profile table when each employee has one profile.

@Entity
class EmployeeProfile {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String phoneNumber;
    private String address;

    @OneToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "employee_id", nullable = false, unique = true)
    private Employee employee;
}

interface EmployeeProfileRepository
        extends JpaRepository<EmployeeProfile, Long> {
    Optional<EmployeeProfile> findByEmployeeId(Long employeeId);
}

PUT /api/employees/10/profile
{ "phoneNumber": "+91-9876543210", "address": "Hyderabad" }

GET /api/employees/10/profile

Many-to-many: Employees to Projects

Many employees can work on many projects. A join table stores the two foreign keys. For role or assignment date, create an explicit EmployeeProject entity instead.

@Entity
class Project {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @ManyToMany
    @JoinTable(
        name = "employee_project",
        joinColumns = @JoinColumn(name = "project_id"),
        inverseJoinColumns = @JoinColumn(name = "employee_id"))
    private Set<Employee> employees = new HashSet<>();
}

interface ProjectRepository
        extends JpaRepository<Project, Long> { }

PUT /api/projects/4/employees/10
GET /api/projects/4/employees
DELETE /api/projects/4/employees/10

One-to-one relationship

Use one-to-one when one row has exactly one related row, such as an employee and an employee profile. The foreign key normally belongs on the dependent table.

@Entity
class EmployeeProfile {
    @Id @GeneratedValue
    private Long id;
    private String phoneNumber;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "employee_id", unique = true)
    private Employee employee;
}

Many-to-many relationship

Use a join table when employees can work on many projects and each project can have many employees. In large systems, an explicit EmployeeProject entity is often better because it can store assignment date, role, or hours.

@Entity
class Project {
    @Id @GeneratedValue
    private Long id;
    private String name;

    @ManyToMany
    @JoinTable(name = "employee_project",
        joinColumns = @JoinColumn(name = "project_id"),
        inverseJoinColumns = @JoinColumn(name = "employee_id"))
    private Set<Employee> employees = new HashSet<>();
}

Complete CRUD operations

A normal CRUD flow separates the HTTP controller, business service, repository, and persistence entity. The same pattern works for employees, departments, customers, and products.

public record CreateEmployeeRequest(
        @NotBlank String name,
        @Email String email,
        @NotNull Long departmentId) { }

public record EmployeeResponse(
        Long id, String name, String email, String departmentName) { }

@Repository
interface EmployeeRepository extends JpaRepository<Employee, Long> { }

@Service
class EmployeeService {
    private final EmployeeRepository employees;
    private final DepartmentRepository departments;

    EmployeeService(EmployeeRepository employees,
                    DepartmentRepository departments) {
        this.employees = employees;
        this.departments = departments;
    }

    EmployeeResponse create(CreateEmployeeRequest request) {
        Department department = departments.findById(request.departmentId())
                .orElseThrow(DepartmentNotFoundException::new);
        Employee employee = new Employee(request.name(), request.email(), department);
        Employee saved = employees.save(employee);
        return toResponse(saved);
    }

    EmployeeResponse findById(Long id) {
        return employees.findById(id)
                .map(this::toResponse)
                .orElseThrow(EmployeeNotFoundException::new);
    }

    EmployeeResponse update(Long id, CreateEmployeeRequest request) {
        Employee employee = employees.findById(id)
                .orElseThrow(EmployeeNotFoundException::new);
        employee.setName(request.name());
        employee.setEmail(request.email());
        return toResponse(employees.save(employee));
    }

    void delete(Long id) {
        if (!employees.existsById(id)) {
            throw new EmployeeNotFoundException();
        }
        employees.deleteById(id);
    }
}

@RestController
@RequestMapping("/api/employees")
class EmployeeController {
    private final EmployeeService service;

    EmployeeController(EmployeeService service) {
        this.service = service;
    }

    @PostMapping
    ResponseEntity<EmployeeResponse> create(
            @Valid @RequestBody CreateEmployeeRequest request) {
        return ResponseEntity.status(HttpStatus.CREATED)
                .body(service.create(request));
    }

    @GetMapping("/{id}")
    EmployeeResponse find(@PathVariable Long id) {
        return service.findById(id);
    }

    @PutMapping("/{id}")
    EmployeeResponse update(@PathVariable Long id,
                            @Valid @RequestBody CreateEmployeeRequest request) {
        return service.update(id, request);
    }

    @DeleteMapping("/{id}")
    @ResponseStatus(HttpStatus.NO_CONTENT)
    void delete(@PathVariable Long id) {
        service.delete(id);
    }
}

Customer and Product CRUD

The same repository pattern can expose departments, customers, and products. Keep one service per business area so validation and rules do not accumulate in one large service.

@Repository
interface DepartmentRepository
        extends JpaRepository<Department, Long> { }

@Repository
interface CustomerRepository
        extends JpaRepository<Customer, Long> {
    Optional<Customer> findByEmailIgnoreCase(String email);
}

@Repository
interface ProductRepository
        extends JpaRepository<Product, Long> {
    Page<Product> findByNameContainingIgnoreCase(
            String name, Pageable pageable);
}

// Typical endpoints
POST   /api/departments
GET    /api/departments/{id}
PUT    /api/departments/{id}
DELETE /api/departments/{id}

POST   /api/customers
GET    /api/customers/{id}
PUT    /api/customers/{id}
DELETE /api/customers/{id}

POST   /api/products
GET    /api/products/{id}
PUT    /api/products/{id}
DELETE /api/products/{id}

For PostgreSQL and MySQL, these endpoints can use the same Java code. Only the active database profile and driver normally change. For MongoDB, create equivalent MongoRepository interfaces and document models rather than JPA entities.

End-to-end use cases

An end-to-end feature follows the complete path from an HTTP request to the database and back to a response:

Use case 1: Employee and Department

A department can contain many employees. The employee owns the foreign key, so the service first verifies the department and then saves the employee.

@RestController
@RequestMapping("/api/employees")
class EmployeeController {
    private final EmployeeService service;

    EmployeeController(EmployeeService service) {
        this.service = service;
    }

    @PostMapping
    ResponseEntity<EmployeeResponse> create(
            @Valid @RequestBody CreateEmployeeRequest request) {
        return ResponseEntity.status(HttpStatus.CREATED)
                .body(service.create(request));
    }

    @GetMapping("/{id}")
    EmployeeResponse find(@PathVariable Long id) {
        return service.find(id);
    }

    @GetMapping
    Page<EmployeeResponse> findAll(Pageable pageable) {
        return service.findAll(pageable);
    }

    @DeleteMapping("/{id}")
    @ResponseStatus(HttpStatus.NO_CONTENT)
    void delete(@PathVariable Long id) {
        service.delete(id);
    }
}

@Service
class EmployeeService {
    private final EmployeeRepository employees;
    private final DepartmentRepository departments;

    @Transactional
    EmployeeResponse create(CreateEmployeeRequest request) {
        Department department = departments.findById(request.departmentId())
                .orElseThrow(DepartmentNotFoundException::new);

        Employee employee = new Employee(
                request.name(), request.email(), department);
        return EmployeeResponse.from(employees.save(employee));
    }

    @Transactional(readOnly = true)
    Page<EmployeeResponse> findAll(Pageable pageable) {
        return employees.findAll(pageable)
                .map(EmployeeResponse::from);
    }

    @Transactional
    void delete(Long id) {
        if (!employees.existsById(id)) {
            throw new EmployeeNotFoundException();
        }
        employees.deleteById(id);
    }
}

Test the complete flow with HTTP requests:

POST http://localhost:8080/api/employees
Content-Type: application/json

{
  "name": "Anand",
  "email": "anand@example.com",
  "departmentId": 1
}

GET http://localhost:8080/api/employees?page=0&size=10&sort=name,asc

DELETE http://localhost:8080/api/employees/10

Use case 2: Customer and Product

Customers and products are usually separate aggregates. A product service can create, update, search, and delete products, while a customer service owns customer email uniqueness and profile rules.

@Repository
interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findByNameContainingIgnoreCase(
            String name, Pageable pageable);
}

@Service
class ProductService {
    private final ProductRepository products;

    ProductService(ProductRepository products) {
        this.products = products;
    }

    Product create(Product product) {
        return products.save(product);
    }

    Page<Product> search(String name, Pageable pageable) {
        return products.findByNameContainingIgnoreCase(name, pageable);
    }

    Product update(Long id, Product changes) {
        Product current = products.findById(id)
                .orElseThrow(ProductNotFoundException::new);
        current.setName(changes.getName());
        current.setPrice(changes.getPrice());
        current.setStockQuantity(changes.getStockQuantity());
        return products.save(current);
    }
}

POST   /api/products
GET    /api/products?name=laptop&page=0&size=20
GET    /api/products/5
PUT    /api/products/5
DELETE /api/products/5

POST   /api/customers
GET    /api/customers/20
PUT    /api/customers/20
DELETE /api/customers/20

Use case 3: PostgreSQL deployment

The employee, department, customer, and product code can run against PostgreSQL by adding the driver and activating a production profile.

# pom.xml
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

# application-prod.properties
spring.datasource.url=${POSTGRES_URL}
spring.datasource.username=${POSTGRES_USER}
spring.datasource.password=${POSTGRES_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false
spring.flyway.enabled=true

java -jar order-service.jar --spring.profiles.active=prod

curl -X POST http://localhost:8080/api/products +  -H "Content-Type: application/json" +  -d '{"name":"Laptop","price":75000,"stockQuantity":12}'

Use case 4: MySQL deployment

MySQL uses the same entity and repository code. Change the runtime driver and profile properties, then run the same REST requests.

# pom.xml
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

# application-mysql.properties
spring.datasource.url=jdbc:mysql://localhost:3306/orders?serverTimezone=UTC
spring.datasource.username=${MYSQL_USER}
spring.datasource.password=${MYSQL_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.open-in-view=false

java -jar order-service.jar --spring.profiles.active=mysql

curl http://localhost:8080/api/employees?page=0&size=10

Use case 5: MongoDB product catalog

Use MongoDB when product attributes vary by category or when a document-shaped catalog is more natural than a fixed relational schema. This flow uses a document, Mongo repository, service, and controller.

@Document("products")
record ProductDocument(
        @Id String id,
        String name,
        BigDecimal price,
        Map<String, Object> attributes) { }

interface ProductMongoRepository
        extends MongoRepository<ProductDocument, String> {
    List<ProductDocument> findByNameContainingIgnoreCase(String name);
}

@RestController
@RequestMapping("/api/catalog/products")
class ProductCatalogController {
    private final ProductMongoRepository products;

    @GetMapping
    List<ProductDocument> search(
            @RequestParam String name) {
        return products.findByNameContainingIgnoreCase(name);
    }

    @PostMapping
    ProductDocument create(@RequestBody ProductDocument product) {
        return products.save(product);
    }
}

# application-mongo.properties
spring.data.mongodb.uri=${MONGODB_URI:mongodb://localhost:27017/catalog}

curl -X POST http://localhost:8080/api/catalog/products +  -H "Content-Type: application/json" +  -d '{"name":"Laptop","price":75000,
       "attributes":{"ram":"16GB","screen":"15 inch"}}'

curl "http://localhost:8080/api/catalog/products?name=laptop"

MongoDB end-to-end example

In this example, we build a product catalog API. A request enters the controller, the service applies business rules, the repository talks to MongoDB, and the saved document is returned as JSON.

MongoDB database structure

MongoDB does not use tables and rows in the relational sense. It uses a database, collections, and documents:

catalog
└── products
    └── {
          "_id": "65f1a8...",
          "name": "Laptop",
          "price": 75000,
          "stockQuantity": 12,
          "attributes": {
            "ram": "16GB",
            "screen": "15 inch"
          }
        }

1. Create the document model

@Document(collection = "products")
public class ProductDocument {
    @Id
    private String id;

    @NotBlank
    private String name;

    @Positive
    private BigDecimal price;

    @PositiveOrZero
    private int stockQuantity;

    private Map<String, Object> attributes = new HashMap<>();

    // constructors, getters, and setters
}

@Document maps the Java class to the products collection. MongoDB can store different fields in different documents, but a consistent document shape is easier to validate and maintain.

2. Create the repository

public interface ProductMongoRepository
        extends MongoRepository<ProductDocument, String> {

    Page<ProductDocument> findByNameContainingIgnoreCase(
            String name, Pageable pageable);

    List<ProductDocument> findByStockQuantityLessThan(int quantity);
}

Spring Data creates the repository implementation. The first method searches product names and supports pagination; the second finds products that need restocking.

3. Create the service

@Service
public class ProductCatalogService {
    private final ProductMongoRepository repository;

    public ProductCatalogService(ProductMongoRepository repository) {
        this.repository = repository;
    }

    public ProductDocument create(ProductDocument product) {
        return repository.save(product);
    }

    public ProductDocument findById(String id) {
        return repository.findById(id)
                .orElseThrow(ProductNotFoundException::new);
    }

    public Page<ProductDocument> search(
            String name, Pageable pageable) {
        return repository.findByNameContainingIgnoreCase(name, pageable);
    }

    public ProductDocument update(
            String id, ProductDocument changes) {
        ProductDocument current = findById(id);
        current.setName(changes.getName());
        current.setPrice(changes.getPrice());
        current.setStockQuantity(changes.getStockQuantity());
        current.setAttributes(changes.getAttributes());
        return repository.save(current);
    }

    public void delete(String id) {
        if (!repository.existsById(id)) {
            throw new ProductNotFoundException();
        }
        repository.deleteById(id);
    }
}

4. Create the controller

@RestController
@RequestMapping("/api/mongo/products")
public class ProductCatalogController {
    private final ProductCatalogService service;

    public ProductCatalogController(
            ProductCatalogService service) {
        this.service = service;
    }

    @PostMapping
    ResponseEntity<ProductDocument> create(
            @Valid @RequestBody ProductDocument product) {
        return ResponseEntity.status(HttpStatus.CREATED)
                .body(service.create(product));
    }

    @GetMapping("/{id}")
    ProductDocument find(@PathVariable String id) {
        return service.findById(id);
    }

    @GetMapping
    Page<ProductDocument> search(
            @RequestParam(defaultValue = "") String name,
            @PageableDefault(size = 20) Pageable pageable) {
        return service.search(name, pageable);
    }

    @PutMapping("/{id}")
    ProductDocument update(@PathVariable String id,
                           @Valid @RequestBody ProductDocument product) {
        return service.update(id, product);
    }

    @DeleteMapping("/{id}")
    @ResponseStatus(HttpStatus.NO_CONTENT)
    void delete(@PathVariable String id) {
        service.delete(id);
    }
}

5. Configure the connection

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

# application-mongo.properties
spring.data.mongodb.uri=${MONGODB_URI:mongodb://localhost:27017/catalog}
spring.data.mongodb.auto-index-creation=false

The URI can point to local MongoDB, Docker, MongoDB Atlas, or a managed cloud deployment. Keep credentials in environment variables or a secret manager.

6. Test all CRUD operations

# Create
curl -X POST http://localhost:8080/api/mongo/products +  -H "Content-Type: application/json" +  -d '{"name":"Laptop","price":75000,"stockQuantity":12,
       "attributes":{"ram":"16GB","screen":"15 inch"}}'

# Read one
curl http://localhost:8080/api/mongo/products/65f1a8

# Search with pagination
curl "http://localhost:8080/api/mongo/products?name=laptop&page=0&size=10"

# Update
curl -X PUT http://localhost:8080/api/mongo/products/65f1a8 +  -H "Content-Type: application/json" +  -d '{"name":"Business Laptop","price":82000,"stockQuantity":8}'

# Delete
curl -X DELETE http://localhost:8080/api/mongo/products/65f1a8

The repository converts these operations into MongoDB insert, find, update, and delete commands. The controller does not know how MongoDB stores the document, and the service remains the right place for validation and business rules.

Derived Query Methods

Spring Data JPA reads the repository method name and builds a query from it. This avoids boilerplate for straightforward lookups.

These patterns compose well for simple filters, but once you need joins, subqueries, aggregation, or conditional logic that does not map cleanly to a method name, switch to @Query.

Using @Query

Reach for @Query when the query is too complex for a derived method name, when you want a specific projection instead of the full entity, or when you need native SQL features that JPQL does not expose (window functions, database-specific syntax, hints).

// JPQL - works against entity names and fields, database-independent
@Query("select o from Order o where o.status = :status order by o.createdDate desc")
List<Order> findRecentByStatus(@Param("status") String status);

// Native SQL - works against actual table and column names
@Query(value = "select * from orders where total_amount > :min", nativeQuery = true)
List<Order> findAboveAmount(@Param("min") BigDecimal min);

Prefer JPQL when possible because it stays portable across databases and understands entity relationships. Fall back to native SQL only when you need a database-specific feature or a hand-tuned query.

Pagination and Sorting

Loading an entire table for a listing page does not scale. Pageable and Sort let the repository ask the database for one page of rows in a given order, and Page<T> carries back the content plus paging metadata.

Pageable pageable = PageRequest.of(0, 20, Sort.by("name").ascending());
Page<Product> page = productRepository.findAll(pageable);

page.getContent();       // the 20 products on this page
page.getTotalElements(); // total rows across all pages
page.getTotalPages();    // total number of pages

Any repository method can also accept a trailing Pageable or Sort parameter, including derived query methods, e.g. findByStatus(String status, Pageable pageable).

End-to-end example: employee directory

Imagine an HR application with thousands of employees. The employee directory must show 20 employees at a time and allow users to sort by name, joining date, or salary. Returning every employee in one response wastes memory and makes the page slow. The API should ask the database for only the requested slice of data.

1. Create the employee entity

@Entity
@Table(name = "employees")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false, unique = true)
    private String email;

    @Column(nullable = false)
    private LocalDate joiningDate;

    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal salary;

    // constructors, getters, and setters
}

2. Add repository methods

Extending JpaRepository gives the application the standard findAll(Pageable) method. A derived query can also be paginated by adding Pageable as the last parameter.

public interface EmployeeRepository
        extends JpaRepository<Employee, Long> {

    Page<Employee> findByDepartmentId(
            Long departmentId,
            Pageable pageable);

    Page<Employee> findByNameContainingIgnoreCase(
            String name,
            Pageable pageable);
}

3. Return a small API response

Returning the JPA Page object directly can expose internal entity details. A response DTO keeps the API contract stable and sends only the fields required by the employee list.

public record EmployeeSummary(
        Long id,
        String name,
        String email,
        LocalDate joiningDate,
        BigDecimal salary) {
}

public record PageResponse<T>(
        List<T> content,
        int page,
        int size,
        long totalElements,
        int totalPages,
        boolean first,
        boolean last,
        String sortBy,
        String direction) {
}

4. Implement the service

Page numbers are zero-based: page=0 is the first page. The service caps the page size and allows only approved fields for sorting. This prevents clients from requesting an expensive unlimited result or sorting by an unexpected database property.

@Service
@Transactional(readOnly = true)
public class EmployeeService {

    private static final int MAX_PAGE_SIZE = 100;

    private static final Set<String> ALLOWED_SORT_FIELDS = Set.of(
            "name", "joiningDate", "salary");

    private final EmployeeRepository employeeRepository;

    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    public PageResponse<EmployeeSummary> search(
            int page,
            int size,
            String sortBy,
            String direction,
            String name) {

        if (page < 0) {
            throw new IllegalArgumentException("Page cannot be negative");
        }

        int safeSize = Math.min(Math.max(size, 1), MAX_PAGE_SIZE);
        String safeSortBy = ALLOWED_SORT_FIELDS.contains(sortBy)
                ? sortBy : "name";

        Sort.Direction sortDirection =
                "desc".equalsIgnoreCase(direction)
                        ? Sort.Direction.DESC
                        : Sort.Direction.ASC;

        Pageable pageable = PageRequest.of(
                page,
                safeSize,
                Sort.by(sortDirection, safeSortBy));

        Page<Employee> employees = name == null || name.isBlank()
                ? employeeRepository.findAll(pageable)
                : employeeRepository
                        .findByNameContainingIgnoreCase(name, pageable);

        Page<EmployeeSummary> summaries = employees.map(employee ->
                new EmployeeSummary(
                        employee.getId(),
                        employee.getName(),
                        employee.getEmail(),
                        employee.getJoiningDate(),
                        employee.getSalary()));

        return new PageResponse<>(
                summaries.getContent(),
                summaries.getNumber(),
                summaries.getSize(),
                summaries.getTotalElements(),
                summaries.getTotalPages(),
                summaries.isFirst(),
                summaries.isLast(),
                safeSortBy,
                sortDirection.name());
    }
}

5. Create the REST controller

@RestController
@RequestMapping("/api/employees")
public class EmployeeController {

    private final EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @GetMapping
    public PageResponse<EmployeeSummary> search(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size,
            @RequestParam(defaultValue = "name") String sortBy,
            @RequestParam(defaultValue = "asc") String direction,
            @RequestParam(required = false) String name) {

        return employeeService.search(
                page, size, sortBy, direction, name);
    }
}

6. Test the API

Get the first page, containing up to 20 employees:

GET /api/employees?page=0&size=20&sortBy=name&direction=asc

Get the third page, sorted by highest salary first:

GET /api/employees?page=2&size=10&sortBy=salary&direction=desc

Search employees whose names contain an:

GET /api/employees?name=an&page=0&size=20&sortBy=joiningDate&direction=desc

Example JSON response

{
  "content": [
    {
      "id": 42,
      "name": "Anand Kumar",
      "email": "anand@example.com",
      "joiningDate": "2024-06-10",
      "salary": 85000.00
    }
  ],
  "page": 0,
  "size": 20,
  "totalElements": 247,
  "totalPages": 13,
  "first": true,
  "last": false,
  "sortBy": "name",
  "direction": "ASC"
}

What SQL does Hibernate execute?

For a page request, Hibernate normally sends a data query with ORDER BY, LIMIT, and OFFSET, plus a count query to calculate the metadata.

select e.id, e.name, e.email, e.joining_date, e.salary
from employees e
order by e.name asc
limit 20 offset 0;

select count(e.id)
from employees e;

Production best practices

• Use a stable sort, especially when records can be inserted during paging. Add id as a tie-breaker when needed.
• Set a maximum page size such as 50 or 100.
• Whitelist sortable fields instead of passing arbitrary property names to Sort.
• Add database indexes for frequently filtered and sorted columns such as name, department_id, and joining_date.
• Use Slice<T> when total-page counts are unnecessary; it avoids the count query.
• Use keyset or cursor pagination for very large tables where high offset values become slow.
• Use DTO projections when the list needs only a few columns.

Lazy loading and eager loading

Lazy loading loads an association only when code accesses it. It reduces the first query size and is usually the safer default for collections. Eager loading loads the association immediately, which can be useful for a small always-needed relationship but can create large joins and unexpected queries.

@ManyToOne(fetch = FetchType.LAZY)
private Department department;

@OneToMany(fetch = FetchType.LAZY, mappedBy = "department")
private List<Employee> employees;

// Fetch only for this query when the API needs the department
@EntityGraph(attributePaths = "department")
Optional<Employee> findWithDepartmentById(Long id);

Do not switch every relationship to EAGER to fix a lazy loading exception. Fetch the required graph in a repository query, map the result inside a transaction, and return a DTO.

Pagination in a REST API

@GetMapping
Page<EmployeeResponse> findEmployees(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size,
        @RequestParam(defaultValue = "name") String sort) {
    Pageable pageable = PageRequest.of(
            page, Math.min(size, 100), Sort.by(sort).ascending());
    return employeeService.findAll(pageable);
}

Limit the maximum page size, whitelist sortable fields, and return metadata such as total elements, total pages, current page, and page size. For very large tables, consider keyset pagination instead of increasingly expensive offsets.

The N+1 Query Problem

The N+1 problem happens when you fetch a list of N parent rows, and then, because an association is lazily loaded, the application triggers one extra query per row to fetch each parent’s related data — N+1 queries instead of one. It is easy to miss in development with small datasets and painful in production with large ones.

Why does N+1 happen?

Suppose the database contains 100 orders. The application first runs one query to load the orders. A loop then calls order.getItems() for every order. Because the collection is lazy, Hibernate sends one additional query for each order:

-- Query 1: load the parent rows
select * from orders;

-- Query 2 through Query 101: one query for every order
select * from order_items where order_id = 1;
select * from order_items where order_id = 2;
select * from order_items where order_id = 3;
-- ... repeated for every order

The result is 1 + N queries. With 100 orders, the application may execute 101 queries instead of one or two. Each query adds database network latency, connection-pool usage, CPU work, and object-mapping overhead.

Typical code that causes it

@Transactional(readOnly = true)
List<OrderResponse> listOrders() {
    List<Order> orders = orderRepository.findAll();

    return orders.stream()
            .map(order -> new OrderResponse(
                    order.getId(),
                    order.getItems().size())) // lazy query per order
            .toList();
}

The problem is not lazy loading by itself. Lazy loading is useful when related data is not always needed. The problem occurs when lazy data is accessed repeatedly inside a loop or after the persistence context has closed.

Two common fixes:

// 1. JOIN FETCH in JPQL - loads the association in the same query
@Query("select o from Order o join fetch o.items where o.status = :status")
List<Order> findWithItemsByStatus(@Param("status") String status);

// 2. @EntityGraph - declare which associations to fetch eagerly for this call
@EntityGraph(attributePaths = "items")
List<Order> findByStatus(String status);

Both approaches turn the N extra queries into a single query with a join. JOIN FETCH is explicit and lives with the query; @EntityGraph keeps the derived query method but lets you opt in to eager loading only for that call, without changing the entity’s default fetch type.

Ways to resolve N+1

DTO projection example

A DTO query is often the best choice for an API list because it selects exactly the columns required by the response.

public record OrderSummary(
        Long id, String customerName, long itemCount) { }

@Query("""
       select new com.javacodeex.orders.OrderSummary(
           o.id, o.customerName, count(i))
       from Order o
       left join o.items i
       group by o.id, o.customerName
       """)
Page<OrderSummary> findOrderSummaries(Pageable pageable);

Batch fetching example

Batch fetching keeps lazy loading but asks Hibernate to load several relationships together. The exact batch size should be measured for your workload.

@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@BatchSize(size = 25)
private List<OrderItem> items;

# application.properties
spring.jpa.properties.hibernate.default_batch_fetch_size=25

How to detect N+1

• Enable SQL logging temporarily in development.
• Count SQL statements in repository integration tests.
• Use Hibernate statistics or a JDBC proxy such as datasource-proxy.
• Inspect slow requests with application metrics and database monitoring.
• Check list endpoints with realistic data, not only one or two rows.

# Development only: do not use verbose SQL logging as a production default
spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

PostgreSQL configuration

PostgreSQL is a strong default for production relational applications. Use environment variables for credentials and let Flyway or Liquibase manage schema changes.

# application-prod.properties
spring.datasource.url=${POSTGRES_URL:jdbc:postgresql://localhost:5432/orders}
spring.datasource.username=${POSTGRES_USER:orders_app}
spring.datasource.password=${POSTGRES_PASSWORD}
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=validate
spring.flyway.enabled=true

MySQL configuration

MySQL uses the same JPA entities and repository interfaces. Normally only the JDBC URL, driver, dialect, and a few database settings change.

# application-mysql.properties
spring.datasource.url=jdbc:mysql://localhost:3306/orders?serverTimezone=UTC
spring.datasource.username=orders_app
spring.datasource.password=${MYSQL_PASSWORD}
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=validate

Keep database-specific differences in profiles. The controller, service, entity, and repository code should remain portable whenever possible.

Using multiple relational databases

Supporting PostgreSQL and MySQL as alternative deployments is simple: activate a different profile. Connecting to both at the same time is a separate multi-data-source design requiring two data sources, two entity-manager factories, and separate repository packages.

# Run with PostgreSQL
java -jar order-service.jar --spring.profiles.active=prod

# Run the same application with MySQL
java -jar order-service.jar --spring.profiles.active=mysql

MongoDB with Spring Data MongoDB

MongoDB is not a JPA database. It stores documents rather than relational tables, so use Spring Data MongoDB repositories and @Document instead of @Entity. MongoDB is a good fit for flexible product catalogs, activity events, or document shaped customer preferences.

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

# application-mongo.properties
spring.data.mongodb.uri=${MONGODB_URI:mongodb://localhost:27017/orders}

@Document("products")
public class ProductDocument {
    @Id
    private String id;
    private String name;
    private BigDecimal price;
    private Map<String, Object> attributes;
}

public interface ProductMongoRepository
        extends MongoRepository<ProductDocument, String> {
    List<ProductDocument> findByNameContainingIgnoreCase(String name);
}

Auditing and Schema Management

Spring Data can stamp entities with creation and modification metadata automatically once auditing is enabled with @EnableJpaAuditing on a configuration class.

@EntityListeners(AuditingEntityListener.class)
@Entity
class Order {
    @CreatedDate
    private Instant createdDate;

    @LastModifiedDate
    private Instant lastModifiedDate;
}

Next: Spring Security →