Blog

Simplify database interactions in Spring Boot with JOOQ and Kotlin

Category
Software development
Simplify database interactions in Spring Boot with JOOQ and Kotlin

Picture this common scenario: a web application has many screens, and all of those screens show tables with data. Tables with data require filtering over different columns. 

You’re not alone if you’ve ever found yourself navigating this intricate terrain. The usual go-to solution is either the JPA Criteria API or QueryDSL. But, let me show you how JOOQ and Kotlin, paired together, can be powerful with a small use of generics and JOOQ’s DSLContext / EntityManager. JOOQ is a powerful and developer-friendly Java library that integrates with databases, providing an API for efficient and type-safe SQL query generation.

This blog aims to unravel a specific use case, a puzzle I, too, faced. But, the marriage of JOOQ and Kotlin emerged as the solution, turning database interactions from a hurdle into a harmonious endeavor

Using Kotlin generics, we will introduce GeneralizedSearchService, the star of the show. It will simplify but also enhance your code with flexibility and reusability and remove a lot of boilerplate code. So, let’s start the journey and explore not just the ‘how’ but the ‘why’ behind this powerful pairing.

Configuring JOOQ Code Generation

Firstly, we need to ensure that our project builds and runs with JOOQ. Let’s assume you already have a Spring Boot project with some basic business logic and entities in mind. 

To unlock the full potential of JOOQ and Kotlin, you must set up the correct dependencies in your `build.gradle.kts` file. (Assuming you’re using that build tool and Kotlin script.) The following entries are your gateway to seamless integration:

implementation("org.jooq:jooq-kotlin")
implementation("org.jooq:jooq")
jooqCodegen(platform("org.springframework.boot:spring-boot-dependencies:3.2.2"))
jooqCodegen("org.jooq:jooq-codegen")Code language: Kotlin (kotlin)

In addition, don’t forget to include the JOOQ Docker plugin in your plugin block:

plugins {
    id("dev.monosoul.jooq-docker") version "6.0.14"
}
Code language: Kotlin (kotlin)

If you want a deeper look at what the plugin does, read about the JOOQ Gradle plugin on GitHub.

The base for setting up database interactions

Once you set up the configuration, the real magic happens with the JOOQ code generation tasks defined in `build.gradle.kts`. These tasks are the catalysts needed for transforming SQL DDL queries from Flyway migration files into generated Java entities. Together, they pave the way for database interactions.

tasks {
    generateJooqClasses {
        schemas.set(listOf("public"))
        basePackageName.set("com.example.jooq")
        migrationLocations.setFromFilesystem(
            project.files("$projectDir/src/main/resources/db/migration"),
        )
    }
}Code language: JavaScript (javascript)

Notably, this script ensures the generation of entity records crucial for your queries and clauses. What makes it important is its integration with Docker, which orchestrates a small PostgreSQL database to retrieve these generated entities. These records are necessary for your JOOQ and Kotlin-powered application to have the foundation for robust database interactions.

Make sure you get the schema, base package, and migration location properties right. If they’re not right, the code generation won’t run. The generated code will most likely go to the build folder of your project if you don’t say otherwise in the `generateJooqClasses` task’s outputDirectory property.

JOOQ and Spring Data Hero: GeneralizedSearchService

Let’s meet GeneralizedSearchService. The code we’re about to see shows a versatile and abstract search service designed to work with JOOQ and Spring Data. It provides a generic solution for searching entities based on user-defined criteria. It also incorporates the most important features for any page with a hub or table – pagination, sorting, and filtering. You can see the whole class implementation on my JOOQ Kotlin Github.

