Native SQL queries in Spring Boot - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this native SQL query execution in Spring Boot?

Consider a Spring Boot repository method using a native query to fetch user names from a table named users. What will be the output list if the table contains three users: Alice, Bob, and Carol?

Spring Boot

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

// Assume the users table has names: Alice, Bob, Carol

A [{"name":"Alice"}, {"name":"Bob"}, {"name":"Carol"}]

B [User{name='Alice'}, User{name='Bob'}, User{name='Carol'}]

C ["Alice", "Bob", "Carol"]

D Empty list []

💡 Hint

The query selects only the 'name' column and returns a list of strings.

Since the query selects only the 'name' column and the method returns List, the output is a list of user names as strings.

📝 Syntax

intermediate

Which native query syntax is correct for updating a user's email in Spring Boot?

You want to update the email of a user with a specific id using a native SQL query in a Spring Boot repository. Which option is syntactically correct?

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Modifying
    @Query(value = "...", nativeQuery = true)
    int updateUserEmailById(String email, Long id);
}

A UPDATE users SET email = ?1 WHERE id = ?2

B UPDATE users SET email = :email WHERE id = :id

C update users set email = :email, id = :id

D update users set email = ?1 where id = ?2

💡 Hint

Native queries are case-insensitive but usually uppercase keywords are preferred. Positional parameters use ?1, ?2.

Option A uses correct SQL syntax with uppercase keywords and positional parameters matching method parameters.

🔧 Debug

advanced

Why does this native query cause a runtime error in Spring Boot?

Given the following repository method, why does executing it cause a runtime exception?

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM users WHERE age > :age", nativeQuery = true)
    List<User> findUsersOlderThan(int age);
}

A Native queries cannot use WHERE clauses

B The User entity does not map all columns returned by SELECT *

C The method should return List<String> instead of List<User>

D The parameter :age is not supported in native queries

💡 Hint

Check if the entity matches the table columns exactly.

If the User entity lacks mapping for some columns returned by SELECT *, Hibernate throws a runtime error.

❓ state_output

advanced

What is the value of 'count' after executing this native query update?

In a Spring Boot repository, you execute this native query to delete users older than 30. What is the value of the returned count?

Spring Boot

public interface UserRepository extends JpaRepository<User, Long> {
    @Modifying
    @Query(value = "DELETE FROM users WHERE age > 30", nativeQuery = true)
    int deleteUsersOlderThan30();
}

// Assume 5 users are older than 30 in the database.

A 0

B -1

C Throws exception

D 5

💡 Hint

The return value of a modifying query is the number of rows affected.

The method returns the number of rows deleted, which is 5 in this case.

🧠 Conceptual

expert

Which statement about native SQL queries in Spring Boot is TRUE?

Select the correct statement about using native SQL queries with Spring Data JPA in Spring Boot.

A Native queries require manual mapping if the result does not match the entity structure.

B Native queries always bypass the entity manager and cannot return entity objects.

C Native queries automatically support JPQL features like entity relationships and inheritance.

D Native queries cannot use parameters and must concatenate values directly.

💡 Hint

Think about how native SQL differs from JPQL in mapping results.

Native queries return raw SQL results; if the result columns don't match entity fields, manual mapping is needed.

Practice

1. What annotation is used in Spring Boot to define a native SQL query inside a repository interface?

easy

A. @SqlQuery

B. @NativeQuery

C. @Query with nativeQuery = true

D. @SQLNative

Solution

1. Step 1: Recognize the annotation for queries in Spring Data JPA

   The @Query annotation is used to define custom queries in repository interfaces.

2. Step 2: Identify how to specify native SQL

   Setting nativeQuery = true inside @Query tells Spring Boot to treat the query as native SQL.

3. Final Answer:

   @Query with nativeQuery = true -> Option C

4. Quick Check:

   Native SQL queries use @Query(nativeQuery = true) [OK]

Hint: Look for @Query with nativeQuery true flag [OK]

Common Mistakes:

• Using a non-existent annotation like @NativeQuery
• Forgetting to set nativeQuery = true
• Confusing @Query with @SqlQuery
• Using @SQLNative which is invalid

2. Which of the following is the correct syntax to write a native SQL query in a Spring Boot repository method?

easy

A. @Query(value = "SELECT * FROM users", nativeQuery = true)

B. @Query(native = true, value = "SELECT * FROM users")

C. @NativeQuery("SELECT * FROM users")

D. @Query(sql = "SELECT * FROM users")

Solution

1. Step 1: Recall the correct attribute names in @Query

   The attribute for the query string is value, and to mark it native SQL, use nativeQuery = true.

