Spring Boot Native Query Example: Master Database Control

Introduction of Spring Boot native query Example

Spring Boot, a powerful Java framework, simplifies backend development significantly. JPA (Java Persistence API) is often used for database interactions, providing an object-oriented abstraction over your database. While JPA is versatile, you may sometimes need to work directly with native SQL queries. Let’s explore how to seamlessly integrate native queries into your Spring Boot application.

What are Native Queries?

• Native queries are written in the native SQL dialect of your chosen database (MySQL, PostgreSQL, Oracle, etc.).
• This approach is useful when You need to leverage database-specific features not readily supported by JPA. You want to execute complex SQL queries or stored procedures. You require fine-grained optimization of your database queries.

Prerequisites:

• Basic understanding of Java and Spring Boot concepts
• An IDE of your choice (IntelliJ, Eclipse, VS Code are popular options)
• A database system installed and running
  
  Project Creation:
• Head to the Spring Initializer to quickly generate a base Spring Boot project.
• Include the following dependencies: Spring Web Spring Data JPA Your database driver (e.g., H2, MySQL, PostgreSQL)

The Code Walkthrough:

1. The Entity

package com.employee.tech.entity;

// Spring Boot native 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;
	}
// Spring Boot native query example
}

2. The Repository

package com.employee.tech.repository;

// Spring Boot native 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;

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

	@Query(value = "SELECT * FROM employees WHERE employee_id = ?1", nativeQuery = true)
	Employee findEmployeeById(Long id);

	@Modifying
	@Transactional
	@Query(value = "INSERT INTO employees (employee_name, employee_address, employee_designation, employee_salary) VALUES (?1, ?2, ?3, ?4)", nativeQuery = true)
	void createEmployee(String name, String address, String designation, double salary);

	@Modifying
	@Transactional
	@Query(value = "UPDATE employees SET employee_name = ?1, employee_address = ?2, employee_designation = ?3, employee_salary = ?4 WHERE employee_id = ?5", nativeQuery = true)
	void updateEmployee(String name, String address, String designation, double salary, Long id);

	@Modifying
	@Transactional
	@Query(value = "DELETE FROM employees WHERE employee_id = ?1", nativeQuery = true)
	void deleteEmployeeById(Long id);
}

3. The Service

package com.employee.tech.service;

// Spring Boot native query example
import java.util.List;
import java.util.Optional;
import org.springframework.stereotype.Service;
import com.employee.tech.entity.Employee;

@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;

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);
	}
}

4. The Controller

package com.employee.tech.controller;

// Spring Boot native query example
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;

@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();
	}
}

Explanation

• @Entity: Defines your ‘Employee’ class as a database entity.
• @Repository: This marks the EmployeeRepository as a Spring Data JPA repository, giving you basic CRUD (Create, Read, Update, Delete) functionality.
• @Query(value = …, nativeQuery = true): This annotation in the repository methods, along with nativeQuery=true, signals Spring Boot to treat them as native SQL queries.
• @Modifying, @Transactional: These annotations tell Spring that the native queries modify data and they should be executed within a transaction.
• Service Layer: The service layer provides a business logic layer and an abstraction over your repository, making your code more organized.
• REST Controller: Your controller class exposes REST endpoints for interacting with employee data (getting, saving, updating, deleting).

Testing It Out

• Run your Spring Boot application.
• Use tools like Postman or curl to experiment with the following HTTP requests:
  GET /api/employees/getAll – Fetches all employees.
  GET /api/employess/{id} – Retrieves an employee by ID.
  POST /api/employees/saveEmployee – Creates a new employee.
  PUT /api/employees/{id} – Updates an existing employee.
  DELETE /api/employees/{id} – Deletes an employee.

Explore More Related Topics:
-> Master Spring Data JPA Custom Query Example: A Simple Guide
 -> 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