Data Access Layer

This document provides a detailed technical overview of the Data Access Layer (DAL) for the application. The DAL is responsible for all interactions with the underlying database, providing an abstraction layer for data persistence, retrieval, and management. It is built upon the robust capabilities of Spring Data JPA, which simplifies data access by reducing boilerplate code and implementing well-established design patterns.

For a broader view of how this layer fits into the overall system, please refer to the Core Architecture documentation.

Repository Pattern

The application's data access strategy is built around the Repository Pattern. This pattern abstracts the data store, allowing the rest of the application to interact with a simple, object-oriented interface without needing to know the details of the underlying persistence mechanism (in this case, JPA and MySQL).

Spring Data JPA is used to implement this pattern. We define repository interfaces that extend JpaRepository, and Spring automatically provides the implementation at runtime.

The primary repository in this module is TicketRepository.

// src/main/java/com/slalom/demo/ticketing/repository/TicketRepository.java

package com.slalom.demo.ticketing.repository;

import com.slalom.demo.ticketing.model.Ticket;
import com.slalom.demo.ticketing.model.TicketStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TicketRepository extends JpaRepository<Ticket, Long> {

    // Custom derived query to find tickets by their status
    List<Ticket> findByStatus(TicketStatus status);

    // Custom derived query to find tickets by the requester's email
    List<Ticket> findByRequesterEmail(String requesterEmail);

    // Custom derived query to find tickets by the assignee
    List<Ticket> findByAssignedTo(String assignedTo);
}

Key Implementation Details:

• @Repository: This annotation marks the interface as a Spring-managed component, making it eligible for component scanning and dependency injection.
• extends JpaRepository<Ticket, Long>: By extending JpaRepository, TicketRepository inherits a rich set of standard CRUD (Create, Read, Update, Delete) and query methods out of the box.
  • The first generic parameter, Ticket, is the JPA entity that this repository manages.
  • The second parameter, Long, is the data type of the entity's primary key (@Id).
• The actual implementation of this interface is generated on the fly by Spring Data JPA, removing the need for manual DAO implementation. The managed entity Ticket is defined in src/main/java/com/slalom/demo/ticketing/model/Ticket.java and maps to the tickets table. For more details on the table structure, see the Database Schema documentation.

Query Methods

The TicketRepository provides several methods for querying data, which fall into two categories: standard inherited methods and custom derived query methods.

Standard JPA Methods

By extending JpaRepository, we immediately gain access to methods like:

• save(Ticket entity): Creates or updates a ticket record.
• findById(Long id): Retrieves a single ticket by its primary key. Returns an Optional<Ticket>.
• findAll(): Retrieves all ticket records. Warning: This can cause performance issues on large datasets (see Performance Optimization).
• deleteById(Long id): Deletes a ticket by its primary key.
• existsById(Long id): Checks if a ticket with the given ID exists.

These methods are used extensively within the TicketService, as shown in the example below for updating a ticket:

// Snippet from src/main/java/com/slalom/demo/ticketing/service/TicketService.java

@Transactional
public TicketResponse updateTicket(Long id, TicketRequest request) {
    log.info("Updating ticket with ID: {}", id);

    // 1. Retrieve the entity using findById()
    Ticket ticket = ticketRepository.findById(id)
            .orElseThrow(() -> new RuntimeException("Ticket not found with id: " + id));

    // ... (update entity fields)

    // 2. Save the updated entity using save()
    Ticket updatedTicket = ticketRepository.save(ticket);
    log.info("Ticket updated: {}", updatedTicket.getId());

    // ...

    return toResponse(updatedTicket);
}

Custom Derived Query Methods

Spring Data JPA offers a powerful query derivation mechanism that automatically generates queries from method names. The TicketRepository defines several such methods:

Method Signature	Generated JPQL (Conceptual)	Purpose
List<Ticket> findByStatus(TicketStatus status)	SELECT t FROM Ticket t WHERE t.status = ?1	Filters tickets by their current status.
List<Ticket> findByRequesterEmail(String requesterEmail)	SELECT t FROM Ticket t WHERE t.requesterEmail = ?1	Finds all tickets submitted by a specific user.
List<Ticket> findByAssignedTo(String assignedTo)	SELECT t FROM Ticket t WHERE t.assignedTo = ?1	Finds all tickets assigned to a specific user.

These methods provide a type-safe and highly readable way to define common queries without writing any JPQL or SQL. They are invoked from the TicketService to fulfill specific business requirements.

