Typed query results without the mapping layer

A list screen needs three columns, a report needs an aggregate, and neither deserves an entity. JPA shapes results with proxies and constructor expressions; in Storm, any data class is a result type.

01The task

An owners list screen needs three fields: id, first name, last name. The owner table has a dozen columns. A reporting endpoint needs visit counts per pet, which matches no table at all. Both want a typed result shape without a hand-written mapping layer between query results and DTOs.

02The JPA way

Spring Data JPA offers two main routes. The first is an interface projection, resolved by method-name convention and implemented by a proxy at runtime:

// Interface-based projection: implemented by a runtime proxy.
interface OwnerListItem {
    val id: Int
    val firstName: String
    val lastName: String
}

interface OwnerRepository : JpaRepository<Owner, Int> {
    fun findAllProjectedBy(): List<OwnerListItem>   // the method name selects the projection type
}

The second is a class-based DTO through a JPQL constructor expression:

data class OwnerDto(val id: Int, val firstName: String, val lastName: String)

interface OwnerRepository : JpaRepository<Owner, Int> {

    // The fully qualified class name lives inside a query string.
    @Query("SELECT new com.acme.owners.OwnerDto(o.id, o.firstName, o.lastName) FROM Owner o")
    fun findAllSummaries(): List<OwnerDto>
}

Both work, and both resolve at runtime. The constructor expression embeds a fully qualified class name inside a string, so renaming the class or reordering its parameters fails when the query first runs, not when the code compiles. Interface projections return proxies, and which columns are actually selected depends on the projection kind and how the query was derived, so the SQL log is the only place to confirm you got the narrow query you wanted.

03The Storm way

Storm needs no special machinery for result shapes. Any data class whose constructor matches the query's columns by position and type hydrates directly. Start with the type-safe query builder: you supply only the select clause, and the where and grouping stay compile-checked against the metamodel:

// The query builder selects into a result type too
data class CityCount(val city: String, val count: Long)

val counts = orm.entity<Owner>()
    .select<CityCount, _, _> { "${Owner_.city}, COUNT(*)" }
    .where(Owner_.city like "S%")
    .groupBy(Owner_.city)
    .resultList

-- only the select clause is yours; the rest stays generated and type-safe
SELECT o.city, COUNT(*)
FROM owner o
WHERE o.city LIKE ?
GROUP BY o.city

No proxy, no naming convention, no class name inside a string. The result is a list of plain immutable objects that serialize to JSON as they are.

04Full SQL works the same way

When a query outgrows the builder, or you simply want to write the SQL, the same positional mapping applies. The DTO is just a class, defined next to the query that fills it:

// Any data class is a result type: no marker interface, no registration
data class OwnerListItem(val id: Int, val firstName: String, val lastName: String)

val owners = orm.query { """
    SELECT id, first_name, last_name
    FROM owner
""" }.resultList<OwnerListItem>()

Aggregates work identically, and interpolated values compile to bind parameters, so dynamic filters stay injection-safe:

// Aggregates and reports work the same way
data class VisitCount(val petName: String, val visits: Long)

val counts = orm.query { """
    SELECT p.name, COUNT(v.id)
    FROM pet p
    JOIN visit v ON v.pet_id = p.id
    WHERE v.visit_date >= $since
    GROUP BY p.name
""" }.resultList<VisitCount>()

-- $since compiles to a bind parameter, never string concatenation
SELECT p.name, COUNT(v.id)
FROM pet p
JOIN visit v ON v.pet_id = p.id
WHERE v.visit_date >= ?
GROUP BY p.name

Define the class next to the query, and delete both together when the report goes away. The full template syntax, including letting Storm generate column lists and joins for you, is covered in the SQL templates tutorial.

05When the shape earns more

06Side by side

07Keep going

The reference documentation covers the mechanics in depth: