Projection in Spring Data JPA for Mapping Aggregation Results to DTOs

Spring Data JPA provides developers with powerful data management tools, among which Projection holds a special place. This article will discuss how to use Projection to map the results of aggregation to DTOs or entities, simplifying data handling and enhancing application efficiency.

Projection in Spring Data JPA for Mapping Aggregation Results to DTOs

Hi, this is Paul, and welcome to this article, we explore how to map non-entity objects like aggregation to Java Objects using Projections Interface.

What is Projection?

Projection, in the context of Spring Data JPA, is a way to define a subset of entity attributes or aggregated values to be returned as the result of a query execution. This allows for reducing the amount of data retrieved, improving performance, and simplifying further result processing.

Example: Mapping Aggregated Values to a DTO

Let’s assume we have an Order entity representing orders in the system:

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

import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "orders")
public class Order {

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

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

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    // equals & hashCode
}

And SQL schema:

CREATE TABLE orders
(
    id         BIGSERIAL PRIMARY KEY,
    price      DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP      NOT NULL
);

Our task is to obtain the total sum of prices for orders within a specific period.

Step 1: Creating a DTO for the Aggregation Result

We will create a class TotalPriceProjection that will be used to map the query result:

public interface TotalPriceProjection {
    BigDecimal getTotalPrice();
}

Step 2: Defining the Method in the Repository

Let’s modify the repository by adding a method that will return an instance of TotalPriceProjection, using a JPQL query with a constructor expression:

import com.example.demo.model.Order;
import com.example.demo.model.TotalPriceProjection;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDateTime;

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("""
        SELECT SUM(o.price) as totalPrice
        FROM Order o
        WHERE o.createdAt BETWEEN :startDate AND :endDate
        """)
    TotalPriceProjection sumTotalPriceBetweenDates(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate
    );
}

Step 3: Utilizing the Result in the Application

Now, we can use this method in our service or controller to get the total sum of prices:

@Slf4j
@Service
@RequiredArgsConstructor
public class OrderServiceImpl implements OrderService {

    private final OrderRepository orderRepository;

    @EventListener(condition = "event.applicationContext.parent == null")
    public void init(ContextRefreshedEvent event) {
        test();
    }

    @Override
    public void test() {
        LocalDateTime startDate = LocalDateTime.of(2024, 4, 1, 8, 30);
        LocalDateTime endDate = LocalDateTime.of(2024, 4, 10, 17, 45);
        TotalPriceProjection totalPriceProjection = orderRepository.sumTotalPriceBetweenDates(startDate, endDate);
        log.info("Total price between dates: {}", totalPriceProjection.getTotalPrice());
    }
}

Conclusion

Using Projection to map aggregation results to DTOs is an effective way to optimize data handling in Spring Data JPA. This approach reduces database load, decreases the volume of data transferred, and simplifies result processing on the application side. Implementing projections makes the code cleaner and more maintainable, and provides additional flexibility when dealing with various types of queries and data aggregations.

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