Native Queries in Spring Data JPA : @Query Annotation

pring Data JPA is powerful. In most cases, you can get away with method naming conventions like findByUsername, or using derived queries. But sometimes, you need more control — like using custom SQL or JPQL (Java Persistence Query Language).

This is where the @Query annotation and native queries in Spring Data come in.

🧠 What You’ll Learn

• What is the @Query annotation?
• Difference between JPQL and native SQL
• How to write native queries
• Named parameters, pagination, projections
• Best practices

🔍 What is the @Query Annotation?

Spring Data JPA allows you to define custom queries using the @Query annotation in repository interfaces.

@Query("SELECT u FROM User u WHERE u.email = ?1")
User findByEmail(String email);

You can write:

• JPQL (uses entity names and field names)
• Native SQL (directly uses table and column names)

🔄 JPQL vs Native SQL

🧪 1. Writing a JPQL Query with @Query

Suppose we have this entity:

@Entity
public class User {
    @Id @GeneratedValue
    private Long id;
    private String name;
    private String email;
}

Now in the UserRepository:

@Query("SELECT u FROM User u WHERE u.email = ?1")
User findByEmail(String email);

You can also use named parameters:

@Query("SELECT u FROM User u WHERE u.email = :email")
User findByEmail(@Param("email") String email);

🛠️ 2. Writing a Native Query

Use nativeQuery = true to write raw SQL.

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

Or with named parameters:

@Query(value = "SELECT * FROM users WHERE email = :email", nativeQuery = true)
User findByEmailNativeNamed(@Param("email") String email);

🔁 3. Returning a List of Results

@Query("SELECT u FROM User u WHERE u.name LIKE %:keyword%")
List searchByName(@Param("keyword") String keyword);

For native:

@Query(value = "SELECT * FROM users WHERE name ILIKE %:keyword%", nativeQuery = true)
List searchByNameNative(@Param("keyword") String keyword);

📄 4. DTO Projection from Native Query

Instead of returning entities, you can project selected fields into a DTO:

DTO

public class UserSummary {
    private String name;
    private String email;

    public UserSummary(String name, String email) {
        this.name = name;
        this.email = email;
    }

    // Getters
}

Query

@Query("SELECT new com.example.UserSummary(u.name, u.email) FROM User u")
List getUserSummaries();

✅ This is constructor-based projection in JPQL.

For native SQL, use an interface projection:

Interface DTO

public interface UserSummaryView {
    String getName();
    String getEmail();
}

Query

@Query(value = "SELECT name, email FROM users", nativeQuery = true)
List fetchUserSummaries();

⏳ 5. Pagination with @Query

Use Pageable as a parameter.

@Query("SELECT u FROM User u")
Page getAllUsers(Pageable pageable);

🚨 6. Update or Delete with @Query

You must add @Modifying and @Transactional.

@Modifying
@Transactional
@Query("UPDATE User u SET u.name = :name WHERE u.email = :email")
int updateNameByEmail(@Param("name") String name, @Param("email") String email);

For Delete:

@Modifying
@Transactional
@Query("DELETE FROM User u WHERE u.email = :email")
int deleteByEmail(@Param("email") String email);

✅ Best Practices

🧹 Summary

Reference

JPA Query Methods