Tutorials/SQL templates

Full SQL with your model

Some queries are best written in the database's own SQL. In JPA that is a native query, a string the entity model does not see. A Storm SQL template is SQL text in which values bind as parameters and entity types and metamodel paths expand into columns, joins and aliases.

Series · JPA to Storm6 min readKotlin

01The task

A report ranks cities by population with a window function, RANK() OVER, and the team writes it in the database's SQL. A second query reads owners by the name of their city, and should not repeat the column list and the join by hand.

02The JPA way

JPA runs SQL through a native query. Spring Data maps its rows onto an interface projection:

CityRepository.kt Kotlin · JPA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// Spring Data maps native rows onto an interface projection by alias
interface RankedCity {
    val name: String
    val population: Long
    val rank: Long
}

@Query(
    value = """
        SELECT name, population,
               RANK() OVER (ORDER BY population DESC) AS rank
        FROM city
        WHERE population >= :minPopulation
    """,
    nativeQuery = true,
)
fun rankedCities(minPopulation: Long): List<RankedCity>

Each property of the interface matches a column alias, and the result is a proxy behind the interface. A class result takes an @SqlResultSetMapping. The SQL text is not checked against the entities, so a column renamed in an entity mapping stays in the native query until it runs.

03The Storm way

A Storm SQL template is a Kotlin string in a lambda. Interpolated values become bind parameters, and rows map by position onto any data class:

ReportService.kt Kotlin · Storm
1
2
3
4
5
6
7
data class RankedCity(val name: String, val population: Long, val rank: Long)

val ranked = orm.query { """
    SELECT name, population, RANK() OVER (ORDER BY population DESC)
    FROM city
    WHERE population >= $minPopulation
""" }.resultList<RankedCity>()
↳ generated sql
-- $minPopulation is a bind parameter
SELECT name, population, RANK() OVER (ORDER BY population DESC)
FROM city
WHERE population >= ?

The Storm compiler plugin, which the Storm Gradle plugin applies, wraps each interpolation in the lambda in a t() call, so $minPopulation reaches the database as a ? with a bound value. Without the compiler plugin, write t(minPopulation) yourself. A null value binds as SQL NULL. RankedCity is an ordinary data class defined next to the query.

04Templates that know your model

A template also takes entity types and metamodel paths. A type in the select list expands to its columns; in the FROM clause it expands to its table and the joins its @FK fields declare. A metamodel path resolves to the column on the right alias:

OwnerService.kt Kotlin · Storm
1
2
3
4
5
6
// A type expands to its columns, or to its table and joins
val owners = orm.query { """
    SELECT ${Owner::class}
    FROM ${Owner::class}
    WHERE ${Owner_.address.city.name} = $cityName
""" }.resultList<Owner>()
↳ generated sql
-- Owner expands to its columns and those of its city, FROM gains the @FK join,
-- and Owner_.address.city.name resolves to the city alias
SELECT o.id, o.first_name, o.last_name, o.street, c.id, c.name, c.population, o.telephone
FROM owner o
LEFT JOIN city c ON o.city_id = c.id
WHERE c.name = ?

The parts you write stay SQL, and the column lists and joins come from the model in the same statement. A renamed field changes the metamodel, so a path to it no longer compiles.

05One-off result shapes

A report rarely needs an entity. A type that implements Data gets template expansion and the joins of its @FK fields, without a repository:

ReportService.kt Kotlin · Storm
1
2
3
4
5
6
7
8
9
10
11
12
13
// Data enables template expansion for a type without a repository
@DbTable("pet")
data class PetWithOwner(
    val name: String,
    val birthDate: LocalDate?,
    @FK val owner: Owner,
) : Data

val pets = orm.query { """
    SELECT ${PetWithOwner::class}
    FROM ${PetWithOwner::class}
    WHERE ${Owner_.address.city} = $city
""" }.resultList<PetWithOwner>()

That gives three steps. A plain data class such as RankedCity takes a SELECT you write in full and maps by position (see Typed query results). A Data type adds expansion and joins. A shape that the codebase reuses, with a repository, nesting and predicates, is a Projection (see Projections).

06Side by side

JPA native queriesStorm SQL templates
ParametersNamed or positional placeholdersInterpolated values, bound as parameters
Result mappingInterface projections by column alias; @SqlResultSetMapping for classesAny data class, by position
Model awarenessThe SQL text is not checked against the entitiesTypes expand to columns and joins; metamodel paths resolve to aliased columns
EngineSeparate from JPQL and the Criteria APIThe same engine the query builder renders through

07Keep going

The reference documentation covers the mechanics in depth: