> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/spring/spring-features/spring-persistence/jpa-java-persistence-api/querying-data/native-sql-queries.md).

# 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

<table data-full-width="true"><thead><tr><th width="239.23046875">Feature</th><th>Description</th></tr></thead><tbody><tr><td><strong>Direct SQL</strong></td><td>You write raw SQL queries, exactly as you would in your database.</td></tr><tr><td><strong>Database-Specific</strong></td><td>Native SQL can be customized to specific database features and syntax.</td></tr><tr><td><strong>No Object Mapping</strong></td><td>Unlike JPQL, native queries work directly with tables and columns, not entities.</td></tr><tr><td><strong>Performance Optimization</strong></td><td>Allows fine-grained control for performance by using specific SQL optimizations (indexes, joins, etc.).</td></tr><tr><td><strong>Flexibility</strong></td><td>You can use all SQL features (like complex joins, unions, and aggregate functions).</td></tr></tbody></table>

## Syntax Structure

In JPA, native SQL queries are executed using the `EntityManager` and `createNativeQuery` method.

#### Basic Syntax Example

```java
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

```java
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`

```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`)

```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

```java
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

```java
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

```java
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

```java
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

```java
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

```java
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

```java
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)

```java
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 ?

<table data-full-width="true"><thead><tr><th>Feature</th><th>Native SQL</th><th>JPQL</th><th>Criteria API</th></tr></thead><tbody><tr><td><strong>SQL Syntax</strong></td><td>✅ Uses exact SQL syntax</td><td>❌ Does not use SQL syntax</td><td>❌ Does not use SQL syntax</td></tr><tr><td><strong>Database-Specific</strong></td><td>✅ Yes</td><td>❌ No</td><td>❌ No</td></tr><tr><td><strong>Type-Safety</strong></td><td>❌ No</td><td>❌ No</td><td>✅ Yes</td></tr><tr><td><strong>Dynamic Query Support</strong></td><td>✅ Yes</td><td>✅ Yes</td><td>✅ Yes</td></tr><tr><td><strong>Portability</strong></td><td>❌ Database-specific</td><td>✅ High</td><td>✅ High</td></tr><tr><td><strong>Complex Queries</strong></td><td>✅ Best</td><td>⚠️ Moderate</td><td>⚠️ Moderate</td></tr></tbody></table>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/spring/spring-features/spring-persistence/jpa-java-persistence-api/querying-data/native-sql-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.