abstract class GeneralizedSearchService<ENTITY : AbstractResourceEntity<*>, SORT>(
    private val entityManager: EntityManager,
    dslContext: DSLContext
) where SORT : AbstractSort, SORT : Enum<*> {
    protected abstract val targetClass: KClass<ENTITY>
    protected abstract val targetTable: Table<out Record>
	…	
}
```
abstract class GeneralizedSearchService<ENTITY : AbstractResourceEntity<*>, SORT>(
    private val entityManager: EntityManager,
    dslContext: DSLContext
) where SORT : AbstractSort, SORT : Enum<*> {
    protected abstract val targetClass: KClass<ENTITY>
    protected abstract val targetTable: Table<out Record>
	…	
}

One of the key features of the service is that it utilizes Kotlin’s abstract classes and generics to create a database search functionality applicable to various entity types. The class is parameterized by the entity type (ENTITY) and the sorting type (SORT), which provides flexibility and reusability.

As for search operations, it implements methods `existsByQuery` and `findAllByQuery` to check the existence of entities or retrieve paginated search results. The `existsByQuery` method will check for the existence of entities based on a specified search condition.

    fun existsByQuery(search: AbstractSearch): Boolean {
        val condition = search.toCondition()
        return softDeletionAwareDslContext
            .select(inline(true))
            .whereExists(
                selectOne().from(targetTable).where(condition)
            )
            .query
            .singleOrNull()?.value1() ?: false
    }Code language: JavaScript (javascript)

`findAllByQuery` will fetch a list of entities according to user defined search criteria, with optional sorting, limiting, and offsetting.

    fun findAllByQuery(
        search: AbstractSearch,
        sort: Sort = Sort.unsorted(),
        limit: Int? = 20,
        offset: Int? = 0
    ): List<ENTITY> =
        findByQuery(search, OffsetBasedPageRequest(offset ?: 0, limit ?: 20, sort)).contentCode language: HTML, XML (xml)

This method utilizes a search object of type `AbstractSearch` to specify the conditions within the SQL-like JOOQ query while incorporating offset and limit parameters for pagination purposes.

`fetchWithWindowFunction` will use EntityManager to turn all of the above into a native SQL query, bind parameter values to it, and, lastly, cast the results as object types we expect when calling this method.

    private fun findByQuery(
        search: AbstractSearch,
        pageable: OffsetBasedPageRequest
    ): Page<ENTITY> {
        val condition = search.toCondition()
        val totalCountField = count(asterisk()).over().`as`("__total_count")
        val (records, counterFromWindowFunction) = softDeletionAwareDslContext.with(cteAlias).`as`(
            select(targetTable.asterisk(), rowNumberField)
                .from(targetTable)
                .where(condition)
                .orderBy(orderBy)
        )
            .select(totalCountField, asterisk())
            .from(table(cteAlias))
            .where(rowNumberField.eq(inline(1)))
            .offset(pageable.offset)
            .limit(pageable.pageSize)
            .query
            .fetchWithWindowFunction(entityManager, totalCountField, targetClass.java)
}
    private fun findByQuery(
        search: AbstractSearch,
        pageable: OffsetBasedPageRequest
    ): Page<ENTITY> {
        val condition = search.toCondition()
        val totalCountField = count(asterisk()).over().`as`("__total_count")
        val (records, counterFromWindowFunction) = softDeletionAwareDslContext.with(cteAlias).`as`(
            select(targetTable.asterisk(), rowNumberField)
                .from(targetTable)
                .where(condition)
                .orderBy(orderBy)
        )
            .select(totalCountField, asterisk())
            .from(table(cteAlias))
            .where(rowNumberField.eq(inline(1)))
            .offset(pageable.offset)
            .limit(pageable.pageSize)
            .query
            .fetchWithWindowFunction(entityManager, totalCountField, targetClass.java)
}
Code language: PHP (php)

Using the abstraction for searching entities

We are all set to use the above abstraction as a showcase example. We will create a `PersonSearchService` that implements `GeneralizedSearchService` for a specific entity, in this case, the `Person` entity. Annotated with `@Service` for Spring auto wiring and bean scanning, this class extends our abstract search service and tailors it to the needs of the `Person` entity. Leveraging JOOQ and Spring Data JPA, the service will efficiently search and retrieve information from the PERSONS table.

class PersonSearchService(
    entityManager: EntityManager,
    dslContext: DSLContext
) : GeneralizedSearchService<Person, PersonSort>(entityManager, dslContext) {
    override val targetClass = Person::class
    override val targetTable: Persons = PERSONS
    override fun sortEnumValues() = PersonSort.entries
}Code language: HTML, XML (xml)

The `sortEnumValues` method is overridden to provide the specific sorting criteria defined in the PersonSort enum. This means you can sort your database search by any enum value you add to PersonSort, using constants for name classes generated by the JOOQ codegen.

Custom Search Sort and Pagination

To complete the picture, let’s introduce the implementation of a PersonSearch data class. This class represents the search criteria for the Person entity and implements AbstractSearch, a familiar abstract class name.

data class PersonSearch(
    var expression: String? = null,
) : Serializable, AbstractSearch {
    override fun toCondition() = DSL.noCondition()
        .andFilter(expression) {
            PERSONS.NAME.containsIgnoreCase(it)
        }
}

With JOOQ, typesafe queries are available, allowing the compiler to detect misspelled column names or types instead of relying on non-typesafe method signatures. However, with PersonSearch, enhancements can be made to accommodate multiple conditions for searching your database tables. Subsequently, in a REST controller, a map of path parameters can be received and seamlessly forwarded, resulting in the most generic solution possible. This advantage stands out compared to Spring Data JPA, where introducing QueryDSL is necessary to achieve similar functionality.

To get a brief overview of what data a person has and what data we can use to sort the entities, we can look at how the Person JPA entity looks.

@Entity
@Table(name = "persons")
class Person : AbstractResourceEntity<PersonUid>() {
    @Column(name = "uid", columnDefinition = "VARCHAR(500)", length = 16, unique = true, updatable = false, nullable = false)
    @Convert(converter = UidConverter::class)
    override var uid: PersonUid? = PersonUid()
    @Column(name = "name", columnDefinition = "VARCHAR(500)", nullable = false)
    lateinit var name: String
    override fun toString(): String {
        return "Person(uid=$uid, name='$name')"
    }
}Code language: HTML, XML (xml)

Now that everything is set up, we can finally use our code to search for people—with custom search sort and pagination parameters, of course.

personSearchService.findAllByQuery(
    search = PersonSearch("John"),
    sort = listOf(PersonSortModel(PersonSortTypeModel.MODIFIED, true)).toSort(),
    limit = 10,
    offset = 0
 )Code language: JavaScript (javascript)

The following code looks clean and simple:

  • we need to find people who contain the string `John` in their names, 
  • we want ten of them on a page, 
  • we want to sort them by created date, descending. 

All we need to do next is inject `personSearchService` in a controller somewhere – users that interact with the UI and table filters will dictate the rest.

New dimension of productivity

In an ever-evolving world of software development, the synergy between technologies can often unlock new dimensions of productivity

The fusion of JOOQ and Kotlin is a potent solution for just that. It addresses the challenges posed by database interactions in Spring Boot applications, which stands out from conventional solutions like JPA Criteria API or QueryDSL.

By configuring JOOQ code generation and using a versatile GeneralizedSearchService (see Github repo), we, as developers, can harness a robust and abstract search functionality adaptable to many entity types. The example we’ve shown proves just that – it offers an efficient approach to custom searches, sorting, and pagination. So next time you need a new column to filter by on the UI, fear not – JOOQ and Kotlin are there for you.

Cover Illustration: AI Generated

CONTACT US

Exceptional ideas need experienced partners.