Welcome to the next step in mastering Spring Data JPA! In our previous lessons, we delved into the essentials of JDBC, JPA, and Spring Data JPA, learning how to create and use JPA repositories and derived query methods. Today, we're going to take it a step 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:

Java
Copy to clipboard
1package 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
8import java.util.List;
9
10public interface TodoRepository extends JpaRepository<TodoItem, Long> {
11
12    @Query("SELECT t FROM TodoItem t WHERE t.title LIKE %:title%")
13    List<TodoItem> findByTitleUsingQuery(@Param("title") String title);
14}

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, while SQL deals directly with tables and columns. This distinction provides a more intuitive and Java-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. Fortunately, 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:

Java
Copy to clipboard
1package 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
8import java.util.List;
9
10public interface TodoRepository extends JpaRepository<TodoItem, Long> {
11
12    @Query(value = "SELECT * FROM todo_item WHERE title LIKE %:title%", nativeQuery = true)
13    List<TodoItem> findByTitleUsingNativeQuery(@Param("title") String title);
14}

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:

Java
Copy to clipboard
1package 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
8import java.util.List;
9
10public interface TodoRepository extends JpaRepository<TodoItem, Long> {
11
12    // JPQL query to find items by description
13    @Query("SELECT t FROM TodoItem t WHERE t.description = :description")
14    List<TodoItem> findByDescription(@Param("description") String description);
15
16    // JPQL query with join clause
17    @Query("SELECT t FROM TodoItem t JOIN t.category c WHERE c.name = :categoryName")
18    List<TodoItem> findByCategoryName(@Param("categoryName") String categoryName);
19
20    // Native SQL query to find completed items
21    @Query(value = "SELECT * FROM todo_item WHERE is_completed = true", nativeQuery = true)
22    List<TodoItem> findCompletedItems();
23
24    // JPQL query to find items created before a certain date
25    @Query("SELECT t FROM TodoItem t WHERE t.creationDate < :date")
26    List<TodoItem> findItemsCreatedBefore(@Param("date") LocalDate date);
27}

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!