Making SQL queries on top of ORMs in Java Spring Boot

Introduction

In Spring Boot, Object-Relational Mapping (ORM) frameworks like Hibernate simplify database interactions by allowing developers to work with Java objects rather than writing complex SQL queries. However, there are scenarios where using plain SQL on top of ORMs becomes necessary. Whether it's for performance optimization, complex queries, or database-specific features, integrating SQL queries within your ORM-managed Spring Boot application can offer the best of both worlds.

This blog will guide you through the process of executing SQL queries on top of an ORM in a Spring Boot application. We'll cover when and why you might need to do this and walk you through the steps with clear examples.

Prerequisites

Before diving into SQL on top of ORM, ensure you have the following:

1. Basic knowledge of Java and Spring Boot.

2. A working Spring Boot application with an ORM like Hibernate.

3. Familiarity with JPA (Java Persistence API) and SQL basics.

Step 1: Understanding When to Use SQL on Top of ORM

The first step is understanding the scenarios where you might prefer SQL over ORM methods:

• Complex Queries: Some queries might be too complex to express using JPQL (Java Persistence Query Language) or the ORM’s Criteria API.

• Performance Optimization: Direct SQL queries can sometimes be optimized better than ORM-generated queries, especially for large datasets.

• Database-Specific Features: If you need to use database-specific features, like stored procedures or native functions, you’ll need SQL.

• Legacy Code Integration: When integrating with a legacy system where raw SQL queries are already in use, it may be easier to execute those queries directly.

Step 2: Setting Up the Repository for Native SQL Queries

Spring Data JPA allows you to define native SQL queries within your repository interfaces. Let's start by creating a repository that includes a native SQL query.

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "SELECT * FROM employees WHERE department = ?1", nativeQuery = true)
    List<Employee> findEmployeesByDepartment(String department);
}

In this example, we use the @Query annotation with the nativeQuery attribute set to true. This tells Spring Data JPA to execute the given SQL query directly.

Step 3: Executing SQL Queries with the EntityManager

For more flexibility, you can use the EntityManager to execute SQL queries. This method allows you to run both native and JPQL queries.

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class EmployeeCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Employee> findEmployeesByCustomQuery(String department) {
        Query query = entityManager.createNativeQuery("SELECT * FROM employees WHERE department = ?", Employee.class);
        query.setParameter(1, department);
        return query.getResultList();
    }
}

Here, we inject the EntityManager to create and execute a native SQL query. This approach provides more control over the query execution, including handling custom mappings and complex joins.

Step 4: Mixing JPQL and SQL in a Repository

You can also mix JPQL and native SQL within the same repository. This is useful when you want to use JPQL for simpler queries and fall back to native SQL for more complex ones.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface MixedEmployeeRepository extends JpaRepository<Employee, Long> {

    // JPQL Query
    @Query("SELECT e FROM Employee e WHERE e.department = ?1")
    List<Employee> findByDepartment(String department);

    // Native SQL Query
    @Query(value = "SELECT * FROM employees WHERE department = ?1", nativeQuery = true)
    List<Employee> findByDepartmentNative(String department);
}

This example shows how to combine JPQL and native SQL in a single repository. You can choose the method based on the complexity and performance needs of your query.

Step 5: Handling Complex Result Mappings

When using native SQL queries, the result might not always map directly to your entity. In such cases, you can use a SqlResultSetMapping to map the results.

import jakarta.persistence.*;

import java.util.List;

@Entity
@SqlResultSetMapping(
    name = "EmployeeDepartmentMapping",
    entities = @EntityResult(
        entityClass = Employee.class,
        fields = {
            @FieldResult(name = "id", column = "id"),
            @FieldResult(name = "name", column = "name"),
            @FieldResult(name = "department", column = "department")
        }
    )
)
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String department;

    // Getters and Setters
}

@Repository
public class CustomEmployeeRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Employee> findEmployeesWithCustomMapping(String department) {
        Query query = entityManager.createNativeQuery("SELECT id, name, department FROM employees WHERE department = ?", "EmployeeDepartmentMapping");
        query.setParameter(1, department);
        return query.getResultList();
    }
}

This approach is useful when the SQL query returns a result set that doesn’t directly match the entity’s structure. The SqlResultSetMapping annotation helps you map the columns to entity fields manually.

Conclusion

While ORMs like Hibernate simplify database operations in Spring Boot, there are situations where using plain SQL on top of an ORM is advantageous. Whether it's for complex queries, performance optimization, or leveraging database-specific features, knowing how to integrate SQL queries into your Spring Boot application is a valuable skill.

By following the steps outlined in this guide, you should now have a solid foundation for executing SQL queries within a Spring Boot project, giving you more control and flexibility in managing your data.

Happy coding!