Spring Boot @Query Example

1. Introduction

The @Query annotation in Spring Boot is used in repository interfaces to define custom SQL or JPQL queries for methods. It allows developers to execute complex queries that can't be easily done through method naming strategies.

Key Points:

1. @Query can be used to specify a JPQL query or a native SQL query.

2. The annotation can be applied to repository methods to define custom query execution.

3. @Query supports both DML (Data Manipulation Language) and DQL (Data Query Language) operations.

2. Implementation Steps

1. Define a Spring Boot application with JPA and database dependencies.

2. Create an entity class to map to the database table.

3. Create a repository interface extending JpaRepository and include methods with the @Query annotation.

4. Implement a service class to use the repository.

5. Write a controller to expose endpoints that use the service.

6. Test the custom queries via HTTP requests.

3. Implementation Example

Here is the complete code that demonstrates the usage of @Query annotation:

// Step 2: Define an entity class
@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String department;
    // constructors, getters, and setters
}

// Step 3: Create a repository with custom queries
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    // JPQL query to retrieve employees by department
    @Query("SELECT e FROM Employee e WHERE e.department = ?1")
    List<Employee> findByDepartment(String department);

    // Native SQL query to count employees in a department
    @Query(value = "SELECT COUNT(*) FROM employees WHERE department = ?1", nativeQuery = true)
    int countByDepartment(String department);
}

// Step 4: Implement a service to use the repository
@Service
public class EmployeeService {
    @Autowired
    private EmployeeRepository employeeRepository;

    public List<Employee> getEmployeesByDepartment(String department) {
        return employeeRepository.findByDepartment(department);
    }

    public int getEmployeeCountByDepartment(String department) {
        return employeeRepository.countByDepartment(department);
    }
}

// Step 5: Write a controller
@RestController
@RequestMapping("/api/employees")
public class EmployeeController {
    @Autowired
    private EmployeeService employeeService;

    @GetMapping("/department/{deptName}")
    public ResponseEntity<List<Employee>> getEmployeesByDepartment(@PathVariable String deptName) {
        List<Employee> employees = employeeService.getEmployeesByDepartment(deptName);
        return ResponseEntity.ok(employees);
    }

    @GetMapping("/department/{deptName}/count")
    public ResponseEntity<Integer> getEmployeeCountByDepartment(@PathVariable String deptName) {
        int count = employeeService.getEmployeeCountByDepartment(deptName);
        return ResponseEntity.ok(count);
    }
}

// Step 6: Main class to run the Spring Boot application
@SpringBootApplication
public class QueryAnnotationExampleApplication {
    public static void main(String[] args) {
        SpringApplication.run(QueryAnnotationExampleApplication.class, args);
    }
}

// To test, make HTTP GET requests to /api/employees/department/{deptName} and /api/employees/department/{deptName}/count

Output:

// For HTTP GET /api/employees/department/Engineering
Status: 200 OK
Body: [{"id": 1, "name": "John Doe", "department": "Engineering"}, ...]
// For HTTP GET /api/employees/department/Engineering/count
Status: 200 OK
Body: 10

Explanation:

1. Employee is an entity that represents the employees table in the database.

2. EmployeeRepository includes methods with @Query annotations to define custom queries: one using JPQL and one using native SQL.

3. findByDepartment uses JPQL to select employees based on the department.

4. countByDepartment uses a native SQL query to count the number of employees in a given department.

5. EmployeeService serves as the business layer, utilizing the repository to serve data.

6. EmployeeController is a REST controller that provides endpoints to access employee data.

7. When endpoints are accessed, they invoke the service methods, which in turn use the @Query annotated methods to retrieve data.

8. QueryAnnotationExampleApplication is the main class that boots the Spring Boot application, enabling the execution of the @Query annotated repository methods.