Week 3 | Lesson 11

Data Layer & Repositories

Repository Pattern, JDBC, Spring Data JPA, JOOQ, Transactions, Repository Testing

Repository Pattern

Repository

The repository layer is responsible for managing the data access logic and performing CRUD _{(Create, Read, Update, Delete)} operations on the database.

Repository is typically a layer between service and data storage (e.g., database).

It provides methods for interacting with the data, such as retrieving, creating, updating, and deleting data. These methods don't contain any business logic, but rather focus on data access and manipulation.

Repositories are often implemented using some data access technology, such as an ORM (Object-Relational Mapping) framework or a SQL library.

Service layer uses repository to access data and perform business logic.

Repository Responsibilities

The repository layer focuses solely on data access and manipulation, without business logic.

Key responsibilities of the repository layer:

Data Access - Retrieving data from the database using queries
Data Persistence - Saving new data and updating existing records
Data Mapping - Converting between database records and domain objects
Query Optimization - Writing efficient database queries
Transaction Handling - Managing database transactions at the data level
Data Validation - Basic data integrity checks (constraints, foreign keys)

What repositories should NOT do:

Business logic implementation
Authorization decisions
Complex data transformations
External service integrations

Data Access Frameworks

There is a variety of ways and frameworks you can use to implement the repository layer.

Exposed
Kotlin SQL framework that provides a DSL for building SQL queries and mapping results to Kotlin objects.
JOOQ
Java SQL framework that provides a fluent API for building SQL queries and mapping results to Java objects. It supports generating code from database schemas, allowing for typesafe SQL queries.
SQLDelight
Kotlin Multiplatform library that generates typesafe Kotlin APIs from SQL statements.
Raw SQL
You can use raw SQL queries to interact with the database directly. I would advise this only for simple applications or for specific cases where you need full control over the SQL queries.
and more ..

Repository Pattern Benefits

The repository pattern provides several important benefits for application architecture and maintainability.

Key benefits of using the repository pattern:

Separation of Concerns - Clear separation between business logic and data access logic
Testability - Easy to mock repositories for unit testing
Flexibility - Can switch between different data sources without changing business logic
Maintainability - Centralized data access code that's easier to maintain
Reusability - Repository methods can be used across multiple services
Type Safety - Strong typing with domain models and DTOs

The repository pattern also enables:

Dependency injection and inversion of control
Consistent error handling for data access operations
Centralized query optimization and performance tuning
Easy migration between different database technologies

JDBC

Repository

The repository layer is responsible for managing the data access logic and performing CRUD _{(Create, Read, Update, Delete)}operations on the database.

There is a variety of ways and frameworks you can use to implement the repository layer.

Java Database Connectivity (JDBC)

Probably simplest option is to use JdbcTemplate, which is a simple JDBC-based template class provided by Spring.
Java Persistence API (JPA)

Java Persistence API is a standard specification for object-relational mapping in Java. It is designed to simplify the development of applications that need to access and manipulate data from a relational database.

Spring Data project offers Spring Data JPA which is and implementation of JPA.

Another JPA implementation is Hibernate.
Other

JOOQ is popular a type-safe SQL query builder for Java/Kotlin.

Using JdbcTemplate

The JdbcTemplate class provides a set of methods for performing CRUD operations on the database, however you need to write the SQL queries yourself.

If you want to use JdbcTemplate, you should create a repository class and annotate it with @Repository annotation.

Then you need to inject the JdbcTemplate bean into the repository class.

@Repository
class TaskRepository(
    private val jdbcTemplate: JdbcTemplate
) {

    fun findAll(): List<TaskEntity> {
        return jdbcTemplate.query("SELECT * FROM tasks") { resultSet, index ->
            TaskEntity(
                id = resultSet.getLong("id"),
                description = resultSet.getString("description"),
                status = TaskStatus.valueOf(resultSet.getString("status")),
                createdBy = resultSet.getLong("created_by")
            )
        }
    }
}

Using JdbcTemplate

Writing queries

Select with parameters

fun findById(id: Long): TaskEntity? {
    return jdbcTemplate.query(
        "SELECT * FROM tasks WHERE id = ? LIMIT 1",
        ::rowMapper,
        id
    ).firstOrNull()
}

Insert/update with parameters and returning results

fun save(task: NewTaskRequest): TaskEntity? {
    return jdbcTemplate.query(
        "SELECT * FROM FINAL TABLE (INSERT INTO tasks (description, status, created_by) VALUES (?, ?, ?))",
        ::rowMapper,
        task.description,
        "NEW",
        task.createdBy
    ).firstOrNull()
}

Using JdbcTemplate

Row mapper function

private fun rowMapper(rs: ResultSet, i: Int): TaskEntity {
    return TaskEntity(
        id = rs.getLong("id"),
        description = rs.getString("description"),
        status = TaskStatus.valueOf(rs.getString("status")),
        createdBy = rs.getLong("created_by")
    )
}

Spring Data JPA

Using Spring Data JPA

Spring Data JPA is implementation of the Java Persistence API (JPA).

It provides a set of interfaces and classes that make it easy to work with databases in a Spring Boot application.

For simple queries, you don't need to write any SQL, you just need to extend JpaRepository interface.

The interface provides several methods for CRUD operations (like save(), findById(), findAll(), etc) that you can use right away without writing any implementation code.

Simplest repository definition:

interface TaskRepository : JpaRepository<TaskEntity, Long>

Repository is defined as TaskRepository interface, extending JpaRepository.
TaskEntity is a class representing data object (entity) managed by the repository.
Long is the primary key type of the TaskEntity type.

Using Spring Data JPA

In order for JPA to work you need to provide class annotated with @Entity annotation.

The TaskEntity entity may look like this

@Entity
@Table(name = "tasks")
data class TaskEntity(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long? = null,

    @Column(name = "description")
    val description: String,

    @Column(name = "status")
    @Enumerated(EnumType.STRING)
    val status: TaskStatus,

    @Column(name = "created_by")
    val createdBy: Long
)

Using Spring Data JPA

Spring Data JPA naming conventions and custom queries

In the example below, Spring Data JPA will automatically generate a query using the findByStatus method name because it follows a Spring Data JPA naming convention.

interface TaskRepository : JpaRepository<TaskEntity, Long> {

    fun findByStatus(status: TaskStatus): List<TaskEntity>

}

If you need to add more advanced queries, you can define them using the @Query annotation.

interface TaskRepository : JpaRepository<TaskEntity, Long> {

    @Query("SELECT t FROM TaskEntity t WHERE t.createdBy = :userId AND t.status = :status")
    fun findTasksByUserAndStatus(@Param("userId") userId: Long, @Param("status") status: TaskStatus): List<TaskEntity>

}

Using Spring Data JPA

Injecting the repository into the service

Using the repository from service is as simple as injecting the dependency and calling the interface methods.

import org.springframework.stereotype.Service

@Service
class TaskService(
    private val taskRepository: TaskRepository
) {

    fun getTasks(): List<Task> {
        return taskRepository.findAll()
            .map { it.toDomain() }
    }

    fun getTasksByStatus(status: TaskStatus): List<Task> {
        return taskRepository.findByStatus(status)
            .map { it.toDomain() }
    }

}

Other Tools

JOOQ

One of the popular alternatives to JPA and JDBC is JOOQ.

JOOQ is a type-safe SQL query builder for Java/Kotlin.

With JOOQ, you can write SQL queries in a type-safe way, using a fluent API.

It also comes with the code generation tool that generates Java/Kotlin classes from the database schema.

val context = DSL.using(dataSource, SQLDialect.H2)

val tasks = context.selectFrom(TASKS)
    .where(
        TASKS.STATUS.eq("NEW"),
        TASKS.CREATED_BY.eq(1L)
    )
    .fetch(Records.mapping(::TaskEntity.java))

Transactions

Whenever we are performing more than one database operation in a single method is that you should use transactions.

Transactions are used to ensure that all operations are executed as a single unit of work. So in case of an error, all operations are rolled back as a whole, preventing data inconsistency.

To setup a transaction, use @Transactional annotation on the method that performs the operations.

There is one caveat to using annotations in Spring though! They do not work on methods called from within the same class. This is because Spring uses proxies to implement the annotations.

So annotations such as @Transactional will only work when the annotated methods is called from another class.

Transactions

Spring transactions can be tricky due to the way Spring implements them using proxies. Proxies are used to intercept method calls and apply additional behavior, such as starting and committing a transaction, but they only work when the method is called from outside the class.

If we called the assignTasks, the call would not be transactional, because the method annotated with @Transactional is called from within the same class.

@Service
class TaskService(
    private val taskRepository: TaskRepository,
    private val auditLogRepository: AuditLogRepository
) {

    fun assignTasks(tasks: List<TaskEntity>, userId: Long) {
        tasks.forEach { task -> assignTask(task, userId) }
    }

    @Transactional
    private fun assignTask(task: TaskEntity, userId: Long): TaskEntity {
        val updatedTask = taskRepository.save(task.copy(assignedTo = userId))
        auditLogRepository.save(AuditLog(action = "ASSIGNED", taskId = updatedTask.id, userId = userId))
        return updatedTask
    }

}

To solve this, we can move the @Transactional annotation to the assignTasks method.

@Service
class TaskService(
    private val taskRepository: TaskRepository,
    private val auditLogRepository: AuditLogRepository
) {

    @Transactional
    fun assignTasks(tasks: List<TaskEntity>, userId: Long) {
        tasks.forEach { task -> assignTask(task, userId) }
    }

    private fun assignTask(task: TaskEntity, userId: Long): TaskEntity {
        val updatedTask = taskRepository.save(task.copy(assignedTo = userId))
        auditLogRepository.save(AuditLog(action = "ASSIGNED", taskId = updatedTask.id, userId = userId))
        return updatedTask
    }

}

Testing Repositories

Repository tests verify database operations using a real database (in-memory or containerized).

Testing the repository layer requires a real database to verify SQL queries, constraints, and data mapping. We need to ensure the database is in a known state before running each test.

To build the correct testing context with Spring, use:

@JdbcTest - For JDBC-based repositories
@DataJpaTest - For JPA repositories

Additional useful annotations:

@Import - Import repository class if needed
@Autowired - Inject repository and dependencies
@DirtiesContext - Reset database state before each test

Key principles:

Never mock the database - Use a real database instance
Use in-memory database (H2) or containerized database (Testcontainers)
Reset database state before each test
Focus on data access logic, not business logic

In-memory H2 database is convenient for testing, but production uses PostgreSQL/MySQL. For more realistic tests, use TestContainers to run tests against containerized production databases.

Testing JDBC Repositories

Testing repositories using JdbcTemplate with @JdbcTest.

@JdbcTest configures an in-memory database and JdbcTemplate for testing JDBC repositories.

@JdbcTest
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.ANY)
@Import(TaskJdbcRepository::class)
class TaskJdbcRepositoryTest : FunSpec() {

    @Autowired
    private lateinit var jdbcTemplate: JdbcTemplate

    @Autowired
    private lateinit var taskRepository: TaskJdbcRepository

    init {
        test("findAll should return all tasks") {
            jdbcTemplate.update(
                "INSERT INTO tasks (description, status, created_by) VALUES (?, ?, ?)",
                "Task 1", "NEW", 1
            )
            jdbcTemplate.update(
                "INSERT INTO tasks (description, status, created_by) VALUES (?, ?, ?)",
                "Task 2", "IN_PROGRESS", 1
            )

            val tasks = taskRepository.findAll()
            tasks.size shouldBe 2
            tasks[0].description shouldBe "Task 1"
            tasks[1].description shouldBe "Task 2"
        }
    }
}

Testing JPA Repositories

Testing Spring Data JPA repositories with @DataJpaTest.

@DataJpaTest configures an in-memory database and JPA components for testing JPA repositories.

@DataJpaTest
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
class TaskJpaRepositoryTest : FunSpec() {

    @Autowired
    private lateinit var entityManager: TestEntityManager

    @Autowired
    private lateinit var taskRepository: TaskJpaRepository

    init {
        test("save should persist task to database") {
            val task = TaskEntity(
                description = "Test JPA",
                status = TaskStatus.NEW,
                createdBy = 1L
            )

            val saved = taskRepository.save(task)
            entityManager.flush()
            entityManager.clear()

            saved.id shouldNotBeNull
            val found = taskRepository.findById(saved.id!!)
            found.isPresent shouldBe true
            found.get().description shouldBe "Test JPA"
        }
    }
}

TestContainers

TestContainers runs tests against real databases in Docker containers for production-like testing.

TestContainers is a library that allows you to run your tests against real databases in isolated Docker containers.

@DataJpaTest
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class TaskRepositoryContainerTest : FunSpec() {

    companion object {
        @Container
        val postgres = PostgreSQLContainer<Nothing>("postgres:15-alpine").apply {
            withDatabaseName("testdb")
            withUsername("test")
            withPassword("test")
        }

        @JvmStatic
        @DynamicPropertySource
        fun configureProperties(registry: DynamicPropertyRegistry) {
            registry.add("spring.datasource.url", postgres::getJdbcUrl)
            registry.add("spring.datasource.username", postgres::getUsername)
            registry.add("spring.datasource.password", postgres::getPassword)
        }
    }

    @Autowired
    private lateinit var taskRepository: TaskJpaRepository

    init {
        test("should work with real PostgreSQL") {
            val task = TaskEntity(
                description = "Test with real database",
                status = TaskStatus.NEW,
                createdBy = 1L
            )
            val saved = taskRepository.save(task)
            saved.id shouldNotBe null
        }
    }
}

Benefits:

Test against the actual production database (PostgreSQL, MySQL, etc.)
Avoid H2 compatibility issues
Automatic container lifecycle management
Isolated test environment (no shared state)

Final Assignment

Final Assignment: Fantasy Game Simulation API

You will build a complete Spring Boot REST API for a fantasy game simulation where players create characters, engage in battles, and compete on leaderboards.

Purpose:

You will build a complete Spring Boot REST API for a fantasy game simulation where players create characters (warriors and sorcerers), engage in battles, and compete on leaderboards. This capstone project integrates all concepts learned throughout the course into a real-world application.

What It's About:

A turn-based fantasy game backend where users manage characters with different classes and attributes, simulate battles with strategic risk/reward mechanics, and track rankings through a leaderboard system. Authentication and a web UI are provided; you will implement the core game logic and data layer.

Expected Deliverable

You will deliver a working Spring Boot application with layered architecture, REST APIs, and comprehensive tests.

You will build and deliver:

A working Spring Boot application with three main REST APIs (Characters, Matches, Leaderboards)
Layered architecture implementation (Controller → Service → Repository)
Proper data transformation between layers using DTOs
Authentication-secured endpoints
Comprehensive test coverage using JUnit and MockK
Error handling and input validation throughout

What's Provided:

User Management API - Complete reference implementation
Authentication System - Spring Security with Basic Auth configured
User Interface - A complete web UI that consumes your API
Database Schema - H2 database schema and initial data

What You Must Implement:

Characters API - All character management endpoints
Matches API - Match creation and battle simulation
Leaderboard API - Rankings and statistics

Learning Outcomes

You will apply all course concepts by building a complete application with proper architecture, testing, and Spring Boot integration.

By completing this assignment, you will:

Apply layered architecture patterns in a complete application
Implement RESTful API design with proper contracts
Practice domain modeling with inheritance (character classes)
Implement complex business logic (battle simulation, experience calculation)
Design for testability and write comprehensive tests
Work with Spring Security for authentication
Integrate all course concepts: Spring Boot, JPA/JDBC, dependency injection, testing, DTOs, and architectural patterns

Technologies You'll Use:

Kotlin & Spring Boot
Spring Data JPA or JDBC
Spring Security (Basic Auth)
H2 Database
JUnit & MockK for testing
REST API design principles

Data Layer & Repositories

Repository Pattern

Repository

Repository Responsibilities

Data Access Frameworks

Repository Pattern Benefits

JDBC

Repository

Java Database Connectivity (JDBC)

Java Persistence API (JPA)

Other

Using JdbcTemplate

Using JdbcTemplate

Using JdbcTemplate

Spring Data JPA

Using Spring Data JPA

Using Spring Data JPA

Using Spring Data JPA

Using Spring Data JPA

Other Tools

JOOQ

Transactions

Transactions

Transactions

Testing Repositories

Testing Repositories

Testing JDBC Repositories

Testing JPA Repositories

TestContainers

Final Assignment

Final Assignment: Fantasy Game Simulation API

Expected Deliverable

Learning Outcomes