@Query for custom JPQL in Spring Boot - Step-by-Step Execution

Concept Flow - @Query for custom JPQL

Define Repository Interface

↓

Add @Query Annotation with JPQL

↓

Spring Data Parses JPQL Query

↓

Method Called in Service

↓

JPQL Query Executed on Database

↓

Results Returned to Caller

Shows how a custom JPQL query is defined in a repository, parsed by Spring Data, executed, and results returned.

Execution Sample

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
  @Query("SELECT u FROM User u WHERE u.email = :email")
  User findByEmail(@Param("email") String email);
}

Defines a repository method with a custom JPQL query to find a User by email.

Execution Table

Step	Action	JPQL Query	Parameter	Result
1	Call findByEmail("[e-mail]")	SELECT u FROM User u WHERE u.email = :email	email = "[e-mail]"	Query prepared with parameter
2	Spring Data executes JPQL	SELECT u FROM User u WHERE u.email = '[e-mail]'	email = "[e-mail]"	Database runs query
3	Database returns matching User entity	N/A	N/A	User object with email [e-mail]
4	Method returns User to caller	N/A	N/A	User object returned

💡 Query completes after returning matching User or null if none found

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
email	N/A	"[e-mail]"	"[e-mail]"	"[e-mail]"	"[e-mail]"
JPQL Query	N/A	"SELECT u FROM User u WHERE u.email = :email"	"SELECT u FROM User u WHERE u.email = '[e-mail]'"	N/A	N/A
Result	N/A	Query prepared	Query executed	User entity fetched	User returned

Key Moments - 3 Insights

Why do we use :email in the JPQL query instead of directly putting the email string?

Using :email is a parameter placeholder. It helps prevent errors and SQL injection. The actual value is set later, as shown in execution_table step 1.

What happens if no User matches the email?

The query returns null or empty result. The method then returns null, as the database found no matching entity (see execution_table step 3).

Can we use native SQL instead of JPQL in @Query?

Yes, by adding nativeQuery = true in @Query. But here we use JPQL which works with entity objects, not tables directly.

Visual Quiz - 3 Questions

Look at the execution_table, what is the JPQL query after Step 2?

A "SELECT u FROM User u WHERE u.email = '[e-mail]'"

B "SELECT * FROM users WHERE email = '[e-mail]'"

C "SELECT u FROM User u WHERE u.email = :email"

D "SELECT u FROM User u"

💡 Hint

Check the JPQL Query column in execution_table row for Step 2

At which step does the database actually run the query?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the Action column describing when Spring Data executes JPQL

If the parameter email was changed to "[e-mail]", what changes in the execution_table?

A Only the Parameter column changes to email = "[e-mail]"

B The JPQL Query column changes to use '[e-mail]' instead of '[e-mail]'

C Both Parameter and JPQL Query columns change accordingly

D No changes in the table

💡 Hint

Check how parameter substitution affects JPQL Query and Parameter columns in steps 1 and 2

Concept Snapshot

@Query lets you write custom JPQL in Spring Data repositories.
Use :paramName as placeholders for parameters.
Spring Data parses and runs the JPQL when method is called.
Results map to entity objects returned by the method.
Use @Param to bind method parameters to JPQL placeholders.

Full Transcript

This visual shows how @Query with custom JPQL works in Spring Boot. First, you define a repository interface method and annotate it with @Query containing JPQL. The JPQL uses placeholders like :email. When the method is called with an argument, Spring Data prepares the query by replacing placeholders with actual values. Then it executes the JPQL on the database. The database returns matching entity objects. Finally, the method returns these objects to the caller. Variables like the email parameter and JPQL query string change step-by-step as the query is prepared and run. Key points include why placeholders are used for safety and flexibility, what happens if no results are found, and that JPQL works with entities, not raw tables. The quiz checks understanding of query substitution, execution timing, and parameter effects.

Practice

1. What is the main purpose of using @Query annotation in Spring Data JPA?

easy

A. To write custom JPQL or SQL queries when default methods are insufficient

B. To automatically generate database tables

C. To configure database connection properties

D. To define entity relationships

Solution

1. Step 1: Understand default query methods

   Spring Data JPA provides default query methods like findById, but they are limited.

2. Step 2: Role of @Query

   @Query allows writing custom JPQL or SQL queries to handle complex or specific data retrieval needs.

3. Final Answer:

   To write custom JPQL or SQL queries when default methods are insufficient -> Option A

4. Quick Check:

   @Query purpose = custom queries [OK]

Hint: Remember @Query is for custom queries beyond defaults [OK]

Common Mistakes:

• Thinking @Query creates tables
• Confusing @Query with database config
• Assuming @Query defines entity relations

2. Which of the following is the correct syntax to define a custom JPQL query using @Query in a Spring Data JPA repository interface?

easy

A. @Query("SELECT u FROM User u WHERE u.name = :name") List<User> findByName(@Param("name") String name);

B. @Query(SELECT * FROM User WHERE name = :name) List<User> findByName(String name);

C. @Query("SELECT * FROM User WHERE name = ?1") List<User> findByName(String name);

D. @Query("FROM User WHERE name = ?") List<User> findByName(@Param("name") String name);

Solution

1. Step 1: Check JPQL syntax

   JPQL uses entity names and fields, not table names or * syntax.

2. Step 2: Parameter binding

   Named parameters use :paramName and must be linked with @Param("paramName") in method.

3. Final Answer:

   @Query("SELECT u FROM User u WHERE u.name = :name") List<User> findByName(@Param("name") String name); -> Option A

4. Quick Check:

   JPQL + named param + @Param = correct syntax [OK]

Hint: Use entity names and :param with @Param for correct JPQL [OK]

Common Mistakes:

• Using SQL syntax (*) instead of JPQL
• Missing @Param annotation for named parameters
• Using positional parameters incorrectly

3. Given the repository method:

@Query("SELECT u FROM User u WHERE u.age > :minAge")
List<User> findUsersOlderThan(@Param("minAge") int minAge);

What will be the result of calling findUsersOlderThan(30)?

medium

A. A list of User entities with age less than 30

B. A list of User entities with age equal to 30

C. A list of User entities with age greater than 30

D. A runtime error due to missing parameter

Solution

1. Step 1: Analyze the JPQL query

   The query selects users where age is greater than the parameter minAge.

2. Step 2: Understand method call

   Calling findUsersOlderThan(30) sets minAge to 30, so users older than 30 are returned.

3. Final Answer:

   A list of User entities with age greater than 30 -> Option C

4. Quick Check:

   minAge=30, query > minAge = users older than 30 [OK]

Hint: Check parameter value and query condition carefully [OK]

Common Mistakes:

• Confusing > with >= or =
• Assuming parameter is ignored
• Expecting users younger than 30

4. Identify the error in the following repository method:

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

medium

A. JPQL query uses wrong entity name

B. Missing @Param annotation for the email parameter

C. Return type should be User, not List<User>

D. Query should use native SQL syntax

Solution

1. Step 1: Check parameter binding

   The query uses a named parameter :email, so the method parameter must have @Param("email") annotation.

2. Step 2: Validate other parts

   Entity name User is correct, return type List<User> is valid, and JPQL syntax is correct.

3. Final Answer:

   Missing @Param annotation for the email parameter -> Option B

4. Quick Check:

   Named param requires @Param annotation [OK]

Hint: Always add @Param for named parameters in @Query [OK]

Common Mistakes:

• Omitting @Param causes runtime errors
• Confusing JPQL with native SQL
• Assuming return type must be single entity

5. You want to write a custom JPQL query using @Query to find all users whose name contains a given substring (case insensitive). Which of the following method definitions correctly achieves this?

hard

A. @Query("SELECT u FROM User u WHERE u.name LIKE :namePart") List<User> findByNameLike(@Param("namePart") String namePart);

B. @Query("SELECT u FROM User u WHERE u.name LIKE '%:namePart%'") List<User> findByNameContains(@Param("namePart") String namePart);

C. @Query("SELECT u FROM User u WHERE u.name = :namePart") List<User> findByNameExact(@Param("namePart") String namePart);

D. @Query("SELECT u FROM User u WHERE LOWER(u.name) LIKE LOWER(CONCAT('%', :namePart, '%'))") List<User> findByNameContainsIgnoreCase(@Param("namePart") String namePart);

Solution

1. Step 1: Understand case insensitive search

   Use LOWER() on both field and parameter to ignore case.

2. Step 2: Use LIKE with wildcards

   Concatenate '%' before and after parameter to find substring matches.

3. Step 3: Check parameter binding

   Named parameter :namePart is linked with @Param("namePart") correctly.

4. Final Answer:

   @Query("SELECT u FROM User u WHERE LOWER(u.name) LIKE LOWER(CONCAT('%', :namePart, '%'))") List<User> findByNameContainsIgnoreCase(@Param("namePart") String namePart); -> Option D

5. Quick Check:

   LOWER + LIKE + CONCAT + @Param = correct case-insensitive contains [OK]

Hint: Use LOWER() and CONCAT('%', param, '%') for case-insensitive contains [OK]

Common Mistakes:

• Using LIKE with parameter inside quotes disables binding
• Not using LOWER() for case insensitivity
• Using = instead of LIKE for substring search