Complex Querying with Spring Data JPA and Kotlin

Welcome to the next step in mastering Spring Data JPA! In our previous lessons, we explored the fundamentals of JDBC, JPA, and Spring Data JPA, learning how to create and use JPA repositories and derived query methods. Today, we're going to advance further by exploring how to write complex custom SQL queries using the @Query annotation, enabling us to handle scenarios where derived queries are insufficient.

In scenarios where your query is too complex to be handled by derived queries or when you require precise control over the query syntax, the @Query annotation comes to the rescue. This annotation allows you to define your custom queries directly within your repository interfaces. Here's a simple example:

package com.codesignal.repositories

import com.codesignal.entities.TodoItem
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param

interface TodoRepository : JpaRepository<TodoItem, Long> {

    @Query("SELECT t FROM TodoItem t WHERE t.title LIKE %:title%")
    fun findByTitleUsingQuery(@Param("title") title: String): List<TodoItem>
}

In this example, we're using the @Query annotation to define a Java Persistence Query Language (JPQL) query that searches for TodoItem entities with titles containing the specified string. JPQL is a query language akin to SQL but operates on the entity objects rather than directly on database tables. We will delve into JPQL in greater detail shortly. The @Param("title") annotation binds the method parameter to the query.

Now that we understand how to use the @Query annotation, let's delve into the distinction between JPQL and native queries. By default, the @Query annotation uses JPQL (Java Persistence Query Language), which operates on the entity object model rather than the database tables directly. This abstraction allows for more flexibility and easier maintenance. Here is a comparison between JPQL and SQL:

JPQL is object-oriented and works with classes and fields, offering a more Kotlin-centric way of querying your data in JPQL.