Native SQL with @Query for Complex Scenarios

1. JPQL vs. Native SQL: A Quick Refresher

Before we write our first native query in Spring, it's essential to solidify the difference between JPQL (Java Persistence Query Language) and native SQL. While they look similar, they operate at different levels of abstraction.

To summarize the key points:

• JPQL is an object-oriented query language. You write queries against your Entity classes and their fields, and the JPA provider (Hibernate) translates them into the appropriate SQL for your configured database.
• Native SQL is the raw SQL you would write directly in a database client like MySQL Workbench. It's powerful but tied to a specific database dialect.

2. Writing Native Queries with @Query

Spring Data JPA makes it seamless to switch from JPQL to native SQL. You use the same @Query annotation but with an additional attribute: nativeQuery = true.

When you set this flag, Spring Data JPA no longer attempts to interpret the query as JPQL. Instead, it passes the query string directly to the underlying database via JDBC.

Let's look at a simple example.

Here is a comparative example based on what you just read. Imagine a Student entity that maps to a students table.

JPQL Query:

@Query("SELECT s FROM Student s WHERE s.age > 21")
List<Student> findOlderStudentsJPQL();

• Refers to the Student entity.
• Uses the age field of the entity.

Native SQL Query:

@Query(
  value = "SELECT * FROM students WHERE age > 21", 
  nativeQuery = true)
List<Student> findOlderStudentsNative();

• Sets nativeQuery = true.
• Refers to the students table.
• Can use * because it's standard SQL.
• The results will still be mapped to the Student entity, provided the column names in the result set (*) match the fields defined in the entity.

3. Handling Dynamic Parameters in Native Queries

Hardcoded queries are of limited use. Just like with JPQL, you can pass method parameters into your native queries. You have two main syntaxes for this:

1. Positional Parameters (?): Parameters are referenced by their position (?1, ?2, etc.). This is simple but can be error-prone if the order of parameters changes.
2. Named Parameters (:): Parameters are referenced by name (:paramName). This requires using the @Param("paramName") annotation on the corresponding method argument but is much more readable and maintainable. Named parameters are the recommended approach.

Now, let's see a more complex example that reflects a real-world use case.

This example shows how you can construct a fairly complex, parameterized query while still leveraging Spring Data JPA's ability to execute it and map the results back to your entities.

4. When Should You Use Native Queries?

Derived queries and JPQL should be your default choices because they are database-agnostic and work well with the object-oriented paradigm. However, you should use native queries for complex scenarios where the alternatives fall short.

Here are the primary reasons to use native SQL:

• Using Database-Specific Features: You need to use a function or keyword that is unique to your database and not part of the standard SQL supported by JPQL. For example, using hierarchical queries in Oracle (CONNECT BY) or vendor-specific JSON functions. Since you have experience with MySQL, an example would be using GROUP_CONCAT() to aggregate strings.
• Highly Complex Joins and Subqueries: While JPQL supports joins and subqueries, some extremely complex query structures can be more clearly and efficiently expressed in native SQL.
• Performance Tuning: Native queries allow you to use database-specific query hints (e.g., forcing the use of a particular index) to optimize a critical query that isn't performing well.
• Calling Stored Procedures: While there are other ways to call them, using @Query can be a straightforward method for simple cases.

The trade-off is clear: you gain power and control at the cost of portability. If you write a native query for MySQL and later decide to migrate to PostgreSQL, you may need to rewrite that query.

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 EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(
      value = "SELECT * FROM employees WHERE department = :dept AND salary > :minSal",
      nativeQuery = true
    )
    List<Employee> findTopEarnersInDepartment(
        @Param("dept") String department, 
        @Param("minSal") double minSalary
    );
}

Conclusion

You have now completed your foundational toolkit for querying data in Spring Data JPA. You can use derived queries for simple cases, JPQL with @Query for custom logic, and now native SQL with @Query(nativeQuery = true) for maximum power and control.

Key Takeaways:

• Native SQL queries are executed directly by the database, allowing you to use vendor-specific features that JPQL doesn't support.
• To enable native SQL in a repository method, you use the @Query annotation with the nativeQuery = true attribute.
• Your query string must use actual table and column names, not entity and field names.
• You can pass parameters to your native queries using positional (?1) or named (:name with @Param) placeholders, with named parameters being the preferred approach for clarity and maintainability.
• Use native queries judiciously for complex scenarios where derived queries or JPQL are insufficient, as they reduce database portability.

What's Next?

So far in this module, we've focused heavily on how to write queries to get data out of the database. In the next lesson, we will shift our focus to the infrastructure that manages the communication itself. We will explore a critical component for any production-grade application: database connection pooling. You will learn its role and benefits, focusing on HikariCP, the default connection pool in Spring Boot.