Native SQL Queries

About

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.

8. Batch Operations (Insert/Update/Delete)

public void insertEmployee(int id, String name, String department, double salary) {
    String sql = "INSERT INTO employee (id, name, department, salary) VALUES (?, ?, ?, ?)";
    Query query = entityManager.createNativeQuery(sql);
    query.setParameter(1, id);
    query.setParameter(2, name);
    query.setParameter(3, department);
    query.setParameter(4, salary);
    query.executeUpdate();
}

Insert a new employee record into the database.

How Native SQL Queries Compare with JPQL and Criteria API ?

Feature	Native SQL	JPQL	Criteria API
SQL Syntax	✅ Uses exact SQL syntax	❌ Does not use SQL syntax	❌ Does not use SQL syntax
Database-Specific	✅ Yes	❌ No	❌ No
Type-Safety	❌ No	❌ No	✅ Yes
Dynamic Query Support	✅ Yes	✅ Yes	✅ Yes
Portability	❌ Database-specific	✅ High	✅ High
Complex Queries	✅ Best	⚠️ Moderate	⚠️ Moderate