Data Aggregation in Hibernate with @SqlResultSetMapping and @NamedNativeQuery

In the world of Java Persistence API (JPA), especially when dealing with large volumes of data and complex queries, developers often encounter the necessity of executing native SQL queries. This need arises for performing aggregate functions such as SUM, AVG, MAX, etc., which might not always be conveniently or efficiently implemented using JPA criteria or JPQL. This article discusses the use of @NamedNativeQuery and @SqlResultSetMapping through the example of aggregating the sum of payments in a database table.

Data Aggregation in Hibernate with @SqlResultSetMapping and @NamedNativeQuery

Hi, this is Paul, and welcome to this article, we explore how to map non-entity objects like aggregation to Java Objects using @NamedNativeQuery and @SqlResultSetMapping annotations.

Example

Let’s assume we have a payments table that contains information about payments. Our goal is to obtain the total sum of all payments. To achieve this, we use NamedNativeQuery for executing a native SQL query and SqlResultSetMapping for mapping the result to a PaymentSummary class, which is not an entity but serves as a DTO (Data Transfer Object) for the aggregated result.

DB:

CREATE TABLE payments
(
    id     BIGSERIAL PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL
);

Entity of Payment:

import jakarta.persistence.*;
import lombok.*;

import java.math.BigDecimal;

@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "payments")
@NamedNativeQuery(name = "Payment.findSummary",
        query = "SELECT SUM(p.amount) AS totalAmount FROM payments p",
        resultSetMapping = "PaymentSummaryMapping")
@SqlResultSetMapping(name = "PaymentSummaryMapping",
        classes = @ConstructorResult(targetClass = PaymentSummary.class,
                columns = {@ColumnResult(name = "totalAmount", type = BigDecimal.class)}))

public class Payment {

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

    @Column(name = "amount")
    private BigDecimal amount;

    // equals and hashode
}

What do we want aggregate:

import jakarta.persistence.ColumnResult;
import jakarta.persistence.ConstructorResult;
import jakarta.persistence.SqlResultSetMapping;
import lombok.*;

import java.math.BigDecimal;

@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@SqlResultSetMapping(
        name="PaymentSummaryMapping",
        classes=@ConstructorResult(
                targetClass=PaymentSummary.class,
                columns={
                        @ColumnResult(name="totalAmount", type= BigDecimal.class)
                }
        )
)
public class PaymentSummary {
    private BigDecimal totalAmount;
}

And repository:

public interface PaymentRepository extends JpaRepository<Payment, Long> {

    @Query(name = "Payment.findSummary", nativeQuery = true)
    List<PaymentSummary> findSummary();

}

The use of NamedNativeQuery and SqlResultSetMapping allows for flexibility in executing queries and handling results without the need to create additional entities in our model.

Advantages

1. Flexibility in Query Execution: Enables the execution of complex SQL queries, which may be inefficient or difficult to implement using JPQL.
2. Direct Database Access: Allows the use of all SQL capabilities, including aggregate functions, joins, and subqueries.
3. High Performance: Native queries can be optimized for a specific database, potentially leading to better performance.
4. Flexible Result Mapping: SqlResultSetMapping allows mapping query results to any class, not necessarily an entity, which is very convenient for data aggregation.

Limitations

1. Portability: Native queries may not be portable between different databases due to differences in SQL dialects.
2. Maintenance Complexity: SQL queries embedded in the code can complicate system maintenance and modification, especially when they are numerous.
3. SQL Injection Risk: Improper use may expose the system to SQL injection threats, posing a security risk.
4. Lack of Compile-Time Type Checking: Errors in queries or mappings may only manifest at runtime.

Conclusion

The use of NamedNativeQuery and SqlResultSetMapping in JPA provides powerful tools for executing complex SQL queries and aggregating data while maintaining the advantages of strong typing and an object-oriented approach. However, developers should be cautious when utilizing these tools, considering their limitations and potential risks.

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn

Paul Ravvich