Using @Query, Named Queries, and Stored Procedures for Custom Queries in Spring Boot

When working on any client project, there always come's a requirement to have some custom queries to read from a table or in some cases to write to a table. There were some requirements where we had to call a specific procedure because the complexity of updating the tables and it's required supporting tables were done by this procedure.

In this blog, we'll explore different methods to fetch records using non-primary key fields in a Spring Boot application.

---

1. Using Derived Query Methods (Recommended)

Spring Data JPA automatically generates queries based on method names. This is the easiest and most readable approach.

Example: Fetching Employees by Name and Department

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    // Fetch an employee by name (Assuming name is unique)
    Employee findByName(String name);

    // Fetch multiple employees by department
    List<Employee> findByDepartment(String department);
}

Spring will automatically generate SQL queries for you:

• findByName(String name) → SELECT * FROM employee WHERE name = ?

• findByDepartment(String department) → SELECT * FROM employee WHERE department = ?

---

2. Using @Query (JPQL or Native SQL)

If you need more control over queries, you can use @Query.

🔹 JPQL Query Example

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    @Query("SELECT e FROM Employee e WHERE e.name = :name")
    Employee getByName(@Param("name") String name);

    @Query("SELECT e FROM Employee e WHERE e.department = :dept")
    List<Employee> getByDepartment(@Param("dept") String department);
}

• Uses JPQL, which operates on entity names and fields rather than database tables.

🔹 Native SQL Query Example

@Query(value = "SELECT * FROM employee WHERE name = :name", nativeQuery = true)
Employee getByNameNative(@Param("name") String name);

• Uses raw SQL, making it database-dependent.

---

3. Using @NamedNativeQuery (For Stored Procedures)

For stored procedures, use @NamedNativeQuery in your entity class.

🔹 Defining Named Query in the Entity

import jakarta.persistence.*;

@Entity
@Table(name = "employee")
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "Employee.findByDepartment",
        query = "CALL GetEmployeesByDepartment(:dept_name)",
        resultClass = Employee.class
    )
})
public class Employee {
    @Id
    private Long id;
    private String name;
    private String department;
}

🔹 Using Named Query in Repository

@Query(name = "Employee.findByDepartment", nativeQuery = true)
List<Employee> findByDepartment(@Param("dept_name") String department);

• Calls a stored procedure instead of a direct SQL query.

---

4. Using @Procedure (For Calling Stored Procedures)

If you have a stored procedure that fetches employees, you can use @Procedure.

🔹 Example

import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Procedure(name = "GetEmployeeByName")
    Employee getEmployeeByName(@Param("name") String name);
}

• This will call a stored procedure in the database.

---

Summary

Approach	When to Use	Example Method
Derived Query Methods	Simple queries on entity fields	findByName(String name)
JPQL @Query	Custom queries on entities	@Query("SELECT e FROM Employee e WHERE e.name = :name")
Native SQL @Query	When JPQL doesn't support complex queries	@Query(value = "SELECT * FROM employee WHERE name = :name", nativeQuery = true)
@NamedNativeQuery	Calling stored procedures	findByDepartment(@Param("dept_name") String department)
@Procedure	Stored procedures with parameter mappings	@Procedure(name = "GetEmployeeByName")

---

Final Thoughts

• If your query is simple, use derived methods like findByName(String name).

• If you need complex conditions, use @Query (JPQL or Native SQL).

• If working with stored procedures, use @NamedNativeQuery or @Procedure.