Tutorials/Mapped collections

Mapped collections vs queried associations

A mapped collection and a queried association are each one line of code. Storm's line also states when it runs, what it loads, and how it is filtered and paged.

Series · JPA to Storm6 min readKotlin
JPA
val pets = owner.pets
Storm
val pets = orm.findAll(Pet_.owner eq owner)

01The task

An owner has pets; a user has roles through a join table. Show the pets of an owner and the roles of a user, the two classic association shapes: one-to-many and many-to-many.

02The JPA way

JPA maps both associations onto the entity, and afterwards navigation is just Kotlin:

Entities.kt Kotlin · JPA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Entity
class Owner(
    @Id @GeneratedValue var id: Int? = null,
    var name: String = "",

    @OneToMany(mappedBy = "owner")
    var pets: MutableList<Pet> = mutableListOf(),
)

@Entity
class User(
    @Id @GeneratedValue var id: Int? = null,
    var name: String = "",

    @ManyToMany
    @JoinTable(name = "user_role")
    var roles: MutableSet<Role> = mutableSetOf(),
)

// Navigation is a property access
val pets = owner.pets
val roles = user.roles

There is no query in the calling code, and that is convenient. The costs come from one fact: owner.pets is an unbounded query behind a property. It runs on first access, once per owner, so a loop over owners is an N+1 unless batch fetching or a fetch join covers it. It loads the whole collection: showing ten of two thousand pets loads two thousand. The list is a lazy collection bound to the session, and touched for the first time outside it, it throws LazyInitializationException. Once the association has to be filtered, sorted or paged, you write petRepository.findByOwner(owner, pageable), and the mapped collection no longer serves that read. On the write side, cascade types and orphan removal decide what a change to the collection means.

03The Storm way: one-to-many

Storm entities hold no collections. The association lives where the schema has it, on the many side, and reading it is a query:

PetService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
// The many side owns the relation, as it does in the schema
data class Pet(
    @PK val id: Int = 0,
    val name: String,
    @FK val owner: Owner,
) : Entity<Int>

// The "collection" is a query: one line, loaded when you ask
val pets = orm.findAll(Pet_.owner eq owner)
↳ generated sql
-- runs when you ask, loads what you asked for
SELECT p.id, p.name, o.id, o.name
FROM pet p
INNER JOIN owner o ON p.owner_id = o.id
WHERE p.owner_id = ?

The query runs when you call it and loads what you ask for. Filtering, sorting, paging and loading for many owners at once use the same query builder:

PetService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
// The association is a query, so it takes an order and a limit
val firstTen = orm.entity(Pet::class).select()
    .where(Pet_.owner eq owner)
    .orderBy(Pet_.name)
    .limit(10)
    .resultList

// The pets of many owners in one query, grouped per owner
val petsByOwner: Map<Owner, List<Pet>> = orm.entity(Pet::class).select()
    .where(Pet_.owner inList owners)
    .resultGroupedBy(Pet_.owner)

04The Storm way: many-to-many

For many-to-many, Storm models the join table as the database has it, an entity with a composite key:

UserRole.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// The join table is a real entity with a composite key
data class UserRolePk(val userId: Int, val roleId: Int)

data class UserRole(
    @PK val userRolePk: UserRolePk,
    @FK @Persist(insertable = false, updatable = false) val user: User,
    @FK @Persist(insertable = false, updatable = false) val role: Role,
) : Entity<UserRolePk>

// Roles of a user, through the join entity ...
val roles = orm.findAll(UserRole_.user eq user).map { it.role }

// ... or Role filtered by the join table, which adds no rows
val sameRoles = orm.entity(Role::class).select()
    .semiJoin<UserRole>().on<Role>()
    .where(UserRole_.user eq user)
    .resultList
↳ generated sql
-- the second form: one query, each role once
SELECT r.id, r.name
FROM role r
WHERE EXISTS (SELECT 1 FROM user_role ur
    WHERE ur.role_id = r.id AND ur.user_id = ?)

The second form uses semiJoin: the join table filters the roles without adding rows, so each role comes back once and the query needs no distinct. See Filtering by Related Rows.

This is more code than @ManyToMany with @JoinTable. In return, writes are plain inserts and deletes of UserRole rows, with no cascade settings involved. And the join table is a type from the start, so when it gets columns such as grantedAt or grantedBy, you add fields to an existing entity. In JPA, that is the point where a @ManyToMany is usually rewritten as an entity of this shape.

05Side by side

JPA mapped collectionsStorm queried associations
Navigationowner.pets, one propertyorm.findAll(Pet_.owner eq owner), one line
When it loadsOn first access, the whole collection, per ownerWhen the query runs, what it selects
Filter, sort, pageA separate repository query in practiceThe same query, composed
Join tableHidden until it needs columns, then refactoredAn entity from day one
Outside the sessionAn untouched lazy collection throws LazyInitializationExceptionResults are plain values

For small associations that are always loaded whole and used inside the session, JPA's property is the shorter code. Associations that grow, or get filtered and paged, end up as queries in either framework; Storm starts there.

06Keep going

The reference documentation covers the mechanics in depth: