Master Spring Data JPA Custom Query Example: A Simple Guide

1. Project Setup

• Spring Initializr: Visit https://start.spring.io to generate a basic Spring Boot project structure. Here’s a configuration suggestion:
  • Project: Maven Project
  • Language: Java
  • Spring Boot Version: (Select an appropriate version)
  • Dependencies:
    • Spring Web
    • Spring Data JPA
    • H2 Database (for in-memory testing, choose a production-grade database later)
• IDE: Download and unzip the project. Import it into your preferred IDE (Eclipse, IntelliJ IDEA, Spring Tool Suite, etc.).

2. Database Model (Employee Entity)

• Understanding @Entity: This annotation marks the Employee class as a database entity. It tells Spring Data JPA to translate this class into a database table.
• @Table: Lets you customize the table name in the database (name = “employees”).
• @Id: Marks the employeeId as the primary key of the table.
• @GeneratedValue: Indicates that the ID value will be automatically generated by the database.
• @Column: Maps your fields (employeeName, employeeAddress, etc.) to columns within the database table.

package com.employee.tech.entity;

// Spring Data JPA custom query example    
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "employees")
public class Employee {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long employeeId;

	@Column(name = "employee_name")
	private String employeeName;

	@Column(name = "employee_address")
	private String employeeAddress;

	@Column(name = "employee_designation")
	private String employeeDesignation;

	@Column(name = "employee_salary")
	private double employeeSalary;

	public long getEmployeeId() {
		return employeeId;
	}

	public void setEmployeeId(long employeeId) {
		this.employeeId = employeeId;
	}

	public String getEmployeeName() {
		return employeeName;
	}

	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}

	public String getEmployeeAddress() {
		return employeeAddress;
	}

	public void setEmployeeAddress(String employeeAddress) {
		this.employeeAddress = employeeAddress;
	}

	public String getEmployeeDesignation() {
		return employeeDesignation;
	}

	public void setEmployeeDesignation(String employeeDesignation) {
		this.employeeDesignation = employeeDesignation;
	}

	public double getEmployeeSalary() {
		return employeeSalary;
	}

	public void setEmployeeSalary(double employeeSalary) {
		this.employeeSalary = employeeSalary;
	}
}

3. Data Access Layer (Employee Repository with Custom Queries)

• @Repository: Tells Spring to create a bean for data access operations on the Employee entity.
• JpaRepository: Spring Data JPA provides built-in CRUD (Create, Read, Update, Delete) operations and more.
• @Query: Lets you write custom SQL-like (JPQL) queries to tailor your data retrieval logic. This is where we’ll focus on our “Spring Data JPA custom query example.”

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import com.employee.tech.entity.Employee;
import jakarta.transaction.Transactional;

// Spring Data JPA custom query example    

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

	@Query("SELECT e FROM Employee e WHERE e.id = :id")
	Employee findEmployeeById(@Param("id") Long id);

	@Modifying
	@Transactional
	@Query("INSERT INTO Employee(employeeName, employeeAddress, employeeDesignation, employeeSalary) VALUES (:name, :address, :designation, :salary)")
	void createEmployee(@Param("name") String name, @Param("address") String address,
			@Param("designation") String designation, @Param("salary") double salary);

	@Modifying
	@Transactional
	@Query("UPDATE Employee e SET e.employeeName = :name, e.employeeAddress = :address, e.employeeDesignation = :designation, e.employeeSalary = :salary WHERE e.id = :id")
	void updateEmployee(@Param("name") String name, @Param("address") String address,
			@Param("designation") String designation, @Param("salary") double salary, @Param("id") Long id);

	@Modifying
	@Transactional
	@Query("DELETE FROM Employee e WHERE e.id = :id")
	void deleteEmployeeById(@Param("id") Long id);
}

• @Modifying and @Transactional: These ensure database modifications are executed within a transaction for data integrity.
• @Param: To bind method parameters to query placeholders.

4. Service Layer (Employee Service)

• @Service: Marks this class as the business logic layer, handling interactions with the repository.
• Interfaces are best practices; they encourage modularity and make your code testable.

package com.employee.tech.service;

import java.util.List;
import java.util.Optional;
import org.springframework.stereotype.Service;
import com.employee.tech.entity.Employee;

// Spring Data JPA custom query example
@Service
public interface EmployeeService {

	public List<Employee> getAllEmployees();

	public Optional<Employee> getEmployeeById(Long id);