2. Step 2: Check each option's syntax

   @Query(value = "SELECT * FROM users", nativeQuery = true) correctly uses @Query(value = "...", nativeQuery = true). Options B, C, and D use invalid attribute names or annotations.

3. Final Answer:

   @Query(value = "SELECT * FROM users", nativeQuery = true) -> Option A

4. Quick Check:

   @Query(value=..., nativeQuery=true) is correct syntax [OK]

Hint: Use value= for query and nativeQuery=true [OK]

Common Mistakes:

• Using native=true instead of nativeQuery=true
• Using @NativeQuery annotation which doesn't exist
• Using sql= instead of value= for query string
• Swapping attribute order incorrectly

3. Given this repository method:

@Query(value = "SELECT * FROM products WHERE price > ?1", nativeQuery = true)
List<Product> findExpensiveProducts(double minPrice);

What will be the result of calling findExpensiveProducts(100.0)?

medium

A. List of products with price less than 100.0

B. Empty list always

C. Syntax error due to ?1 placeholder

D. List of products with price greater than 100.0

Solution

1. Step 1: Understand the native SQL query with parameter

   The query selects all products where price is greater than the first parameter (?1), which is passed as 100.0.

2. Step 2: Predict the method output

   Calling findExpensiveProducts(100.0) returns products priced above 100.0, so the list contains those products.

3. Final Answer:

   List of products with price greater than 100.0 -> Option D

4. Quick Check:

   Native query with ?1 uses method parameter [OK]

Hint: ?1 matches first method parameter in native query [OK]

Common Mistakes:

• Thinking ?1 is invalid in native queries
• Confusing greater than with less than
• Assuming empty list without data
• Believing syntax error due to placeholder

4. Identify the error in this native query method:

@Query(value = "SELECT * FROM orders WHERE status = :status", nativeQuery = true)
List<Order> findByStatus(String status);

medium

A. Named parameter :status is not supported in native queries

B. Query string should use ?1 instead of :status

C. Missing nativeQuery = true flag

D. Method return type should be Optional<Order>

Solution

1. Step 1: Check parameter usage in native queries

   Native SQL queries in Spring Boot do not support named parameters like :status by default; they require positional parameters like ?1.

2. Step 2: Identify correct parameter syntax

   The query should use ?1 to refer to the first method parameter instead of :status.

3. Final Answer:

   Query string should use ?1 instead of :status -> Option B

4. Quick Check:

   Native queries use positional parameters like ?1 [OK]

Hint: Use ?1 for parameters in native queries, not :name [OK]

Common Mistakes:

• Using named parameters in native queries
• Forgetting nativeQuery = true
• Assuming return type must be Optional
• Confusing JPQL and native SQL syntax

5. You want to write a native SQL query in Spring Boot to update the price of all products in a category. Which method signature and annotation is correct?

hard

A. @Modifying
@Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1", nativeQuery = true)
int increasePriceByCategory(String category);

B. @Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1", nativeQuery = true)
void increasePriceByCategory(String category);

C. @Modifying
@Query(value = "UPDATE products SET price = price * 1.1 WHERE category = :category", nativeQuery = true)
int increasePriceByCategory(String category);

D. @Modifying
@Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1")
int increasePriceByCategory(String category);

Solution

1. Step 1: Recognize update queries need @Modifying

   In Spring Boot, native update queries require the @Modifying annotation to indicate a modifying operation.

2. Step 2: Check parameter syntax and nativeQuery flag

   @Modifying @Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1", nativeQuery = true) int increasePriceByCategory(String category); correctly uses positional parameter ?1 and sets nativeQuery = true. @Modifying @Query(value = "UPDATE products SET price = price * 1.1 WHERE category = :category", nativeQuery = true) int increasePriceByCategory(String category); uses named parameter which is invalid in native queries. @Modifying @Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1") int increasePriceByCategory(String category); misses nativeQuery flag. @Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1", nativeQuery = true) void increasePriceByCategory(String category); misses @Modifying.

3. Final Answer:

   @Modifying @Query(value = "UPDATE products SET price = price * 1.1 WHERE category = ?1", nativeQuery = true) int increasePriceByCategory(String category); -> Option A

4. Quick Check:

   Update native queries need @Modifying and nativeQuery=true [OK]

Hint: Use @Modifying and nativeQuery=true for update queries [OK]

Common Mistakes:

• Omitting @Modifying on update queries
• Using named parameters in native queries
• Forgetting nativeQuery=true flag
• Returning void instead of int for update count