Spring Data JPA - Queries

Derived query: https://www.bezkoder.com/jpa-repository-query/

Custom query: https://www.bezkoder.com/spring-jpa-query/

Query

The @Query annotation is one of the cornerstones of Spring Data JPA. At its core, this annotation provides developers with a mechanism to define custom JPQL (Java Persistence Query Language) and native SQL queries directly on repository methods. But there’s a lot more beneath the surface.

Spring Data JPA - Generated Queries automatically generates necessary queries. However, there are times when these method names become unwieldy or you need more control over the generated query. Enter @Query.

By annotating a repository method with @Query, you can provide a custom query to be executed. This can be a JPQL query or, if you set the nativeQuery flag to true, a native SQL query.

Example using JPQL:

@Query("SELECT u FROM User u WHERE u.email = ?1")
List<User> findByEmail(String email);

Native Queries vs. JPQL While JPQL queries are written in a database-independent manner, focusing on the entities, native queries use pure SQL and are written with database-specific syntax. The nativeQuery attribute lets you define these native SQL queries.

Example using native SQL:

@Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
List<User> findByEmailUsingSQL(String email);

However, using native queries should be done judiciously as they can compromise the portability between databases.

Parameter Binding in @Query

Spring Data JPA provides two types of parameter bindings for @Query: positional and named parameters.

Positional Parameters: They are indexed, starting from 1.

@Query("SELECT u FROM User u WHERE u.email = ?1")
List<User> findByEmail(String email);

Named Parameters: These are more readable and are recommended for queries with multiple parameters. They use a colon followed by the parameter name.

@Query("SELECT u FROM User u WHERE u.email = :email AND u.name = :name")
List<User> findByEmailAndName(@Param("email") String email, @Param("name") String nam

Modifying Queries with @Modifying

By default, @Query is designed for select queries. But what if you want to use it for INSERT, UPDATE, or DELETE operations? The @Modifying annotation comes into play.

When combined with @Query, the @Modifying annotation indicates that the query will modify data.

@Modifying
@Query("UPDATE User u SET u.email = :email WHERE u.id = :id")
void updateUserEmail(@Param("id") Long id, @Param("email") String email);

It’s important to remember that when using @Modifying with a transactional method, the underlying JPA EntityManager may need a call to flush() or clear() for synchronization purposes.

How to capture result set from custom queries joining multiple tables?

Use Interface-based DTO projections. Look at this for reference:

Leveraging Native Queries

Native queries in Spring Data JPA allow you to write plain SQL queries for your entities, bypassing the JPQL abstraction. They are incredibly powerful, especially in situations where JPQL falls short in supporting database-specific features or when you need to optimize a particular query at the database level.

What Are Native Queries?

In contrast to JPQL, which is an abstraction over SQL tailored for JPA entities, native queries are raw SQL queries that you can write within your repository. They are executed directly against the database.

Basic Usage

To define a native query, you can use the @Query annotation in combination with the nativeQuery flag set to true. Here’s a simple example:

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM users WHERE name = ?1", nativeQuery = true)
    List<User> findByName(String name);
}

In this example, the SQL is directly targeting the underlying database table users.

Using Named Parameters

Spring Data JPA supports using named parameters in native queries, enhancing readability:

@Query(value = "SELECT * FROM users WHERE name = :name", nativeQuery = true)
List<User> findByName(@Param("name") String name);

Return Projections with Native Queries

You’re not limited to returning entities. With native queries, you can also use projections:

@Query(value = "SELECT name, email FROM users WHERE name = :name", nativeQuery = true)
List<UserNameAndEmail> findNameAndEmailByName(@Param("name") String name);

Benefits of Using Native Queries

Flexibility: Allows you to harness the full power of SQL, including database-specific features.
Performance: Sometimes, the most efficient way to fetch or process data is by using a finely-tuned SQL query.
Migration: If you’re migrating an application that already has raw SQL queries, native queries can provide an easier migration path.

Limitations of Native Queries With Spring Data JPA

When using native queries, you need to be aware of 2 limitations:

Spring Data JPA and your persistence provider don’t adjust the query to your database’s specific SQL dialect. Because of that, you need to ensure that all RDBMS supported by your application can handle the provided statement.
Pagination of native query results requires an extra step. See Spring Data JPA - Pagination
Spring Data JPA doesn’t support dynamic sorting for native SQL statements.

Considerations and Best Practices

Portability Concerns: Native queries might be tied to specific database dialects. This means that if you switch databases, these queries might need to be rewritten.
Maintenance: Native queries can make your application harder to maintain if overused. You bypass many of the advantages and abstractions provided by JPA.
Error Handling: Errors in native queries might not be as descriptive as with JPQL, so be sure to test thoroughly.
Use Judiciously: It’s tempting to use native queries for their power and flexibility, but always assess if JPQL or Criteria API can achieve the same goal. Keep in mind the trade-offs.

