Tutorials/Projections

Projections: read-only views on your data

A Storm projection is a read-only type over a table, a database view or a SQL query. It has a repository without write methods, takes typed predicates, and nests in other types.

Series · JPA to Storm5 min readKotlin

01The task

A reporting screen reads a database view the DBA maintains. An owners list reads three columns of a wide owner table. A dashboard lists owners with their number of pets. None of them writes.

02The JPA way

JPA's mapped type is the entity, and an entity is read-write. Hibernate's @Immutable marks one read-only, which is the usual mapping for a view:

OwnerAddressView.kt Kotlin · JPA
1
2
3
4
5
6
7
8
9
10
// An entity over a view, read-only through a Hibernate annotation
@Entity
@Immutable
@Table(name = "owner_address_view")
class OwnerAddressView(
    @Id var id: Int? = null,
    var fullName: String = "",
    var address: String = "",
    var city: String = "",
)

@Immutable is Hibernate's, not JPA's. Hibernate does not write changes to such an entity, and its instances are still managed by the persistence context. A Spring Data repository over it offers save() unless you declare a narrower one with only the find methods. For a subset of a table, Spring Data's interface and class projections shape the result of a repository method. For a read model backed by SQL, Hibernate has @Subselect.

03The Storm way

Storm has a type for it. An Entity is read-write; a Projection is read-only. A projection can map a database view:

OwnerAddressView.kt Kotlin · Storm
1
2
3
4
5
6
7
// The class name maps to the view: OwnerAddressView -> owner_address_view
data class OwnerAddressView(
    @PK val id: Int,
    val fullName: String,
    val address: String,
    val city: String,
) : Projection<Int>

Or a subset of a table's columns:

OwnerSummary.kt Kotlin · Storm
1
2
3
4
5
6
7
// Three columns of the owner table, read-only
@DbTable("owner")
data class OwnerSummary(
    @PK val id: Int,
    val firstName: String,
    val lastName: String,
) : Projection<Int>

A subset projection is a second type to keep in step with the table, so start with the entity and add one where the width of the entity shows in the slow statement log or the performance log.

A projection's repository has the read API: findAll, findById, counting, pages, slices, scrolling and the query builder. It has no insert, update or remove, so a write does not compile:

OwnerService.kt Kotlin · Storm
1
2
3
4
val ownerSummaries = orm.projection(OwnerSummary::class)

val all = ownerSummaries.findAll()
val smiths = ownerSummaries.findAll(OwnerSummary_.lastName eq "Smith")
↳ generated sql
-- findAll(): the projection's columns only
SELECT os.id, os.first_name, os.last_name
FROM owner os

-- findAll(OwnerSummary_.lastName eq "Smith")
SELECT os.id, os.first_name, os.last_name
FROM owner os
WHERE os.last_name = ?

The results are immutable data classes, not proxies, and OwnerSummary_.lastName is a path in the generated metamodel.

04Nesting and SQL-backed projections

An entity or another projection can reference a projection through @FK. Storm joins it into the same statement, and a predicate follows the path:

PetView.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
@DbTable("pet")
data class PetView(
    @PK val id: Int,
    val name: String,
    @FK val owner: OwnerSummary,   // a projection inside a projection
) : Projection<Int>

// The predicate follows the path into the nested projection
val smithsPets = orm.projection(PetView::class)
    .findAll(PetView_.owner.lastName eq "Smith")
↳ generated sql
-- the nested projection is joined into the same statement
SELECT pv.id, pv.name, os.id, os.first_name, os.last_name
FROM pet pv
INNER JOIN owner os ON pv.owner_id = os.id
WHERE os.last_name = ?

Within one query, equal owner rows hydrate into one OwnerSummary instance, so a thousand pets of fifty owners hold fifty owner objects. The N+1 tutorial shows the same sharing for entities.

@ProjectionQuery backs a projection with SQL of its own. Storm reads that SQL as a derived table, so a typed predicate applies to its columns, the aggregate included:

OwnerWithPetCount.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@ProjectionQuery("""
    SELECT o.id, o.first_name, o.last_name, COUNT(p.id) AS pet_count
    FROM owner o
    LEFT JOIN pet p ON p.owner_id = o.id
    GROUP BY o.id, o.first_name, o.last_name
""")
data class OwnerWithPetCount(
    @PK val id: Int,
    val firstName: String,
    val lastName: String,
    val petCount: Int,
) : Projection<Int>

// A typed predicate on the aggregate
val ownersWithThreePets = orm.projection(OwnerWithPetCount::class)
    .findAll(OwnerWithPetCount_.petCount greaterEq 3)
↳ generated sql
-- the projection query is the derived table; the predicate applies on top
SELECT owpc.id, owpc.first_name, owpc.last_name, owpc.pet_count
FROM (
    SELECT o.id, o.first_name, o.last_name, COUNT(p.id) AS pet_count
    FROM owner o
    LEFT JOIN pet p ON p.owner_id = o.id
    GROUP BY o.id, o.first_name, o.last_name
) owpc
WHERE owpc.pet_count >= ?
A projection is a reusable read model. For a result shape that serves one query, a plain data class is enough: see Typed query results for mapping by position, and SQL templates for shapes inside full SQL.

05Side by side

JPA with HibernateStorm
Read-only typeAn entity with Hibernate's @ImmutableProjection: its repository has no write methods
Database viewAn entity mapped over the viewA projection named after the view
Subset of a tableAn interface or class projection per repository methodA projection with @DbTable and its own repository
SQL-backed read modelHibernate's @Subselect@ProjectionQuery, filtered as a derived table
InstancesManaged entities, or projection proxiesImmutable data classes

06Keep going

The reference documentation covers the mechanics in depth: