@Query for custom JPQL in Spring Boot - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a custom JPQL query to find users by their email.

Spring Boot

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

Drag options to blanks, or click blank then click option'

A:email

B?1

Cemail

D?email

Attempts:

3 left

💡 Hint

Common Mistakes

Using positional parameters like ?1 instead of named parameters.

Omitting the colon before the parameter name.

The correct syntax for named parameters in JPQL is :parameterName. Here, :email matches the method parameter email.

2fill in blank

medium

Complete the code to write a JPQL query that selects users with age greater than a given value.

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.age [1] :age")
    List<User> findUsersOlderThan(int age);
}

Drag options to blanks, or click blank then click option'

A<

B<=

C=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of >.

Using = which would select users with exact age.

The query should select users whose age is greater than the given parameter, so the operator is >.

3fill in blank

hard

Fix the error in the JPQL query to correctly select users by their last name ignoring case.

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE LOWER(u.lastName) = [1]")
    List<User> findByLastNameIgnoreCase(String lastName);
}

Drag options to blanks, or click blank then click option'

ALOWER(:lastName)

B:lastName.toLowerCase()

ClastName.toLowerCase()

D:lastName

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to call Java methods on parameters inside JPQL.

Not using the colon for named parameters.

JPQL supports the LOWER() function on parameters as well. To compare ignoring case, apply LOWER() to the parameter :lastName.

4fill in blank

hard

Fill both blanks to write a JPQL query that selects users whose first name starts with a given prefix.

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.firstName [1] :prefix")
    List<User> findByFirstNameStartingWith([2] prefix);
}

Drag options to blanks, or click blank then click option'

ALIKE

BString

Cint

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of LIKE for pattern matching.

Using int type for a string prefix parameter.

The JPQL operator for pattern matching is LIKE. The method parameter type for a prefix string is String.

5fill in blank

hard

Fill all three blanks to write a JPQL query that selects users with age between two values.

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.age BETWEEN [1] AND [2]")
    List<User> findUsersBetweenAges([3] minAge, [3] maxAge);
}

Drag options to blanks, or click blank then click option'

A:minAge

B:maxAge

Cint

DString

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting colons before parameter names in JPQL.

Using String type for age parameters.

The JPQL BETWEEN clause uses named parameters :minAge and :maxAge. The method parameters for ages should be of type int.

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