	public Employee createEmployee(Employee employee);

	public void updateEmployee(Employee employee);

	public void deleteEmployeeById(Long id);
}

package com.employee.tech.service;

// Spring Data JPA custom query example
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Optional;
import com.employee.tech.entity.Employee;
import com.employee.tech.repository.EmployeeRepository;

@Service
public class EmployeeServiceImpl implements EmployeeService {

	@Autowired
	private EmployeeRepository employeeRepository;

	@Override
	public List<Employee> getAllEmployees() {
		return employeeRepository.findAll();
	}

	@Override
	public Optional<Employee> getEmployeeById(Long id) {
		return employeeRepository.findById(id);
	}

	@Override
	public Employee createEmployee(Employee employee) {
		employeeRepository.createEmployee(employee.getEmployeeName(), employee.getEmployeeAddress(),
				employee.getEmployeeDesignation(), employee.getEmployeeSalary());
		return employee;
	}

	public void updateEmployee(Employee employee) {
		employeeRepository.updateEmployee(employee.getEmployeeName(), employee.getEmployeeAddress(),
				employee.getEmployeeDesignation(), employee.getEmployeeSalary(), employee.getEmployeeId());
	}

	@Override
	public void deleteEmployeeById(Long id) {
		employeeRepository.deleteEmployeeById(id);
	}
}

5. REST Controller (Employee Controller)

• @RestController: Combines @Controller (web request handling) and @ResponseBody (HTTP responses rendered as JSON/XML).
• @RequestMapping: Maps URL patterns to controller methods (/api/employees).
• @Autowired: Injects the EmployeeService for business logic.
• HTTP verbs with their annotations:
  • @GetMapping: Read operations
  • @PostMapping: Create operations
  • @PutMapping: Update operations
  • @DeleteMapping: Delete operation
• ResponseEntity: Allows you to control HTTP status codes and response bodies.

package com.employee.tech.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import com.employee.tech.entity.Employee;
import com.employee.tech.service.EmployeeService;
import java.util.List;
import java.util.Optional;

// Spring Data JPA custom query example
@RestController
@RequestMapping("/api/employees")
public class EmployeeController {

	@Autowired
	private EmployeeService employeeService;

	@GetMapping("/getAll")
	public List<Employee> getAllEmployees() {
		return employeeService.getAllEmployees();
	}

	@GetMapping("/{id}")
	public ResponseEntity<Employee> getEmployeeById(@PathVariable Long id) {
		Optional<Employee> employee = employeeService.getEmployeeById(id);
		return employee.map(ResponseEntity::ok).orElseGet(() -> ResponseEntity.notFound().build());
	}

	@PostMapping("/saveEmployee")
	public ResponseEntity<Employee> createEmployee(@RequestBody Employee employee) {
		Employee createdEmployee = employeeService.createEmployee(employee);
		return new ResponseEntity<>(createdEmployee, HttpStatus.CREATED);
	}

	@PutMapping("/{id}")
	public ResponseEntity<Employee> updateEmployee(@PathVariable Long id, @RequestBody Employee employee) {
		employee.setEmployeeId(id);
		employeeService.updateEmployee(employee);
		return ResponseEntity.ok(employee);
	}

	@DeleteMapping("/{id}")
	public ResponseEntity<Void> deleteEmployeeById(@PathVariable Long id) {
		employeeService.deleteEmployeeById(id);
		return ResponseEntity.noContent().build();
	}
}

7. pom.xml (Dependencies)

Make sure your pom.xml includes the necessary dependencies for Spring Web, Spring Data JPA, and your chosen database .

// Spring Data JPA custom query example
<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version> <!-- Use the appropriate version -->
      </dependency>
</dependencies>

8. Testing the Application

• Use tools like Postman or curl to test your endpoints:
  • GET /api/employees/getAll: Retrieves a list of all employees.
  • GET /api/employees/{id}: Retrieves details of a single employee using their ID.
  • POST /api/employees/saveEmployee: Creates a new employee entry.
  • PUT /api/employees/{id}: Updates an existing employee’s information.
  • DELETE /api/employees/{id}: Deletes an employee from the system.

Explore More Related Topics:
 -> Spring Boot REST API CRUD Example With MySQL Database
-> Login Registration and Forgot Password Form in HTML CSS
-> Step-by-Step Guide: Setting up Jenkins on Amazon EC2 Instance with Ubuntu