@Query for custom JPQL in Spring Boot - Deep Dive

Overview - @Query for custom JPQL

What is it?

The @Query annotation in Spring Boot lets you write your own database queries using JPQL, a special language to talk to databases in Java. Instead of relying on automatic query generation, you can specify exactly what data you want and how to get it. This helps when you need complex or specific queries that default methods can't handle.

Why it matters

Without @Query, you would be limited to simple queries automatically created by Spring Data, which might not fit your needs. Writing custom JPQL queries lets you fetch exactly the data you want, improving performance and flexibility. This means your app can handle more complex data requests and work faster.

Where it fits

Before learning @Query, you should understand basic Spring Data repositories and how automatic query methods work. After mastering @Query, you can explore native SQL queries, query optimization, and advanced database interactions in Spring Boot.

Mental Model

Core Idea

@Query lets you write your own precise instructions to the database using JPQL inside Spring Data repositories.

Think of it like...

It's like giving a chef a custom recipe instead of letting them guess what you want from a menu. You tell exactly how to prepare your dish.

Repository Interface
  ├─ Default Methods (auto-generated queries)
  └─ @Query Annotation (custom JPQL queries)

@Query Usage:
  ┌─────────────────────────────┐
  │ @Query("SELECT u FROM User u WHERE u.age > :age") │
  └─────────────────────────────┘
          ↓
  Executes JPQL query with parameters
          ↓
  Returns matching data from database

Build-Up - 7 Steps

1

Foundation

Basics of Spring Data Repositories

Concept: Learn what Spring Data repositories are and how they provide automatic query methods.

Spring Data repositories are interfaces that let you perform database operations without writing SQL. For example, methods like findById or findByName are automatically created based on method names.

Result

You can fetch data from the database using simple method names without writing queries.

Understanding automatic query generation is key to knowing when and why you need custom queries.

2

Foundation

Introduction to JPQL Language

Concept: JPQL is a query language similar to SQL but works with Java objects instead of tables.

JPQL lets you write queries using entity class names and their fields, not database tables and columns. For example, 'SELECT u FROM User u' fetches User objects.

Result

You can write queries that are database-independent and work with Java entities.

Knowing JPQL syntax helps you write precise queries that Spring Data can execute.

3

Intermediate

Using @Query for Custom JPQL

🤔Before reading on: Do you think @Query can only be used for SELECT queries or also for UPDATE/DELETE? Commit to your answer.

Concept: @Query annotation allows you to write any JPQL query directly in your repository method.

You add @Query above a repository method and write JPQL inside quotes. You can use parameters with :name syntax. Example: @Query("SELECT u FROM User u WHERE u.email = :email") User findByEmail(@Param("email") String email);

Result

The method runs your custom JPQL query and returns the result.

Knowing that @Query supports all JPQL operations, not just SELECT, expands your ability to customize data access.

4

Intermediate

Parameter Binding in @Query

🤔Before reading on: Do you think parameters in @Query must match method argument names exactly? Commit to your answer.

Concept: Parameters in JPQL queries are linked to method arguments using named or positional binding.

You can use named parameters like :email and annotate method arguments with @Param("email") to bind them. Alternatively, use ?1, ?2 for positional parameters matching argument order.

Result

Your query receives the correct values from method arguments when executed.

Understanding parameter binding prevents bugs where queries fail due to mismatched or missing parameters.

5

Intermediate

Pagination and Sorting with @Query

Concept: @Query works with Spring Data features like pagination and sorting by accepting Pageable or Sort parameters.

Add Pageable or Sort as method parameters alongside @Query. Spring Data applies these to your query automatically. Example: @Query("SELECT u FROM User u WHERE u.active = true") Page findActiveUsers(Pageable pageable);

Result

You get paged or sorted results from your custom JPQL query.

Combining @Query with pagination and sorting lets you handle large datasets efficiently.

6

Advanced

Using @Modifying with @Query for Updates

🤔Before reading on: Does @Query alone allow modifying data like UPDATE or DELETE? Commit to your answer.

Concept: To run UPDATE or DELETE JPQL queries, you must add @Modifying annotation along with @Query.

Example: @Modifying @Query("UPDATE User u SET u.status = 'inactive' WHERE u.lastLogin < :date") int deactivateOldUsers(@Param("date") LocalDate date); This tells Spring Data the query changes data and needs a transaction.

Result

The database updates records as specified, and the method returns the number of affected rows.

Knowing to use @Modifying prevents silent failures when trying to change data with @Query.

7

Expert

Performance and Caching Considerations

🤔Before reading on: Do you think @Query queries are always faster than automatic queries? Commit to your answer.

Concept: Custom JPQL queries can be optimized for performance but may bypass some Spring Data optimizations or caching.

Writing complex JPQL lets you fetch only needed data, join tables efficiently, or use database functions. However, you must manage caching and transactions carefully to avoid stale data or overhead.

Result

Well-written @Query queries improve app speed but require careful tuning and testing.

Understanding the tradeoffs of custom queries helps you balance flexibility with maintainability and performance.

Under the Hood

When you use @Query, Spring Data reads the JPQL string and converts it into a database-specific SQL query at runtime. It uses the entity metadata to map Java objects to tables and fields. Parameters are injected into the query safely to prevent injection attacks. For modifying queries, Spring manages transactions to ensure data integrity.

Why designed this way?

Spring Data created @Query to give developers control beyond automatic query generation, which is limited to simple patterns. JPQL was chosen because it works with Java entities, making queries portable across databases. This design balances ease of use with power and flexibility.

Repository Method
  ├─ @Query Annotation with JPQL
  │      ↓
  ├─ Spring Data parses JPQL
  │      ↓
  ├─ Converts JPQL to SQL
  │      ↓
  ├─ Executes SQL on Database
  │      ↓
  └─ Maps results back to Java objects

Myth Busters - 4 Common Misconceptions

Quick: Does @Query only support SELECT queries? Commit to yes or no.

Common Belief: Many think @Query is only for fetching data (SELECT).

Reality: @Query supports all JPQL operations including UPDATE and DELETE when combined with @Modifying.

Why it matters: Believing this limits developers from using @Query for data changes, forcing them to write native queries or extra code.

Quick: Do you think method parameter names must exactly match @Query parameter names? Commit to yes or no.

Common Belief: Some assume method argument names must match named parameters in @Query exactly.

Reality: You must use @Param annotations to bind parameters explicitly; method argument names alone don't bind parameters.

Why it matters: Without @Param, queries fail at runtime due to missing parameters, causing bugs that are hard to trace.

Quick: Is using @Query always faster than derived query methods? Commit to yes or no.

Common Belief: Many believe custom @Query queries are always more efficient than automatic queries.

Reality: Sometimes automatic queries are optimized by Spring Data or the database better; poorly written @Query can be slower.

Why it matters: Assuming custom queries are always better can lead to performance regressions and wasted effort.

Quick: Does @Query automatically handle pagination without extra parameters? Commit to yes or no.

Common Belief: Some think @Query paginates results automatically without Pageable parameters.

Reality: Pagination requires passing Pageable or Sort parameters explicitly; @Query alone does not paginate.

Why it matters: Missing Pageable causes full data loads, leading to memory issues and slow responses.

Expert Zone

1

Using SpEL (Spring Expression Language) inside @Query for dynamic queries is powerful but can complicate readability and debugging.

2

Combining @Query with entity graphs can optimize fetching related entities and avoid N+1 query problems.

3

JPQL supports functions and expressions that differ between databases; knowing these helps write portable yet efficient queries.

When NOT to use

Avoid @Query when queries are simple and can be expressed with method names, as automatic queries are easier to maintain. For very complex or database-specific queries, consider native SQL queries with @Query(nativeQuery = true) or use a dedicated query builder.

Production Patterns

In production, @Query is used for complex filters, joins, and batch updates. Teams often centralize queries in repositories for maintainability and use parameter binding to prevent injection. Pagination and sorting with @Query are common for scalable APIs.

Connections

SQL Prepared Statements

Both use parameter binding to safely insert values into queries.

Understanding how @Query binds parameters helps prevent SQL injection just like prepared statements do in raw SQL.

Object-Relational Mapping (ORM)

@Query works on top of ORM by writing queries in terms of objects, not tables.

Knowing ORM principles clarifies why JPQL uses entity names and fields instead of raw database tables.

Natural Language Processing (NLP) Query Parsing

Both involve translating human-readable queries into structured commands for machines.

Seeing @Query as a translation layer helps appreciate the complexity of converting Java code into efficient database queries.

Common Pitfalls

#1 Forgetting to add @Modifying on update/delete queries.

Wrong approach:

@Query("UPDATE User u SET u.active = false WHERE u.lastLogin < :date")
int deactivateUsers(@Param("date") LocalDate date);

Correct approach:

@Modifying
@Query("UPDATE User u SET u.active = false WHERE u.lastLogin < :date")
int deactivateUsers(@Param("date") LocalDate date);

Root cause:

Without @Modifying, Spring Data treats the query as a select and does not execute the update.

#2 Not using @Param annotation for named parameters.

Wrong approach:

@Query("SELECT u FROM User u WHERE u.email = :email")
User findByEmail(String email);

Correct approach:

@Query("SELECT u FROM User u WHERE u.email = :email")
User findByEmail(@Param("email") String email);

Root cause:

Spring Data cannot match method arguments to query parameters without explicit @Param.

#3 Assuming @Query automatically paginates results.

Wrong approach:

@Query("SELECT u FROM User u")
List findAllUsers(Pageable pageable);

Correct approach:

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

Root cause:

Returning List ignores pagination; returning Page enables Spring Data to apply pagination.

Key Takeaways

@Query lets you write custom JPQL queries inside Spring Data repositories for precise data access.

You must use @Param to bind method arguments to named parameters in your JPQL queries.

For update or delete queries, always add @Modifying to tell Spring Data the query changes data.

Combining @Query with pagination and sorting parameters helps handle large datasets efficiently.

Writing efficient JPQL queries requires understanding both JPQL syntax and Spring Data's runtime behavior.