Tutorials/Query results

Typed query results without an entity

A list screen needs three columns and a report needs an aggregate. Neither needs an entity. Spring Data JPA shapes such results with interface projections and constructor expressions; in Storm, any data class with a matching constructor is a result type.

Series · JPA to Storm5 min readKotlin

01The task

An owners list shows id, first name and last name; the owner table has more columns than that. A report counts owners per city, and another counts visits per pet. Each wants a typed result without a mapping layer of its own.

02The JPA way

Spring Data JPA offers two routes. An interface projection, which Spring Data implements with a proxy and, for a closed projection like this one, reads only its properties:

OwnerRepository.kt Kotlin · JPA
1
2
3
4
5
6
7
8
9
10
// Interface-based projection: Spring Data implements it with a proxy
interface OwnerListItem {
    val id: Int
    val firstName: String
    val lastName: String
}

interface OwnerRepository : JpaRepository<Owner, Int> {
    fun findAllProjectedBy(): List<OwnerListItem>
}

And a class, built from a derived query or a JPQL constructor expression:

OwnerRepository.kt Kotlin · JPA
1
2
3
4
5
6
7
8
data class OwnerDto(val id: Int, val firstName: String, val lastName: String)

interface OwnerRepository : JpaRepository<Owner, Int> {

    // A JPQL constructor expression names the class in the query
    @Query("SELECT new com.acme.owners.OwnerDto(o.id, o.firstName, o.lastName) FROM Owner o")
    fun findAllSummaries(): List<OwnerDto>
}

Both work. Either way the query is written in JPQL against the entities, or as a native query whose rows map onto the projection by column alias.

03The Storm way

In Storm a result type is a data class whose constructor matches the selected columns by position. The query builder takes it with a select clause of your own, and the filter and grouping stay metamodel paths:

ReportService.kt Kotlin · Storm
1
2
3
4
5
6
7
data class CityCount(val city: String, val count: Long)

val counts = orm.entity(Owner::class)
    .select(CityCount::class) { "${Owner_.city}, COUNT(*)" }
    .where(Owner_.city like "S%")
    .groupBy(Owner_.city)
    .resultList
↳ generated sql
-- the select clause is the template; the rest comes from the builder
SELECT o.city, COUNT(*)
FROM owner o
WHERE o.city LIKE ?
GROUP BY o.city

The result is a list of immutable CityCount values, ready to serialize. groupBy also takes a relationship, such as Pet_.owner, and then groups by the related row's key in the form each database accepts.

04Full SQL maps the same way

A SQL template maps its rows by position too, onto a class defined next to the query:

OwnerService.kt Kotlin · Storm
1
2
3
4
5
6
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>()

Interpolated values become bind parameters, so a filter value never ends up in the SQL text:

ReportService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
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>()
↳ generated sql
-- $since is a bind parameter
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

The class and the query live and go together. Templates can also expand an entity into its columns and joins; the SQL templates tutorial shows how.

05When the shape is reused

A result class serves one query. A shape that several places read, that nests in other types or takes typed predicates, is a read model, and Storm models it as a Projection with a repository of its own. See Projections.

06Side by side

Spring Data JPAStorm
Result typeAn interface backed by a proxy, or a class with a matching constructorA data class with a matching constructor
MappingBy property name or column alias, or by constructor arguments in JPQLBy column position
Query languageJPQL against the entities, or native SQLThe query builder with your select clause, or SQL templates
Reused read modelAn entity over the table or a viewA Projection

07Keep going

The reference documentation covers the mechanics in depth: