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

Feature	JPQL	Native SQL
Based On	Entity classes	Database tables
Portable	Yes	No (DB-specific syntax)
Uses	`@Query` (default)	`@Query(..., nativeQuery = true)`
Example	`SELECT u FROM User u`	`SELECT * FROM users`

🧪 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<User> searchByName(@Param("keyword") String keyword);

For native:

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

List<User> 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<UserSummary> 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<UserSummaryView> fetchUserSummaries();

⏳ 5. Pagination with @Query

Use Pageable as a parameter.

@Query("SELECT u FROM User u")

Page<User> 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

Tip	Reason
Use JPQL over native when possible	JPQL is portable and safer
Use named parameters	Improves readability
Keep native SQL in a separate file for complex queries	Easier to maintain
Always test native queries against DB schema	DB syntax may vary
Use projections (DTOs/interfaces) when full entity isn’t needed	Improves performance
Use `@Modifying` and `@Transactional` for updates/deletes	Required for data-chang

🧹 Summary

Use Case	Annotation
Read query with JPQL	`@Query("SELECT u FROM User u")`
Read query with SQL	`@Query(..., nativeQuery = true)`
Update/Delete	Add `@Modifying` and `@Transactional`
Return DTO	Use constructor or interface projection
Add pagination	Include `Pageable` parameter

Reference

JPA Query Methods