Native SQL queries in Spring Boot - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a native SQL query in a Spring Data JPA repository.

Spring Boot

@Query(value = "SELECT * FROM users WHERE id = [1]", nativeQuery = true)
User findUserById(Long id);

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

A:id

B?id

C?1

D:userId

Attempts:

3 left

💡 Hint

Common Mistakes

Using named parameters like :id in native queries causes errors.

The placeholder ?1 refers to the first method parameter in native SQL queries.

2fill in blank

medium

Complete the code to execute a native SQL update query in a Spring Data JPA repository.

Spring Boot

@Modifying
@Query(value = "UPDATE products SET price = [1] WHERE id = ?1", nativeQuery = true)
int updatePriceById(Long id, Double price);

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

A:price

B?price

C?1

D?2

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing parameter order and using ?1 for price.

The placeholder ?2 refers to the second method parameter, which is price here.

3fill in blank

hard

Fix the error in the native query to select users with a specific role.

Spring Boot

@Query(value = "SELECT * FROM users WHERE role = [1]", nativeQuery = true)
List<User> findUsersByRole(String role);

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

A?1

B?role

C:role

D:userRole

Attempts:

3 left

💡 Hint

Common Mistakes

Using named parameters like :role causes errors.

In native queries, positional parameter ?1 correctly refers to the first method argument.

4fill in blank

hard

Fill both blanks to create a native query that deletes products by category and returns affected rows.

Spring Boot

@Modifying
@Query(value = "DELETE FROM products WHERE category = [1] AND available = [2]", nativeQuery = true)
int deleteByCategoryAndAvailability(String category, boolean available);

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

A?1

B:category

C?2

D:available

Attempts:

3 left

💡 Hint

Common Mistakes

Using named parameters causes errors in native queries.

Use positional parameters ?1 and ?2 for the first and second method arguments respectively in native queries.

5fill in blank

hard

Fill all three blanks to write a native query that selects users by status and orders by creation date.

Spring Boot

@Query(value = "SELECT * FROM users WHERE status = [1] ORDER BY [2] [3]", nativeQuery = true)
List<User> findUsersByStatusOrdered(String status, String orderBy, String direction);

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

A?1

Bcreated_at

CASC

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to use parameters for column names or order direction causes errors.

The first blank uses positional parameter ?1 for status. The second and third blanks are column name and order direction respectively.