Native SQL Queries in JPA: A Complete Guide with Examples and Best Practices

The Java Persistence API (JPA) provides JPQL and Criteria API for database interaction, but sometimes these tools are not enough. When dealing with complex queries, vendor-specific SQL features, or legacy databases, developers often need native SQL queries.

Native SQL queries allow developers to write raw SQL directly in JPA, giving maximum flexibility while still leveraging entity mapping and transaction management.

In this tutorial, you’ll learn how to use native SQL queries in JPA, including EntityManager.createNativeQuery(), @NamedNativeQuery, and best practices.

---

What are Native SQL Queries?

• JPQL → Database-agnostic, queries entities.
• Criteria API → Type-safe, dynamic queries.
• Native SQL → Direct SQL execution, bypassing JPQL limitations.

Native SQL queries are useful when:

• You need vendor-specific functions (e.g., Oracle, PostgreSQL).
• You want to leverage advanced features like window functions, CTEs, or database-specific hints.
• You work with legacy schemas that don’t align perfectly with entities.

---

Entity Setup Example

import jakarta.persistence.*;

@Entity
@Table(name = "employees")
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "Employee.findByDepartment",
        query = "SELECT * FROM employees WHERE department = ?1",
        resultClass = Employee.class
    ),
    @NamedNativeQuery(
        name = "Employee.salaryAbove",
        query = "SELECT * FROM employees WHERE salary > ?1",
        resultClass = Employee.class
    )
})
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String department;
    private Double salary;

    // getters and setters
}

---

Using Native Queries with EntityManager

1. Simple Native Query

List<Object[]> results = entityManager
    .createNativeQuery("SELECT name, salary FROM employees WHERE department = ?")
    .setParameter(1, "HR")
    .getResultList();

for (Object[] row : results) {
    System.out.println("Name: " + row[0] + ", Salary: " + row[1]);
}

2. Mapping to Entity

List<Employee> employees = entityManager
    .createNativeQuery("SELECT * FROM employees WHERE salary > ?", Employee.class)
    .setParameter(1, 60000.0)
    .getResultList();

3. Using @NamedNativeQuery

List<Employee> employees = entityManager
    .createNamedQuery("Employee.findByDepartment", Employee.class)
    .setParameter(1, "Finance")
    .getResultList();

---

CRUD Operations with Native Queries

Create (Insert)

entityManager.getTransaction().begin();
entityManager.createNativeQuery("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)")
    .setParameter(1, "Alice")
    .setParameter(2, "Finance")
    .setParameter(3, 90000.0)
    .executeUpdate();
entityManager.getTransaction().commit();

Read (Select)

List<Employee> employees = entityManager
    .createNativeQuery("SELECT * FROM employees", Employee.class)
    .getResultList();

Update

entityManager.getTransaction().begin();
entityManager.createNativeQuery("UPDATE employees SET salary = ? WHERE id = ?")
    .setParameter(1, 95000.0)
    .setParameter(2, 1L)
    .executeUpdate();
entityManager.getTransaction().commit();

Delete

entityManager.getTransaction().begin();
entityManager.createNativeQuery("DELETE FROM employees WHERE id = ?")
    .setParameter(1, 2L)
    .executeUpdate();
entityManager.getTransaction().commit();

---

Real-World Use Cases

• Vendor-Specific Features → PostgreSQL’s JSONB queries, Oracle’s hierarchical queries.
• Complex Reports → Aggregations with window functions.
• Migration Projects → Querying legacy schemas.
• Performance Optimization → Fine-tuned SQL with hints.

---

Anti-Patterns and Pitfalls

• Overusing native SQL → reduces portability.
• Forgetting resultClass → leads to Object[] results instead of entities.
• Not sanitizing inputs → risk of SQL injection.
• Mixing too many native queries with JPA → reduces ORM benefits.

---

Best Practices

• Use native SQL only when JPQL/Criteria API cannot handle the query.
• Always use parameter binding (?1, :param) to prevent injection.
• Map results to DTOs for better performance in read-heavy queries.
• Document queries for maintainability.

---

📌 JPA Version Notes

• JPA 2.0 → Introduced Criteria API, better query support.
• JPA 2.1 → Added stored procedures and entity graphs.
• JPA 2.2 → Enhanced Java 8 time support, streamlined APIs.
• Jakarta Persistence (EE 9/10/11) → Package renamed from javax.persistence → jakarta.persistence.

---

Conclusion and Key Takeaways

• Native SQL queries allow raw SQL execution inside JPA.
• Use EntityManager.createNativeQuery() for ad-hoc queries.
• Use @NamedNativeQuery for reusable queries.
• Prefer JPQL/Criteria API when possible, but leverage native queries for complex or vendor-specific needs.

---

FAQ: Expert-Level Questions

1. What’s the difference between JPA and Hibernate?
JPA is a specification; Hibernate is its implementation.

2. How does JPA handle the persistence context?
It’s like a classroom attendance register, tracking entities automatically.

3. What are the drawbacks of eager fetching in JPA?
It loads unnecessary data, reducing performance.

4. How can I solve the N+1 select problem with JPA?
Use JOIN FETCH, batch fetching, or entity graphs.

5. Can I use JPA without Hibernate?
Yes, alternatives include EclipseLink, OpenJPA, and DataNucleus.

6. What’s the best strategy for inheritance mapping in JPA?
Options: SINGLE_TABLE, JOINED, or TABLE_PER_CLASS.

7. How does JPA handle composite keys?
Using @IdClass or @EmbeddedId.

8. What changes with Jakarta Persistence?
Package renamed to jakarta.persistence.

9. Is JPA suitable for microservices?
Yes, but lighter alternatives may be better for performance-critical services.

10. When should I avoid using JPA?
When working with batch-heavy, reporting-focused, or ultra-low latency applications.

---