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:

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.

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:

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:

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!