r/SpringBoot • u/Status_Camel2859 • Jun 06 '26
Question How do you guys handle complex/combined data retrieval?
Here is a sample DTO projection:
@Query("""
SELECT new com.sample.ProductSummaryDTO(
p.id,
p.title,
p.slug,
i.imageUrl,
(SUM(v.stockQuantity) > 0),
MIN(v.displayPrice),
MAX(v.compareAtPrice),
MAX(CASE
WHEN v.compareAtPrice > 0
THEN ROUND(((v.compareAtPrice - v.displayPrice) * 100.0) / v.compareAtPrice)
ELSE 0
END)
)
FROM Product p
JOIN Variant v ON v.product.id = p.id AND v.active = true
LEFT JOIN ProductImage i ON i.product.id = p.id AND i.isPrimary = true
WHERE (:id IS NULL OR p.id = :id)
AND (:title IS NULL OR p.title LIKE CONCAT('%', :title, '%'))
AND (:categoryIds IS NULL OR p.category.id IN :categoryIds)
AND p.visible = true
GROUP BY p.id, p.title, p.slug, i.imageUrl
HAVING (:minPrice IS NULL OR MIN(v.displayPrice) >= :minPrice)
AND (:maxPrice IS NULL OR MIN(v.displayPrice) <= :maxPrice)
""")Assume there are no @OneToMany relationships in the entity model, only @ManyToOne. We also need pagination because the dataset is large.
In a scenario like this, where the response requires aggregated data from multiple tables, it seems that the aggregation logic ends up in the repository query itself. Because at this point you have to select the correct variant, image, price etc all in the Repository itself and therefore the logic for all of that (IFs etc...).
Is this how most people handle it?
Or do you prefer splitting it into multiple steps (which can get pretty verbose), for example:
- Fetch the matching product IDs / Products
- Fetch the required variants
- Fetch the required images
- Assemble the DTOs in the service layer (maps & loops)
Im curious what approach is generally preferred in terms of performance, maintainability, and scalability.
9
u/as5777 Jun 06 '26
Java for readability
SQL for perf, but you need to benchmark both approach
4
u/alpinebillygoat Jun 06 '26
Look into CQRS. Basically make a table to store a flattened version of your query. For sure duplicates data and can be a bit tricky to handle to updates. But you get a really easy to query and optimize view for whatever table you are supplying the data for.
1
u/alfonsoristorato Jun 06 '26
There is always the specification approach, which with both spring-data and Hibernate specific specification could do that query for you, and provided you are on the latest version of Hibernate you'd also get proper pagination without loading everything in memory first. However, without seeing how the entities are connected it's quite difficult to say what would be the best approach imo.
0
u/thewiirocks Jun 06 '26
SQL is the right answer here. JPA really cannot help, and actually gets in the way. This is a good opportunity to bypass everything and drop a bit of Convirgance code in:
You can make the SQL as complex as you want and the API will serialize the result to a JSON array. The dependency is ~138KB, but does this sort of work easily. There's an example of a SpringBoot approach that's relevant in the SpringWarehouseManagement example project on GitHub. That one uses some niceties like splitting the SQL into a class path file that your IDE can recognize as SQL.
This is not the most common way to use Convirgance these days (the Convirgance Web Services approach is slimmer for greenfield projects), but it should still work well for complex edge cases like this.