A Native SQL Query is a way to use SQL (Structured Query Language) directly within the JPA framework. Unlike JPQL or Criteria API, which are object-oriented and abstract from the underlying database, a Native SQL Query lets you write the exact SQL syntax (such as SELECT, INSERT, UPDATE, DELETE) specific to your relational database.
With native queries, you can execute complex SQL statements that might be difficult or impossible to express with JPQL or Criteria API, such as database-specific functions, complex joins, and performance optimizations (e.g., using database indexes or hints).
Characteristics
Feature
Description
Direct SQL
You write raw SQL queries, exactly as you would in your database.
Database-Specific
Native SQL can be customized to specific database features and syntax.
No Object Mapping
Unlike JPQL, native queries work directly with tables and columns, not entities.
Performance Optimization
Allows fine-grained control for performance by using specific SQL optimizations (indexes, joins, etc.).
Flexibility
You can use all SQL features (like complex joins, unions, and aggregate functions).
Syntax Structure
In JPA, native SQL queries are executed using the EntityManager and createNativeQuery method.
Basic Syntax Example
String sql = "SELECT * FROM Employee WHERE department = :dept";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("dept", "Sales");
List<Object[]> result = query.getResultList();
Example with Native Query and Mapping to Entity
String sql = "SELECT * FROM Employee WHERE department = :dept";
Query query = entityManager.createNativeQuery(sql, Employee.class);
query.setParameter("dept", "Sales");
List<Employee> result = query.getResultList();
In this case, the Employee entity class is mapped to the result of the query.
Examples
Prerequisites
Entity Class - Employee.java
import jakarta.persistence.*;
@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String department;
private double salary;
// Constructors
public Employee() {}
public Employee(String name, String department, double salary) {
this.name = name;
this.department = department;
this.salary = salary;
}
// Getters and setters
public Long getId() { return id; }
public String getName() { return name; }
public String getDepartment() { return department; }
public double getSalary() { return salary; }
public void setId(Long id) { this.id = id; }
public void setName(String name) { this.name = name; }
public void setDepartment(String department) { this.department = department; }
public void setSalary(double salary) { this.salary = salary; }
}
Repository Layer (EmployeeRepository.java)
import org.springframework.stereotype.Repository;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import java.util.List;
@Repository
public class EmployeeRepository {
@PersistenceContext
private EntityManager entityManager;
/**
public List<Employee> findEmployeesWithSalaryGreaterThan(double amount) {
String sql = "SELECT * FROM employee WHERE salary > :amount";
Query query = entityManager.createNativeQuery(sql, Employee.class);
query.setParameter("amount", amount);
return query.getResultList();
}
**/
}
1. Basic SELECT Query
public List<Employee> findEmployeesWithSalaryGreaterThan(double amount) {
String sql = "SELECT * FROM employee WHERE salary > :amount";
Query query = entityManager.createNativeQuery(sql, Employee.class);
query.setParameter("amount", amount);
return query.getResultList();
}
Get all employees with salary greater than 50,000.
2. JOIN Query
public List<Object[]> findEmployeeNamesByDepartment(String dept) {
String sql = "SELECT e.name, d.name " +
"FROM employee e " +
"JOIN department d ON e.department_id = d.id " +
"WHERE d.name = :dept";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("dept", dept);
List<Object[]> result = query.getResultList(); // Each Object[] contains [employeeName, departmentName]
return result;
}
Get employee names and their department names in the HR department.
3. Aggregations
public Double getAverageSalary() {
String sql = "SELECT AVG(salary) FROM employee";
Query query = entityManager.createNativeQuery(sql);
return (Double) query.getSingleResult();
}
Get the average salary of all employees.
4. Update Query
public int increaseSalaryByDepartment(String dept) {
String sql = "UPDATE employee SET salary = salary + 5000 WHERE department = :dept";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("dept", dept);
return query.executeUpdate(); // returns number of rows affected
}
Increase salary by 5,000 for all employees in the IT department.
5. Delete Query
public int deleteInactiveEmployeesBefore(LocalDate date) {
String sql = "DELETE FROM employee WHERE last_login < :date";
Query query = entityManager.createNativeQuery(sql);
query.setParameter("date", date);
return query.executeUpdate(); // returns number of rows deleted
}
Delete employees who have not logged in since January 1, 2020.
6. Using SQL Functions
public Double getMaxSalary() {
String sql = "SELECT MAX(salary) FROM employee";
Query query = entityManager.createNativeQuery(sql);
return (Double) query.getSingleResult();
}
Get the highest salary from all employees.
7. Subqueries
public List<Employee> findEmployeesWithAboveAverageSalary() {
String sql = "SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee)";
Query query = entityManager.createNativeQuery(sql, Employee.class);
return query.getResultList();
}
Get all employees whose salary is above the average salary.
