Querying Data with Query Methods

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:

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;

import java.util.List;

public interface TodoRepository extends JpaRepository<TodoItem, Long> {

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

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.