Today we'll dive into creating custom queries using JPQL (Java Persistence Query Language). This lets us write more complex queries that go beyond the repository's built-in methods. Repository methods are great, but sometimes you need flexibility.
1. Getting to know JPQL
JPQL (Java Persistence Query Language) is the query language provided by JPA, and it's very similar to SQL, but it works with objects instead of tables. That's the key point: we're operating at the object level, not directly on the database tables mapped to entities.
Example of a simple SQL query:
SELECT * FROM employees WHERE department = 'IT';
Example of an equivalent JPQL query:
SELECT e FROM Employee e WHERE e.department = 'IT'
Note:
- We're working with classes and their fields (
Employeeanddepartment), not table and column names (employeesanddepartment). - JPQL is sensitive to the class name and its fields. A typo will lead to a
QuerySyntaxException.
Why use JPQL?
Sometimes the standard repository methods like findById, save, or deleteAll aren't enough to get complex data. JPQL lets you:
- Perform aggregations (for example,
AVG,SUM,COUNT). - Write more complex conditions (for example, joins and filters).
- Have finer control over data selection, like applying ordering and limits.
2. Writing custom queries
Let's create an example to show how to write and use JPQL. Imagine we have an employee management system with an Employee entity that has these fields:
id(unique identifier of the employee).name(employee's name).department(employee's department).salary(employee's salary).
Step 1: Creating the entity
The Employee class looks like this:
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Column;
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
@Column(nullable = false)
private String department;
@Column(nullable = false)
private Double salary;
// Getters and setters
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
}
Step 2: Creating a repository with a custom query
Add the EmployeeRepository interface. It'll extend JpaRepository so we can use the standard methods. We'll also define our own methods using JPQL.
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
// Custom query to get employees of a given department
@Query("SELECT e FROM Employee e WHERE e.department = :department")
List<Employee> findByDepartment(@Param("department") String department);
// Custom query to get employees with salary above a given value
@Query("SELECT e FROM Employee e WHERE e.salary > :salary")
List<Employee> findEmployeesWithSalaryAbove(@Param("salary") Double salary);
// Custom query with aggregation
@Query("SELECT AVG(e.salary) FROM Employee e WHERE e.department = :department")
Double findAverageSalaryByDepartment(@Param("department") String department);
}
Note:
- The
@Queryannotation is used to write a custom JPQL query. - We use parameters (
:departmentor:salary) to pass values into the query. - The
@Paramannotation binds method parameters to query parameters.
Step 3: Using the repository
Let's call our methods through a service layer or a controller.
Service example:
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmployeeService {
private final EmployeeRepository employeeRepository;
public EmployeeService(EmployeeRepository employeeRepository) {
this.employeeRepository = employeeRepository;
}
public List<Employee> getEmployeesByDepartment(String department) {
return employeeRepository.findByDepartment(department);
}
public List<Employee> getEmployeesWithHighSalary(Double salary) {
return employeeRepository.findEmployeesWithSalaryAbove(salary);
}
public Double getAverageSalary(String department) {
return employeeRepository.findAverageSalaryByDepartment(department);
}
}
Controller example:
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/employees")
public class EmployeeController {
private final EmployeeService employeeService;
public EmployeeController(EmployeeService employeeService) {
this.employeeService = employeeService;
}
@GetMapping("/by-department")
public List<Employee> getEmployeesByDepartment(@RequestParam String department) {
return employeeService.getEmployeesByDepartment(department);
}
@GetMapping("/high-salary")
public List<Employee> getEmployeesWithHighSalary(@RequestParam Double salary) {
return employeeService.getEmployeesWithHighSalary(salary);
}
@GetMapping("/average-salary")
public Double getAverageSalary(@RequestParam String department) {
return employeeService.getAverageSalary(department);
}
}
Step 4: Testing the queries
Requesting GET /employees/by-department?department=IT will return all employees from the IT department.
Requesting GET /employees/high-salary?salary=50000 will return all employees with salary greater than 50,000.
And requesting GET /employees/average-salary?department=HR will return the average salary of employees in the HR department.
3. Slightly harder examples
If this all seems too simple, let's add some complexity.
Query with sorting
We can add ordering using ORDER BY in JPQL:
@Query("SELECT e FROM Employee e ORDER BY e.salary DESC")
List<Employee> findAllEmployeesBySalaryDesc();
Query with LIKE
We can search employees by part of their name:
@Query("SELECT e FROM Employee e WHERE e.name LIKE %:keyword%")
List<Employee> findByNameContaining(@Param("keyword") String keyword);
Query with a join
If we have a Department entity related to Employee, we can use JOIN:
@Query("SELECT e FROM Employee e JOIN e.department d WHERE d.name = :departmentName")
List<Employee> findByDepartmentName(@Param("departmentName") String departmentName);
4. Handling common errors
There are usually two types of errors: mistakes in the query syntax and problems with parameters. For example:
- If you wrote
SELECT e FROM Employee e WHERE e.departments = :departmentbut the field in the class is nameddepartment, Spring will throw anIllegalArgumentExceptiondescribing the issue. - If you forget to pass a parameter with
@Param, Spring won't be happy either.
Tip: always test queries early to avoid unexpected surprises.
5. When to use JPQL
Using JPQL makes sense in these cases:
- You need complex data selection (for example, aggregation or joins).
- The standard repository methods are not enough.
- You want to write a query based on business logic without digging deep into SQL.
If queries get too complex, you might consider using native SQL queries or even moving logic into stored procedures.
@Query(value = "SELECT * FROM employees WHERE salary > :salary", nativeQuery = true)
List<Employee> findHighSalaryEmployees(@Param("salary") Double salary);
Hope you now feel confident, like you just finally beat a bug that had been driving you nuts for three weeks. Time to build some truly powerful queries in your apps!
GO TO FULL VERSION