Working with Native Queries and Stored Procedures in Hibernate

Category: Data Access and Transactions

📅 Date: October 6, 2024

---

Hibernate’s powerful ORM capabilities cover most database interactions through JPQL and Criteria API, but sometimes you need to go native—whether for performance reasons, legacy DB support, or complex queries that aren’t easily expressed with JPQL.

In this post, we’ll explore how to:

• Execute native SQL queries with Hibernate
• Map results to entities or DTOs
• Call stored procedures using Hibernate
• Best practices and tips

---

🔥 Why Use Native Queries and Stored Procedures?

• Performance optimizations via tuned SQL
• Using vendor-specific SQL features
• Handling legacy database objects or existing stored procedures
• Complex queries that are difficult or inefficient to express in JPQL

---

🛠 Using Native Queries in Hibernate

Hibernate allows you to execute raw SQL directly with @Query, EntityManager.createNativeQuery(), or the Hibernate Session API.

---

Example 1: Native Query with Entity Mapping

Suppose we have an entity:

@Entity
@Table(name = "employee")
public class Employee {
  @Id
  private Long id;

  private String name;

  private String department;

  // getters and setters
}

We want to get all employees from a specific department using native SQL.

---

Using EntityManager:

String sql = "SELECT * FROM employee WHERE department = :dept";

List<Employee> employees = entityManager.createNativeQuery(sql, Employee.class)
  .setParameter("dept", "Sales")
  .getResultList();

employees.forEach(e -> System.out.println(e.getName()));

---

Using Spring Data JPA @Query annotation:

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "SELECT * FROM employee WHERE department = :dept", nativeQuery = true)
    List<Employee> findByDepartmentNative(@Param("dept") String department);

}

---

Example 2: Native Query Returning Non-Entity Result (DTO)

Suppose you want only employee names and department but not the whole entity.

Create a DTO:

public class EmployeeSummary {
    private String name;
    private String department;

    public EmployeeSummary(String name, String department) {
        this.name = name;
        this.department = department;
    }

    // getters
}

---

Use a native query with a ResultSetMapping:

@SqlResultSetMapping(
    name = "EmployeeSummaryMapping",
    classes = @ConstructorResult(
        targetClass = EmployeeSummary.class,
        columns = {
            @ColumnResult(name = "name", type = String.class),
            @ColumnResult(name = "department", type = String.class)
        }
    )
)
@Entity
public class Employee {
    // entity code here...
}

Then:

List<EmployeeSummary> summaries = entityManager.createNativeQuery(
    "SELECT name, department FROM employee WHERE department = :dept", "EmployeeSummaryMapping")
    .setParameter("dept", "Sales")
    .getResultList();

---

🗄 Calling Stored Procedures with Hibernate

Hibernate supports calling stored procedures via EntityManager.createStoredProcedureQuery() or through @NamedStoredProcedureQuery.

---

Example 3: Calling Stored Procedure via EntityManager

Assuming a stored procedure like:

CREATE PROCEDURE GetEmployeeCountByDept(IN deptName VARCHAR(50), OUT empCount INT)
BEGIN
  SELECT COUNT(*) INTO empCount FROM employee WHERE department = deptName;
END;

---

Call it in Java:

StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GetEmployeeCountByDept");
query.registerStoredProcedureParameter("deptName", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("empCount", Integer.class, ParameterMode.OUT);

query.setParameter("deptName", "Sales");
query.execute();

Integer count = (Integer) query.getOutputParameterValue("empCount");
System.out.println("Employees in Sales: " + count);

---

Example 4: Using @NamedStoredProcedureQuery

Define it on an entity:

@NamedStoredProcedureQuery(
    name = "Employee.getCountByDept",
    procedureName = "GetEmployeeCountByDept",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "deptName", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "empCount", type = Integer.class)
    }
)
@Entity
public class Employee {
    // entity code...
}

Then call it from repository or service:

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("Employee.getCountByDept");
query.setParameter("deptName", "Sales");
query.execute();
Integer count = (Integer) query.getOutputParameterValue("empCount");

---

💡 Tips and Best Practices

• Use native queries sparingly. Prefer JPQL or Criteria API for portability and maintainability.
• Map native query results properly. Use @SqlResultSetMapping or constructor expressions for DTOs.
• Test stored procedures independently before integrating to avoid unexpected DB behavior.
• Be aware of SQL dialect differences when writing native queries for multiple DB platforms.
• Parameterize queries to avoid SQL injection and improve performance.
• Log native queries during development to debug and optimize.

---

🧪 Testing Native Queries and Stored Procedures

Use integration tests with your actual database or in-memory DB like H2 that supports procedures.

Example with Spring Boot Test:

@Test
public void testNativeQuery() {
    List<Employee> employees = employeeRepository.findByDepartmentNative("Sales");
    assertFalse(employees.isEmpty());
}

@Test
public void testStoredProcedure() {
    StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("Employee.getCountByDept");
    query.setParameter("deptName", "Sales");
    query.execute();
    Integer count = (Integer) query.getOutputParameterValue("empCount");
    assertTrue(count > 0);
}

---

🏁 Summary

Native queries and stored procedures offer powerful ways to extend Hibernate’s capabilities for advanced use cases. With proper mapping, parameter handling, and testing, you can leverage native SQL and DB procedures effectively without losing Hibernate’s productivity benefits.

---

If you want, I can follow up with more advanced topics like mapping complex joins with native queries or performance tuning native SQL in Hibernate—just say the word!

---