Mastering Spring Data JPA – JPQL and Native SQL Queries Explained

Custom JPQL and Native SQL Queries using @Query Annotation

In the previous article, we learned how to use Derived Query Methods in Spring Data JPA. Those methods are extremely useful for simple database operations, but in real-world backend applications, developers often need more complex queries.

For example:

• Fetch employees with high salaries

• Join multiple tables

• Select only the required fields

• Use database-specific SQL functions

• Write optimized queries for reports and analytics

In such situations, Spring Data JPA provides the @Query annotation.

Using @Query, developers can write custom JPQL queries or Native SQL queries directly inside repository interfaces.

In this article, we will learn both approaches using a real-world Employee Management System.

What is JPQL?

JPQL stands for Java Persistence Query Language.

It is similar to SQL, but instead of table names and column names, JPQL uses:

• Entity class names

• Java object field names

JPQL works independently of the database being used.

Real-World Scenario

Suppose your HR application requires the following features:

• Fetch employees with a salary greater than a certain amount

• Find employees belonging to multiple departments

• Generate salary reports

• Fetch top-paid employees

Some of these queries become difficult using only Derived Query Methods.

This is where @Query becomes useful.

Employee Entity

We will continue using the same Employee entity.

packagecom.ayshriv.entity;

importjakarta.persistence.*;

@Entity
@Table(name="employees")
publicclassEmployee {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
privateLongid;

privateStringname;

privateStringdepartment;

privateDoublesalary;

// Getters and Setters
}

Writing JPQL Queries

Update the repository interface.

packagecom.ayshriv.repository;

importcom.ayshriv.entity.Employee;
importorg.springframework.data.jpa.repository.JpaRepository;
importorg.springframework.data.jpa.repository.Query;

importjava.util.List;

publicinterfaceEmployeeRepositoryextendsJpaRepository<Employee,Long> {

    @Query("SELECT e FROM Employee e WHERE e.salary > :salary")
List<Employee>findEmployeesWithHighSalary(Doublesalary);

}

Understanding the Query

SELECTeFROMEmployeeeWHEREe.salary> :salary

Here:

• Employee → Entity class name

• e.salary → Java field name

• :salary → Named parameter

Spring automatically maps this query into SQL.

Service Layer

packagecom.ayshriv.service;

importcom.ayshriv.entity.Employee;
importcom.ayshriv.repository.EmployeeRepository;
importorg.springframework.stereotype.Service;

importjava.util.List;

@Service
publicclassEmployeeService {

privatefinalEmployeeRepositoryemployeeRepository;

publicEmployeeService(EmployeeRepositoryemployeeRepository) {
this.employeeRepository=employeeRepository;
    }

publicList<Employee>getHighSalaryEmployees(Doublesalary) {
returnemployeeRepository.findEmployeesWithHighSalary(salary);
    }
}

Controller Layer

packagecom.ayshriv.controller;

importcom.ayshriv.entity.Employee;
importcom.ayshriv.service.EmployeeService;
importorg.springframework.web.bind.annotation.*;

importjava.util.List;

@RestController
@RequestMapping("/employees")
publicclassEmployeeController {

privatefinalEmployeeServiceemployeeService;

publicEmployeeController(EmployeeServiceemployeeService) {
this.employeeService=employeeService;
    }

    @GetMapping("/high-salary/{salary}")
publicList<Employee>getHighSalaryEmployees(@PathVariableDoublesalary) {
returnemployeeService.getHighSalaryEmployees(salary);
    }
}

API Example

GET /employees/high-salary/70000

This API returns employees whose salary is greater than 70000.

Native SQL Queries

Sometimes developers need database-specific queries for optimization or reporting.

In such cases, Native SQL Queries are used.

Example:

@Query(
value="SELECT * FROM employees WHERE department = :department",
nativeQuery=true
)
List<Employee>findEmployeesByDepartment(Stringdepartment);

Difference Between JPQL and Native Query

Selecting Specific Columns

Suppose management only needs employee names.

Instead of fetching the full entity:

@Query("SELECT e.name FROM Employee e")
List<String>getEmployeeNames();

This improves performance because only the required data is fetched.

Sorting Data

@Query("SELECT e FROM Employee e ORDER BY e.salary DESC")
List<Employee>getEmployeesSortedBySalary();

This query returns employees sorted by salary in descending order.

Advantages of @Query Annotation

1. Flexible Query Writing

Developers can create complex queries easily.

2. Better Performance Optimization

Only the required data can be fetched.

3. Supports Both JPQL and Native SQL

Useful for enterprise applications.

4. Cleaner Repository Layer

All database logic remains inside repositories.

Best Practices

• Prefer JPQL for portability

• Use Native Queries only when necessary

• Keep queries readable

• Avoid writing extremely large queries in repositories

• Use DTO projections for large applications

Conclusion

The @Query annotation is one of the most important features of Spring Data JPA. It gives developers complete control over database queries while still keeping the repository layer clean and maintainable.

In this article, we learned:

• JPQL queries

• Native SQL queries

• Named parameters

• Sorting data

• Selecting specific fields

These concepts are heavily used in real-world enterprise backend systems.

In the next article, we will learn:

Pagination and Sorting in Spring Data JPA

We will build APIs that support:

• Page-wise data loading

• Dynamic sorting

• Optimized large dataset handling

• Production-ready pagination APIs