Insert the row if it is new, update it if it exists. Spring Data JPA does this by reading the row first. Storm sends the database's own upsert statement, keyed on the primary key.
A nightly job imports cities from a reference dataset. Each row carries the code the dataset assigns, and the same code arrives every night. The write has to insert a new code, update a known one, and stay correct when two imports run at once.
data class City( @PK(generation = NONE) val code: String, // assigned by the source val name: String, val population: Long, ) : Entity<String>
The JPA specification has no upsert. With an assigned key, Spring Data's save() treats the entity as existing and calls merge(), which loads the row before it writes:
@Transactional fun importCity(row: CityRow) { // The key is assigned, so save() calls merge(): it reads the row first cityRepository.save(City(row.code, row.name, row.population)) }
SELECT ... FROM city WHERE code = ? INSERT INTO city (...) VALUES (...) -- or UPDATE, when the row exists -- two imports of a new code both find no row; -- the second INSERT fails on the primary key
The read and the write are two statements. Two imports of the same new code can both read nothing, and the second insert fails with a constraint violation. Hibernate 6.3 added StatelessSession.upsert(), which writes one statement keyed on the id; it runs outside the persistence context, so it is a second API next to the repositories.
upsert is a repository operation, and the dialect writes the statement:
val city = orm upsert City(code = "NL-AMS", name = "Amsterdam", population = 931_298)
-- PostgreSQL INSERT INTO city (code, name, population) VALUES (?, ?, ?) ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, population = EXCLUDED.population -- SQLite: ON CONFLICT (code) DO UPDATE -- MySQL, MariaDB: INSERT ... ON DUPLICATE KEY UPDATE -- Oracle, SQL Server, H2: MERGE INTO city t ... ON (t.code = src.code)
The database decides between insert and update inside one statement, so concurrent imports of the same code do not collide. Kotlin's orm upsert returns the row as stored; upsert on the repository returns nothing and skips the read.
upsert inserts it while its id is 0 and updates it once the id is set. MySQL and MariaDB are the exception, since ON DUPLICATE KEY UPDATE also fires on any unique key, so be deliberate when a table has several. Upsert needs the dialect module for your database, such as storm-postgresql. See Upserts.A list goes through the same statement, sent as JDBC batches of 1000 rows. Inside a transaction the whole import commits together:
val cities = rows.map { City(it.code, it.name, it.population) } transaction { orm.entity(City::class).upsert(cities) // JDBC batches of 1000 rows }
Calling upsert once per row in a loop sends each row in its own round trip. In development, Storm's statement advice reports a single-row write repeated ten or more times and names the list form. The advice tutorial shows how to read it.
| JPA with Hibernate | Storm | |
|---|---|---|
| Upsert | save() with an assigned key: SELECT, then INSERT or UPDATE. StatelessSession.upsert() since 6.3 | orm upsert city, one statement |
| Concurrent writes of a new key | The read and the write can interleave | The database resolves the conflict |
| SQL | Generated by Hibernate per dialect | ON CONFLICT, ON DUPLICATE KEY UPDATE or MERGE, per dialect |
| Lists | saveAll(), a read per entity | upsert(cities), JDBC-batched |
The reference documentation covers the details: