Multiselect, @SqlResultSetMapping, and Native Projection

JPQL covers 80% of query needs. The other 20% requires SQL features that JPQL does not support: window functions, CTEs, lateral joins, array aggregations, and database-specific functions. When you reach that boundary, you have two choices: fight JPQL or use native queries with proper result mapping.

The Lie

JPQL is sufficient for all query needs. If you need native SQL, you are probably doing something wrong.

The Reality

JPQL is a deliberately limited subset of SQL. It maps to entity relationships, not database structures. It does not support:

• Window functions (ROW_NUMBER(), RANK(), LAG(), LEAD())
• Common Table Expressions (WITH)
• Lateral joins
• Array/JSON aggregation (array_agg, jsonb_agg)
• Full-text search operators (@@, to_tsvector)
• Recursive queries
• INSERT ... ON CONFLICT (upsert)
• Database-specific type casts and operators

For reporting queries, analytics, and anything beyond basic CRUD, you will reach JPQL’s limits. Native queries with proper result mapping are the pragmatic solution.

The Evidence

Consider a dashboard that shows each product’s monthly revenue alongside the previous month’s revenue for trend comparison. This requires LAG(), a window function.

// BAD: Trying to work around JPQL limitations
@Transactional(readOnly = true)
public List<ProductTrend> getProductTrends() {
    // JPQL cannot express LAG() window function.
    // Workaround: load all monthly revenues and compute in Java.
    List<Object[]> monthlyRevenues = entityManager.createQuery("""
        SELECT p.id, p.name,
               FUNCTION('date_trunc', 'month', o.createdAt),
               SUM(oi.quantity * oi.unitPrice)
        FROM OrderItem oi
        JOIN oi.order o
        JOIN oi.product p
        WHERE o.status = 'COMPLETED'
        GROUP BY p.id, p.name,
                 FUNCTION('date_trunc', 'month', o.createdAt)
        ORDER BY p.id, FUNCTION('date_trunc', 'month', o.createdAt)
        """, Object[].class).getResultList();

    // Now you compute LAG manually in Java.
    // You loaded all months for all products into memory.
    // You wrote code the database could have executed in milliseconds.
    // ...
}

The in-Java computation loads all historical data into heap, iterates it, and performs grouping logic that SQL engines are optimized for. On 10,000 products with 24 months of data, you load 240,000 rows to compute what the database can produce as a sorted, windowed result set.

The Fix

Use a native query with @SqlResultSetMapping:

// BETTER: Native query with window function
@Entity
@SqlResultSetMapping(
    name = "ProductTrendMapping",
    classes = @ConstructorResult(
        targetClass = ProductTrend.class,
        columns = {
            @ColumnResult(name = "product_id", type = Long.class),
            @ColumnResult(name = "product_name", type = String.class),
            @ColumnResult(name = "month", type = LocalDate.class),
            @ColumnResult(name = "revenue", type = BigDecimal.class),
            @ColumnResult(name = "prev_revenue", type = BigDecimal.class)
        }
    )
)
public class Product {
    // ... entity fields
}

public record ProductTrend(
    Long productId,
    String productName,
    LocalDate month,
    BigDecimal revenue,
    BigDecimal prevRevenue
) {
    public BigDecimal growthPct() {
        if (prevRevenue == null || prevRevenue.signum() == 0) return null;
        return revenue.subtract(prevRevenue)
            .divide(prevRevenue, 4, RoundingMode.HALF_UP)
            .multiply(BigDecimal.valueOf(100));
    }
}

// Repository or service method
@Transactional(readOnly = true)
public List<ProductTrend> getProductTrends(int year) {
    @SuppressWarnings("unchecked")
    List<ProductTrend> trends = entityManager.createNativeQuery("""
        WITH monthly AS (
            SELECT
                p.id AS product_id,
                p.name AS product_name,
                date_trunc('month', o.created_at)::date AS month,
                SUM(oi.quantity * oi.unit_price) AS revenue
            FROM order_items oi
            JOIN orders o ON o.id = oi.order_id
            JOIN products p ON p.id = oi.product_id
            WHERE o.status = 'COMPLETED'
              AND EXTRACT(YEAR FROM o.created_at) = :year
            GROUP BY p.id, p.name, date_trunc('month', o.created_at)
        )
        SELECT
            product_id,
            product_name,
            month,
            revenue,
            LAG(revenue) OVER (
                PARTITION BY product_id ORDER BY month
            ) AS prev_revenue
        FROM monthly
        ORDER BY product_id, month
        """, "ProductTrendMapping")
        .setParameter("year", year)
        .getResultList();

    return trends;
}

// Generated SQL: exactly what's written above.
// The database computes the window function.
// Result: one row per product per month, with previous month's revenue.
// Memory: only the final result set, no intermediate data.

Simpler Alternative: Tuple Result

If @SqlResultSetMapping feels heavy for a one-off query, use Tuple:

// ALTERNATIVE: Native query returning Tuples
@Query(value = """
    SELECT p.id, p.name,
           date_trunc('month', o.created_at)::date AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    JOIN products p ON p.id = oi.product_id
    WHERE o.status = 'COMPLETED'
    GROUP BY p.id, p.name, date_trunc('month', o.created_at)
    ORDER BY p.id, month
    """, nativeQuery = true)
List<Tuple> findMonthlyRevenue();

Tuple results require no mapping class. The trade-off is stringly-typed access with no compile-time safety.

The Cost Model

Approach	Data Transferred	Computation Location	Memory	Safety
JPQL + Java post-processing	All raw data	JVM heap	High (full data in memory)	Type-safe
Native + @SqlResultSetMapping	Computed result only	Database	Low (result set only)	Type-safe
Native + Tuple	Computed result only	Database	Low (result set only)	Stringly-typed

The performance difference depends on data volume. For the product trend example:

• JPQL approach: Loads 240,000 rows (10k products × 24 months), processes in Java. Transfer: ~10 MB. Heap: ~15 MB. Time: ~500ms.
• Native with LAG(): Database returns 240,000 rows with pre-computed previous revenue. Transfer: ~12 MB (one extra column). Heap: ~12 MB. Time: ~200ms. The database’s sort and window function implementation is faster than Java iteration for this type of computation.

For small data sets (under 1,000 rows), the difference is imperceptible. For reporting and analytics queries over large data sets, native queries with proper result mapping are the right tool. Trying to force everything through JPQL creates worse code and worse performance.