Dropping findById()+save(): Spring Data Now Handles It with a Single SQL

When synchronising data such as product information, payment status, or user profiles, developers often write code that first findById and then save. This results in two separate SQL statements – a SELECT followed by either INSERT or UPDATE – and creates a race window under concurrency.

Problem reproduction

Assume a table payment_state with payment_no as the primary key. Two threads receive the same callback and both execute:

SELECT payment_no = 'P20260830001'   -- returns no row
INSERT ...                         -- both try to insert

One thread succeeds, the other throws DuplicateKeyException. Adding @Transactional does not solve the issue because each transaction is unaware of the other.

Why the SELECT is the bottleneck

The business rule "if exists → UPDATE, else → INSERT" is already known to the database. The Java code unnecessarily pulls the existence check into the application, causing an extra round‑trip and a concurrency window.

Single‑statement Upsert in Spring Data 4.1

Spring Data 4.1 adds a native upsert capability to JDBC and R2DBC templates. The core API is:

JdbcAggregateTemplate.upsert(entity);

Depending on the dialect, Spring generates the appropriate native statement, e.g.:

MySQL/MariaDB: INSERT ... ON DUPLICATE KEY UPDATE PostgreSQL: INSERT ... ON CONFLICT DO UPDATE Other databases: MERGE This reduces the operation to a single database round‑trip.

Practical example

With Java 21, Spring Boot 4.1.1, Spring Data JDBC 4.1.1 and MySQL 8, the service becomes:

@Service
public class PaymentStateService {
    private final JdbcAggregateTemplate template;
    public PaymentStateService(JdbcAggregateTemplate template) { this.template = template; }
    @Transactional
    public void sync(PaymentCallback callback) {
        PaymentState state = new PaymentState(
            callback.paymentNo(),
            callback.userId(),
            callback.amount(),
            callback.status(),
            LocalDateTime.now()
        );
        template.upsert(state);
    }
}

The previous findById → if → save logic disappears entirely.

Concurrency test

A JUnit test launches 50 threads that each call template.upsert(state) for the same primary key. After all threads finish, the table contains exactly one row, demonstrating that the upsert eliminates the duplicate‑key race.

Limitations

Upsert does not support optimistic locking; entities annotated with @Version cannot rely on version checks.

It cannot solve "lost update" problems where the new value depends on the current value (e.g., decrementing stock). In such cases you still need atomic UPDATE … SET column = column - 1 WHERE … or explicit locking.

For pure insert‑only logs (audit, financial ledger) you should keep using plain INSERT to let duplicate‑key errors surface.

When to use Upsert

Ideal scenarios are those where the external system provides a full snapshot and the rule is simply "replace if exists, insert otherwise":

Third‑party status synchronization (payment, logistics, external orders).

State tables updated by message consumers (e.g., customer_snapshot).

Configuration synchronization tables (tenant, shop, channel configs).

Bulk synchronization tasks where eliminating a SELECT per row yields noticeable performance gains.

When not to use Upsert

Do not use it for counters, stock, balances, or any logic that requires reading the current value and applying a delta, because the upsert cannot express the required atomic computation.

Integration notes

If a project already uses Spring Data JPA, the upsert API is not available on JpaRepository. You can add Spring Data JDBC or R2DBC alongside JPA and use JdbcAggregateTemplate.upsert (or R2dbcEntityTemplate.upsert) for the specific tables that benefit from it.

Supported databases include PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, DB2, H2, and HSQLDB. If a dialect lacks native upsert support, calling upsert() will fail rather than silently falling back to a SELECT‑then‑INSERT/UPDATE, preserving the intended concurrency semantics.

Overall, the new single‑statement upsert API lets developers remove boilerplate findById → if → save code, reduce round‑trips, and delegate the existence decision to the database, while being aware of its boundaries.