How to Use Native Query in Spring Boot: Simple Guide

In Spring Boot, you can use native SQL queries by adding the @Query annotation with the attribute nativeQuery = true on repository methods. This lets you write raw SQL directly for complex or optimized queries that JPQL can't handle.

Syntax

Use the @Query annotation on a repository method with your SQL query as a string. Set nativeQuery = true to tell Spring Data JPA this is a native SQL query.

• @Query(value = "SQL_QUERY", nativeQuery = true): Defines the native SQL query.
• Method signature: Matches the expected return type (entity, list, or scalar).

java

public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
    User findByEmailNative(String email);
}

Example

This example shows a Spring Boot repository using a native query to find a user by email from the users table. It demonstrates how to write the query and call it from your service.

java

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.util.List;

@Entity
public class User {
    @Id
    private Long id;
    private String name;
    private String email;
    // getters and setters
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
    User findByEmailNative(String email);

    @Query(value = "SELECT * FROM users WHERE name LIKE %?1%", nativeQuery = true)
    List<User> findByNameContainsNative(String namePart);
}

// Usage in a service class
@Service
public class UserService {
    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public User getUserByEmail(String email) {
        return userRepository.findByEmailNative(email);
    }

    public List<User> searchUsersByName(String namePart) {
        return userRepository.findByNameContainsNative(namePart);
    }
}

Output

Calling getUserByEmail("[e-mail]") returns the User entity with that email if found; calling searchUsersByName("Al") returns a list of users whose names contain "Al".

Common Pitfalls

• Not setting nativeQuery = true causes Spring Data JPA to treat the query as JPQL, which can lead to errors.
• Using incorrect table or column names that don't match the database schema causes runtime SQL errors.
• Parameter placeholders must match method parameters in order and count.
• Native queries bypass JPA's automatic mapping if the result doesn't match the entity structure.

java

public interface UserRepository extends JpaRepository<User, Long> {
    // Wrong: missing nativeQuery=true
    @Query("SELECT * FROM users WHERE email = ?1")
    User findByEmailWrong(String email);

    // Correct
    @Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
    User findByEmailCorrect(String email);
}

Quick Reference

Feature	Description	Example
@Query annotation	Defines the SQL query on repository methods	@Query(value = "SELECT * FROM users", nativeQuery = true)
nativeQuery attribute	Set to true to use native SQL instead of JPQL	nativeQuery = true
Parameter binding	Use ?1, ?2... for method parameters in order	SELECT * FROM users WHERE email = ?1
Return types	Can return entity, List, or scalar values	User findByEmailNative(String email)

Key Takeaways

Use @Query with nativeQuery = true to run raw SQL in Spring Boot repositories.

Ensure SQL syntax matches your database schema exactly to avoid errors.

Match method parameters with query placeholders in order and count.

Native queries bypass JPQL features but allow complex SQL operations.

Always test native queries to confirm correct mapping and results.