Best Practices with @Query Annotation in Spring Data JPA

Using the @Query annotation gives you a lot of control, but it’s something you want to use thoughtfully. It’s easy to overlook things like performance issues or messy query logic if you’re not paying close attention. A little care upfront—writing clear queries, testing them, and keeping an eye on how they’re used—can help avoid problems later on.

The @Query annotation gives you a way to write exactly the queries you need, without relying on long method names or automatic behavior that doesn’t always fit. It works well for quick lookups, joins, projections, and even raw SQL when nothing else will do. But as with most tools that offer this much control, it’s easy to overdo it or overlook small issues that turn into bigger ones over time. Keeping queries clean, naming them well, testing the tricky ones, and checking back every now and then to make sure they still make sense goes a long way toward keeping your data layer easy to work with as your app grows.

Favor JPQL over Native Queries

It’s usually better to stick with JPQL instead of jumping straight into native SQL. JPQL is built to work with JPA entities, so you don’t have to worry about it breaking if you ever switch to a different database. It also tends to be easier to keep things clean and consistent across your codebase. Unless there’s a specific feature you can’t get through JPQL or you’re trying to squeeze out a bit more speed in a very targeted query, the built-in support from JPQL is often the better path.

Use Projections for Specific Data Retrieval

Fetching only the necessary data can significantly reduce the load on your database and application. Instead of fetching entire entities, consider using projections:

@Query("SELECT new com.example.MyProjection(u.name, u.email) FROM User u")
List<MyProjection> findNamesAndEmails();

Beware of the N+1 Problem

Always be on the lookout for the N+1 problem, especially when working with relationships:

Regularly profile and analyze your queries.
Utilize fetch joins or entity graphs to load related entities efficiently.

Validate and Sanitize Inputs

When you’re dealing with dynamic values or pulling in SpEL expressions, it’s important to handle inputs carefully. Even if everything looks fine on the surface, things can go sideways quickly if the input isn’t what you expect.

Instead of building query strings by hand, use bind parameters. It keeps things cleaner and helps protect against unexpected behavior in the database.

Also, try not to show raw error messages to users. It might seem harmless, but those messages can sometimes reveal too much about how your system is structured or what it’s trying to do behind the scenes. Keeping that information out of reach makes your app a little safer by default.

Limit Result Sets with Pagination

Fetching massive datasets can be a performance nightmare. Always consider pagination:

@Query("SELECT u FROM User u")
Page<User> findAllUsers(Pageable pageable);

Pageable gives you a clean way to keep the result set under control. Instead of pulling in everything at once, you can set how much data comes back and in what order. This makes it easier to work with large tables without overwhelming your application or the client.

Document Complex Queries Clearly

If you’re writing a particularly complex or important query, consider leaving a brief comment above it in the code. It helps others (and future you) understand why the query is written a certain way or what edge case it handles.

// This query retrieves users based on multiple filters including status and signup date
@Query("SELECT u FROM User u WHERE ...")
List<User> someComplexQuery();

Even though comments aren’t part of the query itself, they make the code easier to follow and maintain.

Consistent Naming Conventions

When your method names match what they actually do, everything becomes easier. It helps when you’re adding new features, fixing bugs, or just trying to remember what a method was meant to return. If the query pulls users by status and sorts them by date, naming it that way makes it easier to follow what’s happening without digging through the logic.

Even in smaller projects, sticking to clear names builds habits that make the code easier to read and less frustrating to work with. It turns the repository layer into something predictable instead of something you have to reverse-engineer every time.

Test Your Queries

It sounds basic, but it’s worth saying anyway. If you’re writing queries that do anything more than the simplest filtering, test them. Even a small change like adding a join or switching to a projection can behave differently than expected, especially when you’re working with real data.

An in-memory database like H2 is a solid way to do this without much setup. You can run quick tests that show whether the query returns what you think it should. It also helps catch issues with how data gets mapped back to objects, or if a query fails silently when a parameter is missing. Taking a little time to test now can save a lot of guessing later.

Regularly Review and Refactor

As your app grows and changes, your queries should grow with it. What worked fine early on might start dragging things down once the database fills up or the logic around a feature gets more complex. Sometimes a query written months ago made sense for what the app needed back then, but now it’s pulling too much data or doing extra work behind the scenes that no one notices until things slow down.

Taking time now and then to read through your existing queries helps catch issues before they turn into real problems. You might find places where you can use projections instead of full entities, remove an extra join that’s no longer needed, or rewrite a condition that doesn’t make sense anymore. It doesn’t have to be a full rewrite, just a regular habit of checking in can keep things fast and clean without turning into a big project later.