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:
Kotlin1package com.codesignal.repositories 2 3import com.codesignal.entities.TodoItem 4import org.springframework.data.jpa.repository.JpaRepository 5import org.springframework.data.jpa.repository.Query 6import org.springframework.data.repository.query.Param 7 8interface TodoRepository : JpaRepository<TodoItem, Long> { 9 10 @Query("SELECT t FROM TodoItem t WHERE t.title LIKE %:title%") 11 fun findByTitleUsingQuery(@Param("title") title: String): List<TodoItem> 12}
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:
| Operation | JPQL | SQL |
|---|---|---|
| Select all | SELECT t FROM TodoItem t | SELECT * FROM todo_item |
| Condition | WHERE t.title = 'Read Book' | WHERE title = 'Read Book' |
| Join | JOIN t.category c | INNER JOIN categories c ON t.category_id = c.id |
JPQL is object-oriented and works with classes and fields, offering a more Kotlin-centric way of querying your data in JPQL.
In some cases, you might need to use database-specific SQL features or optimize performance, necessitating the use of native queries. Spring Data JPA allows you to write native SQL queries using the @Query annotation with the nativeQuery attribute set to true. Here's an example:
Kotlin1package com.codesignal.repositories 2 3import com.codesignal.entities.TodoItem 4import org.springframework.data.jpa.repository.JpaRepository 5import org.springframework.data.jpa.repository.Query 6import org.springframework.data.repository.query.Param 7 8interface TodoRepository : JpaRepository<TodoItem, Long> { 9 10 @Query(value = "SELECT * FROM todo_item WHERE title LIKE %:title%", nativeQuery = true) 11 fun findByTitleUsingNativeQuery(@Param("title") title: String): List<TodoItem> 12}
This query uses native SQL to perform a search similar to our previous example but directly on the database tables. The nativeQuery = true attribute tells Spring Data JPA that this is a native SQL query.
To further demonstrate the versatility of the @Query annotation, here are a few more examples showcasing different types of queries you can create using this powerful feature:
Kotlin1package com.codesignal.repositories 2 3import com.codesignal.entities.TodoItem 4import org.springframework.data.jpa.repository.JpaRepository 5import org.springframework.data.jpa.repository.Query 6import org.springframework.data.repository.query.Param 7import java.time.LocalDate 8 9interface TodoRepository : JpaRepository<TodoItem, Long> { 10 11 // JPQL query to find items by description 12 @Query("SELECT t FROM TodoItem t WHERE t.description = :description") 13 fun findByDescription(@Param("description") description: String): List<TodoItem> 14 15 // JPQL query with join clause 16 @Query("SELECT t FROM TodoItem t JOIN t.category c WHERE c.name = :categoryName") 17 fun findByCategoryName(@Param("categoryName") categoryName: String): List<TodoItem> 18 19 // Native SQL query to find completed items 20 @Query(value = "SELECT * FROM todo_item WHERE is_completed = true", nativeQuery = true) 21 fun findCompletedItems(): List<TodoItem> 22 23 // JPQL query to find items created before a certain date 24 @Query("SELECT t FROM TodoItem t WHERE t.creationDate < :date") 25 fun findItemsCreatedBefore(@Param("date") date: LocalDate): List<TodoItem> 26}
These examples illustrate using the @Query annotation for various scenarios, including simple JPQL queries, queries with joins, and native SQL queries. This versatility allows you to tailor your data retrieval logic to meet your application's needs effectively.
In this lesson, we've expanded our querying capabilities by learning how to define complex custom queries using the @Query annotation. We covered the difference between JPQL and SQL, emphasizing the object-oriented nature of JPQL and providing examples to illustrate these differences. Moreover, you learned that writing native SQL queries is possible and can be beneficial in certain situations. As you move forward, you will apply these concepts in the upcoming practice exercises, which will solidify your understanding and provide hands-on experience with custom queries in Spring Data JPA. Get ready for some exciting challenges ahead!