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.