Spring JPA Specifications and Kotlin

I was recently working on a piece of work where the number of parameters in a query is variable based on interaction with a web service. Normally I use Spring JPA repositories but in this particular case, there can be as many as 16 items we need to query for. The number of possible combinations gets ridiculous if we had to create a JPA repository method for each.

I did a bit of investigation and found that Spring JPA supports queryDSL and Specifications. These both conceptually allow you to do the same thing: programmatically build up SQL queries using a builder. I first tried to see how easy it is to use queryDSL, but after fiddling and searching and struggling to find how best to get the Maven generators to work in Gradle, I decided to explore Specifications rather. The documentation for Specifications is not super extensive and there are not any examples of the Kotlinesque way of doing this. After a bit of fiddling, I ended up with the following approach

Gradle file changes

// if you do not have this already, you need this to get the Spring JPA repository and other Spring DB support
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

// this makes it much easier to work with Spring JPA Specifications in Kotlin
implementation "au.com.console:kotlin-jpa-specification-dsl:2.0.0"

Now to use it you have to update the Spring JPA repository to inherit from another interface type JpaSpecificationExecutor:

@Repository
interface PersonRepository : JpaRepository<Person, UUID>, JpaSpecificationExecutor<Person>

The above adds a bunch of interface methods that make it easier to work with Specifications. Have a look here for all the details.

Finally, we update the usage of our repository to take in the specification we build dynamically:

//build the query up - `.equal` extension, `and` and `or` are provided by the kotlin Gradle dependency earlier
val complexQuery = request.people
    .map {
        Person::age.equal(it.age) and
                Person::gender.equal(it.gender) and
                Person::occupation.equal(it.occupation) and
                Person::salary.equal(it.salary)
    }.reduce { acc: Specification<Person>, next: Specification<Person> ->
        acc.or(next)
    }

//use the query - findAll is one of the new methods exposed by implementing JpaSpecificationExecutor
personRepository
    .findAll(Person::country.equal("USA") and
                complexQuery
    )