// Snippet from src/main/java/com/slalom/demo/ticketing/service/TicketService.java

@Transactional(readOnly = true)
public List<TicketResponse> getTicketsByStatus(TicketStatus status) {
    log.info("Fetching tickets with status: {}", status);

    // Invokes the custom derived query method
    return ticketRepository.findByStatus(status).stream()
            .map(this::toResponse)
            .collect(Collectors.toList());
}

Transaction Management

The application uses Spring's declarative transaction management, which is orchestrated at the service layer. This ensures data consistency and integrity for all database operations. For a full overview of the service layer's responsibilities, see the Service Layer documentation.

Transaction Boundaries with @Transactional

All public methods in TicketService that modify data (create, update, delete) are annotated with @Transactional.

• A transaction begins when the annotated method is invoked.
• If the method completes successfully, the transaction is committed.
• If the method throws a RuntimeException (or an Error), the transaction is automatically rolled back, undoing any changes made within that transaction's scope.

// Snippet from src/main/java/com/slalom/demo/ticketing/service/TicketService.java

@Transactional // Starts a read-write transaction
public TicketResponse createTicket(TicketRequest request) {
    // ...
    Ticket savedTicket = ticketRepository.save(ticket); // This operation is part of the transaction
    // ...
    publishTicketEvent(savedTicket, "CREATED"); // This is also part of the transaction
    return toResponse(savedTicket);
} // Transaction commits here on successful exit

Read-Only Transactions

For methods that only read data, the @Transactional(readOnly = true) annotation is used. This is a crucial performance optimization.

// Snippet from src/main/java/com/slalom/demo/ticketing/service/TicketService.java

@Transactional(readOnly = true) // Starts a read-only transaction
public List<TicketResponse> getAllTickets() {
    log.info("Fetching all tickets");

    return ticketRepository.findAll().stream()
            .map(this::toResponse)
            .collect(Collectors.toList());
}

Using readOnly = true provides several benefits:

1. Performance: It signals to the persistence provider (Hibernate) that no changes will be made. This allows it to skip dirty checking, which is the process of scanning managed entities for changes to be flushed to the database.
2. Database Hints: It can provide hints to the underlying JDBC driver and database to apply read-only optimizations.
3. Safety: It prevents accidental data modifications within a method that is intended to be read-only.

Performance Optimization

While Spring Data JPA provides significant convenience, developers must remain mindful of performance implications, especially as the application scales.

Pagination Support (Future Enhancement)

The current implementation of getAllTickets() uses ticketRepository.findAll(), which retrieves every single ticket from the database and loads them into memory. This is highly inefficient and will lead to OutOfMemoryError on production systems with a large number of tickets.

Recommendation: Refactor methods that return collections to support pagination.

1. Modify the TicketRepository to extend PagingAndSortingRepository<Ticket, Long>.
2. Update service methods to accept a Pageable parameter.
3. Return a Page<Ticket> object, which includes pagination metadata (total pages, total elements, etc.) along with the data for the requested page.

Example (Proposed Change):

// In TicketRepository
// No change needed if it extends JpaRepository, which already extends PagingAndSortingRepository

// In TicketService
public Page<TicketResponse> getAllTickets(Pageable pageable) {
    log.info("Fetching page {} of tickets", pageable.getPageNumber());
    return ticketRepository.findAll(pageable).map(this::toResponse);
}

Query Optimization

• Database Indexing: The performance of derived queries like findByStatus and findByRequesterEmail depends heavily on proper database indexing. Ensure that the status, requester_email, and assigned_to columns in the tickets table are indexed to prevent slow full-table scans. Refer to the Database Schema documentation for indexing strategies.
• Read-Only Transactions: As noted above, consistently use @Transactional(readOnly = true) for all query operations to reduce overhead.

N+1 Query Prevention

The N+1 query problem is a common performance pitfall in ORM. It occurs when fetching a list of parent entities (1 query) and then subsequently executing a separate query for each parent's related child entities (N queries).

The current Ticket entity is simple and has no @ManyToOne or @OneToMany relationships with FetchType.EAGER. Therefore, it is not currently vulnerable to the N+1 problem.

However, if the data model evolves to include such relationships, developers must be vigilant. To prevent N+1 issues, use one of the following strategies:

• JOIN FETCH: Write a custom JPQL query in the repository using JOIN FETCH to eagerly load the related entities in a single query.
• @EntityGraph: Use the @EntityGraph annotation on a repository method to specify which associations should be fetched eagerly for that specific query. This is a cleaner alternative to JPQL for simple cases.