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.
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.
JPA runs SQL through a native query. Spring Data maps its rows onto an interface projection:
// 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.
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:
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>()
-- $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.
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:
// 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>()
-- 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.
A report rarely needs an entity. A type that implements Data gets template expansion and the joins of its @FK fields, without a repository:
// 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).
| JPA native queries | Storm SQL templates | |
|---|---|---|
| Parameters | Named or positional placeholders | Interpolated values, bound as parameters |
| Result mapping | Interface projections by column alias; @SqlResultSetMapping for classes | Any data class, by position |
| Model awareness | The SQL text is not checked against the entities | Types expand to columns and joins; metamodel paths resolve to aliased columns |
| Engine | Separate from JPQL and the Criteria API | The same engine the query builder renders through |
The reference documentation covers the mechanics in depth: