Four ways to do CRUD on SQL databases in a Kotlin Spring Boot Application

24 min readFeb 9, 2025

In the last few years, two interview assignments required me to build a CRUD REST API with a SQL database behind it. The Java / Kotlin ecosystem has many ways to do this, so I decided to work out four ways in Kotlin for comparison. I provide working code samples for every implementation on GitHub that you can use.

My first Kotlin blog

This will be my first blog post on Kotlin. I have been working with Kotlin professionally for over three years now and I’m starting to feel more confident writing about it. In the first two years my Kotlin was pretty Java-ish as we used Spring Boot with libraries that were written in Java.

I mostly liked Kotlin for:

Immutability by default
The way Kotlin handles null
Being less verbose in general

Another one of Kotlin’s features is the ability to write DSL’s. I did not use that and most likely you don’t need to write a DSL often. But it has been great to use libraries that provide DSLs that I can use.

A Kotlin DSL example

We were using Rest Assured to execute REST calls in our integration tests. And it started to offer a Kotlin DSL. Let’s compare two examples:

Rest Assured in Java (example from code.haleby.se):

List cities = given()
  .queryParam("deleted", false)
  .when()
  .get("/users")
  .then()
  .statusCode(200)
  .body("size()", is(3))
  .body("name.any { it == 'Ervin Howell' }", is(true))
  .body("find { user -> user.username == 'Antonette' }.company.name", equalTo("Romaguera-Jacobson")
  .extract()
  .path("address.city");

Rest Assured using the Kotlin DSL (another example from code.haleby.se):

val cities : List = Given {
        queryParam("deleted", false)
} When {
	get("/users")
} Then {
	statusCode(200)
	body("size()", is(3))
	body("name.any { it == 'Ervin Howell' }", is(true))
	body("find { user -> user.username == 'Antonette' }.company.name", equalTo("Romaguera-Jacobson"))
} Extract {
	path("address.city")
}

You might think that syntax sugar like this doesn’t matter that much, but it can simplify long chains of methods. Especially when method chaining is used to create complex database queries.

Querying databases for a living

For the assignment that I had to do for my last job interview I needed to build a REST API with CRUD functionality (with some tricky things that had to be solved with transactions).

I implemented the assignment using plain SQL with the Spring JdbcClient. The interviewer liked my SQL knowledge but asked why I didn’t use an ORM like JPA / Hibernate. In my case it was because I personally like to stay as close to SQL as possible when doing queries, and JPA tries to abstract it away. He said that they used the Exposed framework that provided a nice Kotlin DSL to write SQL queries. I had not heard of Exposed before. I did know jOOQ had something similar, but never used it.

I got hired. After working with it I think the Exposed library is a great example of how the Kotlin DSL feature can make code easier. It felt more natural than working with Hibernate at earlier jobs.

Whether Exposed is better than using the JdbcClient, JPA / Hibernate and jOOQ depends on your needs. I will show how to build simple CRUD functionality with all four approaches, so you can compare and decide for yourself!

What are we going to build?

I’m trying to keep it as simple as possible. An API with CRUD (Create Read Update Delete) functionality for storing “persons”. I will insert some known football players as test data.

Technically we will be using:

Kotlin
Gradle (with Kotlin DSL)
Spring boot
The H2 in-memory database
JUnit with Rest Assured
Spring JdbcClient
JPA (Hibernate)
Jetbrains Exposed
JOOQ

I drew a picture of what our example will look like:

This is quite a bit of logic already. So here are some things I’m leaving out of this blog post because they make our “simple” example too complicated:

Authentication
Validation
Joins (dynamic WHERE clauses are a good enough example to demonstrate how query builder libraries work)
Hexagonal / Ports and Adapters architecture (I don’t want to define my model multiple times and write lots of mappers)

Using the JdbcClient from the Spring boot starter

Before you learn any other SQL builder or ORM you should probably start with the JdbcClient from the spring-boot-starter-jdbc module.

Let’s start by generating a project with the Spring Initializer:

Go to https://start.spring.io
Select Gradle — Kotlin as project (why would anyone choose Groovy?).
Select Kotlin as language.
Enter whatever package name you want to use and name your project.
Click Add dependencies on the right and add Spring Data JDBC and the H2 Database.
Unzip it on your local machine and open the project with IntelliJ or whatever IDE you use.

In the service we will build we will:

Use jackson for JSON parsing.
Use the springdoc library generate an OpenAPI spec and to have a nice page to test our rest calls easily.
Use Rest Assured with the fancy Kotlin syntax.
The kotlin-logging library because the internet told me that this was the easiest way to set up logging.

In order to use these things we need to add a few dependencies to the build.gradle.kts:

implementation("org.springframework.boot:spring-boot-starter-web")
 implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
 implementation("org.springframework.boot:spring-boot-starter-jdbc")
 implementation("org.jetbrains.kotlin:kotlin-reflect")
 // https://springdoc.org/#general-overview
 implementation("org.springdoc:springdoc-openapi-starter-webmvc-ui:2.6.0")
 implementation("io.github.oshai:kotlin-logging-jvm:7.0.0")

 runtimeOnly("com.h2database:h2")
 testImplementation("org.springframework.boot:spring-boot-starter-test")
 testImplementation("org.jetbrains.kotlin:kotlin-test-junit5")
 testRuntimeOnly("org.junit.platform:junit-platform-launcher")
 testImplementation ("io.rest-assured:rest-assured:5.5.0")
 testImplementation ("io.rest-assured:kotlin-extensions:5.5.0")

You can now build your project using your IDE or on the command line with:

# This should only be needed once
chmod +x gradlew

./gradlew build

Configure the H2 connection

Add the following properties to your application.properties file:

spring.application.name=ways-to-crud
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password

# Without this stupid line the Springdoc UI doesn't work.
server.servlet.context-path=/

Create a schema.sql file in your src/main/resources folder with the following SQL to build a table to store “PERSONS”:

CREATE TABLE IF NOT EXISTS PERSON(
    ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    FULL_NAME VARCHAR(50) NOT NULL,
    DATE_OF_BIRTH DATE NOT NULL,
    HEIGHT_IN_METERS DOUBLE NOT NULL
);

This creates a table in which we can store… persons! Now we have to add some persons to this table. Create a data.sql file in your src/main/resources folder with the following SQL:

INSERT INTO PERSON(FULL_NAME, DATE_OF_BIRTH, HEIGHT_IN_METERS) VALUES ('Lionel Messi', DATE '1987-06-24', 1.70);
INSERT INTO PERSON(FULL_NAME, DATE_OF_BIRTH, HEIGHT_IN_METERS) VALUES ('Christiano Ronaldo', DATE '1985-02-05', 1.87);

The above code inserts two known football players to our person table.

Creating a model

Create a package called model with a Persons.kt file with two data classes:

package net.leejjon.crud.model

import java.time.LocalDate

data class Persons(
    val persons: List<Person>
)

data class Person(
    val id: Int,
    val fullName: String,
    val dateOfBirth: LocalDate,
    val heightInMeters: Double
)

data class NewPerson(
    // No ID because that cannot be changed as ID's are generated by the db.
    val fullName: String,
    val dateOfBirth: LocalDate,
    val heightInMeters: Double
)

Creating a database service

Create a package called database with a PersonRowMapper.kt file:

package net.leejjon.crud.database

import net.leejjon.crud.model.Person
import org.springframework.jdbc.core.RowMapper
import java.sql.ResultSet

class PersonRowMapper : RowMapper<Person> {
    override fun mapRow(rs: ResultSet, rowNum: Int): Person {
        return Person(
            rs.getInt("ID"),
            rs.getString("FULL_NAME"),
            rs.getDate("DATE_OF_BIRTH").toLocalDate(),
            rs.getDouble("HEIGHT_IN_METERS")
        )
    }
}

Create a file called DbService.kt in the database package:

package net.leejjon.crud.database

import io.github.oshai.kotlinlogging.KotlinLogging
import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import org.springframework.http.HttpStatusCode
import org.springframework.jdbc.core.simple.JdbcClient
import org.springframework.jdbc.support.GeneratedKeyHolder
import org.springframework.jdbc.support.KeyHolder
import org.springframework.stereotype.Service
import org.springframework.web.server.ResponseStatusException
import java.time.LocalDate

@Service
class DbService(
    private val jdbcClient: JdbcClient
) {
    private val logger = KotlinLogging.logger {}
    fun getPersonsFromDb(): List<Person> =
        jdbcClient.sql("SELECT * FROM PERSON")
            .query(PersonRowMapper())
            .list()

    fun getPerson(id: Int): java.util.Optional<Person> =
        jdbcClient.sql("SELECT * FROM PERSON WHERE ID = ?")
            .params(id)
            .query(PersonRowMapper())
            .optional()

    fun createPerson(person: NewPerson): Person? {
        val keyHolder: KeyHolder = GeneratedKeyHolder()
        val update =
            jdbcClient.sql("INSERT INTO PERSON(FULL_NAME, DATE_OF_BIRTH, HEIGHT_IN_METERS) VALUES (?, ?, ?)")
                .params(person.fullName, person.dateOfBirth, person.heightInMeters)
                .update(keyHolder)

        if (update == 1) {
            val potentiallyCreatedPerson = getPerson(keyHolder.getKeyAs(Integer::class.java)?.toInt()!!)

            return if (potentiallyCreatedPerson.isPresent) {
                potentiallyCreatedPerson.get()
            } else {
                throw ResponseStatusException(HttpStatusCode.valueOf(404))
            }
        } else {
            logger.error { "Unable to create person $person" }
            throw ResponseStatusException(HttpStatusCode.valueOf(500))
        }
    }

    fun deletePerson(id: Int) {
        val update = try {
            jdbcClient.sql("DELETE FROM PERSON WHERE ID = ?")
                .params(id)
                .update()
        } catch (e: Exception) {
            logger.error(e) { "Unable to delete person due to error with the query or connection" }
            throw ResponseStatusException(HttpStatusCode.valueOf(500))
        }
        if (update == 1) {
            return
        } else {
            logger.error { "Could not find person with id $id" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }

    fun updatePerson(person: Person): Person {
        val update = try {
            jdbcClient.sql("UPDATE PERSON SET FULL_NAME = ?, DATE_OF_BIRTH = ?, HEIGHT_IN_METERS = ? WHERE ID = ?")
                .params(person.fullName, person.dateOfBirth, person.heightInMeters, person.id)
                .update()
        } catch (e: Exception) {
            logger.error(e) { "Unable to delete person due to error with the query or connection" }
            throw ResponseStatusException(HttpStatusCode.valueOf(500))
        }
        if (update == 1) {
            val personFromDb = getPerson(person.id)
            if (personFromDb.isPresent) {
                return personFromDb.get()
            } else {
                logger.error { "Could not update person with id $person.id" }
                throw ResponseStatusException(HttpStatusCode.valueOf(404))
            }
        } else {
            logger.error { "Could not update person with id $person.id" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }
}

You can see I am just putting SQL queries in the JdbcClient and have question marks as placeholders for variables.

Adding the REST API

Create package controllers and create a file named PersonController.kt in it:

package net.leejjon.crud.controllers

import io.swagger.v3.oas.annotations.responses.ApiResponse
import io.swagger.v3.oas.annotations.responses.ApiResponses
import net.leejjon.crud.database.DbService
import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import net.leejjon.crud.model.Persons
import org.springframework.http.ResponseEntity
import org.springframework.web.bind.annotation.DeleteMapping
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.PathVariable
import org.springframework.web.bind.annotation.PostMapping
import org.springframework.web.bind.annotation.PutMapping
import org.springframework.web.bind.annotation.RequestBody
import org.springframework.web.bind.annotation.RestController

@RestController("/v1")
class PersonController(
    private val dbService: DbService,
) {
    @GetMapping("/v1/persons")
    fun listPersons(): ResponseEntity<Persons> {
        return ResponseEntity.ok(Persons(dbService.getPersonsFromDb()))
    }

    @ApiResponses(value =
        [
            ApiResponse(responseCode = "200"),
            ApiResponse(responseCode = "404", description = "User not found"),
            ApiResponse(responseCode = "500", description = "Internal Server Error")
        ]
    )
    @GetMapping("/v1/persons/{id}")
    fun getPerson(
        @PathVariable
        id: Int
    ): ResponseEntity<Person> {
        val potentiallyExistingPerson = dbService.getPerson(id)
        return if (potentiallyExistingPerson.isPresent) {
            ResponseEntity.ok(potentiallyExistingPerson.get())
        } else {
            ResponseEntity.notFound().build()
        }
    }

    @ApiResponses(value =
        [
            ApiResponse(responseCode = "200"),
            ApiResponse(responseCode = "500", description = "Internal Server Error")
        ]
    )
    @PostMapping("/v1/persons")
    fun createPerson(@RequestBody person: NewPerson): ResponseEntity<Person> {
        val createdPerson = dbService.createPerson(person)
        return ResponseEntity.ok().body(createdPerson)
    }

    @ApiResponses(value =
        [
            ApiResponse(responseCode = "200"),
            ApiResponse(responseCode = "404", description = "User not found"),
            ApiResponse(responseCode = "500", description = "Internal Server Error")
        ]
    )
    @DeleteMapping("/v1/persons/{id}")
    fun deletePerson(
        @PathVariable id: Int
    ) {
        dbService.deletePerson(id)
    }

    @PutMapping("/v1/persons")
    fun updatePerson(
        @RequestBody person: Person
    ): ResponseEntity<Person> = ResponseEntity.ok(dbService.updatePerson(person))

    @PatchMapping("/v1/persons/{id}")
    fun updatePerson(@PathVariable id: Int, @RequestBody updatedFields: Map<String, Any?>): ResponseEntity<Person> {
        val updatedPerson = dbService.updatePersonAttributes(id, updatedFields)
        return ResponseEntity.ok(updatedPerson)
    }
}

Testing the endpoint manually

You can run the spring boot application now in your IDE or via the command line:

./gradlew bootRun

Since we added the Springdocs dependency, we can open the Swagger UI:

http://localhost:8080/swagger-ui/index.html

You should be able to do the calls from that interface.

Testing the endpoints automatically

Let’s be good citizens and write some tests. Normally I start with unit tests. But with CRUD functionality on a SQL database I prefer using integration tests, because the logic is mostly in the SQL queries. If you mock the JdbcClient there is little logic that we would actually test. And if we would ever switch to other methods of database querying (like Hibernate or Jetbrains Exposed), we would have to completely rewrite our unit tests.

Create a file called PersonsApiIntegrationTests.kt in the src/test/kotlin folder and copy and paste these beautiful tests that I wrote with rest assured:

package net.leejjon.crud

import io.restassured.http.ContentType
import io.restassured.module.kotlin.extensions.Extract
import org.junit.jupiter.api.Test
import io.restassured.module.kotlin.extensions.Given
import io.restassured.module.kotlin.extensions.Then
import io.restassured.module.kotlin.extensions.When
import io.restassured.specification.RequestSpecification
import net.leejjon.crud.model.Person
import net.leejjon.crud.model.Persons
import org.assertj.core.api.Assertions.assertThat
import org.junit.jupiter.api.BeforeEach
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.boot.test.web.server.LocalServerPort
import org.springframework.test.annotation.DirtiesContext
import java.time.LocalDate

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = [WaysToCrudApplication::class])
// Thank stackoverflow for telling me to put these annotations: https://stackoverflow.com/questions/34617152/how-to-re-create-database-before-each-test-in-spring
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.ANY)
class PersonsApiIntegrationTests {

    private lateinit var requestSpecification: RequestSpecification

    @LocalServerPort
    private val localPort: Int = 0

    @BeforeEach
    fun setup() {
        requestSpecification =
            Given {
                baseUri("http://localhost")
                port(localPort)
                log().all()
                response().log().all()
                contentType(ContentType.JSON)
                accept(ContentType.JSON)
            }
    }


    @Test
    fun `Verify that the GET request on the v1 persons endpoint returns Messi and Ronaldo`() {
        val response = Given {
            spec(requestSpecification)
        } When {
            get("/v1/persons")
        } Then {
            statusCode(200)
        } Extract {
            body().`as`(Persons::class.java)
        }

        assertThat(response.persons).hasSize(2)
        assertMessi(response.persons.first())
        assertRonaldo(response.persons.last())
    }

    @Test
    fun `Verify that the GET request on the v1 single person returns Messi`() {
        val response = Given {
            spec(requestSpecification)
        } When {
            get("/v1/persons/1")
        } Then {
            statusCode(200)
        } Extract {
            body().`as`(Person::class.java)
        }

        assertMessi(response)
    }

    @Test
    fun `Verify that the POST request on the v1 persons endpoint stores the user and returns 200`() {
        val response = Given {
            spec(requestSpecification)
        } When {
            body(
                """{
                  "fullName": "$NEYMAR_NAME",
                  "dateOfBirth": "$NEYMAR_DATE_OF_BIRTH",
                  "heightInMeters": $NEYMAR_HEIGHT
                }"""
            )
            post("/v1/persons")
        } Then {
            statusCode(200)
        } Extract {
            body().`as`(Person::class.java)
        }

        assertNeymar(response)
    }

    @Test
    fun `Verify that the DELETE request on the v1 persons endpoint deletes the user and returns 200`() {
        Given {
            spec(requestSpecification)
        } When {
            delete("/v1/persons/2")
        } Then {
            statusCode(200)
        }

        // Verify if there is one less person
        val response = Given {
            spec(requestSpecification)
        } When {
            get("/v1/persons")
        } Then {
            statusCode(200)
        } Extract {
            body().`as`(Persons::class.java)
        }

        assertThat(response.persons).hasSize(1)
        assertMessi(response.persons.first())
    }

    @Test
    fun `Verify that the PUT request on the v1 persons endpoint updates the user and returns 200`() {
        val response = Given {
            spec(requestSpecification)
        } When {
            // Update ronaldo to neymar
            body("""
                {
                  "id": "2",
                  "fullName": "$NEYMAR_NAME",
                  "dateOfBirth": "$NEYMAR_DATE_OF_BIRTH",
                  "heightInMeters": $NEYMAR_HEIGHT
                }
            """.trimIndent())
            put("/v1/persons")
        } Then {
            statusCode(200)
        } Extract {
            body().`as`(Person::class.java)
        }
        assertNeymar(response, 2)
    }

    @Test
    fun `Verify that the PATCH request on the v1 persons endpoint only updates the height if the request contains just the height`() {
        val response = Given {
            spec(requestSpecification)
        } When {
            // Let's give Ronaldo Messi's height
            body("""
                {
                    "heightInMeters": $MESSI_HEIGHT
                }
            """.trimIndent())
            patch("/v1/persons/1")
        } Then {
            statusCode(200)
        } Extract {
            body().`as`(Person::class.java)
        }

        assertRonaldo(response, MESSI_HEIGHT)
    }

    private fun assertMessi(messi: Person) {
        assertThat(messi.fullName).isEqualTo(MESSI_NAME)
        assertThat(messi.heightInMeters).isEqualTo(MESSI_HEIGHT)
        assertThat(messi.dateOfBirth).isEqualTo(MESSI_DATE_OF_BIRTH)
    }

    private fun assertRonaldo(ronaldo: Person, height: Double = RONALDO_HEIGHT) {
        assertThat(ronaldo.fullName).isEqualTo(RONALDO_NAME)
        assertThat(ronaldo.heightInMeters).isEqualTo(height)
        assertThat(ronaldo.dateOfBirth).isEqualTo(RONALDO_DATE_OF_BIRTH)
    }

    private fun assertNeymar(neymar: Person, id: Int = 3) {
        assertThat(neymar.id).isEqualTo(id)
        assertThat(neymar.fullName).isEqualTo(NEYMAR_NAME)
        assertThat(neymar.heightInMeters).isEqualTo(NEYMAR_HEIGHT)
        assertThat(neymar.dateOfBirth).isEqualTo(NEYMAR_DATE_OF_BIRTH)
    }

    companion object {
        val MESSI_NAME = "Lionel Messi"
        val MESSI_HEIGHT = 1.70
        val MESSI_DATE_OF_BIRTH = LocalDate.of(1987, 6, 24)

        val RONALDO_NAME = "Christiano Ronaldo"
        val RONALDO_HEIGHT = 1.87
        val RONALDO_DATE_OF_BIRTH = LocalDate.of(1985, 2, 5)

        val NEYMAR_NAME = "Neymar Júnior"
        val NEYMAR_HEIGHT = 1.75
        val NEYMAR_DATE_OF_BIRTH = LocalDate.of(1992, 2, 5)
    }
}

You can run this test from your IDE or via the command line:

./gradlew test

You’ll see that all tests succeed except for the test that does a PATCH request.

What about the PATCH endpoint?

The PUT endpoint above technically updates the person and thus counts as the U in CRUD. But it is a bit stupid that with the PUT endpoint you need to provide the entire person object, you cannot simply update one field.

In typical CRUD applications such as content management systems you need partial updates. This behavior gets awkward with the JdbcClient.

Instead of one update query you could split the following line:

jdbcClient.sql("UPDATE PERSON SET FULL_NAME = ?, DATE_OF_BIRTH = ?, HEIGHT_IN_METERS = ? WHERE ID = ?")
                .params(person.fullName, person.dateOfBirth, person.heightInMeters, person.id)
                .update()

Up into three conditional queries:

if (person.name != null) {
    jdbcClient.sql("UPDATE PERSON SET FULL_NAME = ? WHERE ID = ?")
        .params(person.name, person.id)
        .update()
}
if (person.dateOfBirth != null) {
    jdbcClient.sql("UPDATE PERSON SET DATE_OF_BIRTH = ? WHERE ID = ?")
        .params(person.dateOfBirth, person.id)
        .update()
}
if (person.heightInMeters != null) {
    jdbcClient.sql("UPDATE PERSON SET HEIGHT_IN_METERS = ? WHERE id = ?")
        .params(person.heightInMeters, person.id)
        .update()
}

This has the downside that it potentially runs three queries instead of one. You could also build a dynamic efficient query with string concatenation, which I wouldn’t recommend.

One solution that might be acceptable but inefficient, is to retrieve the entire person object and then update one field, and store it again:

package net.leejjon.crud.database

import io.github.oshai.kotlinlogging.KotlinLogging
import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import org.springframework.http.HttpStatusCode
import org.springframework.jdbc.core.simple.JdbcClient
import org.springframework.jdbc.support.GeneratedKeyHolder
import org.springframework.jdbc.support.KeyHolder
import org.springframework.stereotype.Service
import org.springframework.web.server.ResponseStatusException
import java.time.LocalDate
import java.time.format.DateTimeParseException

@Service
class DbService(
    private val jdbcClient: JdbcClient
) {
    private val logger = KotlinLogging.logger {}
   
    // OTHER METHODS

    fun getPerson(id: Int): java.util.Optional<Person> =
        jdbcClient.sql("SELECT * FROM PERSON WHERE ID = ?")
            .params(id)
            .query(PersonRowMapper())
            .optional()

    fun updatePerson(person: Person): Person {
        val update = try {
            jdbcClient.sql("UPDATE PERSON SET FULL_NAME = ?, DATE_OF_BIRTH = ?, HEIGHT_IN_METERS = ? WHERE ID = ?")
                .params(person.fullName, person.dateOfBirth, person.heightInMeters, person.id)
                .update()
        } catch (e: Exception) {
            logger.error(e) { "Unable to delete person due to error with the query or connection" }
            throw ResponseStatusException(HttpStatusCode.valueOf(500))
        }
        if (update == 1) {
            val personFromDb = getPerson(person.id)
            if (personFromDb.isPresent) {
                return personFromDb.get()
            } else {
                logger.error { "Could not update person with id $person.id" }
                throw ResponseStatusException(HttpStatusCode.valueOf(404))
            }
        } else {
            logger.error { "Could not update person with id $person.id" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }

    fun updatePersonAttributes(personId: Int, updatedFields: Map<String, Any?>): Person {
        val person = getPerson(personId)
        if (person.isPresent) {
            val updatedPerson = person.get().copy(
                fullName = updatedFields["fullName"] as? String ?: person.get().fullName,
                dateOfBirth = parseLocalDate(updatedFields["dateOfBirth"] as? String) ?: person.get().dateOfBirth,
                heightInMeters = updatedFields["heightInMeters"] as? Double ?: person.get().heightInMeters
            )
            return updatePerson(updatedPerson)
        } else {
            logger.error { "Could not update person with id $person.id" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }

    private fun parseLocalDate(date: String?): LocalDate? {
        if (date == null) return null
        return try {
            LocalDate.parse(date)
        } catch (e: DateTimeParseException) {
            null
        }
    }
}

In most cases this will be fine, but this implementation is inefficient because it does two queries, first retrieving the current data, and then an update. The full code example can be found on GitHub.

If you need efficient partial updates, I advise you to look to other solutions.

Pros VS Cons of Spring JDBC

Advantages of using Spring JDBC:

No magic. The query you write is being executed on the database.

Disadvantages of using Spring JDBC:

You need to write RowMappers to put the database data into Java or Kotlin classes.
For UPDATES where you ONLY want to update a few specific fields, you need to make your query dynamic with string concatenation OR write lots of very similar queries. Warning: string concatenation can lead to SQL Injection vulnerabilities.
If you have queries that read data and you want FILTER fields on certain columns that the end user selects, you need to make your query dynamic with string concatenation OR write lots of similar queries. Warning: string concatenation can lead to SQL Injection vulnerabilities.

Less SQL, more Java with JPA (and thus Hibernate)

Hibernate is a library that makes it easy to map Java classes to SQL database tables). When I first used it I was excited. But then saw people without SQL knowledge do crazy stuff in Java code that could be done way more efficient in SQL. Java later introduced JPA (Java Persistence API) of which Hibernate became the default implementation in Spring.

Hibernate and JPA are a powerful tool that can solve the partial update problem we encountered when using the JdbcClient. However, when Kotlin was still relatively new there were some problems like:

Kotlin and JPA — a good match or a recipe for failure?

How to use Hibernate in Kotlin in the most efficient way

blog.kotlin-academy.com

But in 2025 it mostly works fine. Let’s update the code we have so far to use JPA. Or if you don’t want to code along, look at the hibernate-jpa branch on GitHub.

Configuring JPA

In the build.gradle.kts:

Add the JPA plugin for kotlin to the plugins section:

plugins {
 kotlin("jvm") version "1.9.24"
 kotlin("plugin.spring") version "1.9.24"
 kotlin("plugin.jpa") version "1.9.24" // You need to add this line
 id("org.springframework.boot") version "3.3.2"
 id("io.spring.dependency-management") version "1.1.6"
}

Replace the spring-boot-starter-jdbc with the spring-boot-starter-jpa:

//  implementation("org.springframework.boot:spring-boot-starter-jdbc")
    implementation("org.springframework.boot:spring-boot-starter-data-jpa")

In the application.properties file, add the following properties:

# Automatically convert camelcase attributes to snakecase database columns in the PersonEntity.
# https://stackoverflow.com/questions/50858718/how-to-make-spring-data-jpa-by-default-naming-from-camelcase-to-snake-case-u
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy
spring.jpa.hibernate.ddl-auto=none
spring.jpa.defer-datasource-initialization=true

The spring.jpa.hibernate.ddl-auto property is an interesting one, you can let Hibernate generate your tables based on your Kotlin classes. However as we already have a working database with the schema.sql script let’s disable it and keep the existing tables.

In the your main class that starts your Spring Boot Application (in my case it was named WaysToCrudApplication.kt) you should add the @EnableJPARepositories annotation:

package net.leejjon.crud

import io.swagger.v3.oas.annotations.OpenAPIDefinition
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.data.jpa.repository.config.EnableJpaRepositories

@SpringBootApplication
@OpenAPIDefinition
@EnableJpaRepositories(
 basePackages = [
  "net.leejjon.crud.database"
 ]
)
class WaysToCrudApplication

Creating a JPA Entity object

In the JDBC solution we had the PersonRowMapper to give back a Person object. Delete that file. We are going to create a PersonEntity.kt class:

package net.leejjon.crud.database

import jakarta.persistence.Entity
import jakarta.persistence.GeneratedValue
import jakarta.persistence.GenerationType
import jakarta.persistence.Id
import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import java.time.LocalDate

@Entity(name = "PERSON")
class PersonEntity(
    var fullName: String,
    var dateOfBirth: LocalDate,
    var heightInMeters: Double
) {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int? = null
}

fun NewPerson.toPersonEntity() = PersonEntity(
    this.fullName,
    this.dateOfBirth,
    this.heightInMeters
)

fun Person.toPersonEntity() = PersonEntity(
    this.fullName,
    this.dateOfBirth,
    this.heightInMeters
)

fun PersonEntity.toPerson(): Person = Person(
    this.id!!,
    this.fullName,
    this.dateOfBirth,
    this.heightInMeters
)

I added some extension functions to map the existing Person and NewPerson class to a PersonEntity and back.

Creating a JPA Repository

This is actually very simple.

package net.leejjon.crud.database

import org.springframework.data.repository.CrudRepository
import org.springframework.stereotype.Repository

@Repository
interface PersonRepository : CrudRepository<PersonEntity, Int>

Updating the DbService

In your DbService.kt file you need to use the PersonRepository to implement the CRUD scenarios:

package net.leejjon.crud.database

import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import org.springframework.stereotype.Service
import java.util.*
import kotlin.jvm.optionals.getOrNull

@Service
class DbService(
    private val personRepository: PersonRepository
) {
    fun getPersonsFromDb(): List<Person> =
        personRepository.findAll().toList().map { it.toPerson() }

    fun getPerson(id: Int): Person? =
        personRepository.findById(id).getOrNull()?.toPerson()

    fun createPerson(person: NewPerson): Person {
        return personRepository.save(person.toPersonEntity()).toPerson()
    }

    fun deletePerson(id: Int) {
        personRepository.deleteById(id)
    }

    fun updatePerson(person: Person): Person? {
        val existingPersonOptional: Optional<PersonEntity> = personRepository.findById(person.id)

        return if (existingPersonOptional.isPresent) {
            val existingPerson = existingPersonOptional.get()
            existingPerson.fullName = person.fullName
            existingPerson.dateOfBirth = person.dateOfBirth
            existingPerson.heightInMeters = person.heightInMeters
            personRepository.save(existingPerson).toPerson()
        } else {
            null
        }
    }
}

As you can see there are no more SQL queries in this class. We don’t have to change anything in the integration tests (Nice!). So run them again:

./gradlew test

Implementing the PATCH endpoint

With the JDBC implementation I explained that it is not suited for writing or constructing queries. We can use the Criteria API. For that we need access to the EntityManager. Create a second repository called PersonCriteriaRepository.kt in the database package. We pass the entitymanager through the constructor:

package net.leejjon.crud.database

import jakarta.persistence.EntityManager
import jakarta.persistence.PersistenceContext
import jakarta.transaction.Transactional
import org.springframework.http.HttpStatusCode
import org.springframework.stereotype.Repository
import org.springframework.web.server.ResponseStatusException
import java.time.LocalDate

@Repository
class PersonCriteriaRepository(
    @PersistenceContext
    val em: EntityManager
) {

    @Transactional
    fun updatePersonAttributes(personId: Int, updatedFields: Map<String, Any?>) {
        if (updatedFields.isNotEmpty() && !updatedFields.containsValue(null)) {
            val cb = em.criteriaBuilder
            val cu = cb.createCriteriaUpdate(PersonEntity::class.java)
            val person = cu.from(PersonEntity::class.java)
            for (fieldToUpdate in updatedFields.entries.iterator()) {
                val castedFieldToUpdateValue = when (fieldToUpdate.key) {
                    "fullName" -> fieldToUpdate.value as String
                    "dateOfBirth" -> LocalDate.parse(fieldToUpdate.value as String)
                    "heightInMeters" -> fieldToUpdate.value as Double
                    else -> throw ResponseStatusException(HttpStatusCode.valueOf(400))
                }

                cu.set(fieldToUpdate.key, castedFieldToUpdateValue)
            }
            cu.where(cb.equal(person.get<Int>("id"), personId))

            em.createQuery(cu).executeUpdate()
        } else {
            throw ResponseStatusException(HttpStatusCode.valueOf(400))
        }
    }
}

This method constructs an update query where we dynamically update fields that are passed in the updatedFields map. The values in this map are of the type Any to support all kinds of attributes, so we cast the attribute to a class based on the attribute name before we insert it to the update criteria object.

In the DbService.kt you need to pass the new PersonCriteriaRepository.kt and add the following function:

package net.leejjon.crud.database

import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import org.springframework.stereotype.Service
import java.util.*
import kotlin.jvm.optionals.getOrNull

@Service
class DbService(
    private val personRepository: PersonRepository,
    private val personCriteriaRepository: PersonCriteriaRepository
) {
   // Other methods
    fun updatePersonAttributes(personId: Int, updatedFields: Map<String, Any?>): Person? {
        personCriteriaRepository.updatePersonAttributes(personId, updatedFields)
        val updatedPerson = personRepository.findById(personId)
        return if (updatedPerson.isPresent) {
            updatedPerson.get().toPerson()
        } else {
            null
        }
    }
}

This calls the method to update the person attributes, and retrieve the entire updated person via the existing personRepository afterwards.

Now in the PersonController.kt, add the patch endpoint:

@PatchMapping("/v1/persons/{id}")
fun updatePerson(@PathVariable id: Int, @RequestBody updatedFields: Map<String, Any?>): ResponseEntity<Person> {
    val updatedPerson = dbService.updatePersonAttributes(id, updatedFields)
    return if (updatedPerson == null) {
        ResponseEntity.notFound().build()
    } else {
        ResponseEntity.ok(updatedPerson)
    }
}

Now the PATCH endpoint should work. Run the tests using ./gradlew test to verify if everything works.

Again, you can find the full code here.

Pros VS Cons of JPA

Advantages of using JPA:

It makes it easy to map database tables to Kotlin classes.
It provides a safer way to construct dynamic queries.
It’s a mature project.

Disadvantages of using JPA:

You can’t use immutable Kotlin data classes as Entity. You need to use mutable classes.
The criteria builder and it’s classes are typically Java. Not everything is immutable.

The (JetBrains) Exposed framework

Exposed calls itself an ORM framework and lightweight SQL library at the same time. The main feature here is that this is a Kotlin library, so it uses all the nice Kotlin syntax sugar.

In the build.gradle.kts add the exposed spring boot starter:

// implementation("org.springframework.boot:spring-boot-starter-jdbc")
implementation("org.jetbrains.exposed:exposed-spring-boot-starter:0.58.0")
implementation("org.jetbrains.exposed:exposed-java-time:0.58.0")

In your main class with the @SpringBootApplication annotation, add the @ImportAutoConfiguration like below to make Exposed work:

package net.leejjon.crud

import io.swagger.v3.oas.annotations.OpenAPIDefinition
import org.jetbrains.exposed.spring.autoconfigure.ExposedAutoConfiguration
import org.springframework.boot.autoconfigure.ImportAutoConfiguration
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration
import org.springframework.boot.runApplication

@SpringBootApplication
@OpenAPIDefinition
@ImportAutoConfiguration(
    value = [ExposedAutoConfiguration::class],
    exclude = [DataSourceTransactionManagerAutoConfiguration::class]
)
class WaysToCrudApplication

fun main(args: Array<String>) {
    runApplication<WaysToCrudApplication>(*args)
}

https://github.com/JetBrains/Exposed/tree/main/samples/exposed-spring

The Exposed version of PersonEntity.kt

You have to create a PersonEntity.kt file like we did with the JPA approach, but this time it looks like this:

package net.leejjon.crud.database

import net.leejjon.crud.model.Person
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.ResultRow
import org.jetbrains.exposed.sql.javatime.date

object PersonEntity : IntIdTable("PERSON") {
    val fullName = varchar("FULL_NAME", 50)
    val dateOfBirth = date("DATE_OF_BIRTH")
    val heightInMeters = double("HEIGHT_IN_METERS")
}

fun ResultRow.toPerson(): Person = Person(
    id = this[PersonEntity.id].value,
    fullName = this[PersonEntity.fullName],
    dateOfBirth = this[PersonEntity.dateOfBirth],
    heightInMeters = this[PersonEntity.heightInMeters]
)

The DbService.kt is implemented like this:

package net.leejjon.crud.database

import io.github.oshai.kotlinlogging.KotlinLogging
import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction
import org.springframework.http.HttpStatusCode
import org.springframework.stereotype.Service
import org.springframework.web.server.ResponseStatusException
import java.time.LocalDate

@Service
class DbService {
    private val logger = KotlinLogging.logger {}
    fun getPersonsFromDb(): List<Person> = transaction {
        PersonEntity.selectAll().map {
                row -> row.toPerson()
        }
    }

    fun getPerson(id: Int): Person? = transaction {
        PersonEntity.selectAll().where { PersonEntity.id.eq(id) }.firstOrNull()?.toPerson()
    }

    fun createPerson(person: NewPerson): Person? = transaction {
        PersonEntity.insert {
            it[fullName] = person.fullName
            it[dateOfBirth] = person.dateOfBirth
            it[heightInMeters] = person.heightInMeters
        }.resultedValues!!.firstOrNull()?.toPerson()
    }

    fun deletePerson(id: Int) = transaction {
        val update = PersonEntity.deleteWhere { PersonEntity.id.eq(id) }
        if (update != 1) {
            logger.error { "Could not find person with id $id" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }

    fun updatePerson(person: Person): Person = transaction {
        val update = PersonEntity.update({ PersonEntity.id.eq(person.id) }) {
            it[fullName] = person.fullName
            it[dateOfBirth] = person.dateOfBirth
            it[heightInMeters] = person.heightInMeters
        }

        if (update != 1) {
            logger.error { "Could not find person with id ${person.id}" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        } else {
            val updatedPersonFromDb = getPerson(person.id)
            if (updatedPersonFromDb != null) {
                return@transaction updatedPersonFromDb
            } else {
                logger.error { "Could not update person with id $person.id" }
                throw ResponseStatusException(HttpStatusCode.valueOf(500))
            }
        }
    }

    fun updatePersonAttributes(personId: Int, updatedFields: Map<String, Any?>): Person = transaction {
        val filterColumnAndValue = updatedFields.entries.map {
            return@map when (it.key) {
                "fullName" -> Pair(PersonEntity.fullName, it.value as String)
                "dateOfBirth" -> Pair(PersonEntity.dateOfBirth, LocalDate.parse(it.value as String))
                "heightInMeters" -> Pair(PersonEntity.heightInMeters, it.value as Double)
                else -> throw ResponseStatusException(HttpStatusCode.valueOf(400))
            }
        }

        val update = PersonEntity.update({
            PersonEntity.id.eq(personId)
        }) {
            filterColumnAndValue.forEach { (column, value) ->
                it[column as Column<Any>] = value
            }
        }

        if (update != 1) {
            logger.error { "Could not find person with id ${personId}" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        } else {
            val updatedPersonFromDb = getPerson(personId)
            if (updatedPersonFromDb != null) {
                return@transaction updatedPersonFromDb
            } else {
                logger.error { "Could not update person with id $personId" }
                throw ResponseStatusException(HttpStatusCode.valueOf(500))
            }
        }
    }
}

Run the PersonsApiIntegrationTests.kt file again to see that our queries created with Exposed work well.

Check out the full code of the Exposed version of this CRUD service on GitHub.

Pros VS Cons of Exposed

Advantages of using Exposed:

Exposed lets you write code that is closer to SQL. No annotations that can feel like magic.
Exposed is a Kotlin library. This means classes will be immutable and it has a fancy DSL for crafting queries.
Exposed is backed by Jetbrains.

Disadvantages of using Exposed:

I don’t think you can use it from Java easily.
It is newer technology and therefore less mature than Jdbc and JPA.

I personally had a pretty good first production experience with Exposed and I will be using Exposed in future projects.

Java Object Oriented Querying, also known as jOOQ

I think I learned about jOOQ on Twitter. Lukas Eder used to post weird SQL things he discovered when implementing parts of jOOQ (unfortunately I think he deleted his “X” account). I feel I just had to include jOOQ as an option in this blog post because of the following difference:

Hibernate and Exposed require you to write classes that match with database tables, jOOQ does it the other way around. You can let jOOQ take a look at either SQL files or have it connect to a running database and generate Java classes that you can use to query the database.

The documentation can be found on https://www.jooq.org/.

The licensing

You can use jOOQ for free with Open Source databases. For commercial databases it costs money. The jOOQ founder explained how their licensing works in this blog post. Don’t forget to include the correct license files in your project if you are going to put it online.

Adding jOOQ to your project

Add the org.jooq.jooq-codegen-gradle plugin to the plugins section of your build.gradle.kts:

plugins {
    kotlin("jvm") version "1.9.24"
    kotlin("plugin.spring") version "1.9.24"
    id("org.springframework.boot") version "3.3.2"
    id("io.spring.dependency-management") version "1.1.6"
    id("org.jooq.jooq-codegen-gradle") version "3.19.18"
}

You need to add a bunch of jOOQ dependencies and the spring-boot-starter-jooq:

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.springframework.boot:spring-boot-starter-jooq")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    // https://springdoc.org/#general-overview
    implementation("org.springdoc:springdoc-openapi-starter-webmvc-ui:2.6.0")
    implementation("io.github.oshai:kotlin-logging-jvm:7.0.0")
    implementation("org.jooq:jooq:3.19.18")
    implementation("org.jooq:jooq-meta-extensions:3.19.18")

    runtimeOnly("com.h2database:h2")
    jooqCodegen("org.jooq:jooq-meta-extensions:3.19.18")

    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.jetbrains.kotlin:kotlin-test-junit5")
    testRuntimeOnly("org.junit.platform:junit-platform-launcher")
    testImplementation ("io.rest-assured:rest-assured:5.5.0")
    testImplementation ("io.rest-assured:kotlin-extensions:5.5.0")
}

jOOQ code generation

As I said earlier, jOOQ generates Java classes from the SQL definition of your tables. We have defined our database structure in the schema.sql file. We need to tell the jOOQ codegeneration plugin to use this same file. In your build.gradle.kts file add:

jooq {
    configuration {
       generator {
          name = "org.jooq.codegen.JavaGenerator"
          database {
             name = "org.jooq.meta.extensions.ddl.DDLDatabase"
             properties {

                // Specify the location of your SQL script.
                // You may use ant-style file matching, e.g. /path/**/to/*.sql
                //
                // Where:
                // - ** matches any directory subtree
                // - * matches any number of characters in a directory / file name
                // - ? matches a single character in a directory / file name
                property {
                   key = "scripts"
                   value = "src/main/resources/schema.sql"
                }

                // The sort order of the scripts within a directory, where:
                //
                // - semantic: sorts versions, e.g. v-3.10.0 is after v-3.9.0 (default)
                // - alphanumeric: sorts strings, e.g. v-3.10.0 is before v-3.9.0
                // - flyway: sorts files the same way as flyway does
                // - none: doesn't sort directory contents after fetching them from the directory
                property {
                   key = "sort"
                   value = "semantic"
                }

                // The default schema for unqualified objects:
                //
                // - public: all unqualified objects are located in the PUBLIC (upper case) schema
                // - none: all unqualified objects are located in the default schema (default)
                //
                // This configuration can be overridden with the schema mapping feature
                property {
                   key = "unqualifiedSchema"
                   value = "none"
                }

                // The default name case for unquoted objects:
                //
                // - as_is: unquoted object names are kept unquoted
                // - upper: unquoted object names are turned into upper case (most databases)
                // - lower: unquoted object names are turned into lower case (e.g. PostgreSQL)
                property {
                   key = "defaultNameCase"
                   value = "as_is"
                }
             }
          }
          target {
             packageName = "net.leejjon.crud.database.model"
             directory = "$buildDir/generated-src/jooq"
          }
       }
    }
}

We can try out the code generation by running:

./gradlew jooqCodegen

If succesful, you can find the generated code in your build folder:

To use these generated classes in your Kotlin code, we need to mark this generated-src/jooq folder as a source directory. We can do that by adding the following code in the build.gradle.kts file:

java {
    toolchain {
       languageVersion = JavaLanguageVersion.of(21)
    }
    sourceSets["main"].java {
       // Add the generated jOOQ source folder to the main source set
       srcDir("$buildDir/generated-src/jooq")
    }
}

This should make it possible for your DbService.kt to import the generated files:

Because both my Person class and the one jOOQ generated are named Person, I used an import alias **PersonTable** to be able to use it easily.

One more thing we should add is the following lines to the build.gradle.kts to make sure the jooqCodegen command is ran automatically before compiling the Kotlin code (otherwise everybody that clones your repo and runs ./gradlew build will have their build fail due to the jOOQ code not being generated):

tasks.named("compileKotlin") {
    dependsOn("jooqCodegen") 
}

Configuring jOOQ in Spring

Before we can do anything with the imported classes, we first need to autowire the DslContext into our DbService.kt. Therefore we need to define some beans. You could put them in a class with the @Configuration annotation, however I just thew it in my application starter class called WaysToCrudApplication.kt:

package net.leejjon.crud

import io.swagger.v3.oas.annotations.OpenAPIDefinition
import net.leejjon.crud.database.ExceptionTranslator
import org.jooq.SQLDialect
import org.jooq.impl.DataSourceConnectionProvider
import org.jooq.impl.DefaultConfiguration
import org.jooq.impl.DefaultDSLContext
import org.jooq.impl.DefaultExecuteListenerProvider
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.context.annotation.Bean
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy
import org.springframework.transaction.annotation.EnableTransactionManagement
import javax.sql.DataSource

@SpringBootApplication
@EnableTransactionManagement
@OpenAPIDefinition
class WaysToCrudApplication {

 @Autowired
 lateinit var dataSource: DataSource

 @Bean
 fun connectionProvider(): DataSourceConnectionProvider {
  return DataSourceConnectionProvider(TransactionAwareDataSourceProxy(dataSource))
 }

 @Bean
 fun exceptionTransformer(): ExceptionTranslator {
  return ExceptionTranslator()
 }

 @Bean
 fun dsl(): DefaultDSLContext {
  return DefaultDSLContext(configuration())
 }

 @Bean
 fun configuration(): DefaultConfiguration {
  val jooqConfiguration = DefaultConfiguration()
  jooqConfiguration.set(connectionProvider())
  jooqConfiguration.set(DefaultExecuteListenerProvider(exceptionTransformer()))

  val dialect = SQLDialect.valueOf("H2")
  jooqConfiguration.set(dialect)

  return jooqConfiguration
 }
}

fun main(args: Array<String>) {
 runApplication<WaysToCrudApplication>(*args)
}

With the jOOQ configuration set up, update the DbService.kt file:

package net.leejjon.crud.database

import io.github.oshai.kotlinlogging.KotlinLogging
import net.leejjon.crud.model.NewPerson
import net.leejjon.crud.model.Person
import org.jooq.DSLContext
import org.springframework.beans.factory.annotation.Autowired

import org.springframework.http.HttpStatusCode
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import org.springframework.web.server.ResponseStatusException
import java.time.LocalDate

import net.leejjon.crud.database.model.tables.Person as PersonTable

@Service
class DbService {
    private val logger = KotlinLogging.logger {}

    @Autowired
    lateinit var dsl: DSLContext

    fun getPersonsFromDb(): List<Person> {
        val persons = dsl.select().from(PersonTable.PERSON).fetch()
        return persons.map {
            Person(
                it.get(PersonTable.PERSON.ID),
                it.get(PersonTable.PERSON.FULL_NAME),
                it.get(PersonTable.PERSON.DATE_OF_BIRTH),
                it.get(PersonTable.PERSON.HEIGHT_IN_METERS)
            )
        }
    }

    fun getPerson(id: Int): Person? {
        val person = dsl.select().from(PersonTable.PERSON)
            .where(PersonTable.PERSON.ID.eq(id))
            .fetchOne()
        return if (person != null) Person(
            person.get(PersonTable.PERSON.ID),
            person.get(PersonTable.PERSON.FULL_NAME),
            person.get(PersonTable.PERSON.DATE_OF_BIRTH),
            person.get(PersonTable.PERSON.HEIGHT_IN_METERS)
        ) else null
    }

    @Transactional
    fun createPerson(person: NewPerson): Person? {
        val createdPerson = dsl.insertInto(PersonTable.PERSON)
            .set(PersonTable.PERSON.FULL_NAME, person.fullName)
            .set(PersonTable.PERSON.DATE_OF_BIRTH, person.dateOfBirth)
            .set(PersonTable.PERSON.HEIGHT_IN_METERS, person.heightInMeters)
            .returningResult(PersonTable.PERSON.ID)
            .fetchOne()
        if (createdPerson != null) {
            val createdPersonId = createdPerson.into(Integer::class.java)
            return getPerson(createdPersonId.toInt())
        } else {
            logger.error { "Unable to create person $person" }
            throw ResponseStatusException(HttpStatusCode.valueOf(500))
        }
    }

    fun deletePerson(id: Int) {
        val deletedRecord = dsl.delete(PersonTable.PERSON).where(PersonTable.PERSON.ID.eq(id)).execute()

        if (deletedRecord == 0) {
            logger.error { "Could not find person with id $id" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }

    @Transactional
    fun updatePerson(person: Person): Person {
        val updatedRecords = dsl.update(PersonTable.PERSON)
            .set(PersonTable.PERSON.FULL_NAME, person.fullName)
            .set(PersonTable.PERSON.DATE_OF_BIRTH, person.dateOfBirth)
            .set(PersonTable.PERSON.HEIGHT_IN_METERS, person.heightInMeters)
            .where(PersonTable.PERSON.ID.eq(person.id))
            .execute()
        if (updatedRecords == 1) {
            return getPerson(person.id)!!
        } else {
            logger.error { "Could not find person with id ${person.id}" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }

    @Transactional
    fun updatePersonAttributes(personId: Int, updatedFields: Map<String, Any?>): Person {
        // Stack overflow told me to use the UpdateSetStep.set(Record) function
        // https://stackoverflow.com/questions/42923028/jooq-dynamic-set
        val recordWithWhereStatements = dsl.newRecord(PersonTable.PERSON)

        for (fieldToUpdate in updatedFields.entries.iterator()) {
            when (fieldToUpdate.key) {
                "fullName" -> recordWithWhereStatements.set(PersonTable.PERSON.FULL_NAME, fieldToUpdate.value as String)
                "dateOfBirth" -> recordWithWhereStatements.set(
                    PersonTable.PERSON.DATE_OF_BIRTH,
                    LocalDate.parse(fieldToUpdate.value as String)
                )

                "heightInMeters" -> recordWithWhereStatements.set(
                    PersonTable.PERSON.HEIGHT_IN_METERS,
                    fieldToUpdate.value as Double
                )

                else -> throw ResponseStatusException(HttpStatusCode.valueOf(400))
            }
        }

        val updatedRecords =
            dsl.update(PersonTable.PERSON)
                .set(recordWithWhereStatements)
                .where(PersonTable.PERSON.ID.eq(personId))
                .execute()
        if (updatedRecords == 1) {
            return getPerson(personId)!!
        } else {
            logger.error { "Could not find person with id ${personId}" }
            throw ResponseStatusException(HttpStatusCode.valueOf(404))
        }
    }
}

As you can see it looks a lot like SQL. Run the integration tests again and everything should work. The final jOOQ code can be found here on GitHub.

Pros and Cons of jOOQ

Advantages of using jOOQ:

It reads like SQL queries. I like this DSL.
It works with Java.
You can generate models easily from existing databases.
Open source (except for the tests)

Disadvantages of using jOOQ:

If you want to use it together with commercial databases, you need to buy a license.

Follow me for more!

The source code of all four approaches can be found in this GitHub project (there is a branch per implementation). If you enjoyed this blog post, please follow me on Medium, X or LinkedIN.

I recently reached 100+ followers on Medium. Thank you all for that. I only post a few articles per year and I do take my time to put good working code examples along with my posts (unlike the many people who spam Medium with AI generated content and hope to generate passive income out of it).

If there are any mistakes in my blog, let me know!