Calling external REST APIs allows a Spring Boot application to retrieve information, submit data, and communicate with other services over HTTP. Spring Boot 4 supports RestClient for making these synchronous requests, providing methods to specify endpoints, add HTTP headers, and send JSON request bodies. When a response comes back, Spring can convert the JSON into a Java object, allowing us to access the returned data without manually parsing it. We can also configure connection and read timeouts, handle HTTP errors, and control how the application responds when an external service encounters a problem.

Preparing RestClient for API Communication

Before sending HTTP requests to an external API, we need to give Spring Boot the necessary dependencies and configure the client that will handle communication. Spring Boot 4 provides an auto-configured RestClient.Builder that we can customize for a particular service, including its base URL and network timeouts. Keeping these settings in a dedicated client also means we don’t have to repeat the same configuration for every request. With the client configured, the application has a consistent point of communication with the external service.

Adding the RestClient Dependency

Spring Boot 4 includes a dedicated starter called spring-boot-starter-restclient for synchronous HTTP communication. It provides the Spring components needed to create REST clients, along with support for converting JSON data through Jackson. Rather than adding individual HTTP libraries and configuring their integration manually, we can add the starter and let Spring Boot handle the default configuration.

For a Maven project, we’ll add the following dependency to the existing pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-restclient</artifactId>
</dependency>

Maven retrieves the dependency and its required libraries during the next project build. We don’t need to specify the version individually when the project already inherits Spring Boot’s dependency management through its parent POM or an imported BOM. This keeps the REST client libraries aligned with the Spring Boot version configured for the application, without requiring us to manage their versions individually.

The dedicated starter is part of Spring Boot 4’s more modular dependency structure. Earlier Spring Boot applications commonly obtained REST client support through broader web starters that also provided components for building HTTP endpoints. With Spring Boot 4, an application that only communicates with external APIs can include spring-boot-starter-restclient without adding Spring MVC or an embedded web server. If the application also exposes HTTP endpoints through Spring MVC, spring-boot-starter-webmvc provides the components needed for that functionality.

Within the starter, Spring supplies RestClient, which was introduced in Spring Framework 6.1 and remains the recommended synchronous HTTP client for new imperative code. Spring Framework 7 also deprecated the older RestTemplate API in favor of RestClient. Both clients communicate over HTTP, but RestClient provides a fluent API where request configuration follows a sequence of method calls such as get(), post(), retrieve(), and body().

The synchronous behavior is important because the calling thread waits for the HTTP operation to finish before execution continues. If we request information from an external service, the method calling RestClient remains occupied until the request completes or fails. This behavior works well in traditional Spring MVC applications and other imperative Java code where operations execute in sequence.

Spring Boot also configures the supporting components needed for the client. Its HTTP message converters handle transformations between Java objects and supported content formats, while a ClientHttpRequestFactory provides the underlying HTTP communication. Spring Boot selects the request factory according to the HTTP libraries available on the application’s classpath, allowing different projects to rely on different HTTP implementations without changing the methods called through RestClient.

An important part of this integration is Spring Boot’s RestClient.Builder bean. Rather than constructing every client directly through RestClient.create(), we can inject the auto-configured builder into a Spring-managed component and customize it for the service we’re contacting. This allows our client to inherit Spring Boot’s existing HTTP configuration, including registered customizations and supported request factory settings.

Configuring the Base URL

External REST APIs generally provide a common server address followed by endpoint locations for particular resources. Instead of repeating the entire address for every operation, RestClient allows us to define a base URL that becomes the starting address for requests made through that client.

For the examples we will be using today, we’ll communicate with JSONPlaceholder, a public testing API that provides sample resources such as posts, users, and comments. Its address is https://jsonplaceholder.typicode.com, and individual resources are available through endpoints like /posts and /users. Because JSONPlaceholder doesn’t require authentication for these operations, we can concentrate on configuring the client before introducing request-specific behavior.

The base URL can be stored in Spring Boot’s application.properties file:

posts.api.base-url=https://jsonplaceholder.typicode.com

Spring Boot loads this property when the application starts, making its value available to components managed by the Spring container. The posts.api.base-url name is a custom application property rather than a predefined Spring Boot setting, so we can give it a name that identifies the external service we’re contacting.

Keeping the address in configuration is helpful because the remote API location can differ between environments. During development, we may communicate with a testing server, while a production deployment communicates with another address. Each environment can supply its own property value without requiring changes to the Java class responsible for creating the client.

Next, we’ll create a service named PostClient that receives the configured address and builds a reusable RestClient instance:

package com.example.apiclient;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestClient;

@Service
public class PostClient {

    private final RestClient restClient;

    public PostClient(
            RestClient.Builder builder,
            @Value("${posts.api.base-url}") String baseUrl) {

        this.restClient = builder
                .baseUrl(baseUrl)
                .build();
    }
}

We register PostClient as a Spring-managed component through the @Service annotation, allowing Spring to create the class and supply its constructor dependencies. During construction, Spring injects the auto-configured RestClient.Builder and resolves the ${posts.api.base-url} placeholder through @Value. The constructor receives the configured address as a string, so the class doesn’t need to contain the actual server address.

Calling baseUrl() assigns that address to the builder, while the following build() call creates the RestClient instance with the selected configuration. We then store the client in the final restClient field, allowing methods inside the service to access the same instance without rebuilding it for every HTTP operation.

With the configured address, requesting the endpoint /posts/1 targets https://jsonplaceholder.typicode.com/posts/1. The base URL supplies the common server address, while the request supplies the location of the resource being accessed. For an API with multiple endpoints, this reduces repeated URL text and keeps the remote server configuration in a single location.

The RestClient.Builder is stateful, meaning changes made to the builder affect clients subsequently created from that builder. Spring Boot provides prototype-scoped builder instances, so a component receiving its own injected builder can configure it without changing the builders injected into other components. If we need to create several clients from the same builder instance, we can also call clone() to obtain an independent builder before applying further configuration.

After construction, the RestClient instance can safely be shared across multiple threads. We don’t need to create a new instance for every HTTP operation, and storing it as a field allows the service to retain the same client configuration throughout its lifetime.

There’s also a distinction between configuring a client through Spring Boot’s injected builder and creating one directly through Spring Framework’s static factory methods. Although RestClient.create() is supported, a client created that way doesn’t automatically inherit the customizations applied to Spring Boot’s injected builder. By receiving the builder through dependency injection, we retain the HTTP configuration already supplied by the application.

At this point, our service has a reusable REST client associated with a particular external API. Before adding the methods that communicate with its endpoints, we can configure how long the client waits when establishing a connection or receiving data from the server.

Configuring Connection Timeouts

Network communication introduces delays that aren’t completely controlled by the application. The remote server could be temporarily unavailable, the network connection could be interrupted, or the service could take longer than expected to respond. Because RestClient performs synchronous requests, the calling thread remains occupied while the HTTP operation is waiting, making timeout configuration an important part of preparing the client. Spring Boot 4 provides configuration properties for controlling connection and read timeouts. These settings determine how long certain network operations are allowed to wait before the HTTP client reports a timeout, giving us control over delays that could leave requests waiting longer than intended.

We can configure both values through application.properties:

spring.http.clients.connect-timeout=3s
spring.http.clients.read-timeout=5s

The connect-timeout property controls the time allowed to establish a connection with the remote server. With a value of three seconds, the client applies that connection limit through its underlying HTTP implementation. If establishing the connection exceeds the permitted duration, the operation can fail with a connection timeout rather than continuing to wait indefinitely.

The read-timeout property controls how long the HTTP client waits for response data according to the selected HTTP library. In our configuration, the read timeout is five seconds, which becomes relevant when communication has begun but the server hasn’t delivered the expected data within the permitted interval.

Connection and read timeouts apply to different stages of HTTP communication. Establishing a connection involves contacting the remote host and opening the connection needed to communicate, while reading occurs during the exchange of response data. The exact behavior of these settings depends on the underlying HTTP client library, including how it measures waiting periods during network activity.

We also need to distinguish a read timeout from a total request duration limit. Some HTTP implementations apply read timeouts to periods spent waiting for network data rather than the complete duration of an operation. Consequently, setting a five-second read timeout doesn’t necessarily mean that every HTTP request will finish within five seconds, as the complete operation can involve multiple stages of communication.

The spring.http.clients property group is the current Spring Boot 4 location for global HTTP client settings. Earlier Spring Boot versions exposed imperative client configuration through spring.http.client, but Spring Boot 4 introduced the plural spring.http.clients group for shared HTTP client configuration.

These properties apply to Spring Boot’s auto-configured HTTP clients, including those created through the injected RestClient.Builder. If we’re communicating with several external APIs through different clients, they can inherit the same timeout values without requiring us to configure each service individually.

Spring Boot selects the underlying HTTP request factory based on the libraries available to the application. Supported implementations include Apache HttpClient, Jetty HttpClient, Reactor Netty, the JDK HTTP client, and the JDK’s HttpURLConnection implementation. The selected factory determines how HTTP operations are performed and can affect the exact behavior of the configured timeouts. For some applications, shared timeout values are sufficient, while others communicate with external services that have different response characteristics. An API that typically responds within a few hundred milliseconds may benefit from shorter limits than a service that performs longer processing before returning data. We can account for these differences by configuring individual clients rather than assigning the same timeout values to every external connection.

Spring Boot provides HttpClientSettings and ClientHttpRequestFactoryBuilder for creating a request factory with settings selected for a particular client. These classes allow us to specify timeout durations directly in Java when the global properties aren’t appropriate for every service.

To give our existing PostClient its own timeout configuration, we can replace its constructor with the following version:

public PostClient(
        RestClient.Builder builder,
        @Value("${posts.api.base-url}") String baseUrl) {

    HttpClientSettings settings = HttpClientSettings.defaults()
            .withConnectTimeout(Duration.ofSeconds(3))
            .withReadTimeout(Duration.ofSeconds(5));

    ClientHttpRequestFactory requestFactory =
            ClientHttpRequestFactoryBuilder.detect().build(settings);

    this.restClient = builder
            .baseUrl(baseUrl)
            .requestFactory(requestFactory)
            .build();
}

We begin by calling HttpClientSettings.defaults() to create an object containing the default HTTP client settings. From there, withConnectTimeout() and withReadTimeout() return updated settings containing the durations we’ve specified. These methods accept Java Duration values, so we can express the time limits through methods such as Duration.ofSeconds() without manually converting between time units.

After defining the settings, we call ClientHttpRequestFactoryBuilder.detect() to select an appropriate HTTP request factory builder according to the libraries available on the classpath. The following build(settings) call creates a request factory configured with those timeout values. Finally, we pass the factory into requestFactory() before constructing the RestClient, giving this particular client its own HTTP communication settings.

The replacement constructor requires the following imports alongside those already present in PostClient:

import java.time.Duration;

import org.springframework.boot.http.client.ClientHttpRequestFactoryBuilder;
import org.springframework.boot.http.client.HttpClientSettings;
import org.springframework.http.client.ClientHttpRequestFactory;

The Duration class represents the timeout intervals, while HttpClientSettings stores the configuration we want to apply. We then rely on ClientHttpRequestFactoryBuilder to construct the request factory, which implements the ClientHttpRequestFactory interface expected by RestClient.Builder. These imports complete the constructor’s dependencies without requiring changes to the service’s existing class declaration or its restClient field.

This configuration allows an individual client to have timeout values that differ from the global properties. If we’re communicating with several external services, we can give each client settings appropriate for the expected response time without changing the configuration of the others.

There is an important distinction when assigning an explicit request factory. The custom factory replaces the factory Spring Boot would normally configure for that client, so other HTTP settings that need to remain active must also be accounted for when constructing the replacement. For most applications, configuring the global spring.http.clients properties and retaining the auto-configured request factory requires fewer individual settings.

Neither connection nor read timeouts automatically cause RestClient to retry a failed operation. If the client exceeds the configured connection or read timeout, the affected HTTP operation can fail with an exception that the calling application needs to handle. The timeout itself doesn’t instruct the client to repeat the request, and any retry behavior would need its own configuration.

Keeping timeout settings at the HTTP client level allows us to control how long network operations wait without adding timing logic to individual request methods. Our PostClient now has a configured base URL, a reusable RestClient instance, and connection settings that govern how communication with the external API is performed.

Making HTTP Requests with RestClient

With our client configured for JSONPlaceholder, we can start communicating with its endpoints through HTTP requests. RestClient provides methods for selecting an HTTP operation, specifying the resource address, supplying request information, and processing the response returned by the server. Spring Boot also handles JSON conversion between Java objects and HTTP content, so we can concentrate on the data being exchanged. The same client can perform different operations depending on the HTTP method and the information required by the external API.

Reading API Responses

Retrieving information from an external API commonly involves sending an HTTP GET request to an endpoint that identifies the resource we want. The server processes that request and returns an HTTP response containing a status code, headers, and potentially a body with the requested information. When the response contains JSON, RestClient can convert that content into a Java object through Spring’s configured HTTP message converters.

JSONPlaceholder provides a /posts/{id} endpoint for retrieving individual posts. Each post contains a user ID, post ID, title, and body, which we can represent through a Java record. This gives Spring a defined type for converting the JSON response into data we can access directly from our application.

We’ll begin by creating a record named Post in its own Post.java file:

package com.example.apiclient;

public record Post(
        int userId,
        int id,
        String title,
        String body
) {}

The record defines four components matching the property names returned by JSONPlaceholder. When Spring receives the JSON response, its configured Jackson converter maps those properties to the corresponding record components. Spring Boot 4 uses Jackson 3 by default, providing JSON conversion without requiring us to manually read or parse the response content.

Java records are well suited for representing API responses because they provide an immutable data carrier with generated accessors, a constructor, and standard object methods. We can access the returned values through methods such as id(), title(), and body() without writing additional accessor methods.

With the record available, we’ll add a method to our existing PostClient service that retrieves a particular post:

public Post getPost(int id) {
    return restClient.get()
            .uri("/posts/{id}", id)
            .retrieve()
            .body(Post.class);
}

We begin the request with get(), which selects the HTTP GET method. The following uri() call specifies the endpoint relative to the base URL configured earlier. Spring replaces the {id} placeholder with the value passed into getPost(), allowing the same method to retrieve different posts without manually constructing a complete URL.

Calling retrieve() prepares the request for response processing. The HTTP operation is executed when we call a terminal method such as body(Post.class), which reads the response content and converts it into the specified Java type. If we call getPost(1), the client requests https://jsonplaceholder.typicode.com/posts/1 and returns the post information as a Post record. From there, we can access its values through the record’s generated methods, while the HTTP communication and JSON conversion remain within PostClient.

The body() method returns null if the response doesn’t contain a body. For successful responses that contain data, the JSON must also be compatible with the requested Java type. If Spring cannot convert the returned content, the operation fails rather than returning an incorrectly populated object.

External APIs also commonly return collections of resources rather than individual records. JSONPlaceholder’s /posts endpoint returns a JSON array containing multiple posts, so we need a Java type that can represent a collection of Post objects.

We can add a second method to PostClient for retrieving the collection:

public List<Post> getPosts() {
    return restClient.get()
            .uri("/posts")
            .retrieve()
            .body(new ParameterizedTypeReference<List<Post>>() {});
}

For this method, we’ll import java.util.List and org.springframework.core.ParameterizedTypeReference.

The request follows the same sequence as our individual post retrieval, but the response conversion differs. Instead of requesting a single Post, we’re requesting a List<Post> containing the objects returned by the API.

Java’s type erasure means that passing List.class wouldn’t preserve the information needed to identify the type of objects inside the collection. ParameterizedTypeReference<List<Post>> retains that generic type information, allowing Spring to convert the JSON array into a list containing Post records.

Each record corresponds to an entry in the returned JSON array, and we can access those records through ordinary Java collection operations. The same technique applies to other parameterized response types when an API returns collections or data structures containing generic types.

Sometimes we need more information from the HTTP response than the converted body. External services can include headers containing caching instructions, content information, or other metadata, while the HTTP status tells us how the server handled the request.

For these situations, RestClient provides toEntity(), which returns a ResponseEntity containing the body along with the HTTP status and headers.

We’ll retrieve that information through the following method in PostClient:

public ResponseEntity<Post> getPostResponse(int id) {
    return restClient.get()
            .uri("/posts/{id}", id)
            .retrieve()
            .toEntity(Post.class);
}

We need to import org.springframework.http.ResponseEntity for this method. The request still targets an individual post, but calling toEntity(Post.class) changes the returned type from Post to ResponseEntity<Post>. We can retrieve the converted record through getBody(), inspect the HTTP status through getStatusCode(), and access the response headers through getHeaders().

This becomes helpful when the response metadata influences what we do next. An API may provide pagination information, caching instructions, or other information through headers, and we can inspect those values while retaining access to the converted response body.

body() and toEntity() follow the default HTTP error handling behavior of RestClient. If the remote API returns an error status, the client normally throws an exception instead of returning the response as an ordinary successful result. We can account for those failures when configuring error handling later in the service.

Sending Request Headers

Request headers allow us to include information about an HTTP operation beyond its URL and body. External APIs commonly inspect headers to determine which response format the client accepts, identify the calling application, or validate authentication credentials. Spring’s RestClient provides methods for setting these values directly within the request chain, allowing us to supply the information required by a particular endpoint.

The Accept header tells the server which content types the client can receive. For a REST API returning JSON, we can specify application/json through the accept() method rather than constructing the header value ourselves.

Custom headers can also carry information associated with an individual request. Some services support request identifiers that allow requests to be traced across application logs and service boundaries. The exact header names and accepted values depend on the API we’re contacting.

To include both a JSON preference and a request identifier, we’ll add the following method to PostClient:

public Post getPostWithHeaders(int id, String requestId) {
    return restClient.get()
            .uri("/posts/{id}", id)
            .accept(MediaType.APPLICATION_JSON)
            .header("X-Request-ID", requestId)
            .retrieve()
            .body(Post.class);
}

The method requires org.springframework.http.MediaType, which provides constants for common HTTP media types. We start by selecting the post endpoint, then call accept(MediaType.APPLICATION_JSON) to request a JSON response. The header() method adds X-Request-ID with the identifier passed into the method, allowing each call to provide its own value without changing the client configuration.

When the request is sent, Spring includes these headers alongside the other HTTP request information. JSONPlaceholder accepts requests without requiring custom identification headers, so the request identifier doesn’t change the returned post data. APIs that support request tracing can read the identifier and associate it with their own processing records.

There are situations where multiple headers need to be configured within the same request. RestClient provides headers(), which accepts a callback that gives us access to the request’s HttpHeaders collection. Through that callback, we can set, replace, or inspect header values before sending the request.

Authentication is a common reason to use this method. For APIs that require bearer tokens, the HttpHeaders class provides setBearerAuth(), which accepts an access token and places it in the HTTP Authorization header with the expected bearer scheme.

We can call headers(headers -> headers.setBearerAuth(accessToken)) within the request chain, provided that accessToken contains a token obtained through the external service’s authentication process.

The token must already be available to the application before the request is made. RestClient doesn’t obtain or refresh authentication credentials automatically merely because we’ve configured an authorization header. Authentication requirements and token management depend on the service being contacted.

JSONPlaceholder doesn’t require bearer authentication for its public endpoints, but the same header configuration applies to services that validate access tokens.

When a header value should be included in every request made through a particular client, we can configure it through defaultHeader() on RestClient.Builder. For values that change between calls, setting the header directly within the request method keeps those values associated with the operation that needs them.

We also need to distinguish the Accept header from Content-Type. The first communicates which response formats the client can receive, while the second identifies the format of content being sent to the server. For requests that submit JSON data, we’ll provide the content type alongside the request body.

Submitting JSON Request Bodies

External APIs frequently accept information through HTTP requests that create or modify resources. HTTP POST requests commonly submit data to collection endpoints, while PUT and PATCH are generally used for resource updates. These operations can include a request body containing the information the server needs to process the request. RestClient allows us to provide that body as a Java object, with Spring converting it into the appropriate HTTP content through its configured message converters. When the server expects JSON, we can declare application/json as the request content type and provide an object containing the values we want to submit.

JSONPlaceholder provides a POST /posts endpoint that accepts information for a new post. The expected request contains a user ID, title, and body, while the server’s response includes an assigned post ID.

We’ll represent the information being submitted through a record named CreatePostRequest, stored in its own CreatePostRequest.java file:

package com.example.apiclient;

public record CreatePostRequest(
        int userId,
        String title,
        String body
) {}

This record contains the three values required for the request, without including an ID that would normally be assigned by the server. Its component names correspond to the JSON property names expected by the endpoint, allowing Jackson to serialize the record without custom property mappings.

The request record and response record serve different purposes. CreatePostRequest represents the information we want to submit, while the existing Post record represents the server’s response after the operation completes. Keeping these two types distinct also means callers don’t have to provide a post ID when creating a resource.

Next, we’ll add a method to PostClient that accepts the request record and submits it through HTTP:

public Post createPost(CreatePostRequest request) {
    return restClient.post()
            .uri("/posts")
            .contentType(MediaType.APPLICATION_JSON)
            .accept(MediaType.APPLICATION_JSON)
            .body(request)
            .retrieve()
            .body(Post.class);
}

We begin with post() to select the HTTP POST method, followed by uri("/posts") to identify the collection endpoint receiving the request. The contentType() call declares that we’re sending JSON, while accept() communicates that we expect JSON in the response.

Calling body(request) supplies the CreatePostRequest record as the outgoing content. Spring selects a compatible HTTP message converter and serializes the record into JSON before sending the request.

Notice that body() appears twice in the method, with each occurrence serving a different purpose. The first call belongs to the request specification and supplies the object we’re submitting. The second follows retrieve() and reads the response body, converting the returned JSON into a Post record.

This distinction is important because the object submitted to the API doesn’t always have the same structure as the object returned by the server. The request may contain only the information required to create a resource, while the response can include additional values such as an assigned identifier.

To submit a post, we can create a CreatePostRequest and pass it to the service method from code that already has an injected PostClient instance:

CreatePostRequest request = new CreatePostRequest(
        1,
        "Calling REST APIs",
        "Submitting a post through Spring Boot RestClient"
);

Post created = postClient.createPost(request);

We supply the user ID, title, and body in the same order they’re declared in CreatePostRequest. The record is then passed into createPost(), which sends the information to the external endpoint and returns the converted response.

During serialization, Jackson produces a JSON object containing the userId, title, and body properties. We don’t need to create a JSON string manually or assign those values individually to the HTTP request content.

JSONPlaceholder responds to a successful creation request with HTTP status 201 Created and a JSON object containing the submitted information along with an assigned ID. Its documented POST behavior returns ID 101 for the simulated resource, which we can read through the id() method on the returned Post record.

The service doesn’t permanently store resources submitted through this endpoint. JSONPlaceholder simulates the creation operation, so requesting the newly assigned post ID afterward doesn’t retrieve a persisted record. The HTTP request and response behavior still follows the documented endpoint contract.

For APIs that modify existing resources, RestClient also provides put() and patch(). The general request construction remains similar, but the HTTP method communicates the intended operation. PUT commonly replaces the representation of a resource, while PATCH submits modifications to part of an existing resource according to the patch format supported by the API.

We should follow the remote API’s endpoint contract when deciding which HTTP method to call and which fields to include in the request body. The Java object must represent the data the endpoint accepts, and the submitted content type must match the format expected by the server.

Response bodies aren’t required for every successful operation, either. Some endpoints return an HTTP status without any content, such as 204 No Content, while others return a newly created or updated resource.

For operations where we don’t need to deserialize a response body, retrieve().toBodilessEntity() completes the request and returns a ResponseEntity<Void> containing the status and response headers. This allows us to inspect the result without expecting JSON content that the server never supplied.

When a response body is expected, methods such as body(Post.class) and toEntity(Post.class) remain available, allowing us to select the method that provides the information needed by the caller.

Handling HTTP Errors

External HTTP requests can fail for reasons related to the request itself, the remote server, or the connection between services. Invalid resource identifiers, rejected credentials, unavailable endpoints, and server failures can all prevent an operation from completing successfully. RestClient provides default exception handling for HTTP error responses, along with methods for customizing how particular statuses are handled.

HTTP status codes identify how the server processed a request. Responses in the 2xx range indicate successful processing, while 4xx responses generally identify request-related problems and 5xx responses indicate server-side failures. For example, an API may return 404 Not Found when a requested resource doesn’t exist or 401 Unauthorized when valid authentication credentials haven’t been provided. Server-side failures can produce responses such as 500 Internal Server Error or 503 Service Unavailable.

When we call retrieve(), RestClient applies default status handling as the response is processed. For HTTP responses with status codes from 400 through 599, it normally throws an exception rather than treating the response as a successful result.

The exact exception depends on the returned status. Client errors produce HttpClientErrorException or an applicable subclass, while server errors produce HttpServerErrorException or a corresponding subclass. These exceptions belong to the RestClientResponseException hierarchy, which gives us access to information about the failed response. Through getStatusCode(), we can inspect the HTTP status returned by the server. The getResponseBodyAsString() method provides the response content as text, which can help when an API includes diagnostic information or a service-specific error response.

Spring also supports custom handling for particular status codes through onStatus(). This method accepts a condition that identifies matching responses and a handler that determines what should happen when the condition is met.

For another example, we may want to distinguish a missing post from other failures by throwing an exception with information about the requested resource.

We’ll add a findPost() method to PostClient that handles HTTP 404 responses:

public Post findPost(int id) {
    return restClient.get()
            .uri("/posts/{id}", id)
            .retrieve()
            .onStatus(
                    status -> status.value() == 404,
                    (request, response) -> {
                        throw new IllegalArgumentException(
                                "Post " + id + " was not found"
                        );
                    }
            )
            .body(Post.class);
}

We begin with the same GET operation we’ve already established, but the response handling now includes a condition that checks the HTTP status code. When the server returns 404, the custom handler throws an IllegalArgumentException identifying the requested post.

The status.value() call retrieves the numeric HTTP status code so we can compare it with 404. The handler also receives access to the outgoing request and incoming response, allowing more detailed error processing when the application needs it.

If the server returns a successful response, the custom handler doesn’t run, and body(Post.class) converts the returned JSON into a Post record as usual. Other HTTP error responses that don’t match the condition continue through the default error handling behavior.

The exception type selected for custom handling depends on what the surrounding application expects. IllegalArgumentException provides a familiar Java exception for this example, but we can define a custom exception class when callers need to identify a particular API failure.

onStatus() applies to the individual request where it’s declared. If several methods need identical error handling, RestClient.Builder also supports defaultStatusHandler() for registering a handler that applies across requests made through the resulting client. Request-level handlers take precedence over builder-level default handlers.

We also need to distinguish HTTP error responses from failures that occur before the client receives a usable HTTP response.

Network interruptions, connection failures, and read timeouts don’t necessarily produce HTTP status codes. If the remote server cannot be reached or communication fails during the operation, Spring can report the problem through ResourceAccessException, which represents an I/O failure during client communication.

These failures are different from a server deliberately responding with an HTTP error. Receiving 500 Internal Server Error means an HTTP response reached the client, while a connection timeout means the client couldn’t complete the affected connection operation within its configured limit.

When we need different behavior for these conditions, we can catch their exceptions individually.

We’ll add the following method to PostClient to distinguish HTTP response failures from connection-related failures:

public Post getPostWithFailureDetails(int id) {
    try {
        return getPost(id);
    } catch (RestClientResponseException ex) {
        throw new IllegalStateException(
                "Post API returned HTTP " + ex.getStatusCode().value(),
                ex
        );
    } catch (ResourceAccessException ex) {
        throw new IllegalStateException(
                "Unable to communicate with the Post API",
                ex
        );
    }
}

For this method, we’ll import org.springframework.web.client.RestClientResponseException and org.springframework.web.client.ResourceAccessException.

We call the existing getPost() method inside a try block, allowing successful responses to return their converted Post records. If the API returns an HTTP error, the first catch block receives the RestClientResponseException and retrieves the status code through getStatusCode().

The method then throws an IllegalStateException containing the HTTP status and preserves the original exception as its cause. Retaining that cause allows the underlying response exception to remain available for logging or further inspection by the calling application. If communication fails because of an I/O problem, the second catch block handles ResourceAccessException. We can report that condition independently of HTTP status errors because there may be no completed response from which to obtain a status code.

Other failures can occur during response processing, including situations where the server returns content that cannot be converted into the requested Java type. Those failures aren’t automatically classified as HTTP status errors or network access failures, so the surrounding application may need to account for other exception types depending on the operation being performed.

HTTP error responses may also contain JSON with error codes or descriptions defined by the remote API. When those details are needed, we can inspect the response body through the exception or configure a status handler to process the returned content. The exact structure depends on the API’s error response contract.

Retry behavior requires attention as well. RestClient doesn’t automatically repeat a failed operation solely because the server returned an error or the request exceeded its timeout. Retrying can be appropriate for temporary communication problems, but repeated requests can have consequences for operations that create or modify resources.

Repeating a POST request after a read timeout could create a duplicate resource if the server completed the original operation before the response was lost. We therefore need to account for the HTTP method and the remote service’s behavior before adding automatic retries.

With the default exception hierarchy, custom status handlers, and network exception handling, we can distinguish successful responses from different failures that occur during external API communication. The methods in PostClient can return converted Java records when requests succeed, while callers can respond to HTTP errors and communication failures according to the operation being performed.

Conclusion

With RestClient in Spring Boot 4, we can make HTTP requests to external APIs, send headers and JSON data, and read the responses as Java objects. Configuring the base URL and timeouts gives us control over how the client communicates with each service, while methods such as retrieve() and body() handle response processing. We can also handle HTTP errors and connection failures, allowing the application to respond appropriately when a request doesn’t complete as expected.

1. Spring Framework REST Clients Documentation

2. Spring Boot REST Client Documentation

3. RestClient API Reference

4. Spring Boot Application Properties

5. JSONPlaceholder API Guide

Share Alexander Obregon's Substack

Large database tables can contain millions of records, but we don’t always need to retrieve every row to inspect data, test queries, or perform statistical calculations. Reading the entire table can take more time and processing resources than necessary, which is where SQL sampling techniques come into play. Depending on what we need, we can retrieve a fixed number of random rows, select an approximate percentage of the table, or sample records from different groups to preserve their proportions. Each method handles row selection differently, and those differences affect the size of the sample, the records included, and how much processing the database needs to perform.

Subscribe now

Random Sampling Methods

Retrieving random records from a database table allows us to inspect different portions of the available data without relying on their existing order or specific identifier values. Rather than selecting the first few hundred rows, we can introduce a random selection process that gives records an opportunity to appear in the result. SQL databases support several techniques for doing this, ranging from sorting rows by randomly generated values to sampling directly from the table’s storage. The differences become more noticeable as tables grow larger, particularly when we’re retrieving a small fraction of millions of records and want to limit the amount of processing involved.

Selecting a Fixed Number of Random Rows

If we already know how large our sample should be, randomly ordering the records before limiting the result is a convenient place to start. We could retrieve 100 orders for inspection or select 500 customer records for database testing without manually choosing individual identifiers. The idea is to assign random sorting values to the eligible records, arrange them according to those values, and return only the requested number of rows.

PostgreSQL provides the random() function, which returns a pseudo-random floating-point number between zero, inclusive, and one, exclusive. Each evaluation generates a value within that range, allowing us to assign random sorting positions to records instead of ordering them by an existing column.

We can combine random() with ORDER BY and LIMIT to retrieve 100 records from an orders table:

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY random()
LIMIT 100;

We begin by selecting the order_id, customer_id, and total_amount columns from the orders table. The ORDER BY random() expression gives the eligible records randomly generated sorting values, which PostgreSQL then uses to determine their order. Finally, LIMIT 100 restricts the result to the first 100 records from that randomized ordering.

Because the generated values change between executions, running the query repeatedly will normally return different combinations of orders. Every eligible row has an equal probability of being selected under the random ordering process, provided the generated values behave as expected.

The requested count is also predictable. If our table contains at least 100 records, the query returns exactly 100. If only 60 records exist, PostgreSQL returns those 60 rather than creating additional rows to reach the requested count.

We can narrow the eligible records with a WHERE clause when the sample needs to come from a particular portion of the table. PostgreSQL applies the filtering criteria to determine which records qualify, and the random ordering operates on those candidates. This allows us to retrieve a fixed-size random sample from records meeting conditions such as a particular order status or date range.

Although the general technique is available across several database systems, the random function and row-limiting syntax differ. MySQL provides the RAND() function, which generates floating-point values within the same range as PostgreSQL’s random() function.

To retrieve 100 randomly selected orders with MySQL, we can write the following query:

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY RAND()
LIMIT 100;

Reading through the statement, we first identify the columns we want to retrieve. Next, ORDER BY RAND() assigns random sorting values to the eligible rows, while LIMIT 100 retains the first 100 records from the resulting order. The process is nearly identical to PostgreSQL, with the function name being the main difference.

SQL Server handles random ordering differently because its NEWID() function generates uniqueidentifier values rather than floating-point numbers. We can still sort by those generated values to obtain a randomized order.

SELECT TOP (100)
    order_id,
    customer_id,
    total_amount
FROM dbo.orders
ORDER BY NEWID();

The NEWID() function generates identifiers that SQL Server can sort, giving each eligible record a randomized position. We then retrieve the first 100 records through TOP (100), which appears near the beginning of the statement rather than at the end like PostgreSQL’s and MySQL’s LIMIT clauses.

Despite their syntax differences, all three queries follow the same general process. We assign randomized ordering values to eligible records and return a fixed number of rows from the resulting order, making the technique convenient when the desired sample size is already known.

There is an important performance detail when applying this method to very large tables. Requesting 100 records doesn’t mean the database can immediately retrieve 100 randomly selected rows without examining the remaining candidates. To produce a random ordering, the database generally needs to examine every qualifying record and evaluate its random sorting expression.

Depending on the execution plan, the database may perform a full sort or an optimized operation that retains only the highest-priority rows needed for the requested limit. An optimized top-N sort can reduce memory consumption compared with sorting the entire result, but the database still needs to evaluate the candidate records to determine which belong in the sample.

If the orders table contains several million records, processing those rows can require far more time than returning the small result itself. The cost depends on the table’s size, available indexes for filtering conditions, memory, and the execution plan selected by the database.

We should also distinguish random ordering from ordinary row limiting. Running a SELECT statement with LIMIT 100 or TOP (100) without a random ordering expression doesn’t create a random sample. Without an ORDER BY clause, the database doesn’t guarantee which qualifying records will be returned.

For smaller tables or occasional inspections, randomly ordering records provides direct control over the number of returned rows. Larger tables can benefit from sampling methods that avoid the random ordering operation entirely, particularly when an approximate sample size is acceptable.

Sampling with Random Probability

Rather than requesting an exact number of records, we can give each row a specified probability of being included in the result. This changes how the sample is selected because we’re evaluating individual records against a probability threshold instead of sorting all candidates and retaining a fixed count. The final number of returned rows can vary between executions, which makes this method appropriate when an approximate sample size is acceptable.

PostgreSQL supports probability-based filtering through the random() function inside a WHERE clause. Every eligible row can be evaluated against a randomly generated value, and the row is retained if that value falls below our chosen threshold.

To select approximately 2% of the records from an orders table, we can write the following query:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE random() < 0.02;

We start by retrieving the same order columns as before, but instead of sorting the records randomly, we place random() directly inside the WHERE condition. The expression random() < 0.02 evaluates to true when the generated value is below 0.02, giving each row a 2% probability of appearing in the result.

Because PostgreSQL evaluates the random condition independently for each eligible record, the number of returned rows depends on how those individual selections turn out.

For a table containing 100,000 records, we would expect approximately 2,000 rows to be selected on average. That number represents an expected count rather than an exact result, so individual executions can return different amounts of data. We could retrieve 1,970 records during one execution and 2,030 during the next, with both outcomes being consistent with a 2% sampling probability.

This differs from ORDER BY random() LIMIT 2000, which returns exactly 2,000 records when enough qualifying rows exist. Probability filtering doesn’t attempt to reach a particular count. Each record receives an independent opportunity to enter the sample, regardless of how many records have already been selected. We can adjust the probability by changing the threshold in the condition. Setting it to 0.10 gives each row a 10% chance of inclusion, while 0.005 corresponds to half of one percent. The values can be adjusted according to the approximate fraction of records we want to retrieve.

Filtering criteria can also be combined with the random condition to narrow the population we’re sampling. If we’re interested only in completed orders, we can restrict the eligible records through the status column before applying the sampling probability:

SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'completed'
  AND random() < 0.05;

We now have two conditions that must both evaluate to true before an order can appear in the result. The status = 'completed' condition restricts the eligible records to completed orders, while random() < 0.05 gives each of those records a 5% probability of selection.

Orders with other status values won’t be included, and the expected sample size depends on the number of completed orders rather than the total number of records in the table.

If the database contains 200,000 orders but only 80,000 have a completed status, our 5% probability would produce approximately 4,000 sampled completed orders on average. This allows us to target a particular portion of the data without calculating its exact size beforehand.

The database can determine the physical evaluation order of the conditions, but the returned records must satisfy both predicates. If an index helps locate completed orders, PostgreSQL may be able to reduce the number of records examined for the status condition before evaluating the random predicate for the relevant rows.

MySQL supports a similar technique through its RAND() function, which can be placed inside a WHERE clause to evaluate an independent random condition for eligible rows. The same probability thresholds apply, although the function name differs from PostgreSQL.

We also need to account for the amount of processing involved. Probability filtering avoids the random sorting operation required by ORDER BY random(), but it doesn’t automatically eliminate the need to examine the candidate records.

To give every eligible row an opportunity to be selected, the database generally has to examine those records and evaluate the random condition. That means a query returning only a small percentage of the table can still read a substantial amount of source data, depending on the filtering conditions and execution plan.

The variability of the result becomes more noticeable when the table is small or the requested probability is very low. If a table contains only 50 records and we apply a 1% probability, the expected sample size is half a row. Records are either included or excluded, so the actual result could contain zero, a single record, or several records.

This behavior is important when the sampled records will be passed to a process expecting a minimum number of rows. Probability filtering cannot guarantee that enough records will be returned, and very small sampling probabilities can produce empty results.

When we can accept an approximate number of records, probability filtering provides a way to sample individual rows without sorting the entire candidate population. If the number of returned records needs to be exact, random ordering with a row limit remains the more appropriate choice.

Sampling Through Table Storage

Some database systems provide built-in sampling operations that retrieve portions of a table without requiring a random sorting expression or an explicit random comparison for every candidate record. These operations can take advantage of how data is stored, allowing the database to retrieve a smaller amount of information from large tables.

PostgreSQL supports the TABLESAMPLE clause, which appears immediately after a table reference in the FROM clause. We specify a sampling method along with a percentage indicating how much of the source table we want to sample.

PostgreSQL provides two built-in methods, SYSTEM and BERNOULLI. Both request an approximate percentage of the table, but they differ in how records are selected and how much data needs to be read.

The SYSTEM method operates at the storage-block level. PostgreSQL stores table records in fixed-size pages, commonly called blocks, which can contain multiple rows. Rather than evaluating an independent selection decision for every record, SYSTEM randomly selects storage blocks and retrieves the rows belonging to those blocks.

We can request approximately 1% of the orders table with the following query:

SELECT order_id, customer_id, total_amount
FROM orders TABLESAMPLE SYSTEM (1);

We begin with the familiar SELECT statement, but the sampling operation is now attached directly to the table reference. The TABLESAMPLE SYSTEM (1) clause gives each storage block a 1% probability of being selected. When a block is selected, PostgreSQL retrieves the rows belonging to that block, subject to ordinary row visibility rules.

This differs from evaluating random() for every record because the database can skip blocks that weren’t selected. With a low sampling percentage, PostgreSQL can avoid reading most of the table’s blocks and retrieve records from the selected portions.

The amount of data returned can vary considerably between executions, partly because storage blocks don’t necessarily contain the same number of records. Some blocks contain more rows than others due to differences in record sizes, updates, deletions, and the physical organization of the table.

If a selected block contains 120 records, all 120 can enter the sample. Meanwhile, a block containing only 30 records contributes fewer rows, despite having the same probability of selection. As a result, SYSTEM (1) requests a 1% sampling probability for storage blocks rather than guaranteeing that exactly 1% of the table’s rows will be returned.

For smaller tables, the variation can become substantial. If the table occupies only a few storage blocks, a low sampling percentage can return no records because none of those blocks were selected.

The physical placement of records can also influence the resulting sample. Records inserted around the same time or stored near each other can occupy neighboring blocks, so selecting those blocks can retrieve several related records.

This behavior makes SYSTEM attractive when we want to reduce the amount of data read from a very large table and can accept an approximate sample size. The tradeoff is that records sharing the same block are sampled as a group rather than receiving independent selection decisions.

PostgreSQL’s BERNOULLI method provides a different selection process. Instead of sampling entire storage blocks, it evaluates individual rows according to the requested probability.

To request approximately 1% of the table through row-level sampling, we can write the following query:

SELECT order_id, customer_id, total_amount
FROM orders TABLESAMPLE BERNOULLI (1);

We keep the same selected columns and table reference, changing the sampling method from SYSTEM to BERNOULLI. The BERNOULLI (1) expression gives each row an independent 1% probability of inclusion, regardless of which storage block contains it.

This is similar in statistical behavior to evaluating random() < 0.01 for every eligible record. Both methods perform independent row-level selection, and neither guarantees an exact sample size.

Their execution mechanisms differ, however. BERNOULLI is a built-in table sampling method, while random() inside a WHERE clause is an ordinary filtering expression evaluated during query execution. PostgreSQL must scan the table for BERNOULLI sampling because each row needs an independent inclusion decision. As a result, it generally reads more source data than a low-percentage SYSTEM sample.

The difference becomes apparent when records are physically grouped near each other. With SYSTEM, selecting a storage block brings in the records belonging to that block. With BERNOULLI, every row receives its own selection decision, so neighboring records aren’t automatically included merely because they occupy the same block.

Both methods accept percentages between zero and 100. Selecting zero requests no records, while selecting 100 includes all rows from the sampled table before any subsequent filters are applied.

SQL Server also provides a TABLESAMPLE clause, although its built-in implementation follows a page-based sampling process similar to PostgreSQL’s SYSTEM method.

We can request approximately 1% of an orders table in SQL Server through the following statement:

SELECT order_id, customer_id, total_amount
FROM dbo.orders TABLESAMPLE (1 PERCENT);

The TABLESAMPLE (1 PERCENT) clause tells SQL Server to sample approximately 1% of the table. The database randomly selects storage pages and returns the rows located on those pages, so the resulting number of records can differ from the requested percentage.

SQL Server supports the SYSTEM sampling method for this operation, and the method name is optional because it is the default.

There is also a difference in how SQL Server accepts the requested sample size. PostgreSQL’s built-in TABLESAMPLE methods accept percentages, while SQL Server allows us to request an approximate number of rows.

We can request approximately 500 records with the following query:

SELECT order_id, customer_id, total_amount
FROM dbo.orders TABLESAMPLE (500 ROWS);

We specify 500 ROWS instead of a percentage, telling SQL Server the approximate number of records we want. The database determines a page-sampling rate based on that request, but the final result can contain fewer or more than 500 rows.

This differs from TOP (500), which limits the result to a maximum of 500 records. The TABLESAMPLE clause doesn’t enforce an exact count because the database selects storage pages that can contain different numbers of rows.

With page-based sampling, the actual sample depends on which pages are selected, how densely records are stored on those pages, and the sampling rate calculated by the database.

Filtering introduces an additional detail that affects the records we receive. In PostgreSQL, TABLESAMPLE operates on the table source before ordinary WHERE conditions are evaluated.

If we request a 1% table sample and then filter for completed orders, PostgreSQL first samples the underlying table and then removes sampled records that don’t have a completed status. The resulting count depends on which completed orders appeared in the initial table sample, so it isn’t equivalent to directly sampling 1% of all completed orders.

This distinction becomes noticeable when the records we’re interested in account for only a small portion of the table. Sampling a small fraction of the entire table can leave very few qualifying rows after filtering, particularly when those records are concentrated in certain storage blocks.

SQL Server follows the same general principle, with TABLESAMPLE selecting records from the table source before later query filtering.

The primary difference between storage-level sampling and the earlier random selection methods comes down to how much source data the database needs to examine. Random ordering generally processes every candidate record to assign randomized positions, while row-level probability methods inspect candidates to make individual inclusion decisions.

Block-based sampling can avoid reading large portions of the table altogether, which can reduce processing time for very large datasets. The exact benefit depends on the table’s physical storage, the requested sampling percentage, and the database’s execution plan.

Representative Sampling Methods

Random selection gives records an opportunity to appear in a sample, but the result won’t necessarily reflect the distribution of every category in the original table. If certain groups contain far more records than others, smaller groups can contribute very few observations or be missing from the sample entirely. Depending on what we’re examining, we may need to control how records are selected from different categories or preserve their original proportions. SQL window functions, grouping operations, and repeatable selection rules give us more control over which records are included and how closely the sample reflects the original population.

Selecting Equal Samples Across Groups

When a table contains records belonging to different categories, selecting the same number of records from each category can make comparisons more manageable. This technique is called stratified sampling, where we divide the available records into groups known as strata before selecting observations from them.

These groups can be based on columns such as geographic region, customer type, product category, or another characteristic relevant to our analysis. The objective is to control how records are distributed across the groups rather than allowing the relative size of each group to determine its contribution. For example, our orders table could contain 500,000 orders from one region and only 10,000 from a smaller region. If we randomly select 100 records from the entire table, the larger region will have a much greater chance of contributing records because it accounts for more of the population.

That distribution can be appropriate for examining the overall population, but it becomes less helpful when we’re interested in comparing records from each region. We may want 25 completed orders from every region so we can inspect their individual characteristics without having the larger regions dominate the result.

PostgreSQL provides the ROW_NUMBER() window function, which we can combine with PARTITION BY to assign row numbers independently within each group. By ordering the records randomly inside those groups, we can select a fixed number of observations from every region.

We can retrieve up to 25 randomly selected completed orders per region with the following query:

WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        region,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY random()
        ) AS sample_rank
    FROM orders
    WHERE status = 'completed'
)
SELECT
    order_id,
    customer_id,
    region,
    total_amount
FROM ranked_orders
WHERE sample_rank <= 25
ORDER BY region, sample_rank;

We begin with a common table expression named ranked_orders, which allows us to calculate randomized row numbers before filtering the final result. Inside that expression, the WHERE status = 'completed' condition restricts our eligible records to completed orders.

Next, ROW_NUMBER() assigns a sequential number to each record, beginning at one within its group. The PARTITION BY region clause tells PostgreSQL to restart the numbering for each region, while ORDER BY random() determines the randomized order of records within that region.

The resulting sample_rank column represents each order’s position within its region’s randomized selection. We then query the common table expression and apply WHERE sample_rank <= 25 to retain only the first 25 records from every region. Finally, ORDER BY region, sample_rank organizes the output so we can read the selected records grouped by region.

Our final result contains up to 25 completed orders from each region. If a region has 500 qualifying orders, we retrieve 25 randomly selected records from that group. Meanwhile, a region containing only 12 completed orders contributes all 12 because there aren’t enough records to reach the requested count.

This technique gives smaller categories more representation than they would typically receive through an unrestricted random selection. We can compare records across regions without needing a different query for each location, and changing the sample_rank threshold allows us to adjust the number of observations per group.

The distribution of the sample won’t necessarily match the distribution of the original table, though. If a smaller region accounts for only 2% of all orders but contributes the same number of sampled records as a region accounting for 60%, the smaller region becomes overrepresented compared with its share of the population.

That can be desirable when our purpose is to inspect or compare individual groups. For statistical calculations intended to represent the entire population, however, equal-size stratified samples may require weighting so that each group’s contribution reflects its actual population size.

We should also account for the processing involved. PostgreSQL needs to identify the qualifying rows, generate randomized ordering values within each region, and assign row numbers before the outer query can retrieve the requested records. The window function can require substantial sorting and memory when applied to millions of rows, so limiting the final result doesn’t necessarily reduce the amount of source data examined.

Preserving Group Proportions

Matching the original distribution of records becomes important when we want our sample to reflect the relative sizes of different categories. Instead of selecting the same number of records from every group, we can calculate how much each group contributes to the population and select records according to those proportions.

This is also a form of stratified sampling, but the objective differs from selecting equal sample sizes. We want larger groups to contribute more records and smaller groups to contribute fewer, keeping their relative representation close to what exists in the full table. Such as, our orders table could contain records from three regions. The first region accounts for 50% of all orders, the second accounts for 30%, and the third accounts for the remaining 20%.

If we want a sample of 1,000 orders that follows those exact proportions, we would select 500 records from the first region, 300 from the second, and 200 from the third. This gives us a sample with the same regional distribution as the original population, provided each group contains enough records. We can accomplish something similar without manually calculating the number of records needed from every region. PostgreSQL allows us to combine ROW_NUMBER() with COUNT() as window functions, giving each record both a randomized position and the total number of records in its group.

The following query retrieves approximately 5% of the completed orders from every region:

WITH ranked_orders AS (
    SELECT
        order_id,
        region,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY random()
        ) AS sample_rank,
        COUNT(*) OVER (
            PARTITION BY region
        ) AS region_size
    FROM orders
    WHERE status = 'completed'
)
SELECT
    order_id,
    region,
    total_amount
FROM ranked_orders
WHERE sample_rank <= CEIL(region_size * 0.05)
ORDER BY region, sample_rank;

We start inside the ranked_orders common table expression, where the WHERE clause limits our records to completed orders. This means the group sizes and sampling percentages are calculated from completed orders rather than every record in the table.

The first window function, ROW_NUMBER(), generates randomized positions within each region. We accomplish this through PARTITION BY region and ORDER BY random(), just as we did when selecting equal samples across groups.

The second window function, COUNT(*) OVER (PARTITION BY region), calculates the number of qualifying records within each region. Instead of reducing the result to a single row per group, the window function attaches that count to every record belonging to the group.

This distinction is important because a regular GROUP BY calculation would combine the records into summary rows. We still need the individual orders available so we can select records after calculating the appropriate sample size.

After calculating those values, we move to the outer query and apply WHERE sample_rank <= CEIL(region_size * 0.05). Multiplying region_size by 0.05 calculates 5% of the records in the region, and CEIL() rounds that number upward to the nearest whole number.

If a region contains 10,000 completed orders, the calculation produces 500, so we retrieve the first 500 records from that region’s randomized ordering. For a region with 2,000 completed orders, we retrieve 100 records.

Every region contributes approximately 5% of its qualifying records, allowing the combined sample to retain the general distribution of the source population. We can adjust the percentage by changing 0.05 to a different fraction without calculating individual group sizes beforehand.

Rounding introduces some variation, particularly for smaller groups. If a region contains only 17 completed orders, multiplying that count by 0.05 produces 0.85. The CEIL() function rounds the value upward to one, meaning that region contributes one record rather than a fractional observation.

For very small groups, this can produce a sampling percentage greater than the requested 5%. The combined result may also contain slightly more than 5% of the eligible population because every group rounds its requested count upward independently.

The distinction between this method and equal-size sampling comes down to how we determine the number of records selected from each category. Equal-size sampling assigns the same maximum count to every group, while proportional sampling calculates that count from the number of records belonging to the group.

There are also statistical considerations when deciding how much representation each category should receive. Keeping the original group proportions can support estimates intended to reflect the overall population, but matching the distribution of a particular column doesn’t guarantee that other characteristics will have the same distribution as the original data.

Preserving regional proportions, for instance, doesn’t automatically preserve the distribution of order amounts, purchase dates, or customer activity within those regions. Random selection can still produce differences, particularly when rare values account for a very small percentage of the population.

We should also account for how PostgreSQL processes the query. The database needs to identify the qualifying records, calculate group counts, and assign randomized row numbers within each region before applying the final sample-size condition.

On large tables, these operations can involve considerable sorting and processing. If we only need approximate group proportions rather than calculated counts from every region, applying the same independent row-level sampling probability to all eligible records can require less processing, although the proportions in any individual sample will vary.

Creating Repeatable Samples

For database testing and comparisons, retrieving different random records during every execution can make it difficult to determine why a result changed. If we’re testing a calculation against 500 sampled orders, changes in the selected records can affect the output even when the calculation itself hasn’t changed.

Repeatable sampling gives us a way to retrieve the same selection across multiple executions, provided the conditions affecting the selection remain unchanged. This lets us compare query behavior against consistent input records while retaining the benefits of sampling a smaller portion of the table.

PostgreSQL supports a REPEATABLE clause with its built-in TABLESAMPLE methods. The clause accepts a numeric seed that influences the pseudo-random selection process.

We can request a repeatable 5% row-level sample with the following query:

SELECT order_id, customer_id, total_amount
FROM orders
TABLESAMPLE BERNOULLI (5) REPEATABLE (73);

We begin by retrieving our order columns and requesting a 5% sample through TABLESAMPLE BERNOULLI (5). The REPEATABLE (73) clause supplies a fixed seed to the sampling method, allowing PostgreSQL to reproduce the selection when the same query is executed again under unchanged table conditions.

If we execute the query repeatedly with the same percentage and seed, PostgreSQL returns the same subset, provided the underlying table hasn’t changed. Changing the seed generally produces a different selection, which gives us a way to create alternative samples without changing the requested percentage.

The seed itself doesn’t represent a percentage, row count, or identifier. It’s a numeric value supplied to the sampling process so the same sequence of random decisions can be reproduced.

There are limits to that consistency. If records are inserted, updated, or deleted, the sampled result can change because the table no longer has the same contents or storage state. Changes to the table’s physical organization can also affect repeatability.

For this reason, seeded sampling is helpful when we’re testing against an unchanged dataset, but it shouldn’t be treated as permanent storage for a particular collection of records.

We can also create repeatable selections through deterministic hashing, which calculates the same output whenever it receives the same input.

PostgreSQL provides the md5() function, which calculates a hexadecimal representation of an MD5 hash from a text value. We can calculate hashes from unique order identifiers and sort records according to those results.

To retrieve 500 records in a repeatable, pseudo-random-looking order, we can write the following query:

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY
    md5(order_id::text || '-sample-73'),
    order_id
LIMIT 500;

We start with the order_id column, converting its value to text through order_id::text. The || operator concatenates that identifier with the fixed text -sample-73, creating an input value for the md5() function.

Each order receives a hash calculated from its unique identifier and the fixed sample label. Because the hash calculation is deterministic, the same identifier and label produce the same value every time the query runs.

We then sort the records according to those calculated hashes and retrieve the first 500 through LIMIT 500. The order_id column provides a consistent secondary sorting order in the unlikely event that two records produce identical hash values.

Changing the sample label changes the calculated hashes and generally produces a different ordering. Keeping the label unchanged allows us to reproduce the same selection from an unchanged collection of unique identifiers.

This method is convenient for test data selection, where we want a consistent collection of records without storing a manually selected list of identifiers. The hashed ordering provides a pseudo-random-looking distribution, although it isn’t a substitute for a sampling method with established statistical guarantees or for cryptographically secure randomness.

There is also a difference between keeping individual record positions consistent and keeping the entire selected collection unchanged.

If an existing order retains its identifier, its hash value won’t change. However, inserting a new record can introduce a hash that sorts ahead of an existing selected record, potentially changing which orders appear among the first 500.

Deleting an order can similarly change the selected collection because a record that previously fell outside the first 500 positions can move into the result. Changing an identifier also changes the value passed to md5(), which can alter that record’s position.

For testing that must always involve exactly the same source records, storing the selected identifiers in a dedicated table provides greater consistency. We can retrieve records by those saved identifiers during later executions, provided the records still exist and remain accessible.

If the values in those records can change, storing identifiers alone won’t preserve the original data. In that situation, retaining a snapshot of the selected records is more appropriate for tests that require identical input values across executions.

Checking Sample Quality

After retrieving a sample, we need to examine how closely its characteristics match the population we’re interested in. Random selection doesn’t guarantee that every category will receive adequate representation, and a sample containing the requested number of records can still differ from the source table in important ways.

The appropriate checks depend on what we’re trying to accomplish. For inspecting individual records, we may care primarily about retrieving a varied collection of observations. Statistical calculations require more attention to how the selected records reflect the population and how much uncertainty comes from sampling.

We can begin by comparing the distribution of categories in our sample against the distribution in the original table. This helps us identify groups that are represented differently from their population percentages.

To examine a consistent collection of records across multiple queries, we can create a temporary table containing 500 randomly selected orders in PostgreSQL:

CREATE TEMP TABLE sampled_orders AS
SELECT
    order_id,
    customer_id,
    region,
    total_amount
FROM orders
ORDER BY random()
LIMIT 500;

We begin with CREATE TEMP TABLE sampled_orders AS, which creates a temporary table from the result of the following SELECT statement. The query retrieves the requested columns from orders, randomly orders the candidate records, and retains a maximum of 500 rows.

Because the selected records are copied into sampled_orders, subsequent queries against that temporary table examine the same stored collection rather than generating a new random selection every time.

PostgreSQL temporary tables normally remain available for the duration of the database session and are automatically removed when that session ends. This makes them convenient for examining sampled data without adding a permanent table to the database.

With our sample stored, we can calculate how the selected records are distributed across regions:

SELECT
    region,
    COUNT(*) AS sampled_rows
FROM sampled_orders
GROUP BY region
ORDER BY region;

Here, we retrieve the region column and apply COUNT(*) to calculate the number of sampled records belonging to each region. The GROUP BY region clause groups records according to their regional values, while ORDER BY region arranges the results by region so we can compare them.

The output contains a row for every region represented in the sample, along with its sampled record count. Regions that weren’t selected at all won’t appear in this result, which is important when examining categories with relatively small populations.

Next, we can calculate the corresponding counts from the original table:

SELECT
    region,
    COUNT(*) AS total_rows
FROM orders
GROUP BY region
ORDER BY region;

This query follows the same grouping process, but we retrieve the counts from orders rather than sampled_orders. The result tells us how frequently each region occurs in the original population.

We can now compare the two results to see how the sampled records are distributed relative to the source data.

Because the datasets have different sizes, comparing raw counts alone won’t tell us very much. If a region has 25,000 records in the original table and 125 in our sample, we need to calculate what percentage of each dataset those counts represent. For a population containing 100,000 orders, those 25,000 records account for 25% of the total. If our sample contains 500 orders, the 125 sampled records also represent 25%, meaning that region has the same proportion in both datasets.

We would generally expect an unrestricted random sample to produce percentages near the population values, although the exact percentages can differ from one selection to the next.

The amount of variation depends partly on the sample size. Smaller samples tend to have greater percentage fluctuations, while larger random samples generally produce more consistent estimates of common population characteristics.

Rare categories deserve attention because they have a greater chance of being absent from smaller samples. If a particular region accounts for only a tiny fraction of the original table, we could retrieve hundreds of randomly selected records without including any from that region.

This doesn’t necessarily mean the random selection process is defective. It can be an ordinary consequence of sampling a small portion of a population containing uncommon observations.

For statistical analysis, we may also need to examine numerical columns rather than only counting categorical values. In our orders table, total_amount provides an opportunity to compare the distribution of monetary amounts between the sampled records and the original population.

We can calculate summary statistics from the sampled orders with PostgreSQL aggregate functions:

SELECT
    COUNT(*) AS sampled_rows,
    ROUND(AVG(total_amount), 2) AS average_amount,
    MIN(total_amount) AS minimum_amount,
    MAX(total_amount) AS maximum_amount
FROM sampled_orders;

In this, we begin with COUNT(*) to retrieve the number of sampled rows, followed by AVG(total_amount) to calculate the average order amount. The ROUND() function rounds that average to two decimal places, assuming total_amount is stored as a numeric or decimal value.

The MIN() and MAX() functions retrieve the smallest and largest non-null order amounts in the sample. By running the equivalent calculations against the original orders table, we can examine how the sampled values compare with the full population.

Average values can be relatively close while the minimum and maximum values differ considerably. If unusually expensive orders are rare, a small random sample can miss them entirely, causing the sample’s maximum value to fall well below the population maximum.

The same concern applies to distributions beyond their average and extreme values. Two datasets can have similar averages while containing different concentrations of small, medium, and large transactions.

For more detailed statistical analysis, we can examine percentiles, frequency distributions, and other measurements relevant to the selected columns. These comparisons help us identify characteristics that may not be adequately represented by the current sample.

The method that produced the sample can also affect its statistical properties. Page-based sampling can retrieve groups of physically nearby records, which can influence results if neighboring records share similar characteristics.

Selecting records from a limited date range introduces a different restriction because records outside that period have no opportunity to enter the sample. If we’re trying to represent the entire history of the table, that restriction changes the population available for selection. We should distinguish these sampling decisions from ordinary random variation. Differences caused by random selection can occur even under an unbiased sampling process, while differences introduced by excluding parts of the population or favoring certain groups can produce systematic bias.

The database processing required to retrieve a sample is worth examining as well. Queries that randomly sort millions of records can consume considerable processing time and memory, even if we only retrieve a few hundred rows.

PostgreSQL provides EXPLAIN (ANALYZE, BUFFERS) to inspect how a query executes and how database buffers are accessed during processing:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY random()
LIMIT 500;

We place EXPLAIN (ANALYZE, BUFFERS) before the query we want to examine. The ANALYZE option tells PostgreSQL to execute the statement and report actual execution measurements, including the number of rows processed and the time spent performing operations.

The BUFFERS option adds information about buffer activity, including blocks accessed from the database buffer cache and blocks that needed to be read. These measurements provide more information about how much database activity occurred while executing the statement.

For our random-ordering query, we can inspect the scan and sorting operations to see how PostgreSQL retrieves the candidate records and selects the final 500 rows.

We can then compare those measurements with a query that retrieves records through a table sampling method. The execution plans can differ substantially because random ordering generally evaluates the candidate records, while block-based sampling can avoid reading large portions of the table.

Performance comparisons should be made against tables and data volumes similar to those being examined. Small test tables can produce execution plans that differ from those selected for larger datasets, and available memory, caching, and table organization can influence execution time.

Taking a look at both the distribution of selected records and the database operations required to retrieve them, we can evaluate the sample according to its intended purpose. The appropriate selection method depends on the required record count, acceptable processing cost, and the statistical characteristics that need to be represented.

Conclusion

Sampling rows with SQL gives us several ways to retrieve smaller portions of large tables without processing every record in the final result. Random ordering lets us select a fixed number of rows, while probability filtering and TABLESAMPLE provide options for retrieving approximate subsets. When we need more control over the distribution of records, window functions allow us to select equal or proportional samples from different groups. We can also reproduce selections through fixed seeds or deterministic hashing, then compare the sampled data against the original population. Choosing the right method comes down to how records are selected, the sample size we need, and the amount of processing required to retrieve them.

1. PostgreSQL SELECT Docs

2. PostgreSQL Window Functions

3. PostgreSQL Mathematical Functions

4. SQL Server FROM Clause Docs

5. MySQL Mathematical Functions

6. PostgreSQL EXPLAIN Docs

Share Alexander Obregon's Substack

Database operations can involve several steps within the same service method, from creating a new record to updating existing data through multiple repository calls. If something fails partway through, earlier changes may already be committed while later operations never finish, leaving the database in an inconsistent state. Spring Boot 4 handles these situations through Spring Framework 7’s transaction management, allowing us to group related database operations within a single transaction through @Transactional. This gives us control over when changes are committed, how exceptions trigger rollbacks, and how read-only operations are handled. Knowing where transactions begin and end can also help explain why certain methods don’t always behave as expected, particularly when calls between service methods bypass Spring’s transaction proxy.

Subscribe now

Establishing the Transaction Boundary

Transactions become important when several database operations contribute to a single action, like creating an order while updating the inventory associated with it. If those operations execute independently, a failure halfway through can leave changes committed that should have been canceled. Spring Boot 4 gives us transaction management through Spring Framework 7, allowing related database operations to share the same transaction boundary. We’ll begin with repository calls that aren’t enclosed by a service-level transaction, then move that responsibility into the service so the database can commit or roll back the entire purchase.

Starting With Multiple Repository Calls

Placing an order usually involves more than inserting a record into a database. We also need to locate the purchased product, check its available quantity, and update inventory to reflect the purchase. These operations depend on each other because saving an order without reserving the inventory could leave the customer with a purchase that cannot be fulfilled.

For this example, we’ll use Spring Boot 4 with Spring Data JPA and PostgreSQL. Spring Data JPA provides repository interfaces for accessing database entities, while Hibernate handles persistence operations through JPA.

The required dependencies can be added to a Gradle project through spring-boot-starter-data-jpa and the PostgreSQL JDBC driver.

import jakarta.persistence.*;

@Entity
@Table(name = "inventory_items")
public class InventoryItem {

    @Id
    private Long id;

    @Version
    private Long version;

    @Column(nullable = false)
    private int availableQuantity;

    protected InventoryItem() {
    }

    public int getAvailableQuantity() {
        return availableQuantity;
    }

    public void reserve(int quantity) {
        if (quantity <= 0) {
            throw new IllegalArgumentException(
                "Quantity must be positive"
            );
        }

        if (availableQuantity < quantity) {
            throw new IllegalStateException(
                "Insufficient inventory"
            );
        }

        availableQuantity -= quantity;
    }
}

We’ve defined the entity with an identifier, an available quantity, and a version number managed by JPA. The id field identifies an existing inventory record, allowing the application to retrieve the corresponding product from the database. Its protected constructor gives JPA a way to instantiate the entity when loading records without requiring callers to create inventory objects through that constructor. The reserve() method handles the inventory adjustment. Before reducing the available quantity, we check that the requested amount is positive and doesn’t exceed the stock currently recorded. If either condition fails, an exception interrupts the operation before the quantity changes.

Keeping this validation within the entity prevents callers from reducing inventory below zero through reserve(). It also provides the ordering service with a way to detect purchases that cannot be completed, without requiring the service to repeat the inventory validation itself.

We’ve also included the @Version annotation, which introduces optimistic locking. Hibernate manages the version value and checks it when updating the entity. If a competing transaction has already committed a modification to the same record, the version comparison can detect that conflict rather than allowing an older inventory value to overwrite the newer one. This becomes important when multiple customers attempt to purchase the same product around the same time.

Next, we need a PurchaseOrder entity to represent an order submitted by a customer:

import jakarta.persistence.*;

@Entity
@Table(name = "purchase_orders")
public class PurchaseOrder {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private Long itemId;

    @Column(nullable = false)
    private int quantity;

    protected PurchaseOrder() {
    }

    public PurchaseOrder(Long itemId, int quantity) {
        this.itemId = itemId;
        this.quantity = quantity;
    }

    public Long getId() {
        return id;
    }
}

Each purchase order records the inventory item being purchased and the quantity requested. Through the constructor, we can create an order from those values, while @GeneratedValue(strategy = GenerationType.IDENTITY) tells Hibernate that the database generates the identifier during insertion.

We now have two entities representing different information about the purchase. PurchaseOrder records what the customer ordered, while InventoryItem tracks the quantity remaining in stock. Completing the purchase requires changes to both records, and a failure affecting either operation should prevent the entire purchase from being committed.

To access these entities, we’ll create two Spring Data JPA repositories by extending JpaRepository. Each interface belongs in its own Java file.

We’ll begin with the inventory repository:

import org.springframework.data.jpa.repository.JpaRepository;

public interface InventoryRepository
        extends JpaRepository<InventoryItem, Long> {
}

By extending JpaRepository<InventoryItem, Long>, the interface inherits methods for retrieving, saving, deleting, and querying inventory entities. The first generic argument identifies the entity managed by the repository, while Long represents the type of its identifier.

The purchase order repository follows the same structure but operates on PurchaseOrder entities:

import org.springframework.data.jpa.repository.JpaRepository;

public interface PurchaseOrderRepository
        extends JpaRepository<PurchaseOrder, Long> {
}

Spring Data JPA supplies implementations for inherited methods such as save() and findById(), allowing us to access both tables without writing SQL statements for these operations. With the repositories defined, we can concentrate on coordinating the purchase rather than implementing database access methods individually.

Initially, the ordering operation could be written as a sequence of repository calls without placing a transaction around the entire service method:

PurchaseOrder order = orderRepository.save(
    new PurchaseOrder(itemId, quantity)
);

InventoryItem item = inventoryRepository.findById(itemId)
    .orElseThrow(() ->
        new IllegalArgumentException("Item not found")
    );

item.reserve(quantity);

inventoryRepository.save(item);

We begin by creating a PurchaseOrder and passing it to orderRepository.save(). After saving the order, we call inventoryRepository.findById() to retrieve the inventory record associated with the requested item. If the record doesn’t exist, orElseThrow() raises an IllegalArgumentException instead of allowing the purchase to continue.

Once the inventory item has been retrieved, reserve() checks the requested quantity and reduces the available stock. We then pass the modified entity to inventoryRepository.save() so the updated quantity can be persisted.

The sequence follows the expected purchasing process, but executing these statements inside an ordinary service method doesn’t automatically mean the database treats them as one purchase.

Spring Data JPA already provides transactional behavior for inherited repository methods. When we call save() without an existing transaction, Spring Data JPA can begin a transaction for that repository operation and commit it after the method finishes successfully.

This starts to be a problem if the order is saved before the inventory check fails.

Imagine a customer requests an inventory item that no longer exists. Our initial orderRepository.save() call may have already committed the purchase order, but the following findById() call throws an IllegalArgumentException. The earlier repository transaction has already finished, so the exception cannot undo the committed order.

Insufficient stock creates a similar situation. If the item exists but its available quantity is lower than the amount requested, reserve() throws an IllegalStateException. The inventory remains unchanged, but the purchase order could already be stored in the database.

We could also encounter a failure during the final inventory update because of a database constraint, connection problem, or conflicting modification. Without a transaction covering the complete purchase, successful earlier commits remain unaffected by that later failure.

The underlying issue comes from where the transaction boundary is established. Each inherited repository method has its own transaction configuration, but the service hasn’t told Spring that the order insertion and inventory update belong to the same database operation.

What we need is a transaction that remains active throughout the purchase, allowing both changes to commit successfully or be rolled back if the operation fails. The service is the appropriate location for that boundary because it coordinates the repository calls involved in completing the order.

Adding @Transactional to the Service

Spring provides the @Transactional annotation to declare transaction behavior on a method or class. Rather than manually beginning a transaction, performing database operations, and committing the result, we can annotate the service method responsible for the complete purchase.

The annotation tells Spring’s transaction infrastructure to establish a transaction boundary when the method is invoked through Spring’s transactional proxy.

For our ordering operation, we want the transaction to begin before the order is inserted and remain active until the inventory adjustment has finished. If something interrupts the purchase during that period, the database changes should be rolled back according to the transaction’s rollback rules.

We’ll move the repository calls into an OrderService class and place @Transactional directly above placeOrder():

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class OrderService {

    private final InventoryRepository inventoryRepository;
    private final PurchaseOrderRepository orderRepository;

    public OrderService(
            InventoryRepository inventoryRepository,
            PurchaseOrderRepository orderRepository) {

        this.inventoryRepository = inventoryRepository;
        this.orderRepository = orderRepository;
    }

    @Transactional
    public Long placeOrder(Long itemId, int quantity) {
        if (quantity <= 0) {
            throw new IllegalArgumentException(
                "Quantity must be positive"
            );
        }

        PurchaseOrder order = orderRepository.save(
            new PurchaseOrder(itemId, quantity)
        );

        InventoryItem item = inventoryRepository.findById(itemId)
            .orElseThrow(() ->
                new IllegalArgumentException("Item not found")
            );

        item.reserve(quantity);

        inventoryRepository.save(item);

        return order.getId();
    }
}

We’ve marked OrderService with @Service, allowing Spring to register it as a managed component and inject both repositories through its constructor. The @Transactional annotation then establishes a transaction boundary around placeOrder() when the method is invoked through Spring’s transaction infrastructure.

Notice that the repository calls haven’t changed from the earlier sequence. We still create the order, retrieve the inventory item, reserve the requested quantity, and persist the inventory modification. The difference is that Spring now coordinates those operations within a transaction rather than allowing each repository method to complete its own independent transaction.

When placeOrder() is called without an existing transaction, Spring begins a new transaction before executing the method. Both repositories participate in that transaction, meaning their database changes belong to the same operation and remain subject to the same final commit or rollback.

We’ve also added a quantity check at the beginning of the service method. This prevents an invalid quantity from reaching the database operation, while the validation inside InventoryItem.reserve() continues to protect inventory changes when that method is called from other locations.

Now let’s follow what happens if the purchase cannot be completed.

If the inventory lookup fails, orElseThrow() raises an IllegalArgumentException. That exception escapes placeOrder(), causing Spring to roll back the transaction. The purchase order inserted earlier is discarded because its insertion belongs to the same uncommitted transaction.

Insufficient stock follows the same general process. When reserve() throws an IllegalStateException, execution stops before the inventory update is completed. Spring detects the exception as it leaves the transactional method and rolls back the database changes associated with the purchase.

Both exceptions extend RuntimeException, which Spring treats as a reason to roll back under its default transaction configuration.

Successful execution follows a different outcome. After the repository operations complete and the service method returns, Spring proceeds with committing the transaction. Provided the database accepts the changes, the purchase order and updated inventory quantity become committed as part of the same transaction.

There’s an important distinction between calling save() and committing a transaction. In Spring Data JPA, save() requests that an entity be persisted or merged into the current persistence context. Hibernate determines when the corresponding SQL statements need to be executed, and those statements can execute before the transaction finishes.

Our PurchaseOrder entity provides a good example of this behavior. Because its identifier comes from GenerationType.IDENTITY, Hibernate generally needs to execute the insertion to retrieve the generated ID. That allows order.getId() to return the identifier before the enclosing transaction has committed.

Although the insertion has already happened at the SQL level, it can still be rolled back if a later operation fails. The database doesn’t treat the row as permanently committed merely because Hibernate executed the insertion.

The inventory entity behaves somewhat differently. After findById() retrieves the record inside the active transaction, the entity is managed by the JPA persistence context. When we call reserve(), we modify the entity’s quantity in memory, and Hibernate tracks that modification.

Because the entity is already managed, Hibernate can detect the changed quantity and persist it during synchronization with the database. Calling inventoryRepository.save(item) is valid, but it isn’t strictly required for an entity already managed within the transaction.

We could therefore remove the final save() call without changing how JPA tracks the quantity modification in this particular method. Keeping it in the example makes the intended persistence operation explicit, while the surrounding transaction remains responsible for the final commit.

Spring Boot 4 normally configures the transaction infrastructure automatically when the JPA starter, a configured data source, and the standard auto-configuration are present. We generally don’t need to register a transaction manager manually or add @EnableTransactionManagement to the application configuration.

The @Transactional annotation still comes from org.springframework.transaction.annotation.Transactional, and its basic behavior remains consistent with Spring Boot 3.

By placing the transaction boundary around placeOrder(), we’ve connected the database changes to the complete purchasing operation. An order that fails its inventory reservation won’t leave behind a committed purchase record, and successful inventory modifications are committed within the same transaction as their associated orders.

Following the Transaction Lifecycle

After adding @Transactional, we can examine how the transaction progresses from the initial service call through the final database commit. The timing is important because Hibernate can execute SQL statements before the transaction finishes, and certain failures aren’t detected until the remaining changes are synchronized with the database.

The process begins when a caller invokes placeOrder() through the Spring-managed service. Spring’s transaction proxy intercepts that call and delegates transaction management to the configured transaction manager. For a standard Spring Data JPA application connected to a single data source, this is normally a JpaTransactionManager.

Before the service method executes, the transaction manager establishes the transactional context. Hibernate then associates its persistence operations with that context, allowing the entity changes and repository calls to participate in the same database transaction.

As we enter placeOrder(), the quantity validation happens first. If the amount is invalid, the method throws an exception before creating an order or accessing the inventory repository.

For a valid quantity, we create a PurchaseOrder and pass it to the repository. Because the entity receives its identifier through GenerationType.IDENTITY, Hibernate generally executes the insertion at this point to retrieve the database-generated value.

The order row has now been inserted, but the surrounding transaction hasn’t committed. If the service encounters an exception that triggers rollback, the database can still discard that insertion.

Next, we retrieve the inventory item through findById(). Hibernate loads the corresponding entity into the current persistence context, allowing changes to its fields to be tracked for the duration of the transaction.

Calling reserve() modifies the availableQuantity field after checking that the requested quantity is valid. Hibernate doesn’t necessarily execute an UPDATE immediately when the Java method changes that value. Instead, the modification is tracked until Hibernate synchronizes the pending entity changes with the database.

This synchronization process is called a flush. During a flush, Hibernate executes the SQL statements needed to reflect pending changes in the database. Depending on the operations being performed and the configured flush mode, synchronization can happen before certain queries, through an explicit flush request, or during transaction completion.

Flushing and committing are different operations. Executing an UPDATE during a flush doesn’t mean the modified inventory quantity has been permanently committed.

If the transaction rolls back after that SQL statement executes, the database discards the uncommitted modification. This distinction becomes important when examining database logs because seeing an INSERT or UPDATE statement doesn’t prove that the transaction ultimately committed.

After the final repository call completes, placeOrder() returns the generated order ID to Spring’s transaction interceptor. From the service method’s perspective, the Java statements have finished successfully, but Spring still needs to complete the transaction.

The transaction manager begins that completion process by coordinating any remaining persistence operations. Hibernate synchronizes pending entity changes as needed, and the transaction manager requests that the database commit.

Certain failures can still occur during this stage.

Database constraints may reject an insertion or update, while concurrency checks can detect that an entity was modified by a competing transaction. These problems may surface during flushing rather than at the exact Java statement that originally changed the entity.

As a result, the service method body can finish without throwing an exception while the overall transactional invocation still fails during commit.

The caller receives a successful result only after Spring’s transaction interceptor finishes processing the commit. Returning the generated order ID from the service method doesn’t bypass that final transaction step.

Our inventory entity also includes an optimistic locking version field, which becomes important when multiple purchases affect the same inventory record.

Imagine two customers requesting the same product at nearly the same time. Their transactions could initially retrieve the same available quantity and version number, with both attempting to reserve inventory based on that earlier information.

When Hibernate updates a versioned entity, it checks the expected version against the version stored in the database. If a competing transaction has already committed a modification to that record, the version comparison detects the conflict.

The affected transaction then encounters an optimistic locking failure instead of silently overwriting the newer inventory quantity.

Depending on when Hibernate executes the update, the failure can occur during an earlier flush or during the synchronization performed as part of transaction completion. If the failure causes the transaction to roll back, the associated purchase order insertion is rolled back as well.

Through this behavior, @Version complements the transaction boundary. The transaction groups related database modifications into a single operation, while optimistic locking detects conflicting updates to the inventory record. Neither mechanism automatically retries a purchase that fails because of a concurrency conflict.

We should also keep in mind that database transactions only control operations participating in the transaction. If our service sends an email, invokes an external payment API, or publishes a message directly to an independent external system, rolling back the database transaction cannot undo those completed external actions.

For that reason, keeping the transaction focused on the database operations involved in the purchase is generally preferable. Holding a transaction open while waiting for an external network request can also keep database connections and locks occupied longer than necessary, potentially affecting concurrent operations.

In our ordering service, the transaction begins around placeOrder(), includes the purchase order insertion and inventory modification, and finishes when Spring processes the method’s outcome. Successful database changes commit within the same transaction, while failures that trigger rollback cause the participating changes to be discarded.

With the transaction boundary established at the service level, we can perform the related repository operations without allowing an earlier successful commit to leave the purchase partially recorded if a later database operation fails.

Configuring Transaction Behavior

With a transaction boundary established, Spring provides several settings that determine how the transaction responds to exceptions, handles read operations, and interacts with other transactional methods. These settings become important as service methods begin coordinating more database activity, particularly when certain failures need different treatment or multiple services participate in the same operation. Spring Boot 4 and Spring Framework 7 allow us to configure these behaviors through @Transactional, giving us control over transaction handling directly from the service methods responsible for database access.

Controlling Rollback Rules

Spring’s default transaction behavior depends on the type of exception that interrupts a transactional method. Java distinguishes between checked exceptions, which generally need to be handled or declared, and unchecked exceptions, which can propagate without being declared in the method signature.

By default, Spring rolls back transactions when an exception extending RuntimeException escapes the transactional method. Instances of Error also trigger rollback, while checked exceptions such as IOException don’t automatically cause rollback with the default configuration.

For our earlier ordering service, IllegalArgumentException and IllegalStateException are both runtime exceptions. If either escapes placeOrder(), Spring rolls back the transaction, preventing an unsuccessful purchase from leaving committed database changes.

Checked exceptions need some attention because they don’t receive that same default treatment.

Imagine we’re importing purchase records from a file and saving them through a repository. The method reads each record and inserts it into the database, but an IOException could interrupt the import after several records have already been processed.

Without an explicit rollback rule, Spring’s default behavior allows the transaction to commit despite the checked exception escaping the method. That could leave a partially imported collection of records in the database.

We can change this through the rollbackFor attribute of @Transactional. Let’s create a method that imports purchase orders from a file while treating an IOException as a reason to cancel the entire transaction.

@Transactional(rollbackFor = IOException.class)
public void importOrders(Path file) throws IOException {
    try (BufferedReader reader = Files.newBufferedReader(file)) {
        String line;

        while ((line = reader.readLine()) != null) {
            String[] values = line.split(",");

            Long itemId = Long.parseLong(values[0]);
            int quantity = Integer.parseInt(values[1]);

            orderRepository.save(
                new PurchaseOrder(itemId, quantity)
            );
        }
    }
}

For this method, we’re expecting a file containing an item identifier and quantity on each line, with the values divided by a comma. We’ll assume the input follows that format, keeping the focus on transaction behavior rather than file validation.

We begin by opening the file through Files.newBufferedReader(), which provides a BufferedReader for reading the records individually. Each line is divided into values, converted into the expected Java types, and passed to orderRepository.save() as a new PurchaseOrder.

The important addition is rollbackFor = IOException.class. If reading the file fails after several records have been saved within the transaction, the escaping IOException tells Spring to roll back those database changes rather than commit a partial import.

The transaction still rolls back for runtime exceptions because adding rollbackFor doesn’t remove Spring’s normal rollback behavior. Instead, it extends the exceptions covered by the transaction’s rollback rules.

We can also specify a broader exception type when the operation should be canceled for any checked exception extending Exception:

@Transactional(rollbackFor = Exception.class)

Setting rollbackFor to Exception.class tells Spring to roll back for checked exceptions extending Exception, along with the unchecked exceptions already handled by default. This can be appropriate when a service operation shouldn’t commit after an exception escapes the method, although specifying individual exception types provides more control over which failures require rollback.

Spring Framework 7 also supports configuring rollback for all exceptions at the transaction management level through @EnableTransactionManagement(rollbackOn = ALL_EXCEPTIONS). This capability was introduced in Spring Framework 6.2 and remains available with Spring Boot 4.

The global setting allows applications to adopt rollback behavior that includes checked exceptions without repeating rollbackFor across individual methods. It must be explicitly configured, though, because Spring’s default behavior still distinguishes checked exceptions from runtime exceptions.

There’s also an important difference between an exception escaping a method and an exception being caught inside it.

If we catch an exception before it escapes the transactional method, Spring’s transaction interceptor doesn’t automatically treat that caught exception as a reason to roll back.

Think about the ordering service encountering insufficient inventory. We could catch the exception from reserve() and return a result rather than allowing the exception to propagate:

try {
    item.reserve(quantity);
} catch (IllegalStateException ex) {
    return null;
}

The catch block prevents the IllegalStateException from leaving the transactional method. If no earlier operation has already marked the transaction for rollback, Spring sees a normal method return and proceeds toward committing the transaction.

Because the purchase order was inserted before the inventory reservation, that insertion could be committed despite the failed reservation. The returned null value doesn’t tell Spring that the database changes should be discarded.

Catching an exception doesn’t automatically cancel a transaction, so we need to be careful about handling failures inside methods that have already modified database records.

For failures that should cancel the entire operation, allowing the exception to propagate outside the transactional method is generally preferable. Spring can then apply the rollback rules, while the calling layer can handle the exception after the transaction has been completed.

Spring also provides a way to mark the current transaction for rollback manually through TransactionAspectSupport.currentTransactionStatus().setRollbackOnly(). However, allowing exceptions to propagate generally keeps the service focused on its database operations without introducing direct calls to the transaction infrastructure.

We should also remember that an earlier database or repository failure may have already marked the transaction for rollback. Catching that exception doesn’t necessarily restore the transaction’s ability to commit.

To verify the intended behavior, we can write an integration test that invokes the service and inspects the database afterward. For an insufficient-inventory purchase, the test could record the existing order count, invoke the service with an unavailable quantity, and check that the count hasn’t increased.

Running that verification without a transaction managed by the test itself allows us to examine the service’s transaction behavior rather than relying on an automatic test rollback.

Marking Read Operations as Read-Only

Service methods don’t always need to insert records or modify existing data. We regularly retrieve information for controllers, reports, and other application features, and Spring allows us to identify transactions intended exclusively for reading from the database. The readOnly attribute of @Transactional communicates that intention to the transaction infrastructure. With JPA, this can reduce the persistence processing associated with tracking modifications to entities that we only intend to retrieve.

Let’s create an InventoryQueryService that returns the available quantity of an inventory item without modifying its stored values:

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class InventoryQueryService {

    private final InventoryRepository inventoryRepository;

    public InventoryQueryService(
            InventoryRepository inventoryRepository) {
        this.inventoryRepository = inventoryRepository;
    }

    @Transactional(readOnly = true)
    public int getAvailableQuantity(Long itemId) {
        InventoryItem item = inventoryRepository.findById(itemId)
            .orElseThrow(() ->
                new IllegalArgumentException("Item not found")
            );

        return item.getAvailableQuantity();
    }
}

We’ve defined a Spring-managed service with a repository dependency and a public method for retrieving inventory information. The getAvailableQuantity() method calls findById() to locate the requested entity and throws an exception if the identifier doesn’t correspond to an existing record.

After retrieving the entity, we return the value from getAvailableQuantity() without changing any of its fields. The repository handles the database lookup, while the service determines what information should be returned to the caller.

The @Transactional(readOnly = true) annotation tells Spring that this method is intended for reading data. With Hibernate, the read-only transaction setting can reduce processing associated with detecting modifications to loaded entities, including changes to the flush behavior.

Spring also communicates the read-only setting to the underlying database infrastructure where supported, allowing the database or JDBC driver to apply its own behavior.

However, readOnly = true doesn’t universally prevent database writes.

The annotation communicates a transaction preference rather than providing a guaranteed restriction against modifications. Depending on the database and transaction manager, certain writes may be rejected, while other configurations don’t enforce that restriction.

We should therefore keep methods marked as read-only focused on retrieval operations instead of depending on the annotation to prevent accidental updates.

Spring Data JPA already provides read-only transaction settings for inherited repository reading methods, which is worth keeping in mind before adding annotations throughout a service.

Methods such as findById() and findAll() inherit transactional configuration from Spring Data JPA’s repository implementation. For ordinary calls to those methods without an existing transaction, the repository can establish its own read-only transaction.

This means we don’t necessarily need an additional service-level read-only transaction for a method that performs a single repository lookup.

The service annotation becomes more valuable when several database queries need to execute within a common transaction boundary.

Imagine we’re building a reporting service that retrieves purchase orders, loads their associated inventory records, and calculates information from the retrieved entities. By placing a read-only transaction around the service method, we keep those repository operations within the same transaction and JPA persistence context.

Sharing the persistence context can also help when accessing lazily loaded entity relationships during service execution, provided the required entities remain associated with the active persistence context.

Still, a read-only transaction doesn’t automatically guarantee that every query observes an identical database snapshot. The visibility of concurrent changes depends on the database’s transaction isolation level.

For our inventory query service, the main benefit is communicating that the method retrieves data without intending to modify it. This gives Spring and Hibernate information they can use when managing the persistence context and database interaction, while the service remains responsible for performing only the intended reading operations.

Recognizing Calls That Bypass the Transaction Proxy

Spring normally applies @Transactional through proxies that intercept calls to Spring-managed service methods. The proxy allows Spring to begin or join a transaction before passing execution to the actual method, applying the transaction settings declared on that method.

This mechanism explains why an annotated method can sometimes execute without its expected transaction settings being applied. What matters is how execution reaches the method.

When we invoke a transactional service through an injected Spring-managed bean, the call passes through Spring’s proxy. The transaction infrastructure can then inspect the annotation and apply the associated settings before the method executes. Calls between methods inside the same service instance behave differently because they don’t pass through that proxy.

Returning to our OrderService, imagine we add a method that internally calls the existing placeOrder() method:

public Long submitInternalOrder(Long itemId, int quantity) {
    return placeOrder(itemId, quantity);
}

We’ve introduced submitInternalOrder() as an ordinary public method without @Transactional. Its implementation calls the existing placeOrder() method directly from the same service instance.

If another component invokes submitInternalOrder(), Spring doesn’t establish a service-level transaction for that outer method because it has no transactional annotation.

The internal call to placeOrder() also doesn’t pass through the transaction proxy. As a result, the @Transactional annotation on placeOrder() isn’t applied to this invocation. That can recreate the earlier problem where repository operations execute within their individual transactions rather than participating in a transaction covering the entire purchase.

The annotation on placeOrder() remains valid, but Spring needs the call to reach the method through its transaction infrastructure for those settings to be applied.

We can address this by moving the calling operation into a different Spring-managed service. Through dependency injection, we’ll receive a reference to the transactional service that allows Spring to intercept the call.

For our ordering operation, let’s introduce a CheckoutService responsible for forwarding purchase requests to OrderService:

import org.springframework.stereotype.Service;

@Service
public class CheckoutService {

    private final OrderService orderService;

    public CheckoutService(OrderService orderService) {
        this.orderService = orderService;
    }

    public Long submit(Long itemId, int quantity) {
        return orderService.placeOrder(itemId, quantity);
    }
}

The CheckoutService constructor receives the Spring-managed OrderService bean. When we call submit(), it forwards the purchase request through that injected reference rather than calling a method on its own instance.

Spring can intercept the call to orderService.placeOrder() and apply the @Transactional annotation before the ordering operation executes.

This keeps the purchase transaction in the service responsible for modifying the database, while the checkout service coordinates access to that operation.

We could also place @Transactional on the outer method when it represents the complete database operation. The appropriate location depends on which method should establish the transaction boundary.

There’s an important distinction involving transactions that are already active.

Calling an annotated method from the same instance doesn’t cancel an existing transaction. If the outer method was invoked through Spring’s proxy and already established a transaction, the internal method executes within that existing transactional context.

What the internal call cannot do through ordinary proxy interception is apply the called method’s own transaction settings independently.

This becomes relevant when the called method declares different propagation behavior, a different isolation level, or other transactional attributes. Those settings aren’t applied merely because execution enters the annotated method through an internal Java call.

Private helper methods have a related limitation. Spring’s ordinary proxy-based transaction management cannot intercept private method calls, so placing @Transactional on a private helper doesn’t establish the expected transaction boundary.

Keeping transactional service methods public and calling them through Spring-managed beans gives us a consistent way to apply the intended transaction settings.

We also need to pay attention to thread boundaries. Transactions managed through the ordinary JPA transaction manager are associated with the current execution thread. If a service starts database operations through @Async, a new thread, or an executor, the caller’s transaction isn’t automatically transferred into that execution.

Database operations running asynchronously therefore need an appropriate transaction boundary of their own.

This distinction is important for services that perform background processing because an annotation on the original calling method doesn’t automatically cover database operations executed later in a different thread.

Controlling Transaction Participation

Transactions can involve calls across multiple services, and Spring needs to determine how those methods participate when a transaction is already active.

The propagation attribute of @Transactional controls this behavior. It determines if a transactional method joins an existing transaction, establishes an independent transaction, or follows a different participation rule.

Spring’s default propagation setting is Propagation.REQUIRED.

With REQUIRED, Spring joins an existing transaction when an appropriate transaction is available. If the calling thread doesn’t already have a transaction managed by the selected transaction manager, Spring starts a new transaction.

We can explicitly declare that setting through the annotation:

@Transactional(propagation = Propagation.REQUIRED)

This declaration provides the same propagation behavior as the default @Transactional annotation. We don’t normally need to specify it unless we’re documenting a particular transaction requirement or comparing it with a different propagation option.

Think about our ordering service calling a second transactional service that updates a related database record.

If both methods use REQUIRED, the second service participates in the transaction already established by the first. Its database changes belong to the same transaction, so an eventual rollback can discard changes made by both services.

This behavior is valuable when multiple service methods contribute to the same database operation, allowing the outer service to coordinate their database changes within a shared transaction.

There is, however, an important consequence when an inner transactional method encounters an exception.

If the inner method marks the shared transaction for rollback, catching its exception in the outer method doesn’t automatically make the transaction eligible for commit again.

The outer method can finish normally and attempt to commit, but Spring recognizes that the transaction has already been marked for rollback. In that situation, the outer caller can receive an UnexpectedRollbackException, indicating that its attempted commit couldn’t succeed because the shared transaction was rolled back.

This prevents callers from receiving an apparently successful transaction result when the database changes were actually discarded.

Spring also provides Propagation.REQUIRES_NEW for operations that need an independent transaction:

@Transactional(propagation = Propagation.REQUIRES_NEW)

With REQUIRES_NEW, Spring begins a new transaction for the annotated method rather than participating in an existing transaction. If an outer transaction is active, Spring suspends its transaction context while the independent transaction executes, provided the transaction manager supports that behavior.

The new transaction can commit or roll back independently of the outer transaction.

This can be appropriate for certain auditing operations where the application needs to record a failure even if the business operation that produced it is rolled back.

For instance, we could create an auditing service that stores an event through an AuditRepository, giving the audit operation its own transaction:

@Service
public class AuditService {

    private final AuditRepository auditRepository;

    public AuditService(AuditRepository auditRepository) {
        this.auditRepository = auditRepository;
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void recordFailure(Long orderId, String reason) {
        auditRepository.save(
            new AuditEntry(orderId, reason)
        );
    }
}

For this example, we’re assuming AuditEntry is a JPA entity representing a stored audit event and AuditRepository is its corresponding Spring Data JPA repository.

We’ve placed REQUIRES_NEW on recordFailure() so the audit entry can be persisted within its own transaction. If the method is invoked through the Spring-managed AuditService proxy while an ordering transaction is active, Spring suspends the ordering transaction and establishes a new transaction for the audit entry.

After the audit operation completes, Spring resumes the suspended transaction.

If the audit transaction commits successfully, its record isn’t automatically rolled back when the original ordering transaction later fails. Similarly, a rollback of the audit transaction doesn’t automatically mark the suspended ordering transaction for rollback.

The outcome still depends on how exceptions propagate through the calling service, so an audit failure that escapes into the outer transactional method can affect that method’s rollback behavior.

We also need to account for database connections when starting independent transactions.

The outer transaction may retain its database connection while the inner transaction requires an additional connection from the connection pool. If several requests use REQUIRES_NEW concurrently, the application can exhaust its available connections if the pool doesn’t have enough capacity.

For that reason, independent transactions are generally best reserved for operations that actually require their own commit or rollback behavior.

The proxy limitation discussed earlier also applies to REQUIRES_NEW. Calling an annotated method internally from the same service instance doesn’t create the independent transaction through ordinary Spring proxy-based transaction management. The method must be invoked through the appropriate transactional proxy for its propagation setting to take effect.

For most service operations, the default REQUIRED propagation setting remains appropriate because related database changes should generally participate in the same transaction.

Transaction propagation should also be distinguished from transaction isolation. Propagation controls how a method participates in an existing transaction, while isolation governs how concurrent transactions interact with database changes. Spring’s default isolation setting delegates to the database, so the resulting behavior depends on the database configuration.

We can configure an isolation level through the isolation attribute of @Transactional, but changing that setting doesn’t replace database constraints or concurrency controls.

In the ordering service, the @Version field on InventoryItem remains valuable because multiple customers can attempt to reserve the same inventory record concurrently. The transaction groups the related purchase modifications, while optimistic locking detects conflicting updates that could cause an outdated inventory quantity to overwrite a newer value.

When choosing the appropriate propagation setting for each service method, we control which database operations share a transaction and which need independent commit behavior. Spring handles transaction participation according to those settings, while the service methods remain responsible for the database operations they perform.

Conclusion

Managing transactions in Spring Boot 4 starts with placing @Transactional around service methods that need related database operations to commit or roll back as a single unit. From there, we can adjust rollback rules for different exceptions, mark retrieval methods as read-only, and control how transactions behave across service calls. Keeping transaction boundaries in the right places and accounting for Spring’s proxy behavior helps prevent partial updates while giving us more control over how database changes are committed.

1. Spring Framework Declarative Transaction Management

2. Spring Framework @Transactional Annotation Settings

3. Spring Framework Transaction Rollback Rules

4. Spring Framework Transaction Propagation

5. Spring Data JPA Transactionality

Share Alexander Obregon's Substack

In my last SQL article, we went over Pivot Queries in SQL Server, where we turned row values into columns to summarize data. Now we’re going the other direction with UNPIVOT, which takes values stored across multiple columns and turns them into rows. Some tables store related information across individual columns, like monthly sales figures, survey responses, or measurements collected over time. While this structure can make individual records convenient to read, filtering, grouping, or summarizing values across those columns can become more complicated. With UNPIVOT, we can move those values into a single column while keeping their original column names as identifiers, giving us a format better suited to reporting, aggregation, and further SQL queries without changing the original table.

Subscribe now

Preparing the Source Data

Before applying UNPIVOT, we need to look at how the original data is organized and which columns contain the values we want to transform. The columns supplied by the source table or query determine how we can perform that transformation, so we need to pay attention to their names, data types, and relationships. We’ll use a table containing monthly sales figures to examine how wide data is stored and what needs to be prepared before moving those values into rows.

What Wide Data Looks Like

Some database tables store related information across multiple columns rather than keeping every measurement in its own row. This is commonly called a wide table, where each record contains several values representing different categories, periods, or attributes. We can encounter this structure in sales reports, financial records, survey results, or other data where individual columns represent specific measurements.

Think about a table containing monthly sales figures for different products. Each product has its own record for a particular year, with January, February, and March stored as individual columns. This allows someone viewing the table to read several months of sales figures for a product without needing to look through multiple records.

We can create a table named MonthlySales to represent this structure, with two identifying columns followed by three monthly sales columns:

CREATE TABLE dbo.MonthlySales
(
    ProductID INT NOT NULL,
    SalesYear INT NOT NULL,
    January DECIMAL(12, 2) NULL,
    February DECIMAL(12, 2) NULL,
    March DECIMAL(12, 2) NULL
);

INSERT INTO dbo.MonthlySales
    (ProductID, SalesYear, January, February, March)
VALUES
    (101, 2026, 1250.00, 1400.00, 1600.00),
    (102, 2026, 900.00, NULL, 1150.00);

We start by creating dbo.MonthlySales with five columns, then insert two records containing sales figures for different products. The ProductID column identifies which product each record belongs to, while SalesYear tells us the year associated with those figures. Both columns provide context for the sales amounts, allowing us to distinguish records belonging to different products or years.

Meanwhile, the January, February, and March columns hold the actual sales amounts. Each column represents a particular month, so its name tells us which period the recorded amount belongs to. We define these columns as DECIMAL(12, 2) to store numeric values with two decimal places, which is appropriate for the sales amounts in our example.

Notice that the month names are part of the table structure rather than values stored in a month column. This difference becomes important when we want to query sales across different periods because SQL Server treats January, February, and March as individual fields.

We can retrieve the original records with a regular SELECT statement to see how the information is arranged:

SELECT
    ProductID,
    SalesYear,
    January,
    February,
    March
FROM dbo.MonthlySales
ORDER BY ProductID;

The query retrieves all five columns from MonthlySales and orders the records by ProductID. We receive two rows, each containing the sales figures for an individual product. Product 101 has amounts recorded for all three months, while product 102 has values for January and March but contains NULL for February.

Having these columns side by side is convenient when someone wants to compare the monthly figures for a particular product. If we needed to retrieve January sales for product 101, we could reference the January column directly without involving the other months.

However, queries that need to treat months as a category require more SQL expressions. Calculating sales totals for each month would involve referencing the corresponding monthly columns, while filtering records according to a particular month would depend on which column held that information. The same limitation becomes more noticeable as additional months are stored in the table. If the structure eventually included all twelve months, queries covering the entire year would need to account for twelve different columns rather than referring to a single month field.

This brings us to the distinction between wide and long data. Wide data keeps related measurements across columns, while long data stores those measurements vertically, with a column identifying the category and a second column containing its value.

For our sales example, the long representation would have a month field containing values such as January or February, followed by a field containing the associated sales amount. Product identifiers and years would still provide context for those measurements, allowing us to identify the original record associated with each value.

SQL Server’s UNPIVOT operator allows us to produce that arrangement from the existing wide table without permanently changing its structure. Before applying it, though, we need to confirm that the columns containing the monthly figures have compatible definitions.

Preparing Compatible Columns

The columns selected for an UNPIVOT operation must have matching data types because their values will eventually be placed into a single output column. If those source columns have incompatible definitions, SQL Server can return a type conflict instead of completing the query.

For our MonthlySales table, we defined January, February, and March as DECIMAL(12, 2). This means every monthly value follows the same numeric definition, with a precision of twelve digits and a scale of two decimal places.

Precision represents the maximum total number of digits stored in a decimal value, while scale determines how many of those digits appear after the decimal point. With DECIMAL(12, 2), we can store up to ten digits before the decimal point and two afterward. Keeping the monthly columns consistent allows their values to be placed into a common output column without requiring additional conversions. SQL Server also requires matching lengths when dealing with character columns. For instance, VARCHAR(50) and VARCHAR(100) aren’t interchangeable within the same UNPIVOT column list merely because both store character data. Their declared lengths differ, which prevents SQL Server from treating them as identical source types.

The same concern applies to numeric columns defined with different precision or scale values. If January were declared as DECIMAL(10, 2) and February as DECIMAL(12, 2), their definitions wouldn’t match, despite both being capable of storing monetary amounts.

Before preparing a query against an existing table, we can inspect the column definitions through SQL Server’s catalog views. This allows us to check the metadata directly rather than relying on the values currently stored in each column:

SELECT
    c.name AS ColumnName,
    t.name AS DataType,
    c.precision AS NumericPrecision,
    c.scale AS NumericScale,
    c.is_nullable AS AllowsNull
FROM sys.columns AS c
INNER JOIN sys.types AS t
    ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.MonthlySales')
    AND c.name IN ('January', 'February', 'March')
ORDER BY c.column_id;

We begin with sys.columns, which contains metadata about the columns defined within database objects, including their numeric precision, scale, and nullability. By joining it with sys.types, we can retrieve the corresponding data type names alongside those properties.

Next, we use OBJECT_ID to identify our MonthlySales table and restrict the results to January, February, and March through the IN condition. The ORDER BY clause follows their original column positions, making it convenient to compare their definitions in the order they appear within the table.

For the table we created, all three columns return decimal as their data type, a precision of 12, and a scale of 2. Their AllowsNull values are also 1, indicating that each column permits NULL values.

We don’t need matching nullability across every source column for UNPIVOT to accept them. Nullability determines if a particular column can contain missing values, while data type compatibility determines if values from different columns can be placed into the same output field.

If the source columns have different data types, we can prepare them through a derived table or common table expression before applying the operator. SQL Server’s CAST or CONVERT functions allow us to give the selected values a common data type, provided their original contents support the conversion.

For numeric sales figures, converting the relevant columns to the same DECIMAL precision and scale would allow us to treat them consistently. We should select a type with enough capacity to represent the existing values without unwanted rounding or loss of information. Character columns follow a similar principle, where the chosen length needs to accommodate the values being converted.

Not every column in the source table needs to share the same data type. Our ProductID and SalesYear columns happen to be integers, but they aren’t part of the monthly values being transformed. These identifying columns can retain their existing definitions because their purpose is to associate the measurements with the original records.

We also need to distinguish the columns that identify a record from those containing the measurements. For our sales table, ProductID and SalesYear establish the context, while January, February, and March contain the amounts we intend to turn into rows. Identifying these groups before applying UNPIVOT helps us select the intended columns without accidentally treating product identifiers or years as sales measurements.

Finally, our sample contains a NULL value for February under product 102. This represents a missing or unknown sales amount rather than a recorded value of zero. We want to preserve that distinction in the source data because replacing missing information with zero would assign a meaning that wasn’t present in the original record.

SQL Server has specific behavior for these missing values during UNPIVOT, which we’ll examine when applying the operator. The monthly columns now have matching numeric definitions, and we’ve identified which fields contain the measurements and which provide the context needed to associate them with their original records.

Transforming Data with UNPIVOT

With our monthly sales data prepared, we can now use UNPIVOT to turn the individual month columns into rows. The operator performs this transformation within the FROM clause, allowing us to retrieve the original records in a different format without modifying the source table. We’ll start with the basic syntax, examine the resulting records, and then look at how missing values and additional SQL operations affect the results.

Writing the UNPIVOT Query

We can begin by transforming the three monthly sales columns from our MonthlySales table. Rather than retrieving January, February, and March as individual fields, we want the query to return a month name alongside its corresponding sales amount.

The UNPIVOT operator lets us define which columns should become rows, what to call the new column containing their names, and where their original values should appear.

Our first query will transform the monthly sales figures while retaining the product identifiers and sales years:

SELECT
    ProductID,
    SalesYear,
    SalesMonth,
    SalesAmount
FROM dbo.MonthlySales
UNPIVOT
(
    SalesAmount FOR SalesMonth IN (January, February, March)
) AS SalesRows
ORDER BY ProductID, SalesMonth;

We start by selecting ProductID and SalesYear, which identify the original sales records. The next two fields, SalesMonth and SalesAmount, are created through the UNPIVOT operation and contain the transformed information.

Inside the UNPIVOT clause, SalesAmount defines the output column that receives the values originally stored under January, February, and March. Because all three source columns have the same DECIMAL(12, 2) definition, their values can be placed into this common numeric field.

Next, FOR SalesMonth defines the column that will hold the original column names. Instead of having January, February, and March across the top of the result, we’ll have their names stored as text values within SalesMonth.

The IN (January, February, March) portion tells SQL Server exactly which source columns to transform. Only the columns included in this list participate in the operation, so adding April or May to the original table wouldn’t automatically include those months in our results.

We also give the resulting table expression the alias SalesRows, which provides a name for the transformed data within the query. SQL Server requires an alias for the result of UNPIVOT, and we can reference it when qualifying columns in more involved queries. The final ORDER BY organizes the results by product identifier and month name, making it convenient to examine the records belonging to each product.

Notice that the UNPIVOT clause doesn’t require an aggregate function such as SUM or AVG. We’re moving existing column values into rows without calculating new amounts or combining measurements from different records.

This is an important distinction from PIVOT, which performs aggregation as part of its operation. With UNPIVOT, the amounts remain associated with their original product and year, while the month names become values that we can reference in later queries.

Reading the Transformed Rows

After executing our query, the original two records produce five rows containing individual monthly sales amounts. The output still includes the product identifiers and years, but the three monthly columns have been replaced with SalesMonth and SalesAmount.

For product 101, we receive three records. January has a sales amount of 1250.00, February contains 1400.00, and March contains 1600.00. Each record retains the product identifier 101 and the sales year 2026, allowing us to identify where the amount originally came from.

Product 102 produces only two records, with January containing 900.00 and March containing 1150.00. The February value was NULL in the original table, so it doesn’t produce a row during the transformation.

We can compare this result with the original wide table to see what changed. Previously, each product occupied a single row containing three monthly amounts. After applying UNPIVOT, each available monthly amount occupies its own row, accompanied by the month name and identifying information.

The relationship between the sales amounts and their products remains intact. We haven’t changed which product owns a particular value, and the year associated with each amount remains available. If our source table contained records for multiple years, those year values would continue to accompany the corresponding monthly amounts.

What changes is how we can reference the months in SQL. Instead of selecting different columns for different periods, we can refer to SalesMonth when we want to identify a particular month or SalesAmount when we want to access its numeric value.

The number of rows produced depends on how much information is present in the selected source columns. If a record contains values across five columns included in UNPIVOT, that record can produce five output rows. Missing values reduce that number because NULL entries are excluded.

This also means the transformed result can contain considerably more rows than the original table. For a source containing thousands of products and twelve monthly columns, the resulting row count could be substantially larger if most of those columns contain recorded amounts.

Keep in mind that a greater number of rows doesn’t mean the query has created additional sales measurements. We’re retrieving the existing values in a different arrangement, where each generated row corresponds to a particular source column from an original record.

The ORDER BY clause from our earlier query sorts SalesMonth alphabetically according to the applicable collation. As a result, February appears before January, followed by March. If we need chronological ordering for a report, we can provide an expression that assigns each month its calendar position rather than sorting the names directly.

It’s also important to recognize that UNPIVOT isn’t guaranteed to reverse an earlier PIVOT operation perfectly. The two operators transform data in opposite directions, but PIVOT can combine multiple source records through aggregation.

If several original values were combined into a total, applying UNPIVOT afterward would return that total as an individual value rather than recovering the records that contributed to it. The original information has already been aggregated, so those individual values aren’t available to reconstruct.

Missing values introduce a similar limitation because UNPIVOT excludes NULL entries from its output. This means we can’t assume that every source column will produce a corresponding row, which becomes important when reports depend on the number of recorded periods or measurements.

Handling Missing Values

Our sample table contains a NULL value for February under product 102, which gives us an opportunity to examine how UNPIVOT handles missing information. SQL Server doesn’t produce an output row for a source column containing NULL, so the February record for that product is absent from the transformed result.

This behavior is part of the operator itself, and SQL Server doesn’t provide an INCLUDE NULLS option within its UNPIVOT syntax.

The distinction between NULL and zero is important when deciding how the resulting data should be interpreted. Zero represents an actual numeric value, while NULL indicates that the value is missing or unknown.

If a product recorded zero sales for February, that amount would still produce a row because zero is a valid numeric value. However, if the monthly column contains NULL, there is no corresponding output row. This difference can affect reports that count recorded periods, calculate averages, or identify products with incomplete sales information.

Depending on the information we’re retrieving, we may want to retain a row for every month, including those without recorded amounts. If the missing values should genuinely be treated as zero, we can substitute them before applying UNPIVOT.

We can do this through a derived table and the ISNULL function:

SELECT
    ProductID,
    SalesYear,
    SalesMonth,
    SalesAmount
FROM
(
    SELECT
        ProductID,
        SalesYear,
        ISNULL(January, 0.00) AS January,
        ISNULL(February, 0.00) AS February,
        ISNULL(March, 0.00) AS March
    FROM dbo.MonthlySales
) AS SourceData
UNPIVOT
(
    SalesAmount FOR SalesMonth IN (January, February, March)
) AS SalesRows
ORDER BY ProductID, SalesMonth;

We begin with an inner SELECT statement that retrieves the existing sales records and applies ISNULL to each monthly column. The function evaluates its first argument and returns that value when it isn’t NULL. If the value is missing, the function returns the replacement provided as its second argument.

For our query, we supply 0.00 as that replacement, so any missing monthly amount becomes zero in the derived table. Because the source columns are defined as DECIMAL(12, 2), the ISNULL expressions retain that numeric definition.

The outer query then applies UNPIVOT to the results returned by SourceData. We haven’t updated the original table, and the replacement values exist only within the query.

Product 102 now produces a February record with a SalesAmount of 0.00, giving both products three monthly records in the output. The other sales figures remain unchanged because ISNULL returns their existing amounts rather than replacing them.

We should only make this substitution when zero accurately represents the missing information. If February’s sales amount hasn’t been collected or is genuinely unknown, replacing it with zero would make the result appear as though no sales occurred during that period.

There are also situations where we want to retain missing values as NULL while still receiving a row for every month. SQL Server’s CROSS APPLY operator can accomplish this with a VALUES table constructor, providing an alternative to UNPIVOT when preserving those entries is necessary.

We can retrieve the same monthly measurements through the following query:

SELECT
    s.ProductID,
    s.SalesYear,
    v.SalesMonth,
    v.SalesAmount
FROM dbo.MonthlySales AS s
CROSS APPLY
(
    VALUES
        ('January', s.January),
        ('February', s.February),
        ('March', s.March)
) AS v(SalesMonth, SalesAmount)
ORDER BY s.ProductID, v.SalesMonth;

We begin by retrieving records from MonthlySales with the alias s. The CROSS APPLY clause then evaluates the VALUES expression for each source record, producing three month and amount pairs.

Each pair contains the month name as a text value followed by the corresponding amount from the source table. The alias v(SalesMonth, SalesAmount) names the two columns returned by that expression, allowing us to reference them in the outer SELECT.

Unlike UNPIVOT, this VALUES expression retains the February row for product 102 with its original NULL amount. We now receive six rows because every product contributes three monthly entries, including the missing measurement.

The CROSS APPLY query also preserves the original numeric values without replacing them. Product 101 retains its three recorded amounts, while product 102 still has 900.00 for January and 1150.00 for March. February remains NULL, allowing the resulting data to distinguish missing information from an actual zero.

This gives us an alternative when reports require every period to appear without assigning numeric values to unknown measurements. The original source remains unchanged, and we can choose the transformation according to how the missing information should be represented.

Summarizing the Results

After transforming our monthly columns into rows, we can apply familiar SQL operations to the resulting data. The new SalesMonth column allows us to group records by month, while SalesAmount provides the numeric values needed for calculations.

We can begin by calculating the combined sales amount for each month across all products. Rather than referencing January, February, and March individually in the calculation, we’ll group the transformed rows according to their month names:

SELECT
    SalesMonth,
    SUM(SalesAmount) AS TotalSales
FROM dbo.MonthlySales
UNPIVOT
(
    SalesAmount FOR SalesMonth IN (January, February, March)
) AS SalesRows
GROUP BY SalesMonth
ORDER BY SalesMonth;

We first apply UNPIVOT to produce the individual monthly records, then use GROUP BY SalesMonth to collect records belonging to the same month. The SUM function adds the corresponding SalesAmount values within each group and returns the result as TotalSales.

With our sample data, January produces a total of 2150.00 from the amounts recorded for products 101 and 102. February returns 1400.00 because only product 101 has a recorded value, while March returns 2750.00 from both products.

The original records contained three monthly columns, but our calculation now references only the SalesAmount field. We can apply the same aggregate function to every month included in the transformation without creating an individual expression for each source column. We should also pay attention to the identifying columns when calculating totals across different periods. Our table currently contains records for 2026, so grouping by month alone produces the expected totals for that year.

If the table contained records from multiple years, however, grouping only by SalesMonth would combine January amounts from every year into a single total. To calculate monthly totals for individual years, we would include SalesYear in both the SELECT and GROUP BY clauses.

Past aggregation, we can filter the transformed rows through a regular WHERE clause. Suppose we want to retrieve monthly sales amounts of at least 1000.00 from 2026, regardless of which original month column contained those values.

SELECT
    ProductID,
    SalesMonth,
    SalesAmount
FROM dbo.MonthlySales
UNPIVOT
(
    SalesAmount FOR SalesMonth IN (January, February, March)
) AS SalesRows
WHERE SalesYear = 2026
    AND SalesAmount >= 1000.00
ORDER BY ProductID, SalesMonth;

We begin with the same transformation, but this time the WHERE clause restricts the rows returned by the query. The first condition selects records associated with 2026, while the second keeps only those with a monthly sales amount of at least 1000.00.

For product 101, all three months meet the minimum amount, so we receive January, February, and March. Product 102 contributes only March because its January amount is 900.00, while February doesn’t produce a row due to its original NULL value.

The benefit of this format becomes apparent when we need to apply the same condition across multiple source columns. We can reference SalesAmount directly rather than creating conditions for every individual month, and the SalesMonth field tells us where each matching amount originated.

We can also incorporate these results into larger queries through joins, derived tables, or common table expressions. For instance, we could join the transformed records to a product table through ProductID, allowing us to retrieve product names or categories alongside their monthly sales amounts.

There are some performance considerations when transforming larger datasets. If a source table contains a substantial number of records and several monthly columns, UNPIVOT can produce far more rows than the original source. Subsequent grouping, sorting, and filtering operations may need to process those additional records.

SQL Server documentation also notes that repeatedly applying PIVOT and UNPIVOT within the same statement can negatively affect query performance. When queries involve multiple transformations, we can examine the execution plan to identify expensive operations and determine where unnecessary processing occurs.

For our monthly sales example, we should include only the source columns needed by the query. Transforming three months when a report requires only those three periods avoids processing additional monthly values that won’t contribute to the requested results.

We can apply the same SQL operations to the transformed records as we would to other query results, including filtering by product, calculating monthly totals, and retrieving values that meet particular conditions. The original table continues to store its sales figures across individual columns, while UNPIVOT provides the row-based representation needed for those calculations.

Conclusion

With UNPIVOT, we can turn selected columns into rows by defining where their original names and values should appear while retaining the identifying information from each record. We also need to account for how NULL values are excluded during the transformation, with options to handle missing data before retrieving the results. From there, we can filter, group, and calculate totals across the resulting rows without changing the original table.

1. FROM — PIVOT and UNPIVOT

2. FROM Clause in SQL Server

3. ISNULL Function

4. Table Value Constructor

5. CAST and CONVERT Functions

Share Alexander Obregon's Substack

As a Spring Boot application moves through development, testing, staging, and production, its configuration usually needs to change along the way. Development may call for detailed logging and a local database, while production typically requires different database connections, logging levels, and processing limits. Spring Boot profiles let us manage these differences by keeping common settings in one place and defining only the values that change between environments, rather than copying entire configuration files. We can also provide settings through environment variables, map related properties to Java objects with @ConfigurationProperties, and control which components Spring creates with @Profile. Spring Boot 4 supports these features, allowing each environment to receive the configuration it needs without changing the application’s Java code for every deployment.

Subscribe now

Organizing Environment Configuration

Spring Boot reads configuration from several sources when an application starts, including property files, YAML files, environment variables, and command-line arguments. Profiles determine which environment-specific files participate in configuration loading, allowing development, testing, staging, and production to have their own settings while sharing common values. Rather than maintaining a complete configuration file for every environment, we can keep properties that rarely change in one location and define replacements only where they’re needed. Also, Spring Boot loads the appropriate files based on the active profile, so we can move the application between environments without making changes to its Java code.

Keeping Shared Settings in One File

Most Spring Boot applications have configuration values that remain the same regardless of where they’re running. Properties such as the application name, default logging level, or general processing settings don’t necessarily need to change between development and production. Keeping these values in a central file reduces repetition and gives every environment the same starting configuration.

Spring Boot automatically reads application.properties from src/main/resources, making it a natural location for properties shared across environments. When we package the application into an executable JAR file, this configuration is included with it and remains available wherever the application runs.

For our report-processing application, we can begin by adding the following properties to application.properties:

spring.application.name=report-service

app.reports.format=summary
app.reports.batch-size=50

logging.level.root=INFO

The spring.application.name property identifies our application as report-service, while logging.level.root establishes INFO as the default logging level. Messages at the INFO level and higher are permitted by the root logger unless more specific logging configuration changes that behavior.

Meanwhile, the properties beginning with app.reports represent custom settings we’ve defined for our application. Spring Boot doesn’t automatically assign behavior to these properties, but it makes their values available for our Java classes to read. The format property has a default value of summary, and batch-size is set to 50, giving us starting values for how our application processes reports.

Because these values are defined in the shared configuration, they remain available when any of our environment profiles become active. If development needs detailed reports, we can replace the format for that environment while leaving the batch size unchanged. Production could retain summary reports while increasing the number processed per batch, without requiring us to repeat every property from the original file.

This also gives us a central location for changes that should affect every environment. If we decide to change the default report format, we can update its shared property rather than edit the same setting across several environment files. Properties that already have appropriate defaults don’t need to appear again in the environment-specific files.

Spring Boot supports application.yaml as well, which represents configuration through nested YAML entries rather than individual property assignments. Both formats support shared configuration, although sticking with the same format throughout an application helps avoid confusion. If .properties and YAML files exist in the same location, Spring Boot gives the .properties format priority.

Sensitive credentials should generally remain outside the packaged configuration. Database passwords, access tokens, and similar values can be supplied through external configuration or a dedicated secrets service rather than being committed to source control. Shared property files are better suited to settings that can safely accompany the application across environments.

Adding Profile-Specific Overrides

Environment-specific configuration files follow a naming convention that Spring Boot recognizes automatically. The filename begins with application-, followed by the profile name and the configuration file extension. For a profile named dev, the corresponding properties file is application-dev.properties. Spring Boot doesn’t require particular names for environments, so dev, test, staging, and prod are names we choose for our application. What counts is that the profile name in the filename matches the profile activated during startup.

For our report application, we can organize the files inside src/main/resources like this:

src/
└── main/
    └── resources/
        ├── application.properties
        ├── application-dev.properties
        ├── application-test.properties
        ├── application-staging.properties
        └── application-prod.properties

All five files belong in the same resources directory, but they serve different purposes. The original application.properties provides common values, while the remaining files contain properties intended for their respective environments.

Spring Boot reads the shared configuration alongside the file associated with the active profile. If the same property exists in both files, the profile-specific value takes priority within that configuration location. Properties absent from the active profile’s file can still receive their values from application.properties, so there’s no need to repeat settings that haven’t changed.

We’ll start with development by creating application-dev.properties and adding the following settings:

app.reports.format=detailed
logging.level.com.example.reports=DEBUG

The report format changes from summary to detailed, allowing development to receive a more comprehensive reporting format when our application code reads that property.

We’ve also introduced a more specific logging configuration for the com.example.reports package. Assuming our report-related classes belong to that package, setting its logging level to DEBUG allows more detailed messages from those classes during development.

The shared root logging level remains INFO, while the configured package and its descendants receive the more specific DEBUG setting. This lets us examine report-related activity in greater detail without changing the logging level throughout the entire application.

Notice that app.reports.batch-size doesn’t appear in the development file. Spring Boot still has access to the shared value of 50, so repeating it would serve no purpose. We only needed to add the properties that differ from the defaults.

Testing can have different requirements, particularly when we want to process smaller batches of data. We can create application-test.properties with the following property.

app.reports.batch-size=5

With the test profile active, the batch size becomes 5, while the report format remains summary because we haven’t provided a replacement.

The smaller batch size can help keep report-processing tests focused on limited amounts of data, although Spring Boot doesn’t require testing environments to have smaller processing limits. We’re choosing a value that makes sense for our application’s testing needs.

It’s also important to distinguish the profile name from the actual testing process. Naming a file application-test.properties doesn’t automatically activate it whenever a test runs. The test profile still needs to be selected for Spring Boot to load that configuration, which gives us control over the settings applied during testing.

For staging, we’ll create application-staging.properties and provide a different batch size:

app.reports.batch-size=25

Staging environments are commonly used to evaluate an application before releasing changes to production. Depending on the deployment, staging may have settings similar to production or smaller resource limits to accommodate its available infrastructure.

Our staging configuration keeps the shared summary format while changing the batch size to 25. We can introduce other properties later if staging requires different service addresses, timeouts, or logging levels, without needing to copy the entire shared configuration.

Production receives its own configuration through application-prod.properties, where we’ll increase the batch size again:

app.reports.batch-size=100

With prod active, the application receives a batch size of 100 while retaining the shared report format and default logging configuration.

We now have four environments with different settings, but we haven’t duplicated the application name, report format, or logging properties unnecessarily. Every environment begins with the common configuration and receives replacements only for properties that need different values.

This becomes more valuable as the application grows. If we introduce settings for database connection pools, request timeouts, or external service addresses, we can keep common defaults in application.properties and place environment-specific values in their corresponding files. Changes intended for production won’t require editing development configuration, while changes shared across environments can remain in the central file.

Spring Boot also supports profile-specific YAML files, like application-prod.yaml, with the same general loading behavior.

For applications with relatively few environment differences, we have an alternative that avoids creating multiple physical configuration files. Spring Boot allows a properties file to contain multiple configuration documents, divided by #---. Individual documents can then be activated conditionally through spring.config.activate.on-profile.

We could represent the shared report settings and the development override inside a single application.properties file:

spring.application.name=report-service

app.reports.format=summary
app.reports.batch-size=50

#---
spring.config.activate.on-profile=dev
app.reports.format=detailed

The first document contains properties available regardless of the active profile. The #--- marker begins a new configuration document, and spring.config.activate.on-profile=dev tells Spring Boot that the properties following it belong to the dev profile.

If development is active, the report format becomes detailed. For other profiles, that conditional document doesn’t contribute its properties, leaving the shared value of summary available.

This produces the same development format override as our earlier application-dev.properties file, so we’d choose either method rather than define the same property in both locations.

YAML supports multiple configuration documents as well, although its document separator is --- without the comment prefix.

For applications with several environments, individual profile-specific files give us dedicated locations for their settings. Projects with fewer differences may benefit from conditional documents in a single file instead. Both methods let us keep shared defaults while providing different values for the environments that need them.

Activating the Right Profile

Creating profile-specific configuration files gives Spring Boot the files it needs, but we still need to tell it which profile should be active when the application starts. The selected profile determines which environment-specific configuration participates in the startup process, allowing us to control the application’s settings without editing the files whenever we change environments.

Spring Boot provides the spring.profiles.active property for selecting active profiles. We can supply this value through shared configuration, an operating system environment variable, or a command-line argument.

During local development, passing the profile directly when starting the packaged application is a convenient option. Assuming our application is packaged as app.jar, we can start it with the development profile through the following command.

java -jar app.jar --spring.profiles.active=dev

The java -jar app.jar portion starts our packaged application, while --spring.profiles.active=dev tells Spring Boot to activate the dev profile before completing configuration loading.

Spring Boot then loads the common settings from application.properties and applies the properties defined in application-dev.properties.

For our report application, this gives us a detailed report format, a batch size of 50, and DEBUG logging for the configured report package. We haven’t changed any Java classes or modified the shared property values to get those development settings.

The same application can be started with the staging profile by providing --spring.profiles.active=staging instead. Spring Boot then loads the staging configuration, giving our report processor a batch size of 25 while preserving the shared report format.

To start the application with the production configuration, we’ll change the argument to prod:

java -jar app.jar --spring.profiles.active=prod

Production now receives the batch size of 100 from application-prod.properties, while the shared report format remains summary and the root logging level stays at INFO.

The packaged JAR remains identical between these launches. We don’t need to modify our Java classes or rebuild the application to select a different environment. Changing the active profile is enough for Spring Boot to load the corresponding configuration files.

For deployments, specifying the active profile through an operating system environment variable is another common option. This lets the deployment environment provide its configuration without requiring us to include the profile name directly in the Java startup command.

Spring Boot recognizes SPRING_PROFILES_ACTIVE as the environment variable corresponding to spring.profiles.active.

In a Bash-compatible shell, we can set that variable before starting our application:

export SPRING_PROFILES_ACTIVE=prod
java -jar app.jar

The export command assigns prod to the environment variable and makes it available to processes launched from that shell. When we start the application with java -jar app.jar, Spring Boot reads the variable and activates the production profile.

Deployment platforms can provide the same variable through their configuration facilities, allowing the appropriate profile to be selected without modifying the packaged application. We can give development, staging, and production their own profile values while deploying the same JAR file across those environments.

Spring Boot also permits spring.profiles.active inside the shared application.properties file. However, defining it externally gives each deployment control over its environment rather than permanently associating the packaged configuration with a particular profile.

If we start the application without specifying an active profile, Spring Boot falls back to a default profile named default. The shared application.properties file still loads normally, and Spring Boot also checks for application-default.properties if that file exists.

We can change the default profile through spring.profiles.default, although leaving it as default is sufficient for applications where deployment environments explicitly select their active profiles.

There is an important restriction on where these properties can be declared. Both spring.profiles.active and spring.profiles.default must be defined in non-profile-specific configuration sources. We cannot place them inside files such as application-dev.properties or documents activated through spring.config.activate.on-profile.

This keeps the selection of active profiles independent of the configuration documents that depend on those profiles being active.

Spring Boot also supports activating multiple profiles during the same application startup. We can provide their names as a comma-delimited value, which allows more than one profile-specific configuration file to participate in configuration loading.

java -jar app.jar --spring.profiles.active=dev,metrics

Both profiles become active, so Spring Boot can load configuration associated with dev and metrics alongside the shared properties.

The metrics profile is an optional name we’ve chosen for this example. We could create an application-metrics.properties file containing monitoring-related settings and activate it alongside whichever environment requires those settings.

If multiple active profiles contain conflicting properties in profile-specific files at the same configuration location, Spring Boot applies a last-wins rule. With dev,metrics, a value from application-metrics.properties can replace the same property defined in application-dev.properties.

This gives us flexibility when certain configuration groups should apply alongside the main environment. Still, for development, testing, staging, and production, selecting a single environment profile generally keeps the configuration more organized and reduces the chance of conflicting values.

Spring Boot reports active profile information during startup, which can help us confirm that the intended environment has been selected. If our application starts with unexpected settings, checking the reported active profiles gives us a starting point before examining the individual configuration files.

With the appropriate profile active, we can keep shared settings in application.properties and let Spring Boot load the environment-specific values that belong to that deployment.

Applying Configuration in Java

Configuration files determine which property values are available in each environment, but our Java classes still need a way to access those values and respond to the active profile. Spring Boot provides annotations that let us register components conditionally, bind related properties to Java objects, and read the final configuration without manually opening property files. We can also control which source takes priority when the same property has been defined in multiple locations. These features allow us to keep environment differences in configuration while the application continues running the same Java code across development, testing, staging, and production.

Selecting Beans with @Profile

Configuration files handle differences in property values, but some environments need different Spring components rather than different settings. Development may require a local implementation of a service, while production connects to an external provider. Spring gives us the @Profile annotation to control which beans are registered in the application context based on the active profiles.

Spring beans are objects managed by the Spring container, which creates them and makes them available to other components through dependency injection. Normally, Spring registers eligible beans when the application starts, but adding @Profile lets us restrict their registration to particular environments.

We can place @Profile on classes annotated with @Component or @Configuration, or apply it directly to individual @Bean methods. The placement determines how broadly the condition applies. Adding it to a configuration class restricts registration of that class and the beans declared within it, while placing it on a method restricts only the bean created by that method.

For our report application, we’ll create a configuration class that registers a development-specific startup message:

package com.example.reports;

import org.springframework.boot.ApplicationRunner;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;

@Configuration(proxyBeanMethods = false)
public class ProfileNoticeConfiguration {

    @Bean
    @Profile("dev")
    ApplicationRunner developmentNotice() {
        return args -> System.out.println(
            "Development profile is active"
        );
    }
}

We begin with @Configuration, which identifies ProfileNoticeConfiguration as a class containing Spring bean definitions. The proxyBeanMethods = false setting tells Spring that it doesn’t need to intercept calls between @Bean methods in this configuration class.

Inside the class, we’ve defined an ApplicationRunner bean through the developmentNotice() method. Spring Boot executes registered ApplicationRunner beans during startup after the application context has been initialized, making this a convenient place for actions that should happen as the application starts.

The @Profile("dev") annotation controls when Spring registers this particular bean. If we activate the dev profile, Spring creates the runner, and it prints the development message during startup.

Starting the application with prod, however, means the bean won’t be registered, so the message doesn’t appear. We don’t need to add conditional statements inside developmentNotice() because Spring determines if the bean belongs in the application context before creating it.

This becomes more practical when different environments require different implementations of the same service. We could register a local report delivery component for development and a production component that communicates with an external service. Other classes could depend on the same Java interface, with Spring injecting the implementation registered for the active profile.

When defining alternative implementations, we should arrange their profile conditions so Spring doesn’t register multiple competing beans for a dependency that expects a single implementation. Conflicting bean registrations can lead to dependency injection errors if Spring can’t determine which bean to inject.

The annotation also accepts multiple profile names. Writing @Profile({"dev", "test"}) allows a component to be registered when either dev or test is active, which can help when both environments need the same supporting component.

Spring supports profile expressions as well, including negation through !. With @Profile("!prod"), the component is registered whenever the prod profile is not active. That includes startup with the default profile if prod hasn’t been activated.

We should keep in mind that @Profile controls bean registration rather than property values or access permissions. For settings such as report format, batch size, or logging level, configuration properties are generally sufficient. Conditional beans become appropriate when the components themselves need to differ between environments.

Binding Settings with @ConfigurationProperties

Custom configuration properties become more convenient when we can access them through Java objects instead of retrieving individual values throughout the application. Spring Boot provides @ConfigurationProperties for mapping related properties into a class or record, giving us a central location to represent configuration that belongs to the same feature.

Our report application already defines app.reports.format and app.reports.batch-size, which control the report format and processing batch size. Rather than retrieving these properties individually whenever we need them, we can create a Java record that holds both values.

We’ll name the record ReportProperties and give it the same property prefix we’ve been defining in our configuration files:

package com.example.reports;

import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "app.reports")
public record ReportProperties(
    String format,
    int batchSize
) {
}

The @ConfigurationProperties annotation tells Spring Boot that this record represents properties beginning with app.reports. We don’t include the individual property names in the annotation because Spring Boot matches them to the record components during configuration binding.

Our format component receives the value of app.reports.format, while batchSize receives the value of app.reports.batch-size.

Notice that the property name contains a hyphen, but the Java component follows camel case naming. Spring Boot supports this through relaxed binding, which allows property names written in different supported naming styles to map to Java fields or record components without requiring identical spelling.

Spring Boot also handles converting values into the declared Java types. The report format remains a String, while the batch size becomes an int. If we provide a value that cannot be converted into the required type, configuration binding can fail during application startup.

Records are a good choice for this type of configuration because their components are initialized through the constructor and can’t be reassigned afterward. We can read their values through generated accessor methods without writing getters, setters, or a constructor ourselves.

To make ReportProperties available through Spring’s dependency injection system, we need to register it as a bean. Spring Boot provides @ConfigurationPropertiesScan, which searches for eligible classes annotated with @ConfigurationProperties.

We can add this annotation to our main application class:

package com.example.reports;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.ConfigurationPropertiesScan;

@SpringBootApplication
@ConfigurationPropertiesScan
public class ReportApplication {

    public static void main(String[] args) {
        SpringApplication.run(ReportApplication.class, args);
    }
}

The @SpringBootApplication annotation identifies our main application class and includes the standard Spring Boot configuration and component scanning behavior.

By adding @ConfigurationPropertiesScan, we’re telling Spring Boot to search for configuration property classes beginning in the package containing ReportApplication.

Because our record belongs to com.example.reports, Spring Boot can discover it during startup and register it as a bean. If the record were located outside the scanned package hierarchy, we’d need to specify the appropriate package for scanning.

We also don’t need to annotate ReportProperties with @Component. Configuration property scanning handles its registration, allowing Spring Boot to create the record through constructor binding.

With the configuration record registered, we can inject it into a service that needs access to report settings.

For our application, we’ll create ReportDescriptionService, which reads the report format and batch size:

package com.example.reports;

import org.springframework.stereotype.Service;

@Service
public class ReportDescriptionService {

    private final ReportProperties properties;

    public ReportDescriptionService(ReportProperties properties) {
        this.properties = properties;
    }

    public String description() {
        return "Format = " + properties.format()
            + ", batch size = " + properties.batchSize();
    }
}

The @Service annotation registers ReportDescriptionService as a Spring-managed component. We’ve declared a ReportProperties field and provided it through the constructor, allowing Spring to inject the configuration object when creating the service.

Inside description(), we call properties.format() and properties.batchSize() to retrieve the current configuration values. These accessor methods are generated automatically because ReportProperties is a Java record.

With the dev profile active, calling description() returns Format = detailed, batch size = 50, reflecting the development format override and the shared batch size.

If we run the application with prod, the same method returns Format = summary, batch size = 100.

We haven’t changed the service or created different versions of the record for each environment. Spring Boot resolves the active configuration before binding the properties, so our service receives the values associated with the current environment.

This becomes valuable when an application has several related configuration properties. We can group report settings into ReportProperties, create a different configuration class for database-related custom settings, and continue accessing those values through constructor injection.

Properties that must meet particular requirements should also be checked through configuration validation when appropriate. Spring Boot supports Jakarta Bean Validation for @ConfigurationProperties classes when the necessary validation dependencies and annotations are present. This allows invalid configuration to be rejected during startup rather than reaching a component later.

The record itself doesn’t automatically provide our report defaults. Those values come from the configuration files we’ve already defined, giving us a consistent location for managing differences between environments.

Following Property Precedence

Different configuration sources can define the same property, so Spring Boot needs a consistent way to determine which value the application receives. This is handled through property source precedence, where sources with higher priority can replace values from sources with lower priority.

We’ve already seen how an active profile can replace a value from application.properties. That behavior is part of a broader ordering system that includes external configuration files, operating system environment variables, Java system properties, and command-line arguments. For normal application startup, packaged application.properties values have lower priority than packaged profile-specific configuration values. External configuration files have higher priority than their packaged counterparts, with external profile-specific files taking priority over external shared files.

Operating system environment variables can then override values from those configuration files. Java system properties have higher priority than operating system environment variables, while command-line arguments take priority over these sources by default.

Spring Boot supports other property sources as well, including overrides intended for tests, but these are the sources we’ll encounter most frequently when configuring applications for different deployment environments.

Returning to our report application, the shared configuration provides a batch size of 50, while the production profile replaces it with 100.

Now, our production deployment may need a batch size of 150 without modifying the packaged configuration. We can provide that value through an operating system environment variable.

In a Bash-compatible shell, we’ll define it through the following command:

export APP_REPORTS_BATCHSIZE=150

The variable APP_REPORTS_BATCHSIZE corresponds to our custom property app.reports.batch-size.

Spring Boot supports converting property names to their environment variable equivalents by replacing periods with underscores, removing hyphens, and converting letters to uppercase.

Following those rules, app.reports.batch-size becomes APP_REPORTS_BATCHSIZE, which Spring Boot can bind to our configuration record.

If the prod profile is active and this environment variable has been exported, the final batch size becomes 150, replacing the value of 100 from application-prod.properties.

This allows deployment configuration to take priority over packaged defaults without requiring changes to the JAR file. We can provide different externally managed values depending on the environment where the application runs, while keeping the original configuration files unchanged.

Command-line arguments give us another way to override individual properties. Assuming the production profile has already been selected through the deployment environment, we can start the application with a different batch size.

java -jar app.jar --app.reports.batch-size=200

The --app.reports.batch-size=200 argument tells Spring Boot to provide 200 for that property during startup.

Because command-line arguments have higher priority than operating system environment variables and configuration files, our ReportProperties record now receives a batch size of 200.

The environment variable can still contain 150, and application-prod.properties can still contain 100, but neither takes priority over the command-line value in this situation.

This precedence behavior gives us several ways to control configuration depending on how the application is deployed. Packaged properties provide defaults, profile-specific files contain environment differences, and external sources can supply replacement values without rebuilding the application.

External configuration files also participate in the same system. Spring Boot searches supported locations outside the packaged application, including the current directory and its config directory.

For deployments requiring configuration files in other locations, we can provide spring.config.additional-location to include extra directories or files alongside Spring Boot’s default configuration locations. This differs from spring.config.location, which replaces the default search locations with the locations we specify.

The distinction becomes important when a deployment provides configuration through mounted files or directories. We can retain the packaged defaults while allowing properties supplied by the deployment environment to take priority where appropriate.

Sensitive values are another reason to keep configuration external. Database credentials and service tokens can be supplied through managed environment configuration or dedicated secrets services rather than being committed into repository files.

When a property receives an unexpected value, checking the different sources that define it can help identify the cause. The value in application-prod.properties may be correct, but an environment variable or command-line argument could be replacing it during startup.

Our Java classes don’t need special handling for those differences. Spring Boot resolves the property according to its precedence rules, and @ConfigurationProperties receives the final value during binding.

Checking Spring Boot 4 Behavior

The profile features we’ve been applying remain available in Spring Boot 4, including @Profile, @ConfigurationProperties, @ConfigurationPropertiesScan, and the existing profile-specific configuration conventions. We can continue organizing environment configuration through the same annotations and property files while following the current Spring Boot configuration system.

Spring Boot 4 builds on Spring Framework 7 and requires Java 17 or later. It also reorganizes several internal modules and updates configuration properties associated with particular technologies.

Some built-in property names changed during the transition from Spring Boot 3.x to Spring Boot 4. MongoDB configuration gives us a good example, where properties like spring.data.mongodb.uri and spring.data.mongodb.host have moved to spring.mongodb.uri and spring.mongodb.host.

Not every property beginning with spring.data.mongodb changed, however. Certain settings associated specifically with Spring Data MongoDB retain that prefix, including spring.data.mongodb.auto-index-creation.

For applications moving from Spring Boot 3.x, we should check the configuration properties associated with the dependencies our application relies on. The profile structure itself doesn’t need to change, but renamed configuration properties must be updated so Spring Boot can apply the intended settings.

Testing the configuration gives us a way to confirm that each environment receives the values we’ve defined, rather than relying entirely on what appears in the property files.

Spring Boot supports the @ActiveProfiles annotation through the Spring Test framework, allowing us to activate particular profiles while running tests.

For our report application, we’ll create a JUnit test that starts the Spring application context with the test profile active and checks the values bound to ReportProperties.

With spring-boot-starter-test available as a test dependency, we can create the following test with JUnit Jupiter and AssertJ:

package com.example.reports;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class ReportPropertiesTests {

    @Autowired
    private ReportProperties properties;

    @Test
    void loadsTestProfileSettings() {
        assertThat(properties.format())
            .isEqualTo("summary");

        assertThat(properties.batchSize())
            .isEqualTo(5);
    }
}

The @SpringBootTest annotation tells Spring Boot to create an application context for the test, allowing it to load the application’s configuration and register the Spring beans required for testing.

We’ve also added @ActiveProfiles("test"), which activates the test profile for this test class. Spring Boot therefore loads our testing configuration alongside the shared properties when preparing the application context.

The @Autowired annotation injects the ReportProperties bean into the test, giving us access to the values Spring Boot has already bound.

Inside loadsTestProfileSettings(), we check the report format and batch size through AssertJ assertions. The assertThat() method receives the actual property value, while isEqualTo() checks that it matches the value we expect.

The first assertion expects summary, which comes from our shared application.properties file because the testing profile doesn’t replace that setting.

Our second assertion expects 5, matching the batch size provided by application-test.properties.

If either assertion receives an unexpected value, the test fails and identifies which expectation wasn’t met. Assuming no higher-priority property source overrides the values, both assertions pass with the configuration we’ve established.

We can follow the same testing method for other profiles when their configuration needs verification. Tests can check values directly through configuration property objects, which helps us confirm that the application receives the intended settings without manually examining every property file.

Spring Boot also provides configuration inspection facilities through its Actuator module. The env endpoint can provide information about environment properties and their sources, while configprops provides information about configuration properties bound to Spring beans.

These endpoints require Spring Boot Actuator and appropriate endpoint exposure settings. Access should be restricted because configuration information can contain sensitive details, and Spring Boot applies sanitization rules to endpoint responses.

For our report application, these facilities can help us investigate situations where the application receives a different value from the expected configuration. We can examine the active environment, identify the property source involved, and compare the resolved values with those received by ReportProperties.

By combining profile-aware tests with Spring Boot’s configuration inspection capabilities, we can verify the settings applied to each environment while keeping our Java components focused on reading and applying the configuration they receive.

Conclusion

Spring Boot profiles let us manage environment-specific settings while keeping common properties in application.properties. We can activate profiles at startup, override values through external configuration, and rely on property precedence to determine which settings take priority. With @Profile, we control which beans Spring registers, while @ConfigurationProperties binds the resolved values to Java objects. Profile-aware tests then help us verify that development, testing, staging, and production receive their intended configuration without changing the application’s Java code.

1. Spring Boot Profiles

2. Spring Boot Externalized Configuration

3. Type-Safe Configuration Properties

4. Spring Framework Bean Definition Profiles

5. Spring Boot Testing

6. Spring Boot 4.0 Migration Guide

Share Alexander Obregon's Substack

Sales tables usually store each transaction on its own row, with details such as the region, sales channel, and amount recorded in columns. For reports that compare sales across different categories, those records need to be summarized and arranged so the totals can be read side by side. SQL Server provides the PIVOT operator to handle this transformation by turning selected category values into columns while calculating totals, counts, averages, or other aggregate results. The original table remains unchanged because the transformation happens within the query. To produce the expected results, we need to select the appropriate source columns, determine how SQL Server groups the records, and specify which categories should appear in the final report.

Subscribe now

How SQL Server PIVOT Works

The PIVOT operator changes how query results are arranged by turning selected category values from rows into columns. Instead of displaying every transaction individually, we can group related records and calculate values for each category through an aggregate function. This is helpful for reports where information needs to be compared across regions, sales channels, or other categories. To see how the transformation happens, we need to identify the columns that determine the output rows, the category values that become column headings, and the numeric data that will be calculated.

Starting with Sales Records

Sales information is commonly stored with each row representing an individual transaction. Every record contains details about that sale, such as its identifier, the region where it occurred, the sales channel involved, and the amount recorded. As transactions accumulate, the same regions and channels appear repeatedly because every sale is stored independently.

We’ll begin by creating a temporary table containing sales from three regions and three sales channels. The table will include eight transactions, giving us enough records to see how the categories relate to each other.

DROP TABLE IF EXISTS #Sales;

CREATE TABLE #Sales (
    SaleId INT NOT NULL,
    Region VARCHAR(20) NOT NULL,
    SalesChannel VARCHAR(20) NOT NULL,
    Amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO #Sales (SaleId, Region, SalesChannel, Amount)
VALUES
    (1, 'North', 'Online', 1200.00),
    (2, 'North', 'Online', 300.00),
    (3, 'North', 'Retail', 750.00),
    (4, 'South', 'Retail', 900.00),
    (5, 'South', 'Wholesale', 500.00),
    (6, 'West', 'Online', 600.00),
    (7, 'West', 'Wholesale', 1000.00),
    (8, 'West', 'Online', 150.00);

We create #Sales as a temporary table, which SQL Server stores in tempdb and makes available within the current session. The table is automatically removed when the session ends, although we can also drop it manually. Including DROP TABLE IF EXISTS at the beginning allows us to execute the example again without encountering an error if the temporary table already exists.

Each column serves a particular purpose. SaleId identifies the transaction, while Region and SalesChannel tell us where the sale belongs. The Amount column stores the monetary value as DECIMAL(10, 2), allowing up to ten total digits with two digits after the decimal point. This gives us a consistent numeric data type for the calculations we’ll perform later.

Looking at the inserted records, North appears three times because it contains three transactions. Two belong to the Online channel, while the remaining transaction belongs to Retail. West also contains multiple Online transactions, and South has sales recorded through Retail and Wholesale.

We can retrieve the sales information to examine how these records are currently arranged.

SELECT Region, SalesChannel, Amount
FROM #Sales
ORDER BY Region, SalesChannel;

We retrieve all eight transactions, sorted first by region and then by sales channel. Because we haven’t applied any aggregation, North’s two Online transactions remain on different rows, with amounts of 1,200 and 300. The query preserves each transaction’s individual amount while arranging the records in an order that’s convenient to read.

This structure makes sense for storing transaction details, but a regional sales report needs a different arrangement. If we want to compare how much each region earned through Online, Retail, and Wholesale sales, reading through individual transactions would require us to calculate the totals and compare the records manually.

Instead, we want the report to contain a single row for North, South, and West, with columns representing the three sales channels. Each region would then have its corresponding sales totals displayed beneath those headings, allowing us to compare the categories without reading through every transaction.

The source data already contains everything needed to produce that report. Region identifies which transactions belong to the same reporting group, SalesChannel determines their category, and Amount provides the values to be added.

We can perform that transformation through PIVOT without modifying the stored transactions. The operator takes the source records, applies an aggregate calculation, and returns the results arranged according to the selected categories. Our original sales remain available in the table, while the query produces a different presentation of the information.

Before constructing the complete query, we need to examine how the operator identifies these columns and determines what appears in the result.

Reading the PIVOT Syntax

SQL Server places the PIVOT operator within the FROM clause of a SELECT statement, where it operates on an existing table source. That source can be a table, a derived table created through a subquery, or another supported table expression.

For reporting queries, we can use a derived table to supply the exact columns needed for the transformation. This gives us control over the information passed to PIVOT before the aggregation takes place. Rather than passing every column from the original table, we can select only the columns that contribute to the intended report. The PIVOT syntax contains an aggregate function, a FOR clause, and an IN clause. These components tell SQL Server what calculation to perform, which source column contains the categories, and which category values should become output columns.

For our sales data, we can express the operator as follows:

PIVOT (
    SUM(Amount)
    FOR SalesChannel
    IN ([Online], [Retail], [Wholesale])
) AS PivotResult

We begin with SUM(Amount), which determines how the values will be calculated. SQL Server adds the amounts belonging to each matching group and sales channel, producing the totals that will populate the report. For North’s Online sales, this means adding the transactions of 1,200 and 300 to produce a total of 1,500.

The aggregate function doesn’t have to be SUM(), though the appropriate choice depends on what we want the report to calculate. COUNT() can count non-NULL values, AVG() calculates an average, and MIN() or MAX() retrieves the lowest or highest value within a group. Changing the aggregate function changes what the resulting numbers represent, while the categories can remain arranged in the same columns.

Following the aggregation, FOR SalesChannel identifies the source column whose values will become column headings. Our temporary table contains Online, Retail, and Wholesale within SalesChannel, so those values provide the categories for the transformation.

We then specify which categories should appear through the IN clause. By listing [Online], [Retail], and [Wholesale], we’re requesting three output columns with those names. The values inside the brackets correspond to category values stored in the SalesChannel column, allowing SQL Server to associate the appropriate transactions with their respective output columns.

Square brackets are SQL Server’s syntax for delimiting identifiers. They allow names containing spaces or certain special characters to be referenced as identifiers, and they can also prevent conflicts with reserved words. Our category names don’t require brackets in every SQL context, but including them makes their intended use as column identifiers explicit.

The IN clause also determines which categories are included in the result. It doesn’t automatically create a new column for every distinct category found in the source data.

If a new sales channel named Direct is added to #Sales, an ordinary pivot query containing only the original three channels won’t include a Direct column. We would need to add [Direct] to the IN list before that category could appear in the report.

This behavior gives a regular PIVOT query a consistent set of output columns, which is helpful when preparing reports that expect particular column names every time they run.

Past the category columns, we also need to know how the operator determines which transactions contribute to each output row. SQL Server handles this through the source columns that remain after identifying the category column and the value being aggregated.

With our sales data, SalesChannel supplies the categories and Amount supplies the values for SUM(). If we provide only Region, SalesChannel, and Amount as the source, the remaining Region column determines how the records are grouped.

Transactions belonging to North are therefore calculated within the North group, while South and West receive their own groups. Within each region, SQL Server calculates the amount for every sales channel listed in the IN clause. We end up with a regional summary rather than individual transaction records because the region is the only remaining column identifying the output rows.

This grouping behavior is why we need to pay attention to the columns supplied to the operator. The source determines the level at which records are aggregated, so including columns beyond those needed for the report can change how the resulting rows are organized.

Finally, AS PivotResult assigns an alias to the pivoted table expression. SQL Server requires the result of the PIVOT operation to have a table alias, allowing the transformed data to be referenced by the surrounding query. The fragment we’ve examined belongs after its source within the FROM clause, rather than being an executable statement by itself.

Through these components, we can combine category-based aggregation with the creation of output columns. The original transaction records remain available in their stored format, while the query produces a different arrangement for reporting.

For our sales report, the three source columns contain enough information to produce regional totals across Online, Retail, and Wholesale sales. We identify Region as the grouping column, calculate the amounts through SUM(), and select the categories that should appear as columns through the FOR and IN clauses.

Creating Pivot Reports

With the sales data available and the PIVOT syntax established, we can now put those components into a complete query. The goal is to produce a report where each region appears on its own row, with the total sales amounts distributed across Online, Retail, and Wholesale columns. We’ll start with a fixed set of categories, then examine how to handle missing values and control the grouping before moving into dynamically generated columns.

Building the First Pivot Query

Our first report will calculate the total sales amount for each region, organized by sales channel. Rather than retrieving individual transactions, we want each region to appear on a single row so the totals can be compared across the three channels.

To accomplish this, we’ll select the columns needed for the calculation, apply PIVOT to aggregate the amounts, and retrieve the resulting columns in the order we want them displayed.

SELECT
    Region,
    [Online],
    [Retail],
    [Wholesale]
FROM (
    SELECT Region, SalesChannel, Amount
    FROM #Sales
) AS SourceRows
PIVOT (
    SUM(Amount)
    FOR SalesChannel
    IN ([Online], [Retail], [Wholesale])
) AS PivotResult
ORDER BY Region;

We begin with the inner SELECT, which retrieves Region, SalesChannel, and Amount from our temporary table. Those three columns contain everything needed to calculate the regional totals. The derived table receives the alias SourceRows, allowing its results to serve as the source for the PIVOT operator.

Next, SUM(Amount) calculates the combined sales amount for matching records. The FOR SalesChannel clause identifies the categories, while the IN list determines which sales channels will appear as output columns. Because Region is the only remaining source column, SQL Server groups the transactions by region.

Looking at North, we have two Online transactions worth 1,200 and 300, so their combined amount becomes 1,500 under the Online column. The Retail column contains 750 because North has a single retail transaction, while Wholesale has no matching records.

South produces 900 under Retail and 500 under Wholesale. West has two Online transactions totaling 750, along with a Wholesale transaction worth 1,000. These calculations give us three output rows, each representing the combined sales for its region.

The outer SELECT retrieves the region and the three pivoted columns. We can specify their order independently of how the categories appeared in the original table, which helps when preparing reports that need a consistent column arrangement. Finally, ORDER BY Region sorts the results alphabetically, placing North before South and West.

Notice that we haven’t included a GROUP BY clause. The PIVOT operator performs the aggregation according to the source columns and the selected categories. We identify the values through SUM(), and SQL Server calculates the totals for each combination of region and sales channel.

The result gives us the regional totals without changing the transactions stored in #Sales. We can still query those individual records whenever we need their original details, while the pivot query provides a summarized view for reporting.

This structure can also support other calculations. Replacing SUM(Amount) with AVG(Amount) would calculate the average transaction amount for each region and sales channel rather than their combined totals. The category columns would remain the same, but the numbers would represent a different measurement.

Handling Missing Values

Some regions don’t have transactions recorded through every sales channel. In our sample data, North has no Wholesale sales, South has no Online sales, and West has no Retail sales. The report still contains columns for all three channels because they’re explicitly included in the PIVOT clause.

For our SUM(Amount) calculation, SQL Server returns NULL when a region has no matching transactions for a listed category. This means North’s Wholesale column contains NULL, while its Online and Retail columns contain their calculated totals.

The distinction is important because NULL represents the absence of a value, while zero is an actual numeric value. In a sales report, we may prefer zero for missing combinations so that every region displays numeric amounts across all the channels.

We can handle this directly in the outer SELECT by applying COALESCE() to each pivoted column:

SELECT
    Region,
    COALESCE([Online], 0) AS Online,
    COALESCE([Retail], 0) AS Retail,
    COALESCE([Wholesale], 0) AS Wholesale
FROM (
    SELECT Region, SalesChannel, Amount
    FROM #Sales
) AS SourceRows
PIVOT (
    SUM(Amount)
    FOR SalesChannel
    IN ([Online], [Retail], [Wholesale])
) AS PivotResult
ORDER BY Region;

We apply COALESCE() after the PIVOT operation has calculated the totals. The function examines its arguments from left to right and returns the first value that isn’t NULL. If a pivoted column contains an amount, that amount is returned. If the result is NULL, the function returns zero instead.

For North, the Wholesale column now displays 0, while Online remains at 1,500 and Retail remains at 750. South displays 0 under Online, and West displays 0 under Retail. None of the existing sales amounts are changed by this adjustment.

The aliases following each COALESCE() expression give the output columns their intended names. Without those aliases, the result would contain expression-based column headings rather than the familiar Online, Retail, and Wholesale names.

We should also distinguish missing categories from transactions that actually contain an amount of zero. Those situations can produce the same displayed value after applying COALESCE(), but they represent different information. If the distinction is important to a report, preserving NULL may be the better choice.

In our case, Amount was declared NOT NULL, so every stored transaction contains a numeric value. The missing results come from region and sales channel combinations that have no matching transactions, rather than from missing amounts within existing records.

This adjustment affects only the query output. The original data remains unchanged, and PIVOT still performs the same aggregation before COALESCE() replaces the missing results. We can therefore decide how those values should be presented according to the requirements of the report without changing how the totals are calculated.

Controlling the Grouping

The columns we provide to PIVOT determine how SQL Server groups the source records, so selecting the appropriate columns is necessary for getting the expected totals. Earlier, we supplied only Region, SalesChannel, and Amount, which allowed the operator to calculate a single row per region. SQL Server identifies its grouping columns from the source data by excluding the column named in the FOR clause and the column being aggregated. Every remaining source column contributes to how the records are grouped, meaning the result can contain more rows if we include information that identifies individual transactions.

We can take a look at this behavior by adding SaleId to the source query while keeping the same aggregation and sales channel categories:

SELECT
    SaleId,
    Region,
    [Online],
    [Retail],
    [Wholesale]
FROM (
    SELECT SaleId, Region, SalesChannel, Amount
    FROM #Sales
) AS SourceRows
PIVOT (
    SUM(Amount)
    FOR SalesChannel
    IN ([Online], [Retail], [Wholesale])
) AS PivotResult
ORDER BY Region, SaleId;

The query executes successfully, but the results differ from our regional sales report. Because we included SaleId in the source, SQL Server groups records by both SaleId and Region. Every sale ID in our sample identifies an individual transaction, so the result contains eight rows instead of three.

We can examine North to see why this happens. Its Online transactions have sale IDs of 1 and 2, with amounts of 1,200 and 300. Rather than adding those amounts into a regional total of 1,500, the query returns each sale on its own row because the sale IDs differ.

The same behavior applies to the remaining transactions. Each result row contains the corresponding sale amount beneath its sales channel, while the remaining channels contain NULL because that particular sale belongs to only a single channel.

To restore the intended regional totals, we need to remove SaleId from the inner query and the outer SELECT. That returns us to the original three source columns, allowing SQL Server to group transactions solely by region.

This behavior also explains why retrieving every source column with SELECT * can cause unexpected results. If the table contains transaction identifiers, dates, customer information, or other fields, those columns can become part of the grouping calculation and produce more detailed output than the report requires.

There are situations where grouping by multiple columns is appropriate. If we wanted to compare sales by region for each calendar year, we could provide both Region and a year column in the source. SQL Server would then calculate totals for every distinct region and year combination, allowing the report to retain that extra level of detail.

The important decision is determining what each output row should represent before selecting the source columns. For regional totals, we need only the region as the grouping column. For more detailed reports, we can include other grouping columns that match the information we’re trying to retrieve.

We can also filter the source records before aggregation when a report needs data from a particular period or category. By applying a WHERE clause inside the derived table, we limit the transactions passed to PIVOT, so the resulting totals reflect only the records that meet those conditions.

Keeping the source focused on the required records and columns helps us control the level of detail in the report. The aggregation then follows the grouping established by that source, giving us totals that correspond to the categories and reporting periods we’ve selected.

Generating Dynamic Columns

Fixed pivot queries require us to specify the category values that will appear as output columns. Our earlier examples included Online, Retail, and Wholesale because those were the sales channels available in the sample data. In some reporting situations, the available categories can change as new records are added. Sales channels may be introduced or discontinued, and reports may need to reflect the values currently stored in the database without manually updating the column list.

SQL Server doesn’t automatically discover new output columns through a regular PIVOT statement. Instead, we can construct a dynamic SQL statement that retrieves the available category values, converts them into column identifiers, and includes those identifiers in the query.

We’ll do this by retrieving the distinct sales channels from #Sales and generating the column list before executing the completed pivot query:

DECLARE @Columns NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

SELECT @Columns =
    STRING_AGG(
        CONVERT(NVARCHAR(MAX), QUOTENAME(SalesChannel)),
        N', '
    ) WITHIN GROUP (ORDER BY SalesChannel)
FROM (
    SELECT DISTINCT SalesChannel
    FROM #Sales
) AS Channels;

SET @Sql = N'
SELECT Region, ' + @Columns + N'
FROM (
    SELECT Region, SalesChannel, Amount
    FROM #Sales
) AS SourceRows
PIVOT (
    SUM(Amount)
    FOR SalesChannel IN (' + @Columns + N')
) AS PivotResult
ORDER BY Region;';

EXEC sys.sp_executesql @Sql;

We begin by declaring two variables, @Columns and @Sql, both with the NVARCHAR(MAX) data type. The first will store the generated column names, while the second will contain the complete SQL statement that we want to execute.

Next, we retrieve the distinct values from SalesChannel. The SELECT DISTINCT statement prevents repeated categories from appearing in the generated list, which is necessary because the original table contains multiple transactions through the same sales channels.

We then pass each category value through QUOTENAME(). This function returns the value enclosed in delimiters, which are square brackets by default, so the resulting text can be referenced as a SQL Server identifier. It also handles closing brackets within identifier names. QUOTENAME() accepts identifiers up to 128 characters, which is sufficient for the VARCHAR(20) sales channels in our table.

The CONVERT(NVARCHAR(MAX), QUOTENAME(SalesChannel)) expression gives STRING_AGG() a large string type for constructing the column list. This prevents the aggregate result from being restricted to the smaller maximum length associated with a regular NVARCHAR expression.

After converting the category names, STRING_AGG() combines them into a comma-delimited string. The WITHIN GROUP (ORDER BY SalesChannel) clause determines their order, allowing us to produce a consistent column sequence from the available categories.

With our original sales data, the generated value stored in @Columns is [Online], [Retail], [Wholesale].

We then construct the SQL statement by placing that value into both the outer SELECT and the IN clause of PIVOT. This gives the query the same general structure as our earlier static version, except the category columns now come from the values retrieved from the table.

Finally, we execute the generated statement through sys.sp_executesql. This system stored procedure executes the SQL text stored in @Sql, returning the regional sales totals across the generated columns. The dynamic statement can also access our existing #Sales temporary table because it executes within the same session.

To see how the report responds to a new category, we can add a transaction through a sales channel that didn’t appear in the original dataset:

INSERT INTO #Sales (SaleId, Region, SalesChannel, Amount)
VALUES (9, 'South', 'Direct', 250.00);

We now have a ninth transaction belonging to South, recorded through a sales channel called Direct. The original three categories remain in the table, but the available category values have increased from three to four.

If we execute the dynamic pivot query again, the SELECT DISTINCT portion retrieves Direct alongside Online, Retail, and Wholesale. The generated column list now includes [Direct], and the resulting report contains a corresponding column without requiring us to manually edit the PIVOT statement.

South will have 250 under Direct, while North and West will have NULL in that column because neither region has Direct transactions. The remaining sales totals stay the same as before.

Dynamic SQL allows us to accommodate changing categories, but it also introduces considerations for how the results are consumed. The number of columns and their names can differ between executions, so applications or reporting tools that expect a fixed result structure may need a consistent category list instead.

We also need to handle dynamically generated SQL carefully. Column identifiers cannot be supplied as ordinary parameters to sp_executesql, which is why we construct the category list as SQL text. Applying QUOTENAME() to the retrieved identifiers handles their delimiters and escaped brackets, while externally supplied filter values should be passed through the parameter support provided by sp_executesql rather than inserted directly into the statement text.

Performance remains relevant when the source table contains a large volume of transactions. The PIVOT operator performs aggregation over the rows supplied to it, so applying appropriate filters beforehand can reduce the records involved in the calculation. Repeated PIVOT operations within the same SQL statement can also increase processing costs.

For reports with established categories, the static query provides a predictable result with less SQL text to manage. When categories can change and the output needs to reflect the values currently stored in the database, generating the IN list dynamically allows us to build the report from those values while retaining the same aggregation behavior.

Conclusion

SQL Server’s PIVOT operator turns category values into report columns by grouping records and calculating aggregate results. We control how the data is arranged through the source columns, the aggregate function, and the FOR and IN clauses. Missing values can be handled with COALESCE(), while dynamic SQL allows the column list to adapt as categories change. With these components, we can produce reports that organize totals across categories without modifying the original data.

1. FROM Clause with PIVOT and UNPIVOT

2. STRING_AGG (Transact-SQL)

3. QUOTENAME (Transact-SQL)

4. COALESCE (Transact-SQL)

Share Alexander Obregon's Substack

File uploads begin with MultipartFile, though handling them well goes past accepting bytes from an HTTP request. We need to set size limits before oversized requests reach controller code, account for several files in the same request, check incoming metadata, treat client-provided names carefully, and keep permanent storage outside the controller. Spring Boot already configures multipart support for Spring MVC and exposes the main limits through application properties, while Spring Framework provides MultipartFile for reading uploaded content. From there, we can build a well-organized flow where the controller receives the request, validation checks the file, a storage service saves it, and error handling deals with failures in a consistent way.

Subscribe now

Receiving Files in Spring Boot

Spring MVC can bind parts from a multipart/form-data request directly to MultipartFile, giving the controller access to uploaded content without manually parsing multipart boundaries. Spring Boot configures servlet multipart handling for a typical Spring MVC application, so receiving a file normally begins with a controller parameter rather than custom multipart infrastructure. At this stage, we can focus on how files enter the application, how large those requests are allowed to be, and how several files can arrive through the same endpoint.

Receive a MultipartFile

To receive a single uploaded file, we can declare MultipartFile as a controller parameter and connect it to a named multipart field through @RequestParam. The field name sent by the client needs to match the annotation value so Spring MVC can associate that part with the correct parameter:

package com.example.uploads;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
public class FileUploadController {

    @PostMapping("/files")
    public ResponseEntity<String> upload(
            @RequestParam("file") MultipartFile file) {

        if (file.isEmpty()) {
            return ResponseEntity.badRequest()
                    .body("File cannot be empty");
        }

        return ResponseEntity.ok(
                "Received " + file.getSize() + " bytes"
        );
    }
}

The multipart field named file becomes the MultipartFile passed into upload. Calling isEmpty() tells us that the submitted part contains no file data, while getSize() reports its size in bytes. MultipartFile also gives us methods such as getContentType(), getOriginalFilename(), getInputStream(), getBytes(), and transferTo(Path), so later stages can inspect or copy the received content in the form that makes sense for the storage flow.

Spring treats uploaded content as request data rather than permanent storage. Depending on multipart configuration and the servlet container, content can remain in memory or move into temporary disk storage while the request is being handled. Anything the application needs after request handling finishes has to be copied to its intended destination later, which means MultipartFile represents the incoming upload rather than the final stored file.

The value inside @RequestParam also tells us what the client must send. With @RequestParam("file"), the multipart request needs a field named file. Spring MVC resolves that part before the controller body runs, allowing the method to receive a MultipartFile instead of dealing with raw multipart boundaries from the HTTP request.

Different MultipartFile methods serve different needs. getBytes() returns the entire upload as a byte array, while getInputStream() gives us a stream for reading the content. Basic information such as emptiness and byte count does not require reading the full file into memory first, so isEmpty() and getSize() are better choices when we only need information about the incoming part.

Configure Upload Limits

Size boundaries belong in multipart configuration so Spring Boot and the servlet container can reject requests that exceed the allowed limits before regular controller handling continues. Spring Boot provides spring.servlet.multipart.max-file-size for an individual file and spring.servlet.multipart.max-request-size for the complete multipart request.

For a project that accepts files up to 10 MB while allowing up to 25 MB across the entire request, application.properties can contain:

spring.servlet.multipart.max-file-size=10MB
spring.servlet.multipart.max-request-size=25MB

The first property limits every uploaded file independently. The second applies to the complete multipart request, including all file parts and the rest of the multipart content. This means a request can remain below the individual file limit and still exceed the total request limit when several files arrive at the same time.

Spring Boot defaults to 1MB for max-file-size and 10MB for max-request-size. Smaller uploads can stay below those values during early development, while larger files expose the limits quickly. Keeping the intended values in configuration makes the accepted request sizes part of the application settings rather than leaving the only size check inside controller code.

Multipart configuration also includes spring.servlet.multipart.file-size-threshold, which defaults to 0B and controls the threshold after which file content is written to disk during multipart handling. spring.servlet.multipart.location can provide an intermediate directory when the project needs a particular temporary location. These settings deal with request handling rather than the permanent destination where an accepted upload will eventually be stored.

Spring Boot keeps multipart support active by default through spring.servlet.multipart.enabled. For a regular Spring MVC application, we do not need to register an extra multipart library merely to receive MultipartFile parameters because Spring Boot configures multipart support around the servlet facilities already available to the application.

The individual file limit and total request limit should match what the endpoint is expected to receive. An endpoint accepting a single profile image can keep the total request limit relatively close to its per-file limit, while a batch endpoint needs enough request capacity for several accepted files along with multipart overhead.

Accept Multiple Files

Several uploads can arrive through the same multipart field name, and Spring MVC can bind them to List<MultipartFile>. This gives us a single controller parameter for a batch of related files rather than requiring a different method parameter for every file that could arrive.

We can receive a batch through a multipart field named files like this:

package com.example.uploads;

import java.util.List;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
public class BatchUploadController {

    @PostMapping("/files/batch")
    public ResponseEntity<String> uploadFiles(
            @RequestParam("files") List<MultipartFile> files) {

        long nonEmptyFiles = files.stream()
                .filter(file -> !file.isEmpty())
                .count();

        return ResponseEntity.ok(
                "Received " + nonEmptyFiles + " non-empty files"
        );
    }
}

Spring MVC collects multipart parts named files and places them into the list passed to uploadFiles. We then receive every submitted MultipartFile in the collection, while the isEmpty() check prevents an empty part from being counted as actual file content.

Clients send several files by repeating the same multipart field name. From a terminal, the request can be sent like this:

curl -X POST http://localhost:8080/files/batch \
  -F "files=@report.pdf" \
  -F "files=@photo.png"

Both -F entries carry the field name files, which matches @RequestParam("files") in the controller. Spring MVC collects those parts before uploadFiles runs, so our controller receives the collection without parsing repeated multipart fields itself.

The total multipart request limit still applies across the entire batch. With a 10 MB max-file-size and a 25 MB max-request-size, three files close to 10 MB each can stay below the individual file boundary while exceeding the total request boundary. Batch endpoints therefore need both limits selected around the amount of data they are expected to receive.

Spring MVC can also bind uploaded files into Map<String, MultipartFile> or MultiValueMap<String, MultipartFile> when files arrive under different multipart field names. List<MultipartFile> remains a natural choice when several uploads belong to the same field, while the map forms are better suited to requests where different field names represent different file inputs.

Building the Storage Flow

Receiving multipart data only gets the file into the application. Before anything is kept permanently, the upload should pass application rules, receive a server-generated storage name, and move through a dedicated service that owns filesystem operations. Keeping those responsibilities away from the controller gives the HTTP layer a narrower job, while validation and storage behavior can change without turning every upload endpoint into a large method.

Check the Uploaded Data

Before a file reaches permanent storage, we can reject content that does not meet the rules for the endpoint. Spring Boot multipart limits already place an outer boundary around incoming requests, while application validation can apply more specific rules. An image endpoint, for instance, can accept a smaller size than the global multipart limit and restrict the declared media types to JPEG and PNG.

We can place those rules inside a dedicated component:

package com.example.uploads;

import java.util.Set;

import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

@Component
public class ImageUploadValidator {

    private static final long MAX_SIZE = 5 * 1024 * 1024;

    private static final Set<String> ALLOWED_TYPES =
            Set.of("image/jpeg", "image/png");

    public void validate(MultipartFile file) {
        if (file.isEmpty()) {
            throw new InvalidUploadException("File cannot be empty");
        }

        if (file.getSize() > MAX_SIZE) {
            throw new InvalidUploadException("File is too large");
        }

        String contentType = file.getContentType();

        if (contentType == null || !ALLOWED_TYPES.contains(contentType)) {
            throw new InvalidUploadException("File type is not accepted");
        }
    }
}

The first check rejects a multipart part with no file data, while the second places a 5 MB application rule around this particular upload. That value can be lower than the global Spring Boot multipart limit because the two limits apply at different stages. The final condition accepts only the declared media types allowed by this endpoint.

getContentType() returns the media type supplied with the multipart file. Because request metadata comes from the client, matching that value should not be treated as proof that the bytes truly belong to the claimed format. If the application needs file-format verification, the uploaded content can be read by a parser or detection library that recognizes the expected format before permanent storage.

The custom validation exception only needs to carry the reason the upload was rejected:

package com.example.uploads;

public class InvalidUploadException extends RuntimeException {

    public InvalidUploadException(String message) {
        super(message);
    }
}

Throwing a dedicated exception lets the validation component stop the upload without building an HTTP response itself. Status-code decisions can stay in centralized exception handling, while the validator remains focused on deciding if the incoming file meets the endpoint rules.

Validation can grow with the needs of the endpoint. Image dimensions, document structure, permitted extensions, or other file-specific rules can be checked before storage when those properties belong to the upload contract. The main boundary stays consistent, with validation deciding if the upload is acceptable and the storage component taking responsibility for accepted data.

Treat File Names Safely

Client-provided filenames should be treated as metadata rather than trusted filesystem destinations. MultipartFile.getOriginalFilename() returns the name supplied by the client, and that value can contain directory information or sequences like ... Generating the stored name on the server keeps request data from deciding where a file is written.

A generated identifier can become the physical filename:

package com.example.uploads;

import java.util.UUID;

public final class StoredFileName {

    private StoredFileName() {
    }

    public static String create() {
        return UUID.randomUUID().toString();
    }
}

UUID.randomUUID() produces the value stored on disk, so an incoming filename such as vacation.png never becomes the filesystem destination. The original filename can still be retained as metadata if the application needs to display it later or associate it with a database record.

Leaving the extension off the generated storage name also avoids trusting an extension supplied by the client. If an extension is needed later, it can be derived after the file format has passed the rules established for that upload type. The original name and declared media type can remain metadata without controlling the physical filename.

Server-generated names also prevent duplicate client filenames from replacing each other accidentally. Two uploads can both arrive as photo.png, while each stored file receives its own generated identifier. The name shown to a user and the name stored on disk therefore do not need to be the same value.

Move Storage Into a Service

Controllers stay easier to read when filesystem operations live in a dedicated service. The controller can receive the request, pass the file through validation, send accepted content to storage, and return an HTTP response. Directory creation, generated names, file transfer, and storage failures can remain inside the service.

A filesystem-backed service can keep its destination directory in application configuration:

package com.example.uploads;

import java.io.File;
import java.io.IOException;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

@Service
public class FileStorageService {

    private final File uploadDirectory;

    public FileStorageService(
            @Value("${app.upload.directory}") String uploadDirectory) {

        this.uploadDirectory =
                new File(uploadDirectory).getAbsoluteFile();
    }

    public String store(MultipartFile file) {
        try {
            prepareDirectory();

            String storedName = StoredFileName.create();
            File destination =
                    new File(uploadDirectory, storedName);

            file.transferTo(destination);

            return storedName;
        }
        catch (IOException ex) {
            throw new FileStorageException(
                    "Could not store uploaded file", ex
            );
        }
    }

    private void prepareDirectory() throws IOException {
        if (uploadDirectory.exists()) {
            if (!uploadDirectory.isDirectory()) {
                throw new IOException(
                        "Upload location is not a directory"
                );
            }

            return;
        }

        if (!uploadDirectory.mkdirs()) {
            throw new IOException(
                    "Could not create upload directory"
            );
        }
    }
}

The constructor turns the configured location into an absolute File, while prepareDirectory() checks that the location is a directory or creates it when it does not exist. After that, the service generates a server-controlled filename and passes the destination to MultipartFile.transferTo(File), which transfers the received upload to that destination.

Because StoredFileName.create() returns a UUID value rather than client input, the destination filename does not carry directory traversal data from getOriginalFilename(). The service returns the generated name after the transfer finishes, giving the controller an application-level reference to the stored file.

The base directory can stay outside Java source code through application.properties:

app.upload.directory=uploads

Spring injects that value into the service constructor through @Value. Changing the destination then becomes a configuration change rather than a controller edit, and different environments can provide their own directory values through Spring configuration.

With validation and storage delegated, the controller becomes smaller:

package com.example.uploads;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

@RestController
public class ImageUploadController {

    private final ImageUploadValidator validator;
    private final FileStorageService storageService;

    public ImageUploadController(
            ImageUploadValidator validator,
            FileStorageService storageService) {

        this.validator = validator;
        this.storageService = storageService;
    }

    @PostMapping("/images")
    public ResponseEntity<UploadResponse> uploadImage(
            @RequestParam("file") MultipartFile file) {

        validator.validate(file);

        String storedName = storageService.store(file);

        return ResponseEntity.status(HttpStatus.CREATED)
                .body(new UploadResponse(storedName));
    }

    record UploadResponse(String fileName) {
    }
}

The request enters through MultipartFile, then the controller sends it to ImageUploadValidator before calling FileStorageService. Successful storage returns the generated filename, which becomes part of the 201 Created response. Filesystem details never enter the controller method, so the HTTP flow remains easy to follow as storage requirements grow.

This boundary also leaves room for a different storage implementation later. Local disk storage can be replaced by object storage or a different persistence mechanism while the controller continues sending accepted uploads to a storage service with the same responsibility. The HTTP endpoint does not need details about how the bytes reach their final destination.

Return Upload Errors

Failures can happen at several points during an upload. Validation can reject empty or unsupported content, filesystem operations can fail, and multipart parsing can reject a request before the controller receives it if the configured size limit is exceeded. Central exception handling gives those failures consistent HTTP responses without adding repeated try and catch blocks to every upload controller.

Storage failures can carry their original I/O cause through a dedicated runtime exception:

package com.example.uploads;

public class FileStorageException extends RuntimeException {

    public FileStorageException(
            String message, Throwable cause) {

        super(message, cause);
    }
}

Wrapping IOException this way lets FileStorageService report that persistence failed without deciding which HTTP status the client should receive. That decision can stay at the web layer, where validation failures, multipart failures, and storage failures can all be translated into HTTP responses from the same location.

@RestControllerAdvice can handle those exceptions across REST controllers:

package com.example.uploads;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import org.springframework.web.multipart.MaxUploadSizeExceededException;

@RestControllerAdvice
public class UploadExceptionHandler {

    @ExceptionHandler(InvalidUploadException.class)
    public ResponseEntity<String> handleInvalidUpload(
            InvalidUploadException ex) {

        return ResponseEntity.badRequest()
                .body(ex.getMessage());
    }

    @ExceptionHandler(MaxUploadSizeExceededException.class)
    public ResponseEntity<String> handleMaximumSize(
            MaxUploadSizeExceededException ex) {

        return ResponseEntity.status(
                        HttpStatus.PAYLOAD_TOO_LARGE
                )
                .body("Upload exceeds the allowed size");
    }

    @ExceptionHandler(FileStorageException.class)
    public ResponseEntity<String> handleStorageFailure(
            FileStorageException ex) {

        return ResponseEntity.status(
                        HttpStatus.INTERNAL_SERVER_ERROR
                )
                .body("File could not be stored");
    }
}

InvalidUploadException becomes 400 Bad Request, while an upload beyond the multipart maximum receives 413 Payload Too Large. FileStorageException maps to 500 Internal Server Error because the request reached storage but the server could not complete the write.

The response for a storage failure does not expose the underlying I/O exception text. Internal directory names or operating-system details do not belong in the client response, while the original exception can still remain attached to FileStorageException for server-side logging.

Size failures need different treatment from application validation because they can happen during multipart parsing before the controller method receives a MultipartFile. Controller-level getSize() checks therefore do not replace Spring Boot multipart limits. Configuration provides the outer request boundary, validation applies endpoint-specific rules after the request reaches application code, and centralized exception handling turns failures from both stages into HTTP responses.

Conclusion

Spring Boot file uploads become much easier to manage when each responsibility stays in its own place. MultipartFile brings the request into the controller, multipart settings set the upload boundaries, validation checks the incoming data, generated names keep client filenames away from storage decisions, and a storage service handles the final write. Central exception handling then turns validation, size, and storage failures into consistent HTTP responses without filling the controller with repeated error logic.

1. Spring Boot Multipart Configuration

2. Spring Framework MultipartFile Documentation

3. Spring MVC Multipart Forms

4. Spring Framework Multipart Exceptions

Share Alexander Obregon's Substack

SQL data can pick up duplicate rows through repeated imports, retry logic, merged datasets, or several records that refer to the same business item. Removing those records safely requires a rule for deciding which row should remain rather than deleting every matching value. ROW_NUMBER() handles this by dividing records into duplicate groups, ordering the rows within each group, and assigning a sequence number that starts at 1. We can treat the row numbered 1 as the preferred record, while higher numbers identify the remaining duplicates. The ordering can favor the newest date, highest priority, lowest identifier, most recent update, or another column that determines which record should stay.

Subscribe now

Finding Duplicate Rows

Duplicate detection starts with deciding which columns make two records belong to the same group. That choice controls the entire query because ROW_NUMBER() can rank records correctly only after we define which rows should be compared with each other. Some tables may treat a repeated email address as enough to identify a duplicate, while others need several matching columns such as a customer ID, order reference, and date. From there, the window function can number rows inside each duplicate group without removing anything yet, giving us time to inspect the result before changing stored data.

Defining the Duplicate Group

The duplicate rule belongs inside PARTITION BY, where matching values are placed into the same window partition. Rows that share the partition columns are numbered as members of the same group, while different values start a new group.

We can begin with a customer table that contains repeated email addresses:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(255),
    created_at TIMESTAMP
);

INSERT INTO customers (customer_id, email, created_at)
VALUES
    (101, 'alex@example.com', '2026-01-12 09:15:00'),
    (102, 'kaitlyn@example.com', '2026-01-13 11:30:00'),
    (103, 'alex@example.com', '2026-02-04 14:20:00'),
    (104, 'pippin@example.com', '2026-02-08 08:45:00'),
    (105, 'kaitlyn@example.com', '2026-03-01 16:10:00');

We now have two email addresses that appear more than once. alex@example.com appears twice, while kaitlyn@example.com also appears twice. The rows still carry different customer_id and created_at values, so they are not exact copies across every column, but they can still count as duplicates if email is the business value we care about.

We can assign row numbers inside each email group like this:

SELECT
    customer_id,
    email,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY email
        ORDER BY created_at
    ) AS row_num
FROM customers;

PARTITION BY email creates a new numbering group for every distinct email address. Within each group, ORDER BY created_at arranges the records from earliest to latest before ROW_NUMBER() assigns 1, 2, and later values. Email addresses that appear only once still receive 1 because their partition contains a single record.

The duplicate definition can also depend on more than one column. Order data is a good case because the same reference value may be valid for different customers, while the same customer and reference combination may represent a repeated record.

We can express that rule by partitioning on both values:

SELECT
    order_id,
    customer_id,
    order_reference,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id, order_reference
        ORDER BY created_at
    ) AS row_num
FROM orders;

Now two records belong to the same group only when both customer_id and order_reference match. Matching just one of those columns is no longer enough, which gives the query a more specific duplicate rule.

That distinction matters because PARTITION BY does not decide what duplicate means by itself. We make that decision through the columns placed inside the partition. Too few columns can group unrelated records, while too many can split records that should have been treated as duplicates because some less important value differs.

Reading the Row Numbers

After the groups are defined, ROW_NUMBER() gives every row a sequence number inside its partition. Numbering starts at 1 and increases according to the window ORDER BY, which gives us a direct way to tell the first record apart from later records in the same group.

The first row in every partition receives row_num = 1, while later rows receive 2, 3, and so on. Unique records receive 1 as well because there is no second row in their partition. That gives us a practical dividing point because values greater than 1 can be treated as duplicate candidates. Rather than filtering directly on the window expression at the same query level, we can calculate the row number first and then filter it from an outer query.

A common table expression can work well here:

WITH ranked_customers AS (
    SELECT
        customer_id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at
        ) AS row_num
    FROM customers
)
SELECT
    customer_id,
    email,
    created_at,
    row_num
FROM ranked_customers
WHERE row_num > 1;

The common table expression performs the ranking first, which gives the outer query a normal row_num column to filter. The final WHERE row_num > 1 condition removes position 1 from the displayed result and leaves only later records from repeated email groups.

We can read the output as a duplicate report rather than a deletion instruction. A row numbered 2 tells us that one record from the same partition came before it, while a row numbered 3 tells us that two records from that group ranked ahead of it. This also helps when a duplicate group contains several rows. If an email appears four times, the sequence becomes 1, 2, 3, and 4, which lets us see the size of the repeated group while still keeping every record available for inspection.

If we only wanted to know which values repeat, GROUP BY with COUNT(*) could answer that question. ROW_NUMBER() goes further because every original row stays available and receives its own position, which becomes important when later logic needs to identify specific records rather than only count them.

Controlling Which Row Comes First

The duplicate group comes from PARTITION BY, while the record that receives 1 comes from the window ORDER BY. Because of that split, the same group of duplicate rows can produce different first records depending on how we sort them.

If the earliest customer record should come first, ascending date order gives us that result:

ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY created_at ASC
)

Within each email group, the smallest created_at value receives 1, and later dates receive higher numbers. The duplicate grouping has not changed because PARTITION BY email remains the same.

If the newest record should come first, we can reverse the date order:

ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY created_at DESC
)

The latest timestamp now receives 1, followed by older records in descending date order. We are still ranking the same duplicate group, but the preferred row changes because the ordering rule changed.

Ties need more detail in the ordering rule. Two duplicate records can share the same created_at timestamp, and if that is the only ordering column, the query has no extra value that decides which tied row receives the lower row number.

We can add a unique identifier as the final tiebreaker:

ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY created_at DESC, customer_id DESC
)

The newest date still takes precedence. If two rows share that date, the larger customer_id comes first because the second expression also uses descending order. Several columns can be chained this way when the ranking rule needs more detail. SQL evaluates the ordering expressions from left to right, so the first column has the highest priority, the next resolves ties from that result, and later columns continue resolving any remaining ties.

Priority values can be placed before dates as well if the data calls for that. The underlying idea stays the same. PARTITION BY decides which records compete within the group, while ORDER BY decides which one receives the earliest row number.

Changing the ordering does not change which rows count as duplicates. It changes only their sequence inside the duplicate group, which is why this part of the query becomes so important before we later decide which records should remain.

Inspecting Duplicate Candidates

Before changing any rows, it helps to display the full ranking so we can verify the grouping and ordering rules against the records themselves. That gives us a chance to check which row received 1, which rows received higher numbers, and how tied values are being resolved.

We can sort the ranked result by email and row number so duplicate groups appear next to each other:

WITH ranked_customers AS (
    SELECT
        customer_id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC, customer_id DESC
        ) AS row_num
    FROM customers
)
SELECT
    customer_id,
    email,
    created_at,
    row_num
FROM ranked_customers
ORDER BY email, row_num;

The result places each email group next to itself, with the preferred row first and later records directly below it. Unique email addresses appear only with row_num = 1, while repeated addresses continue to higher numbers.

Reading the complete ranking first gives us a better view of the duplicate rule. If an unexpected row receives 1, we can revisit the window ordering before doing anything with the higher-numbered records. If unrelated rows appear in the same group, the partition columns need to be changed.

After the full ranking looks right, we can narrow the result to duplicate candidates:

WITH ranked_customers AS (
    SELECT
        customer_id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC, customer_id DESC
        ) AS row_num
    FROM customers
)
SELECT
    customer_id,
    email,
    created_at
FROM ranked_customers
WHERE row_num > 1
ORDER BY email, row_num;

The ranking still runs across every row, while the outer query displays only records numbered above 1. Those are the rows that came after the preferred record within their duplicate group.

The same inspection method can handle more detailed duplicate rules without changing the basic structure. If records should match on customer_id, product_id, and order_date, those columns can all go inside PARTITION BY, while the ordering columns continue deciding which matching record receives the first position.

Keeping every duplicate candidate readable at this stage gives us room to review the grouping and row numbers before later logic acts on them. That keeps the ranking tied directly to the rules expressed inside the window function and makes it easier to verify which records belong to each duplicate group.

Keeping the Preferred Record

After duplicate groups have been identified, the next step is deciding which record should remain from each group. That decision usually comes from values already stored in the table, such as a timestamp, priority level, status, source ranking, or identifier. ROW_NUMBER() gives us a practical way to express that preference because the row placed first inside each partition receives 1, while later rows receive higher values. From there, records above 1 can be reviewed or removed without changing the preferred row.

Keeping Records by Date

Date-based rules are common when repeated imports, updates, or retries create several records for the same entity. If the newest version should remain, descending date order places the latest timestamp first inside every duplicate group, giving that row position 1.

We can start with a contact import table where repeated email addresses have different phone numbers and import times:

CREATE TABLE contact_imports (
    import_id INT PRIMARY KEY,
    email VARCHAR(255),
    phone VARCHAR(30),
    imported_at TIMESTAMP
);

INSERT INTO contact_imports (import_id, email, phone, imported_at)
VALUES
    (201, 'alex@example.com', '715-555-0101', '2026-04-02 08:00:00'),
    (202, 'alex@example.com', '715-555-0188', '2026-04-06 10:30:00'),
    (203, 'kaitlyn@example.com', '715-555-0124', '2026-04-03 12:15:00'),
    (204, 'kaitlyn@example.com', '715-555-0197', '2026-04-09 09:40:00');

Each repeated email address has an older record and a newer record. Because the phone number can change between imports, keeping an arbitrary row could leave older contact data in place, so the timestamp needs to control the ranking.

We can rank the newest import first:

WITH ranked_imports AS (
    SELECT
        import_id,
        email,
        phone,
        imported_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY imported_at DESC, import_id DESC
        ) AS row_num
    FROM contact_imports
)
SELECT
    import_id,
    email,
    phone,
    imported_at,
    row_num
FROM ranked_imports
ORDER BY email, row_num;

PARTITION BY email keeps matching email addresses in the same group, while ORDER BY imported_at DESC places the newest import first. The import_id DESC expression provides a final tiebreaker when two rows share the same timestamp, giving the ranking a defined order in that case.

For alex@example.com, the April 6 record receives row_num = 1, while the April 2 record receives 2. The same rule applies to the Kaitlyn records, where the April 9 row comes before the April 3 row. We can read those numbers directly as the retention order, with position 1 representing the record we plan to keep.

After checking the ranking, rows above 1 can be targeted for removal. PostgreSQL can delete the identifiers returned from the ranked result like this:

WITH ranked_imports AS (
    SELECT
        import_id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY imported_at DESC, import_id DESC
        ) AS row_num
    FROM contact_imports
)
DELETE FROM contact_imports
WHERE import_id IN (
    SELECT import_id
    FROM ranked_imports
    WHERE row_num > 1
);

The common table expression assigns the ranking first, while the inner SELECT returns only identifiers whose row number is greater than 1. The outer DELETE removes those specific records, leaving the preferred row from every email group in place.

Date rules can also keep the earliest record rather than the newest. Changing DESC to ASC reverses which timestamp receives position 1, while PARTITION BY email continues grouping the same records. That small change can support data where the original record should remain instead of the latest update.

Keeping Records by Priority

Time does not always decide which duplicate should remain. Some tables contain a source rank, status value, score, approval level, or similar column that takes precedence when several rows represent the same item.

Product feeds are a good case because the same product code can arrive from different sources, and some sources may carry a higher preference than others. If a larger source_priority means the record should come first, we can place that column at the beginning of the window ordering:

SELECT
    feed_id,
    product_code,
    source_priority,
    updated_at,
    ROW_NUMBER() OVER (
        PARTITION BY product_code
        ORDER BY source_priority DESC,
                 updated_at DESC,
                 feed_id DESC
    ) AS row_num
FROM product_feed;

The ranking starts by grouping matching product_code values. Inside each product group, the highest source_priority receives the earliest position. If two records share the same priority, updated_at DESC favors the newer record, while feed_id DESC resolves any remaining tie.

Column order inside ORDER BY directly controls the preference hierarchy. If updated_at DESC appeared before source_priority DESC, a newer record from a lower-priority source could receive 1 ahead of an older record from the preferred source. Placing priority first tells the query that source ranking comes before recency. Several preference columns can follow each other when the retention rule needs more detail. Records could rank by approval state first, then source priority, then update time, with a unique identifier at the end to resolve any remaining tie. SQL evaluates those expressions from left to right, so every later column handles ties left by the earlier column.

Null values also deserve attention when a preference column can be empty. PostgreSQL supports NULLS FIRST and NULLS LAST, which lets us decide where null values belong instead of relying on the default sort behavior.

If rows with an actual update timestamp should rank ahead of rows where updated_at is null, we can write:

ROW_NUMBER() OVER (
    PARTITION BY product_code
    ORDER BY updated_at DESC NULLS LAST,
             feed_id DESC
)

Rows with timestamps now come before rows without them, while feed_id resolves ties among equal timestamps or among records where the timestamp is null. That keeps the row-number sequence aligned with the retention rule rather than allowing null placement to decide the result indirectly.

Priority-based ranking follows the same mechanics as date-based ranking. The main difference comes from the column placed first inside the window ORDER BY, because that first expression has the greatest influence over which record receives position 1.

Preventing Duplicates From Returning

Removing existing duplicates fixes the current records, but repeated cleanup queries should not become the normal way to control values that are never supposed to repeat. If the data rules say a value must be unique, a database constraint can enforce that requirement when later inserts or updates occur.

After the existing duplicate emails have been removed from customers, a unique constraint can prevent the same email from being stored more than once:

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

After the constraint is added, an insert or update that would create a duplicate email is rejected by the database. That moves the duplicate rule closer to the point where data enters the table rather than waiting until repeated records need to be removed later.

Some uniqueness rules depend on more than one column. An order reference may repeat across different customers while still needing to remain unique for each individual customer.

We can express that rule with a multi-column unique constraint:

ALTER TABLE orders
ADD CONSTRAINT uq_customer_order_reference
UNIQUE (customer_id, order_reference);

The combination of customer_id and order_reference must now remain unique. The same order_reference can still appear for a different customer because the full pair differs.

That matches the duplicate grouping we could define with PARTITION BY customer_id, order_reference. The window query identifies repeated combinations that already exist, while the constraint blocks later rows that would violate the same rule.

Constraints still need to reflect what the table represents. Event logs, audit tables, history tables, and versioned records can legitimately contain repeated business values because every row may represent a different event or state. In those tables, repeated values can be expected data rather than records that should automatically be rejected.

Incoming data can also be ranked before it reaches the destination table. A staging table can collect repeated records, then ROW_NUMBER() can rank them before only the preferred rows move forward.

We could keep the newest staging record for every email like this:

WITH ranked_stage AS (
    SELECT
        customer_id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC, customer_id DESC
        ) AS row_num
    FROM customer_stage
)
SELECT
    customer_id,
    email,
    created_at
FROM ranked_stage
WHERE row_num = 1;

The staging records are grouped by email, ranked from newest to oldest, then filtered so only position 1 remains in the result. That result can feed a later insert, keeping duplicate handling earlier in the data flow rather than waiting until repeated rows have accumulated in the destination table.

Over these cases, ROW_NUMBER() follows the same mechanics. PARTITION BY defines which records belong to the same duplicate group, while the window ORDER BY decides which record takes position 1. The columns chosen for that ordering carry the retention rule, giving us direct control over which row remains before higher-numbered records are removed.

Conclusion

SQL deduplication with ROW_NUMBER() comes down to grouping matching records with PARTITION BY, ordering each group so the preferred row receives 1, then treating higher row numbers as duplicate candidates. Dates, priorities, and tiebreaker columns let us control which record stays, while unique constraints can stop unwanted duplicates from being added later.

1. PostgreSQL Window Functions Documentation

2. SQL Server ROW_NUMBER Documentation

3. Oracle ROW_NUMBER Documentation

4. PostgreSQL Unique Constraints Documentation

Share Alexander Obregon's Substack

REST APIs are much more practical to inspect when their endpoints, request bodies, responses, and authentication requirements are collected in generated documentation. springdoc-openapi reads Spring MVC controllers and builds an OpenAPI 3 description from the mappings and Java types already present in the application, while Swagger UI presents that information in a browser where we can review endpoints and send requests. Much of the documentation comes directly from the existing controller structure, and OpenAPI annotations let us add details that Spring cannot infer from the code alone. We’ll begin with the generated document, see what Springdoc discovers from the controllers, then add operation descriptions, response definitions, and request schemas.

Subscribe now

Steps to Generate OpenAPI Documentation

Before adding custom descriptions or response details, we can let Springdoc read what the application already exposes. Spring MVC controller mappings, HTTP methods, parameters, request bodies, return types, and validation metadata give Springdoc enough information to build an OpenAPI 3 document with very little documentation-specific code. Swagger UI then reads that document and presents the endpoints in a browser, which gives us a practical place to inspect what Springdoc found before adding richer metadata later.

Add Springdoc OpenAPI

The first step is adding the Springdoc starter that matches the Spring Boot version and web stack in the project. For a Spring Boot 4 application built with Spring MVC that needs both the generated OpenAPI document and Swagger UI, Maven can include springdoc-openapi-starter-webmvc-ui:

<dependency>
    <groupId>org.springdoc</groupId>
    <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
    <version>3.1.1</version>
</dependency>

This starter brings Spring MVC integration and Swagger UI into the application. After the dependency is present, Springdoc can inspect registered REST controllers and build documentation from the mappings and Java types it finds there. We do not need to create an OpenAPI YAML file by hand before documentation appears, and controller methods do not need OpenAPI annotations before Springdoc can discover them.

The webmvc-ui portion of the artifact name identifies the Spring MVC starter that also includes Swagger UI support. Projects built on Spring WebFlux use the corresponding springdoc-openapi-starter-webflux-ui artifact instead, so the dependency should match the web framework already present in the project.

Gradle projects can add the same Springdoc module through build.gradle:

implementation 'org.springdoc:springdoc-openapi-starter-webmvc-ui:3.1.1'

The Gradle declaration brings in the same Springdoc module as the Maven dependency. Only the build tool changes, while Springdoc still reads Spring MVC controllers and publishes the same OpenAPI endpoints after startup.

No extra controller registration is required for the basic generated documentation. Springdoc discovers Spring MVC request mappings from the application context and translates that information into OpenAPI operations, so the generated document stays connected to the controller definitions rather than depending on a second manually maintained route list.

Read Existing Controllers

Controller metadata gives Springdoc much of the structure it needs before we add documentation annotations. We can start with a small REST controller that retrieves books and creates new ones:

package com.example.books;

import jakarta.validation.Valid;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/books")
public class BookController {

    private final BookService bookService;

    public BookController(BookService bookService) {
        this.bookService = bookService;
    }

    @GetMapping("/{id}")
    public ResponseEntity<BookResponse> getBook(@PathVariable Long id) {
        return bookService.findById(id)
                .map(ResponseEntity::ok)
                .orElseGet(() -> ResponseEntity.notFound().build());
    }

    @PostMapping
    public ResponseEntity<BookResponse> createBook(
            @Valid @RequestBody BookRequest request) {

        BookResponse book = bookService.create(request);
        return ResponseEntity.status(HttpStatus.CREATED).body(book);
    }
}

Springdoc reads @RequestMapping("/api/books") as the shared base path for the controller. The @GetMapping("/{id}") method becomes a GET operation for /api/books/{id}, while @PostMapping becomes a POST operation for /api/books. From the method parameters, Springdoc can identify that id belongs in the request path and that BookRequest represents the request body sent to the creation endpoint.

Return types add more information to the generated document. ResponseEntity<BookResponse> tells Springdoc that the endpoint can return data represented by BookResponse, which allows that Java type to appear as an OpenAPI schema. The request type follows the same idea, so fields declared inside BookRequest can become part of the request body schema without creating a second model only for documentation.

Validation metadata can contribute to those generated schemas as well. If the request record requires values through Bean Validation, Springdoc can reflect supported constraints while reading the DTO:

package com.example.books;

import jakarta.validation.constraints.NotBlank;

public record BookRequest(
        @NotBlank String title,
        @NotBlank String author
) {
}

Both fields carry @NotBlank, while @Valid on the controller request body tells Spring to run Bean Validation when the request arrives. Springdoc can read supported validation metadata from the same request model, so the OpenAPI schema can carry information derived from the Java contract rather than from a duplicate documentation model.

The generated description follows controller changes because Springdoc reads the mappings Spring registers in the application context. If a path variable name changes, a route moves, or a new controller method is added, the OpenAPI document reflects the controller metadata available when the description is generated. This keeps the generated route information tied directly to the HTTP endpoints defined by Spring MVC.

Springdoc can infer structure from Java and Spring annotations, but some details still need to be supplied later. Method signatures, Java types, and mappings tell it what the API exposes, while richer descriptions, response definitions, request examples, and security metadata can be added where the generated information needs more context.

Open the Generated Docs

After the application starts, Springdoc publishes the OpenAPI document at /v3/api-docs by default. Opening that endpoint returns JSON that follows the OpenAPI 3 format and contains the operations, paths, schemas, parameters, and other metadata collected from the application.

The YAML form is available at /v3/api-docs.yaml. Both endpoints represent the same API description in different formats, so we can choose the format that matches the tool or workflow reading the document. Browser inspection, API tooling, generated clients, and other OpenAPI-aware software can consume the document directly.

Swagger UI provides the browser interface at /swagger-ui.html. The page reads the OpenAPI document generated by Springdoc and presents its contents as interactive API documentation. We can expand an endpoint, inspect its parameters and schemas, enter request data, and send requests from the browser when the endpoint allows it.

The JSON endpoint also helps when we want to inspect exactly what Springdoc discovered from the controllers. If a request model does not appear as expected or a route contains unexpected metadata, /v3/api-docs exposes the generated OpenAPI structure directly, while Swagger UI presents the same information through its browser interface.

Projects can move the default documentation locations through Springdoc properties. If we want shorter routes for both the document and Swagger UI, application.properties can contain:

springdoc.api-docs.path=/api-docs
springdoc.swagger-ui.path=/docs

With those values in place, the OpenAPI JSON document moves from /v3/api-docs to /api-docs, while Swagger UI moves from /swagger-ui.html to /docs. The REST controller paths remain unchanged because these properties affect Springdoc endpoints rather than application routes such as /api/books.

Changing those paths can help when an application follows its own URL conventions or places documentation behind routing rules outside the controller layer. The generated content still comes from the same Spring MVC metadata, so moving the documentation endpoint does not change what Springdoc discovers from the controllers.

At this stage, we have the main documentation flow in place. Springdoc reads the registered REST controllers, produces an OpenAPI 3 document from their mappings and Java types, and Swagger UI presents that document in the browser. That gives us the base needed before adding richer endpoint descriptions, response metadata, and schema details.

Giving the OpenAPI Docs More Detail

The generated OpenAPI document already knows a lot about the controller from Spring MVC annotations, method parameters, and Java types. From there, we can add the information that cannot be inferred directly from method signatures, such as what an endpoint does, which HTTP responses callers should expect, and what individual request fields represent. Spring MVC still controls request handling, while OpenAPI annotations supply documentation metadata beside the controller and DTO code.

Explain Each Operation

Controller mappings tell Springdoc which route exists and which HTTP method belongs to it. @Operation lets us add a short summary along with a longer explanation so someone reading Swagger UI does not have to infer the endpoint purpose from a Java method name.

We can place that metadata directly above the existing book lookup endpoint:

import io.swagger.v3.oas.annotations.Operation;

@Operation(
        summary = "Get a book by ID",
        description = "Returns the book associated with the supplied database ID."
)
@GetMapping("/{id}")
public ResponseEntity<BookResponse> getBook(@PathVariable Long id) {
    return bookService.findById(id)
            .map(ResponseEntity::ok)
            .orElseGet(() -> ResponseEntity.notFound().build());
}

@GetMapping still controls the request route, while @Operation contributes documentation for the generated OpenAPI operation. Springdoc gets the path, HTTP method, route variable, and return type from the controller method, then includes the summary and longer text beside that information.

Short summaries are usually best when they state the action directly. Get a book by ID tells someone what the endpoint does without repeating the full route, while the longer text can explain details that are not obvious from the method name.

Route variables and query parameters can receive their own metadata through @Parameter. We can give the existing id variable more context without changing how Spring MVC binds it:

import io.swagger.v3.oas.annotations.Parameter;

@GetMapping("/{id}")
public ResponseEntity<BookResponse> getBook(
        @Parameter(
                description = "Database ID of the book",
                example = "15"
        )
        @PathVariable Long id) {

    return bookService.findById(id)
            .map(ResponseEntity::ok)
            .orElseGet(() -> ResponseEntity.notFound().build());
}

@PathVariable still reads the value from the URL, while @Parameter contributes documentation for that value. The sample gives Swagger UI data it can display beside the field, and the accompanying text tells the reader what the number represents.

Springdoc can already tell that id belongs to the path from the Spring MVC mapping, so there is little value in repeating that fact through extra metadata. Parameter notes are better suited to information such as units, accepted formats, identifier meaning, or restrictions that the Java name does not communicate by itself.

The same idea applies to query parameters. Spring MVC defines how the value reaches the controller method, while OpenAPI annotations add context for readers without replacing the mapping that handles the request.

Map Out API Responses

Return types give Springdoc part of the response information, but a controller method can return more than a single HTTP status. The book lookup returns 200 OK when a matching record exists and 404 Not Found when no book matches the supplied ID, so both outcomes belong in the generated documentation.

We can record those responses beside the controller method:

import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.media.Content;
import io.swagger.v3.oas.annotations.media.Schema;
import io.swagger.v3.oas.annotations.responses.ApiResponse;
import io.swagger.v3.oas.annotations.responses.ApiResponses;

@Operation(summary = "Get a book by ID")
@ApiResponses({
        @ApiResponse(
                responseCode = "200",
                description = "Book found",
                content = @Content(
                        mediaType = "application/json",
                        schema = @Schema(implementation = BookResponse.class)
                )
        ),
        @ApiResponse(
                responseCode = "404",
                description = "Book not found",
                content = @Content
        )
})
@GetMapping("/{id}")
public ResponseEntity<BookResponse> getBook(@PathVariable Long id) {
    return bookService.findById(id)
            .map(ResponseEntity::ok)
            .orElseGet(() -> ResponseEntity.notFound().build());
}

@ApiResponses groups the known outcomes for the endpoint, while every @ApiResponse represents a status the caller can receive. The 200 entry declares JSON content backed by BookResponse, so the generated OpenAPI document can associate that response with the proper schema. The 404 branch has no response body in this controller, so its content stays empty.

These annotations document behavior that already exists in the controller. Declaring a 404 response does not make Spring MVC return that status, and declaring BookResponse as the 200 schema does not alter the value returned by the method. The code still controls the HTTP behavior, which means the response metadata should stay aligned with what the endpoint actually returns.

Creation endpoints can follow the same idea, if POST /api/books returns 201 Created, the OpenAPI metadata can record that status along with the response schema. Validation failures can also appear as 400 Bad Request when that matches the exception handling configured by the application. Response bodies deserve the same attention as response codes. If an error branch returns a structured error object, the matching @ApiResponse can reference that class through @Schema. If no body is returned, leaving the content empty prevents Swagger UI from advertising a response model callers will never receive.

HTTP headers can also be documented through OpenAPI response metadata when an endpoint returns information there. The main goal is to make the generated response section match the contract exposed by the controller rather than treating every endpoint as if it only returns its successful Java type.

Give Request Models More Context

Request DTOs already tell Springdoc the field names and Java types that belong in the request body. @Schema lets us attach extra information to those fields, including text for readers and sample values that Swagger UI can present beside the generated model.

We can keep validation rules on the request record while adding schema metadata beside them:

package com.example.books;

import io.swagger.v3.oas.annotations.media.Schema;
import jakarta.validation.constraints.NotBlank;

public record BookRequest(

        @Schema(
                description = "Title of the book",
                example = "Spring in Action"
        )
        @NotBlank
        String title,

        @Schema(
                description = "Author of the book",
                example = "Craig Walls"
        )
        @NotBlank
        String author
) {
}

@Schema contributes documentation for the properties, while @NotBlank remains a Bean Validation rule checked when the controller receives a request marked with @Valid. Springdoc can read supported validation metadata from the same Java type, so the OpenAPI schema stays tied to the DTO the endpoint actually receives.

Sample values inside @Schema do not become runtime defaults. They exist for documentation, while Bean Validation annotations still decide which incoming values are accepted by validation. Keeping those responsibilities distinct lets the request model carry both runtime rules and API metadata without confusing the two.

Metadata can also be placed on the record itself when the whole request model needs extra context:

@Schema(description = "Data required to create a book")
public record BookRequest(
        @NotBlank String title,
        @NotBlank String author
) {
}

The record-level annotation applies to the complete schema, while property-level annotations can still add field-specific information. If several endpoints accept BookRequest, Springdoc can reference the same generated schema from each operation rather than repeating the field definitions for every route.

Nested DTOs follow the same general behavior. If a request contains another record or class, Springdoc can resolve that Java type into a referenced schema and connect it to the parent model. Collections become array schemas from their Java types, while enum properties can expose the permitted enum values in the generated document.

Fields representing dates, prices, external identifiers, or restricted strings can benefit from extra metadata when the Java type does not communicate enough on its own. Sample values can also make Swagger UI more practical because someone preparing a request can see the expected format before sending it.

The controller continues to receive the request through Spring MVC in the usual way:

@PostMapping
public ResponseEntity<BookResponse> createBook(
        @Valid @RequestBody BookRequest request) {

    BookResponse book = bookService.create(request);
    return ResponseEntity.status(HttpStatus.CREATED).body(book);
}

@RequestBody tells Spring MVC to deserialize the incoming JSON into BookRequest, while @Valid starts Bean Validation for that object. Springdoc reads the same method signature and connects the generated request body to the BookRequest schema, so the documentation stays tied to the type accepted by the endpoint.

With these additions, the OpenAPI document gives readers more than route information alone. They can see what each endpoint does, which responses belong to it, what request data it expects, and how individual fields should be interpreted without moving those details away from the controller and DTO classes that define the API.

Conclusion

Springdoc takes the controller mappings and Java types already in the application and turns them into OpenAPI documentation that Swagger UI can present in the browser. From there, annotations like @Operation, @ApiResponse, @Parameter, and @Schema let us fill in the details that the code cannot communicate by itself, so the finished docs stay closely connected to the endpoints and request models they explain.

1. Springdoc OpenAPI Documentation

2. OpenAPI Specification

3. Swagger UI Documentation

4. Swagger OpenAPI Annotations

Share Alexander Obregon's Substack

Percentiles can help explain where values fall across an ordered dataset without relying only on an average. They’re often applied to salary data, API response times, sales figures, test scores, delivery times, and other numeric data where distribution is important. Instead of looking only at an average salary, you can calculate the 25th, 50th, 75th, or 90th percentile to see how values are spread across the group. SQL provides functions such as PERCENTILE_CONT, PERCENTILE_DISC, and PERCENT_RANK for different types of percentile calculations. Some return the value found at a requested percentile, while others return the relative position of each row within the ordered data. Learning how these calculations differ makes it much more natural to choose the right function for the result you need.

Subscribe now

Calculating Percentile Values

Percentile calculations depend on an ordered set of values because the position of each value determines where a requested percentile falls. We sort the selected column, identify the requested location across that ordered data, then return a value based on the percentile function we chose. This can answer questions such as where the middle salary falls, what response time marks the 95th percentile, or what sales amount represents the upper quarter of a dataset. The same mechanics also apply when the data needs to be broken into categories such as departments, regions, or product groups.

Percentiles From Ordered Data

Before we calculate a percentile, the values need to be placed in an order because percentiles are based on position rather than the original order of rows in the table. For most salary, sales, and response-time queries, that means arranging the values from lowest to highest so higher percentile numbers move toward the upper end of the distribution.

We can start with a small employee table that stores annual salaries:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, department, salary)
VALUES
    (1, 'Engineering', 52000),
    (2, 'Engineering', 61000),
    (3, 'Engineering', 68000),
    (4, 'Engineering', 74000),
    (5, 'Engineering', 83000),
    (6, 'Engineering', 91000),
    (7, 'Engineering', 105000),
    (8, 'Engineering', 118000);

We now have eight salary values available for the percentile calculation, and when we order them from lowest to highest, they are 52000, 61000, 68000, 74000, 83000, 91000, 105000, and 118000. Their positions within that sequence are what percentile functions rely on, so the calculation is based on where a value falls after ordering rather than where its row happened to appear in the table.

Percentile functions normally express the requested point as a decimal between 0 and 1, which means 0.25 represents the 25th percentile, 0.50 represents the 50th percentile, and 0.90 represents the 90th percentile. The percentage and decimal refer to the same location, only written in different forms.

We can calculate the median salary with PERCENTILE_CONT like this in PostgreSQL:

SELECT
    PERCENTILE_CONT(0.50)
    WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

The WITHIN GROUP clause tells the database which ordering belongs to the percentile calculation, while 0.50 asks for the halfway point across those ordered salary values. Because the table contains eight salaries, there isn’t a single row directly in the middle, so the center falls between the fourth value of 74000 and the fifth value of 83000.

PERCENTILE_CONT treats the values as part of a continuous distribution and interpolates between those two numbers, producing 78500. That result does not need to exist as a stored salary because the function is calculating a point within the distribution rather than selecting one specific row from the table.

We can change only the percentile argument when we want a different point from the same salary distribution:

SELECT
    PERCENTILE_CONT(0.90)
    WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees;

This time we request 0.90, which moves the calculation much farther toward the upper end of the salaries. Choosing a lower value such as 0.10 would move toward the lower end instead, while 0.50 stays centered, so the decimal passed to PERCENTILE_CONT directly controls which point across the ordered values we want to retrieve.

Ordering direction changes the meaning of the result as well. Salary and response-time reports normally place smaller values first with ascending order, which makes higher percentile numbers correspond to higher measurements. Reversing the order would reverse that interpretation, so the ORDER BY direction needs to match the way the percentile is meant to be read.

Null values also need to be accounted for when reading percentile results because ordered-set percentile functions such as PostgreSQL PERCENTILE_CONT ignore null input values instead of placing missing values somewhere inside the numeric distribution. If a salary row contains NULL in the salary column, that row does not receive a position within the ordered salaries used for the calculation.

Continuous Versus Discrete Percentiles

Two common percentile functions can answer closely related questions while returning different values because they follow different rules when the requested percentile falls between rows. PERCENTILE_CONT treats the ordered data as a continuous distribution and can interpolate between neighboring values, while PERCENTILE_DISC returns the first observed value whose cumulative position reaches or passes the requested percentile.

We can run the same median calculation from earlier with PERCENTILE_DISC:

SELECT
    PERCENTILE_DISC(0.50)
    WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

The query still orders the same salaries and still asks for the 50th percentile, but the function changes how that position is resolved. Rather than calculating a value between 74000 and 83000, PERCENTILE_DISC returns an observed salary from the ordered data, which gives us 74000 for these rows.

That leaves us with two valid results from the same dataset that represent different interpretations of the midpoint. PERCENTILE_CONT returned 78500 because it calculated the continuous point between the two middle salaries, while PERCENTILE_DISC returned 74000 because its result had to come directly from the observed values.

Continuous percentiles make sense for measurements where values between two recorded observations still carry meaning, such as salary, transaction amount, response duration, temperature, and distance. If neighboring response times are 114 and 127 milliseconds, a calculated value somewhere between them can still represent a valid location within the distribution.

Discrete percentiles are better suited to reports where the returned value needs to come directly from the stored observations rather than from interpolation. Instead of calculating a number between two rows, the database selects an existing value that reaches the requested percentile.

We can see that difference again with response-time data:

CREATE TABLE api_requests (
    request_id INT PRIMARY KEY,
    response_ms INT
);

INSERT INTO api_requests (request_id, response_ms)
VALUES
    (1, 95),
    (2, 101),
    (3, 108),
    (4, 114),
    (5, 127),
    (6, 145),
    (7, 183),
    (8, 240);

This table gives us eight recorded response times measured in milliseconds, all stored as integers. Those integer values do not require the percentile result to remain an integer when we choose a continuous calculation because interpolation can place the requested percentile between two observations.

We can request the 95th percentile with PERCENTILE_CONT:

SELECT
    PERCENTILE_CONT(0.95)
    WITHIN GROUP (ORDER BY response_ms) AS p95_response_ms
FROM api_requests;

The response times are ordered before the 95th percentile position is located, and if that position falls between two stored rows, PERCENTILE_CONT interpolates between them. The returned result can therefore contain a fractional value even though every value in response_ms was stored as a whole number.

If the report needs the percentile to match one of the recorded response times, we can change the function while keeping the percentile itself the same:

SELECT
    PERCENTILE_DISC(0.95)
    WITHIN GROUP (ORDER BY response_ms) AS p95_response_ms
FROM api_requests;

We still ask for the 95th percentile, but the result now comes from one of the response times already present in the table. The percentile argument has not changed, so any difference between the two results comes from the continuous and discrete calculation rules rather than from a different requested position.

Choosing between these functions depends on what the returned number needs to represent. When an interpolated measurement makes sense, PERCENTILE_CONT gives us that point within the distribution, while PERCENTILE_DISC keeps the result tied directly to an observed value.

Quartiles From Percentile Functions

Quartiles are specific percentile locations that divide ordered values into four portions, with the first quartile often written as Q1 at the 25th percentile, the median at the 50th percentile, and the third quartile written as Q3 at the 75th percentile. Because each quartile is already a percentile position, we do not need a different family of functions to calculate them.

We can return all three salary quartiles in one query by changing only the percentile argument passed to PERCENTILE_CONT:

SELECT
    PERCENTILE_CONT(0.25)
        WITHIN GROUP (ORDER BY salary) AS q1,
    PERCENTILE_CONT(0.50)
        WITHIN GROUP (ORDER BY salary) AS median,
    PERCENTILE_CONT(0.75)
        WITHIN GROUP (ORDER BY salary) AS q3
FROM employees;

All three expressions read from the same salary column and order the values the same way, but each asks for a different location across that ordered distribution. The first expression finds the quarter point, the second finds the midpoint, and the third finds the three-quarter point, giving us three reference values from the same salary data.

Reading those results side by side gives us more information about how the center of the salary distribution is spread out. If Q1, the median, and Q3 fall relatively close to each other, the middle half of the salaries covers a narrower range, while greater distance between those values means that central portion spans a wider range.

Quartiles can also be paired with percentile positions farther toward the lower or upper portions of the data when a report needs more detail. We can use a small sales table for the sales examples that follow:

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    region VARCHAR(50),
    amount DECIMAL(10, 2),
    sale_date DATE
);

INSERT INTO sales (sale_id, region, amount, sale_date)
VALUES
    (1, 'North', 120, DATE '2026-01-10'),
    (2, 'North', 180, DATE '2026-02-14'),
    (3, 'North', 180, DATE '2026-03-03'),
    (4, 'North', 260, DATE '2026-04-20'),
    (5, 'South', 90, DATE '2025-12-18'),
    (6, 'South', 140, DATE '2026-01-25'),
    (7, 'South', 220, DATE '2026-03-12'),
    (8, 'South', 310, DATE '2026-05-06');

Sales figures can then be examined at the 10th, 25th, 50th, 75th, and 90th percentiles in the same query:

SELECT
    PERCENTILE_CONT(0.10)
        WITHIN GROUP (ORDER BY amount) AS p10,
    PERCENTILE_CONT(0.25)
        WITHIN GROUP (ORDER BY amount) AS p25,
    PERCENTILE_CONT(0.50)
        WITHIN GROUP (ORDER BY amount) AS p50,
    PERCENTILE_CONT(0.75)
        WITHIN GROUP (ORDER BY amount) AS p75,
    PERCENTILE_CONT(0.90)
        WITHIN GROUP (ORDER BY amount) AS p90
FROM sales;

We now get several reference points from the same ordered set of sales amounts, with p50 representing the median and p25 and p75 matching Q1 and Q3. The added p10 and p90 values extend farther toward the lower and upper ends, which gives the report more detail about how sales amounts are distributed beyond the middle half.

Quartiles also give us the values needed for the interquartile range, which is calculated as Q3 - Q1 and represents the span covered by the middle 50 percent of the ordered values. We can calculate that distance directly from the salary data:

SELECT
    PERCENTILE_CONT(0.75)
        WITHIN GROUP (ORDER BY salary)
    -
    PERCENTILE_CONT(0.25)
        WITHIN GROUP (ORDER BY salary) AS interquartile_range
FROM employees;

Both percentile expressions are calculated from the same ordered salary column, and subtracting the 25th percentile from the 75th percentile leaves the distance between those two quartile boundaries. Because the calculation focuses on the middle half of the data, the smallest and largest salaries do not directly determine its value, which makes the interquartile range different from the full range calculated from the minimum and maximum.

Percentiles Within Groups

Reports frequently need percentile values for individual categories rather than one calculation across the entire table, so salary data can be grouped by department, sales can be grouped by region, and response times can be grouped by endpoint. Each category then gets its own ordered distribution, which lets us calculate a percentile from only the rows that belong to that group.

With PostgreSQL ordered-set aggregates, we can pair PERCENTILE_CONT with GROUP BY to calculate a median for every department:

SELECT
    department,
    PERCENTILE_CONT(0.50)
        WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

The rows are grouped by department, then the salaries within each department are ordered for the percentile calculation. Engineering salaries therefore have no effect on the median returned for Finance, Sales, or any other department because every group is evaluated from its own rows.

That distinction becomes important when categories have very different value ranges. An organization-wide salary median can answer a broad question about all employees, while department-level calculations let us see how the midpoint changes across departments without combining every salary into the same distribution.

The same structure can be applied to regional sales figures when we want the 90th percentile calculated independently for every region:

SELECT
    region,
    PERCENTILE_CONT(0.90)
        WITHIN GROUP (ORDER BY amount) AS p90_sale
FROM sales
GROUP BY region;

Each returned row represents a region and its own 90th percentile sales amount, so transactions from one region do not affect the percentile calculated for another. We first divide the rows through GROUP BY, then the percentile expression evaluates the ordered amount values within each resulting group.

Several percentile values can also be returned for every group rather than limiting each category to a single point. A regional report can return Q1, the median, and Q3 side by side:

SELECT
    region,
    PERCENTILE_CONT(0.25)
        WITHIN GROUP (ORDER BY amount) AS q1,
    PERCENTILE_CONT(0.50)
        WITHIN GROUP (ORDER BY amount) AS median,
    PERCENTILE_CONT(0.75)
        WITHIN GROUP (ORDER BY amount) AS q3
FROM sales
GROUP BY region;

For every region, we now get three points from its own ordered sales distribution, which gives us the quarter point, midpoint, and three-quarter point without combining sales from unrelated regions. That makes comparisons between groups more informative because every set of percentile values comes from the data belonging to that category.

Filtering changes the population that participates in the calculation as well, because rows that fail a WHERE condition are removed before the grouping and percentile calculation take place. We could limit the sales report to transactions from January 1, 2026 onward like this:

SELECT
    region,
    PERCENTILE_CONT(0.90)
        WITHIN GROUP (ORDER BY amount) AS p90_sale
FROM sales
WHERE sale_date >= DATE '2026-01-01'
GROUP BY region;

With the date condition applied first, only sales from January 1, 2026 or later participate in the grouped calculation, after which we divide those remaining rows by region, order the amounts within every group, and calculate the 90th percentile from that filtered set rather than from every sale stored in the table.

That order affects how we read the result because the returned percentile represents only the rows that passed the filter. Changing the date range, region criteria, or any other condition can therefore change the percentile values even when the percentile expression itself remains unchanged.

Syntax for percentile calculations varies across database products. PostgreSQL exposes PERCENTILE_CONT and PERCENTILE_DISC as ordered-set aggregate functions, which lets them pair directly with GROUP BY, while SQL Server provides these percentile functions through window syntax and Oracle supports aggregate and analytic forms. Across those database engines, the same core calculation remains in place as values are ordered, a percentile position is requested, and the result comes from the rows included in the relevant dataset or group.

Finding Percentile Position

Percentile values answer questions about where a number falls within an ordered distribution, while percentile rank looks at the rows already present and tells us where each of those rows falls relative to the others. Instead of asking for the salary at the 90th percentile, we may want to know how every salary compares with the rest of the salaries in the same result. PERCENT_RANK handles that type of calculation by assigning each row a relative position based on its rank within the ordered data.

Ranking Every Row

Relative ranking begins by ordering the rows according to the value we want to compare, then PERCENT_RANK calculates where each row falls within that ordering. The returned values range from 0 through 1, which gives us a normalized rank rather than a raw row number.

For the employee salary data from earlier, we can rank every salary from lowest to highest like this:

SELECT
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percent_rank
FROM employees;

The OVER clause defines the rows that participate in the calculation, while ORDER BY salary places those rows in salary order before their relative ranks are assigned. We receive a result for every employee, so the query gives us row-by-row positions rather than a single percentile value for the entire table.

PERCENT_RANK bases its result on the row rank and the total number of rows in the ordered set. The formula is:

(rank - 1) / (rows - 1)

From that formula, we can see why the first ranked row receives 0. Its rank is 1, so subtracting 1 leaves 0 before the division takes place, while rows farther through the ordered set receive larger values as their rank increases.

With eight employees, a row ranked sixth receives a percentile rank of about 0.7143, which corresponds to roughly 71.43 percent when multiplied by 100. We can perform that conversion directly in the query:

SELECT
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) * 100 AS salary_percent_rank
FROM employees;

The ranking calculation stays the same, and multiplying by 100 only changes how we present the result. Instead of reading 0.7143, we can read about 71.43, which is usually more familiar when discussing percentile rank as a percentage.

This type of result is different from asking for a percentile value. PERCENTILE_CONT(0.90) returns the salary located at the 90th percentile, while PERCENT_RANK() tells us the relative position assigned to every salary already present in the result. We can think about those questions in opposite directions. With PERCENTILE_CONT, we provide a percentile and receive a value back. With PERCENT_RANK, the rows already exist, and we calculate a relative position for each of them.

Tied values affect the ranking because PERCENT_RANK follows the same ranking behavior as RANK. Rows with equal ordering values receive the same rank, so they also receive the same percentile rank.

We can see that more easily by displaying RANK() beside PERCENT_RANK():

SELECT
    sale_id,
    amount,
    RANK() OVER (ORDER BY amount) AS sale_rank,
    PERCENT_RANK() OVER (ORDER BY amount) AS sale_percent_rank
FROM sales;

If two sales share the same amount, both receive the same rank and the same percentile rank. The next distinct amount can then jump ahead because RANK() leaves a gap after tied rows, and PERCENT_RANK() reflects that rank when calculating the relative position.

Partitioning lets us calculate those positions inside categories rather than across the entire result. Salary data can be ranked within each department with PARTITION BY:

SELECT
    department,
    employee_id,
    salary,
    PERCENT_RANK() OVER (
        PARTITION BY department
        ORDER BY salary
    ) AS department_percent_rank
FROM employees;

Each department receives its own ranking sequence because PARTITION BY department divides the rows into department groups before the ranking calculation is applied. We then order salaries inside each department, so employees are compared only with coworkers from that department.

Because the ranking restarts for every partition, the same salary can receive different percentile ranks across different departments. A salary near the top of one department could fall closer to the middle of another if the surrounding salaries are different. The same idea applies to response times and sales data. Requests can be ranked within an endpoint, sales can be ranked within a region, and transaction amounts can be ranked within a customer category. Partitioning changes the population that each row is compared against while leaving the PERCENT_RANK formula itself unchanged.

Ordering direction deserves attention as well. With ORDER BY salary, lower salaries receive lower percentile ranks and higher salaries move toward 1. Reversing the order to ORDER BY salary DESC changes that interpretation because the highest salary becomes the first ranked row and receives 0.

For salary and response-time reporting, ascending order usually matches the familiar reading where larger values appear farther up the percentile scale. If the business question calls for the opposite direction, descending order can still be valid, but the returned ranks have to be read with that reversed ordering in mind.

Choosing the Calculation

Picking the right percentile function becomes much more natural when we start with the result we need. If we want the salary value at the 75th percentile, we are asking for a point from the distribution. If we want to know where each employee salary falls compared with the rest, we are asking for a relative position. PERCENTILE_CONT and PERCENTILE_DISC belong to the first case because we provide a percentile position and receive a value. PERCENT_RANK belongs to the second because it evaluates the existing rows and returns a relative rank for each row in the result.

We can see the difference with response-time data. If we want the response time at the 95th percentile, the query asks for a percentile value:

SELECT
    PERCENTILE_CONT(0.95)
    WITHIN GROUP (ORDER BY response_ms) AS p95_response_ms
FROM api_requests;

The result contains a single value representing the requested point across the ordered response times. We are not ranking every request individually, so the query returns the percentile measurement itself rather than a row-by-row comparison.

If the question changes and we want to see where every request falls compared with the rest, PERCENT_RANK matches that need:

SELECT
    request_id,
    response_ms,
    PERCENT_RANK() OVER (ORDER BY response_ms) AS response_percent_rank
FROM api_requests;

Every request now receives its own relative position. Faster responses appear nearer the lower end when response_ms is ordered ascending, while slower responses move closer to the upper end.

That difference becomes important when reading labels such as p50, p90, p95, and p99. Those names refer to percentile points within an ordered distribution, so a p95 response time answers a question about the response-time value reached at the 95th percentile. Percentile rank attached to an individual request answers a different question by telling us how that request compares with the other rows.

Sales data follows the same logic. If we want the sales amount located at the 90th percentile, we calculate a percentile value. If we want every sale assigned a relative position, we calculate a percentile rank for each sale.

We can also apply that comparison within categories. The following query ranks each sale only against sales from the same region:

SELECT
    region,
    sale_id,
    amount,
    PERCENT_RANK() OVER (
        PARTITION BY region
        ORDER BY amount
    ) AS regional_percent_rank
FROM sales;

Each returned row now carries a percentile rank based only on its region. We divide the rows by region, order the sales amounts inside each partition, then calculate the relative position from that smaller group rather than from the full sales table.

Percentile rank also behaves differently when the group contains very few rows. Because the formula depends on both rank and row count, smaller groups produce larger jumps between neighboring percentile-rank values, while larger groups give us more rank positions between the bottom and top.

Groups containing a single row need special handling because the standard formula would place 0 in the denominator. SQL implementations define that case directly, and commonly supported implementations return 0 for the only row in the group. Tied values also influence how we interpret the result because equal values share the same rank. If several sales have the same amount, they receive the same percentile rank rather than receiving slightly different percentages based only on their physical row positions.

Taken as a whole, these functions answer related but distinct questions. PERCENTILE_CONT returns an interpolated value at a requested percentile, PERCENTILE_DISC returns an observed value from that percentile position, and PERCENT_RANK tells us where each existing row falls relative to the other rows in its ordered group.

Conclusion

Percentile queries come down to ordering values, choosing the percentile point we need, and deciding what type of result should come back. PERCENTILE_CONT can interpolate between values, PERCENTILE_DISC returns an observed value, and PERCENT_RANK gives each row a relative position within its ordered group. Those mechanics let us examine salary, response-time, sales, and similar data across different points in a distribution rather than relying on a single average.

1. PostgreSQL Aggregate Functions

2. PostgreSQL Window Functions

3. SQL Server PERCENTILE_CONT

4. SQL Server PERCENT_RANK

Share Alexander Obregon's Substack

Unit tests are great for checking individual classes, but database behavior can be harder to test through mocks alone. An in-memory database can help, but PostgreSQL, MySQL, MongoDB, and other databases can behave differently from the database the application runs against outside the test suite. Testcontainers lets integration tests start temporary containerized databases for the duration of the test run, then remove them when testing finishes. Spring Boot can connect supported containers through @ServiceConnection, letting repository and service tests run against the same database technology the application depends on without keeping a dedicated test database running.

Subscribe now

Building the Test Environment

Integration testing gets more valuable when the database involved in the test behaves like the database the application depends on outside the test suite. Testcontainers fills that gap by starting short-lived services inside containers for the duration of testing, which gives Spring Boot a database it can connect to rather than a mocked repository or an unrelated in-memory engine. The container can come from a normal database image, receive temporary connection details, and disappear after testing finishes. That keeps database-dependent tests self-contained while still exercising behavior that mocks cannot reproduce.

Why Containers Belong in Integration Tests

Mocks are good for checking logic inside a class because they let us control what a dependency returns. Repository behavior goes further than that. JPA mappings, generated SQL, database constraints, column types, indexes, native queries, transactions, and vendor-specific SQL all depend on the database engine receiving the request. Replacing that database with a mock removes those pieces from the test completely.

An in-memory database goes a step further because it executes SQL and stores rows, so it can help with fast database-related tests. The limitation comes from differences between database engines. H2, PostgreSQL, and MySQL do not behave identically, and the differences can show up in SQL syntax, data types, identity generation, JSON support, locking rules, indexes, and built-in functions. Passing a test against an in-memory database confirms behavior against that engine, not automatically against the database chosen for the application.

PostgreSQL-specific SQL gives us a good example of where that distinction becomes relevant:

SELECT id, metadata ->> 'author' AS author
FROM books
WHERE title ILIKE '%spring%';

The ->> operator reads a JSON value as text, while ILIKE performs case-insensitive matching in PostgreSQL. If our repository depends on SQL like this, testing it against PostgreSQL gives us feedback from the same database technology that will process the query later. An in-memory database could reject the syntax, interpret it differently, or lack the same feature entirely.

Testcontainers handles this by starting the requested database image inside a container. Our test still communicates through the normal database driver and Spring data access stack, while the database process exists only for the testing lifecycle. Depending on the container lifecycle we choose later, the same container can remain available across a Spring test context before it is stopped. Host ports are assigned dynamically rather than forcing every PostgreSQL test to claim port 5432. That helps when PostgreSQL is already running locally and also prevents multiple test environments from competing for the same host port. Testcontainers keeps track of the assigned port and connection information, so our test configuration can receive the actual address instead of assuming a fixed value.

The first test run can download the requested container image if it is not already stored locally. Later runs can start from the locally available image, which avoids downloading it every time. Testcontainers still needs access to a supported container runtime, so the developer machine or CI runner must provide an environment where the requested containers can start.

Containers do not replace unit tests. Logic that has no dependency on database behavior can still be tested quickly with normal objects or mocks. Container-backed integration tests become more relevant around repository mappings, custom queries, schema migrations, transaction behavior, database constraints, and service methods whose behavior depends heavily on persisted data.

Spring Boot adds service connection support on top of Testcontainers. When we mark a supported container with @ServiceConnection, Spring Boot can create connection details for that service and pass them into the matching auto-configuration. This removes the need to manually copy a generated host, mapped port, username, or password into test properties. The actual container declaration comes later, but the important idea at this stage is that Spring Boot can receive those temporary connection details through its normal test configuration.

Add the Test Dependencies

Before PostgreSQL can become part of the test suite, the project needs dependencies for normal database access and dependencies that exist only during testing. The application still needs Spring Data JPA and the PostgreSQL JDBC driver, while the test classpath gets Spring Boot JPA testing support, Spring Boot Testcontainers integration, and the PostgreSQL Testcontainers module.

With Maven and Spring Boot dependency management in place, individual version numbers can stay out of these dependency declarations:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-testcontainers</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.testcontainers</groupId>
        <artifactId>testcontainers-postgresql</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

spring-boot-starter-data-jpa provides Spring Data JPA and Hibernate for the application. The PostgreSQL dependency supplies the JDBC driver that allows Java code to communicate with PostgreSQL. Testcontainers does not replace that driver because the container module is responsible for starting PostgreSQL, while the JDBC driver is responsible for the database connection itself.

spring-boot-starter-data-jpa-test provides the Spring Boot 4 test support intended for JPA-focused testing. It also brings in the general Spring testing pieces needed for JUnit Jupiter, assertions, and Spring test contexts. Keeping that dependency in test scope means those libraries stay limited to testing rather than becoming part of the normal application runtime.

spring-boot-testcontainers adds the Spring Boot integration needed for features such as @ServiceConnection. The testcontainers-postgresql dependency adds PostgreSQL-specific Testcontainers support, including the current PostgreSQLContainer class that we will configure later.

Gradle projects can declare the same dependencies with their matching configurations:

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'org.postgresql:postgresql'

    testImplementation 'org.springframework.boot:spring-boot-starter-data-jpa-test'
    testImplementation 'org.springframework.boot:spring-boot-testcontainers'
    testImplementation 'org.testcontainers:testcontainers-postgresql'
}

implementation keeps Spring Data JPA available to the application code, while runtimeOnly places the PostgreSQL driver on the runtime classpath without treating it as an API dependency. The three testImplementation entries are available during test compilation and execution, which is where Spring Boot testing support and Testcontainers belong.

The PostgreSQL module name deserves attention when reading older tutorials. Testcontainers 2 gives PostgreSQL support through the testcontainers-postgresql artifact, and the current container class lives in org.testcontainers.postgresql.PostgreSQLContainer. Older material can still contain org.testcontainers.containers.PostgreSQLContainer, but that class belongs to the older package location and is deprecated in Testcontainers 2.

No JUnit Testcontainers extension is required for the Spring-managed container bean style covered later. Spring Boot can manage container beans through the application context, including starting them before dependent beans need their connection information and stopping them when that context closes. Projects that choose the JUnit @Testcontainers and @Container style can add testcontainers-junit-jupiter, though that follows a different lifecycle model from the Spring-managed configuration we will build next.

Keeping the Testcontainers dependencies in test scope also prevents the container libraries from becoming part of normal application startup. The application retains Spring Data JPA and the PostgreSQL driver it already needs, while container management and Spring Boot Testcontainers integration remain limited to the test classpath.

Testing the Application Against PostgreSQL

With the project dependencies in place, we can connect the application layer to a temporary PostgreSQL database and follow the request from Java code through persistence and back again. The production classes stay unaware of Testcontainers because container configuration belongs on the test side of the project. Spring Boot receives temporary connection details through the test context, while the entity, repository, and service keep the same responsibilities they have during normal application startup.

Create a Small Data Layer

We can begin with a compact JPA entity so the database behavior stays easy to follow. Book represents a row in the books table, with an automatically generated identifier and a required title. Nothing in the entity refers to Testcontainers, PostgreSQL credentials, or a test-specific connection because those details belong outside the domain class.

package com.example.books;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "books")
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String title;

    protected Book() {
    }

    public Book(String title) {
        this.title = title;
    }

    public Long getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }
}

JPA needs a public or protected no-argument constructor so the persistence provider can create entity instances when rows come back from the database. The second constructor gives application code a direct way to create a book from a title, while GenerationType.IDENTITY leaves identifier generation to PostgreSQL. With schema generation active during these tests, @Column(nullable = false) also contributes a non-null constraint for the title column.

The repository can stay small because Spring Data JPA can derive the query from the method name:

package com.example.books;

import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;

public interface BookRepository extends JpaRepository<Book, Long> {

    Optional<Book> findByTitle(String title);
}

Spring Data reads findByTitle from the repository interface and creates a query based on the title property from Book. Returning Optional<Book> gives the caller an explicit result when PostgreSQL has no matching row, rather than passing a null value back to the service layer. That return type also pairs naturally with the service method we will add next.

The service adds application logic above the repository. In this case, we ask the repository for a title and throw an exception when no matching book exists:

package com.example.books;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional(readOnly = true)
public class BookService {

    private final BookRepository bookRepository;

    public BookService(BookRepository bookRepository) {
        this.bookRepository = bookRepository;
    }

    public Book getByTitle(String title) {
        return bookRepository.findByTitle(title)
                .orElseThrow(() -> new IllegalArgumentException("Book not found"));
    }
}

Constructor injection gives BookService access to the repository through Spring, while @Transactional(readOnly = true) places its read operations inside a read-only transaction when transaction management is active. The service does not need database credentials, container details, or a JDBC URL. Those concerns remain in test configuration, which lets this class behave the same during normal startup and integration testing.

Start PostgreSQL for Tests

Container configuration belongs under src/test/java because it exists only for testing. We can declare PostgreSQL as a Spring bean inside a class marked with @TestConfiguration, then place @ServiceConnection on the bean method so Spring Boot can derive connection details from the typed PostgreSQL container:

package com.example.books;

import org.springframework.boot.test.context.TestConfiguration;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.springframework.context.annotation.Bean;
import org.testcontainers.postgresql.PostgreSQLContainer;

@TestConfiguration(proxyBeanMethods = false)
public class PostgresTestConfiguration {

    @Bean
    @ServiceConnection
    PostgreSQLContainer postgresContainer() {
        return new PostgreSQLContainer("postgres:17-alpine")
                .withDatabaseName("books_test")
                .withUsername("test")
                .withPassword("test");
    }
}

PostgreSQLContainer starts PostgreSQL from the requested image and exposes connection information after the container is running. The database name and credentials belong only to this temporary instance, so they do not need to match production values. Testcontainers maps PostgreSQL to an available host port rather than requiring port 5432 on the machine running the tests, which lets the temporary database start without depending on a fixed local port.

@ServiceConnection links the container with Spring Boot database auto-configuration. Because the bean method returns the typed PostgreSQLContainer, Spring Boot can identify the service and create JDBC connection details for it. JPA then receives a DataSource backed by the container without a manually assembled spring.datasource.url, host name, or mapped port. Service connection details take precedence over normal connection properties for the matching service.

Marking the class with @TestConfiguration keeps it on the test side of the project rather than regular application component scanning. The proxyBeanMethods = false setting tells Spring that this configuration does not need method interception for calls between bean methods. Later test classes can bring the configuration into their context through @Import(PostgresTestConfiguration.class).

The container bean follows the lifecycle of the Spring test context. Spring Boot starts container beans early enough for dependent connection details to become available, then stops them when that context closes. No manual start() or stop() calls are needed for the Spring-managed bean style shown here.

Test the Repository

Repository tests can stay focused on JPA rather than loading the full application context. @DataJpaTest brings in entity scanning, Spring Data JPA repositories, Hibernate, JDBC support, and the related test auto-configuration, while regular application components such as BookService are left out of this slice by default. Spring Boot also runs JPA tests inside transactions and rolls them back after each test method.

We can import the PostgreSQL configuration and let Hibernate create the schema for this compact example:

package com.example.books;

import static org.assertj.core.api.Assertions.assertThat;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.data.jpa.test.autoconfigure.DataJpaTest;
import org.springframework.context.annotation.Import;

@DataJpaTest(properties = "spring.jpa.hibernate.ddl-auto=create-drop")
@Import(PostgresTestConfiguration.class)
class BookRepositoryTest {

    @Autowired
    private BookRepository bookRepository;

    @Test
    void findsBookByTitle() {
        Book savedBook = bookRepository.saveAndFlush(
                new Book("Spring Testing")
        );

        Book foundBook = bookRepository.findByTitle("Spring Testing")
                .orElseThrow();

        assertThat(foundBook.getId()).isEqualTo(savedBook.getId());
        assertThat(foundBook.getTitle()).isEqualTo("Spring Testing");
    }
}

@Import adds the test configuration, which brings the PostgreSQL container bean into this test context. The ddl-auto=create-drop property tells Hibernate to create the table from the entity mapping for this example and remove the generated schema when the persistence context closes. Projects that manage schema changes through Flyway or Liquibase can let those migrations prepare the container database instead.

Inside the test method, saveAndFlush() persists the new Book and flushes pending SQL to PostgreSQL before the lookup runs. findByTitle() then sends the repository query to PostgreSQL, and the assertions confirm that the row returned through JPA carries the generated identifier and expected title. This verifies more than a mocked repository could because Hibernate produces SQL, PostgreSQL processes it, and Spring Data maps the result back into the entity.

Spring Boot 4 treats a database supplied through @ServiceConnection as a test database for test database replacement. The default replacement mode is NON_TEST, so this container-backed DataSource remains in place rather than being replaced by an embedded database. No @AutoConfigureTestDatabase(replace = NONE) annotation is needed for this configuration.

The transactional behavior of @DataJpaTest also prevents repository tests from leaving committed rows behind. Each method can prepare the records it needs, execute repository calls, and finish with the transaction rolled back, which keeps later methods from inheriting data created by an earlier test. That isolation becomes more important as the repository test class grows and different methods insert records with different values.

Test the Service

Service-level integration testing loads more of the Spring Boot context because we now want Spring to create BookService along with the repository, Hibernate, transaction management, and the PostgreSQL connection. @SpringBootTest loads the application context rather than the narrower JPA slice, while the same test configuration can supply the temporary PostgreSQL instance.

We can store a book through the repository and then retrieve it through the service:

package com.example.books;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.annotation.Import;
import org.springframework.transaction.annotation.Transactional;

@SpringBootTest(properties = "spring.jpa.hibernate.ddl-auto=create-drop")
@Import(PostgresTestConfiguration.class)
@Transactional
class BookServiceIntegrationTest {

    @Autowired
    private BookRepository bookRepository;

    @Autowired
    private BookService bookService;

    @Test
    void returnsBookStoredInPostgres() {
        bookRepository.saveAndFlush(
                new Book("Testing Spring Boot")
        );

        Book book = bookService.getByTitle("Testing Spring Boot");

        assertThat(book.getTitle()).isEqualTo("Testing Spring Boot");
    }
}

The test context creates the normal service and repository beans, then imports the PostgreSQL container configuration beside them. Saving through BookRepository places the row in PostgreSQL, while the following BookService call passes through the service method, repository query, Hibernate, JDBC driver, and temporary database. The assertion checks the value returned after that call chain completes, so we are testing the service with the same persistence stack configured for the application.

Placing @Transactional on the test class keeps changes from each test method inside the test transaction, which Spring rolls back afterward. The service already carries @Transactional(readOnly = true), while the surrounding test transaction lets us prepare data before calling the read method without leaving committed rows behind.

The missing-record branch can live in the same integration test class:

@Test
void throwsExceptionWhenBookDoesNotExist() {
    assertThatThrownBy(
            () -> bookService.getByTitle("Missing Book")
    ).isInstanceOf(IllegalArgumentException.class);
}

assertThatThrownBy receives the service call as a lambda and checks that it ends with IllegalArgumentException. No row is inserted for the requested title, so findByTitle() returns an empty Optional and BookService reaches its orElseThrow branch. We still pass through the repository and PostgreSQL for the lookup, which lets the test cover the service behavior without replacing persistence with a mock.

The same service connection model can support other database technologies. MySQL follows a similar relational flow with its Testcontainers module and container class, while MongoDB projects rely on Spring Data MongoDB and MongoDBContainer rather than JPA and JDBC. The container type changes with the database, but Spring Boot can still receive supported connection details through @ServiceConnection.

Conclusion

Testcontainers gives Spring Boot integration tests a temporary PostgreSQL database that can be started from test configuration and connected through @ServiceConnection. From there, @DataJpaTest can exercise repository behavior through JPA and PostgreSQL, while @SpringBootTest can load the broader application context for service-level testing. The container stays outside production code, Spring Boot supplies the connection details, and each test can run against PostgreSQL without keeping a dedicated test database running.

1. Spring Boot Testcontainers Support

2. Testcontainers PostgreSQL Module

3. Spring Boot Data JPA Testing

4. Testcontainers Database Modules

Share Alexander Obregon's Substack

Finding the middle value answers a different question than calculating an average. An average adds every value and divides the total by the number of rows, which means unusually high or low values can pull the result away from the center of the data. The median instead orders the values and finds the middle position, making it a good fit for salaries, prices, response times, order totals, delivery times, and other data where extreme values can affect an average. SQL offers us several ways to calculate that middle point. PostgreSQL, SQL Server, and Oracle has percentile functions that can return the 50th percentile directly, while ranking functions provide another option on database products without the same percentile support. These calculations can cover an entire result set or run within groups such as departments, regions, products, or reporting periods.

Subscribe now

Median Mechanics

Finding a median starts with position rather than addition. We first arrange the values from lowest to highest, then identify the center of that ordered set. The center behaves differently depending on the row count. Odd totals have a single middle value, while even totals have two middle values that are averaged for the usual numeric median. Thinking through the calculation by position gives us a solid foundation for the percentile and ranking methods that come later because both rely on this same ordering logic.

Sorting to the Middle

Ordering determines which value belongs at the center. If we arrange five order totals from lowest to highest, the third value becomes the median because two values appear below it and two appear above it.

18
25
31
46
120

We can read the ordered values from top to bottom and count inward toward the center. With five rows, position three is the middle, so 31 becomes the median. No averaging is needed here because an odd row count gives us a single center position.

That differs from an average, where every value contributes directly to the result. The final value of 120 raises the average quite a bit, but it does not move the median away from 31 because its location stays near the upper end of the ordered data. This is why median values can be helpful with prices, salaries, response times, delivery durations, and other numeric data that can contain unusually high or low entries.

An even row count changes the calculation because no single row occupies the exact center. If we add one more value, the ordered set now contains six rows.

18
25
31
46
120
200

We now have two middle positions rather than one. Rows three and four contain 31 and 46, so we average those values to find the numeric median.

(31 + 46) / 2 = 38.5

The result becomes 38.5, even though that number does not appear anywhere in the stored values. We are measuring the point halfway between the two center positions, so the median can fall between values when the row count is even.

Duplicate values do not change this rule because every row still occupies a position after sorting. Repeated values can appear before, after, or directly at the center without requiring special treatment.

10
20
20
20
50

Reading through these five rows places the center at position three, where the value is 20. The repeated 20 values around that position do not alter the calculation because the median depends on ordered position rather than the number of distinct values.

Null values need different treatment because null represents missing data rather than the number zero. Treating null as zero would introduce a value that was never recorded and could move the center of the ordered set. Median calculations generally exclude null values before counting rows and locating the center.

Take these values:

15
NULL
25
35
45

After excluding the null, we are left with four numeric values.

15
25
35
45

We count four rows, which places the center between positions two and three. Those rows contain 25 and 35, so averaging them gives us a median of 30. If the null had been changed to zero, the ordered data would contain five numeric positions and the median would become 25, answering a different statistical question because zero was not part of the original data.

Sorting from highest to lowest would still lead to the same numeric median as long as the positions are handled consistently. Ascending order is usually more natural to read because we can move from the smallest value toward the largest, but the center itself does not change when the ordering direction is reversed.

Continuous Versus Discrete Results

Percentile calculations give us two related ways to interpret a requested position. Continuous calculation can return a value between stored rows, while discrete calculation returns a value that already exists in the ordered data.

The difference is easiest to see with an even number of rows. We can return to the six-value set from earlier.

18
25
31
46
120
200

The 50th percentile falls halfway between positions three and four. For a continuous median, those positions contain 31 and 46, so the result becomes 38.5.

We can think of continuous calculation as allowing movement between neighboring numeric values when the requested percentile does not land directly on a row. If the percentile position reaches an exact row, the value from that row is returned without any interpolation.

The position can be expressed with this formula:

1 + P * (N - 1)

P represents the requested percentile as a decimal, while N represents the number of non-null values. For a median, we set P to 0.5 because the median is the 50th percentile.

With six rows, we can substitute those values into the formula.

1 + 0.5 * (6 - 1)
1 + 2.5
3.5

Position 3.5 falls halfway between rows three and four. We therefore look at the values stored in those two positions and calculate the midpoint between them, which produces the continuous median of 38.5.

Five rows produce a different result because the percentile position falls directly on a row.

1 + 0.5 * (5 - 1)
1 + 2
3

Position 3 points directly to the third value. If our ordered values are 18, 25, 31, 46, and 120, we return 31 because no interpolation is needed.

Discrete percentile behavior follows a different rule. Rather than creating a value between neighboring rows, it returns a value already present in the ordered data. The selected value is the first ordered value whose cumulative position reaches the requested percentile.

For the six-value set containing 18, 25, 31, 46, 120, and 200, a continuous 50th percentile gives us 38.5 because the center lies between 31 and 46. A discrete percentile does not create 38.5 between those rows and instead returns an existing value from the ordered set.

That distinction becomes more relevant when interpolation has a natural numeric meaning. Prices, salaries, durations, and measurements can reasonably produce a midpoint that was never directly recorded because the values exist on a numeric scale. Ordered categories can call for discrete percentile behavior because returning an existing category value can be more appropriate than producing something between two entries.

Both forms still begin with the same foundation. We order the values, locate the requested percentile position, then determine how to return the result from that location. Continuous calculation can interpolate between neighboring rows, while discrete calculation stays with a value already present in the ordered data.

Median Queries

Turning the median rules into SQL depends on the database product and the type of result we need. Some databases provide percentile functions that return the 50th percentile directly, while window functions let us number rows and calculate the center ourselves. Reports can also restart the calculation within categories such as departments or regions, which lets the same query return a median for every group rather than a single value across the full table.

Percentile Functions

PostgreSQL gives us percentile_cont, which calculates a continuous percentile from an ordered set of values. For a median, we request 0.5 because that represents the 50th percentile.

SELECT
    percentile_cont(0.5)
        WITHIN GROUP (ORDER BY amount) AS median_amount
FROM sales;

We pass 0.5 to percentile_cont and place amount inside the ORDER BY expression. WITHIN GROUP tells PostgreSQL which ordering belongs to the percentile calculation. If sales contains an odd number of non-null amounts, the result comes from the center value. With an even count, PostgreSQL interpolates between the two center values when needed. Null values in the ordered input are ignored by PostgreSQL percentile aggregates.

The ORDER BY inside WITHIN GROUP belongs to the percentile calculation itself and does not sort the final query output. That distinction becomes more relevant when a query returns several rows, but we can read it here as the instruction that determines how values are positioned before the percentile is found.

SQL Server also provides PERCENTILE_CONT, though its syntax follows a window-function form. We can request the same median like this:

SELECT DISTINCT
    PERCENTILE_CONT(0.5)
        WITHIN GROUP (ORDER BY amount)
        OVER () AS median_amount
FROM sales;

The 0.5 still represents the median, and ORDER BY amount still supplies the ordered numeric values. SQL Server adds OVER() because PERCENTILE_CONT operates as a window function there. Without DISTINCT, the calculated median appears on every qualifying row, which can help when the value needs to stay beside row-level data. When the result only needs the median itself, DISTINCT removes those repeated copies and leaves a single displayed value.

Oracle supports PERCENTILE_CONT as well and allows it as an aggregate or analytic calculation. Its continuous percentile calculation interpolates between neighboring values when the requested percentile falls between row positions.

Across these products, the calculation follows the same general idea. We request percentile 0.5, provide the numeric expression that needs to be ordered, and let the database return the center according to continuous percentile rules. Exact grammar changes between products, so SQL copied from PostgreSQL to SQL Server or Oracle usually needs syntax changes even when the calculation itself stays the same.

Grouped Medians

Reports frequently need a median within each category rather than across every row. Department salaries, regional order totals, response times by service, or monthly invoice values all follow this idea. We divide rows into groups, then calculate the median independently inside each group.

PostgreSQL can pair percentile_cont with GROUP BY. If our sales table contains a region column, we can calculate a regional median like this:

SELECT
    region,
    percentile_cont(0.5)
        WITHIN GROUP (ORDER BY amount) AS median_amount
FROM sales
GROUP BY region
ORDER BY region;

We first group the rows by region. PostgreSQL then evaluates the percentile calculation against the amount values belonging to each region rather than against the full table. ORDER BY region at the end controls the displayed row order, while ORDER BY amount inside WITHIN GROUP controls the ordering needed for each median.

If Eau Claire has amounts of 20, 30, and 80, its median is 30. If Madison has 10, 40, 60, and 100, its center falls between 40 and 60, producing 50. Each region gets its own row count and center position because the aggregation restarts for every group.

SQL Server handles grouped percentile calculations through PARTITION BY inside the OVER clause rather than an ordinary GROUP BY for PERCENTILE_CONT.

SELECT DISTINCT
    region,
    PERCENTILE_CONT(0.5)
        WITHIN GROUP (ORDER BY amount)
        OVER (PARTITION BY region) AS median_amount
FROM sales
ORDER BY region;

PARTITION BY region divides the rows into regional windows. We still order amount within each partition, then SQL Server returns the 50th percentile for that region. DISTINCT removes repeated copies when the report only needs a single median row per region.

Leaving DISTINCT out gives us a different reporting layout:

SELECT
    region,
    amount,
    PERCENTILE_CONT(0.5)
        WITHIN GROUP (ORDER BY amount)
        OVER (PARTITION BY region) AS regional_median
FROM sales
ORDER BY region, amount;

Every sale remains in the result, and the regional median appears beside every row from the same partition. That format can help when we later compare an individual amount with the median for its region.

Grouped medians depend on the grouping boundary we choose. Changing the partition from region to department, product_category, or a different column changes which rows participate in each median while leaving the percentile rule itself unchanged.

Ranking Without Percentile Functions

Window functions can calculate a median without PERCENTILE_CONT. We can number the ordered rows, count how many rows belong to the group, retain the middle position or middle pair, then average the retained values.

Starting with a single population lets us follow the row-position logic directly:

WITH ranked_sales AS (
    SELECT
        amount,
        ROW_NUMBER() OVER (ORDER BY amount) AS row_num,
        COUNT(*) OVER () AS row_count
    FROM sales
    WHERE amount IS NOT NULL
)
SELECT
    AVG(amount * 1.0) AS median_amount
FROM ranked_sales
WHERE ABS(row_num - (row_count + 1) / 2.0) < 1;

The common table expression gives every non-null amount an ordered row number and stores the total row count beside it. ROW_NUMBER() starts at 1, while COUNT(*) OVER() returns the same population count for every row in that window.

Next, the filter calculates the mathematical center through (row_count + 1) / 2.0. With five rows, that expression returns 3, so only row three remains. With six rows, it returns 3.5, which keeps rows three and four because both are less than one position away from the center. AVG then returns the single center value for an odd count or the average of the center pair for an even count.

Multiplying amount by 1.0 moves the expression into decimal arithmetic in SQL dialects where integer arithmetic could discard the fractional portion of the result. That keeps a median such as 38.5 from being reduced to an integer when the surrounding numeric types would otherwise do so.

Grouped ranking follows the same logic but restarts the numbering and count for each category:

WITH ranked_sales AS (
    SELECT
        region,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY amount
        ) AS row_num,
        COUNT(*) OVER (
            PARTITION BY region
        ) AS row_count
    FROM sales
    WHERE amount IS NOT NULL
)
SELECT
    region,
    AVG(amount * 1.0) AS median_amount
FROM ranked_sales
WHERE ABS(row_num - (row_count + 1) / 2.0) < 1
GROUP BY region
ORDER BY region;

Both window expressions now contain PARTITION BY region. We number rows independently within each region, then calculate that region’s row count before filtering for its center. The outer GROUP BY region combines the retained center row or center pair into the final median.

We can also inspect the intermediate positions before applying the final calculation:

SELECT
    region,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount
    ) AS row_num,
    COUNT(*) OVER (
        PARTITION BY region
    ) AS row_count
FROM sales
WHERE amount IS NOT NULL
ORDER BY region, amount;

If a region contains four amounts, the result gives them row numbers 1 through 4 and repeats a row_count of 4 beside every row. We can then see why positions 2 and 3 form the center pair. With five amounts, numbering runs from 1 through 5, leaving position 3 at the center.

Duplicate numeric values do not break this median calculation. ROW_NUMBER() gives tied rows different position numbers, but equal numeric values remain equal regardless of which tied row receives the earlier number. Their internal order therefore does not change the numeric median when the ordering expression is the value being measured.

MySQL 8.4 provides ROW_NUMBER() and the window clauses needed for this ranking technique. Its window-function set includes ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, and PERCENT_RANK, which gives us the pieces needed to build the row-position method.

Database Differences

Exact median syntax varies enough between database products that queries usually need product-specific forms. PostgreSQL 18 provides percentile_cont and percentile_disc as ordered-set aggregates. The continuous form can interpolate between adjacent values, while the discrete form returns an input value from the ordered set. Null values in their aggregated input are ignored.

SQL Server has PERCENTILE_CONT with WITHIN GROUP and an OVER clause. Current SQL Server releases also provide APPROX_PERCENTILE_CONT when an approximate percentile is acceptable. That function is available in SQL Server 2022 and later and returns an approximate percentile rather than the exact result produced by PERCENTILE_CONT.

Its syntax differs from the windowed exact form:

SELECT
    region,
    APPROX_PERCENTILE_CONT(0.5)
        WITHIN GROUP (ORDER BY amount) AS approximate_median
FROM sales
GROUP BY region;

We group rows by region, then request the approximate 50th percentile from each regional set. The function can reduce memory demand on large data sets, though the returned percentile remains approximate and should not replace an exact median when the report requires the exact mathematical result. Null values are ignored by APPROX_PERCENTILE_CONT.

Oracle provides PERCENTILE_CONT and PERCENTILE_DISC as aggregate and analytic functions. Its continuous form applies linear interpolation between ordered rows when the percentile position falls between them, while the discrete form returns a value already present in the ordered set.

MySQL 8.4 includes window functions like ROW_NUMBER, RANK, DENSE_RANK, NTILE, CUME_DIST, and PERCENT_RANK, but it does not provide PERCENTILE_CONT in its window-function set. The row-number method therefore gives us an exact median calculation on MySQL 8.4 while following the same center-row logic covered earlier.

The mathematical calculation stays familiar across these database products, but the SQL that expresses it changes. PostgreSQL can aggregate directly with percentile_cont, SQL Server places exact PERCENTILE_CONT inside a window, Oracle supports aggregate and analytic forms, and MySQL can derive the result through row numbering. Keeping those dialect differences in view helps prevent valid SQL from being copied into a database whose grammar expects a different form.

Conclusion

Median calculations come down to ordering values, locating the center, and handling odd or even row counts correctly. SQL can reach that result through percentile functions or row ranking, while grouped calculations repeat the same logic within each category. The exact syntax changes across database products, but the core mechanics stay the same from the first ordered value through the final median.

1. PostgreSQL Aggregate Functions

2. SQL Server PERCENTILE_CONT

3. SQL Server APPROX_PERCENTILE_CONT

4. Oracle PERCENTILE_CONT

5. MySQL Window Function Reference

Share Alexander Obregon's Substack

Routine maintenance can pile up quickly when an application depends on manual steps, command-line scripts, or reminders someone has to remember at the right time. Spring Boot can bring recurring jobs into the application through scheduling support, letting methods run after a fixed delay, at a fixed rate, or at specific times through cron expressions. Time zones can be assigned directly to cron schedules, which keeps clock-based jobs tied to the intended region rather than the server’s local time. That makes scheduling a good option for removing expired records, refreshing stored data, generating reports, or checking for unfinished processing, while giving you control over how each run is timed and what happens if an earlier run is still active.

Subscribe now

Starting Scheduled Jobs

Spring’s annotation-based scheduler lets recurring application jobs live beside the services they call instead of depending on someone to launch a script at the right time. We first want to activate scheduling support, after which Spring can register methods marked with @Scheduled as the application context starts. From there, each job can follow a timing rule that matches what it needs to do, while the method itself stays focused on the operation being performed.

Turn On Scheduling

Before Spring can register @Scheduled methods, scheduling support has to be active in the application context. @EnableScheduling handles that, and the main Spring Boot application class is a common place for the annotation because it keeps the scheduling configuration near the application entry point.

package com.example.scheduling;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;

@SpringBootApplication
@EnableScheduling
public class SchedulingApplication {

    public static void main(String[] args) {
        SpringApplication.run(SchedulingApplication.class, args);
    }
}

We add @EnableScheduling beside @SpringBootApplication, so Spring checks its managed beans for scheduled methods during application startup. SpringApplication.run() creates the application context as usual, while @EnableScheduling adds the scheduling support needed for Spring to register methods carrying @Scheduled.

That managed-bean detail is important because placing @Scheduled on an object created manually with new does not place that object under Spring’s scheduling management. Classes containing scheduled methods are commonly registered through annotations such as @Component or @Service, which lets Spring create the instance and inspect it during startup.

The scheduler belongs to the running application process, so scheduled methods stop when the application stops. Spring does not store missed executions in a durable queue for later recovery, which means a basic scheduled method should be treated as part of the application’s active runtime rather than as a persistent job record. If the process is offline when a scheduled time passes, that missed execution is not automatically replayed after startup.

Create the First Job

Scheduled methods are commonly kept small so they can call a service that contains the application logic. This keeps timing concerns in the scheduling class while database access, file processing, HTTP calls, and other application behavior stay in the service that already owns them.

package com.example.scheduling;

import java.util.concurrent.TimeUnit;

import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

@Component
public class MaintenanceJobs {

    @Scheduled(fixedDelay = 15, timeUnit = TimeUnit.MINUTES)
    public void removeExpiredRecords() {
        System.out.println("Removing expired records");
    }
}

Spring registers MaintenanceJobs because @Component makes the class a managed bean. The @Scheduled annotation tells Spring when removeExpiredRecords() should be called, while timeUnit = TimeUnit.MINUTES lets us express the interval in minutes instead of converting fifteen minutes into milliseconds. Without timeUnit, Spring treats fixed delay, fixed rate, and initial delay values as milliseconds.

The method itself takes no arguments, which lets Spring call it without having to supply input values. Returning void also matches the common synchronous form because the scheduler is interested in invoking the method at the configured time rather than receiving a value back from it.

As the operation grows beyond a small demonstration, we can move the application logic into a service and leave the scheduling method responsible for triggering that service.

package com.example.scheduling;

import java.util.concurrent.TimeUnit;

import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

@Component
public class MaintenanceJobs {

    private final ExpiredRecordService expiredRecordService;

    public MaintenanceJobs(ExpiredRecordService expiredRecordService) {
        this.expiredRecordService = expiredRecordService;
    }

    @Scheduled(fixedDelay = 15, timeUnit = TimeUnit.MINUTES)
    public void removeExpiredRecords() {
        expiredRecordService.removeExpiredRecords();
    }
}

Spring supplies ExpiredRecordService through the constructor when it creates MaintenanceJobs, and the scheduled method then calls removeExpiredRecords() at the configured interval. That keeps the scheduling class centered on timing while the service handles the actual record removal. Method names are worth keeping specific because scheduled jobs usually run without someone directly triggering them. Names such as removeExpiredRecords, refreshCatalogCache, or recalculateDailyTotals make logs and stack traces more informative than a generic name such as run.

Spring also supports reactive scheduled methods, but regular synchronous jobs can stay with the no-argument form shown above. For the maintenance jobs covered here, that keeps the execution flow easy to follow while we focus on how Spring determines the next run time.

Run With a Fixed Delay

With fixedDelay, Spring calculates the next execution from the completion of the previous execution. The waiting period begins after the method finishes, so the amount of processing time naturally affects when the following run begins.

@Scheduled(
    initialDelay = 1,
    fixedDelay = 10,
    timeUnit = TimeUnit.MINUTES
)
public void removeExpiredSessions() {
    sessionService.removeExpiredSessions();
}

We give the first execution a one-minute startup delay through initialDelay, then tell Spring to wait ten minutes after every completed run through fixedDelay. If removeExpiredSessions() takes three minutes, Spring waits until those three minutes are finished before starting the ten-minute delay. The next execution therefore begins about thirteen minutes after the previous start.

Longer processing follows the same rule, if session removal takes twenty minutes, Spring waits for those twenty minutes to finish and then waits the full ten-minute delay before calling the method again. Execution duration becomes part of the overall spacing between starts because the delay clock does not begin until the previous call has returned.

This timing model works well for recurring operations where some breathing room after completion is desirable. Polling a data source, deleting expired sessions, checking an import directory, or refreshing data from a remote service can all benefit from a full pause between runs because the next execution does not try to preserve an earlier start schedule.

initialDelay affects only the first execution after scheduling begins. Later calls follow the configured fixedDelay, so the startup pause does not get added again after every run.

Fixed delay can also make timing easier to reason through when processing duration changes. We always have the same relationship between executions, which is completion first, followed by the configured delay, followed by the next start.

Run at a Fixed Rate

With fixedRate, Spring bases the schedule on planned start times rather than waiting for the previous execution to finish and then beginning a new delay. If the rate is five minutes, the scheduler targets five-minute intervals from the established schedule:

@Scheduled(
    initialDelay = 30,
    fixedRate = 5,
    timeUnit = TimeUnit.MINUTES
)
public void refreshInventoryTotals() {
    inventoryService.refreshTotals();
}

We delay the first execution by thirty minutes, then give later executions a five-minute fixed rate. If refreshInventoryTotals() finishes well before the next planned start, the remaining time passes before Spring calls it again. If processing takes longer than five minutes, a later execution can begin late because the scheduled start time has already passed.

With Spring Boot’s regular ThreadPoolTaskScheduler, successive executions of the same fixed-rate job do not run concurrently. If one execution is still running when the next scheduled start arrives, that later call waits rather than running beside the earlier call. With virtual threads enabled, Spring Boot uses SimpleAsyncTaskScheduler, which can start fixed-rate executions on separate threads.

The difference from fixed delay becomes easier to see when the method takes a noticeable amount of time. If a fixed-rate job begins at 2 PM with a five-minute period, its planned schedule continues around 2:05, 2:10, 2:15, and later intervals. If an execution lasts two minutes, the remaining time before the next planned start is about three minutes.

With a five-minute fixed delay, that same two-minute execution would finish first and then begin a full five-minute wait. The next start would come about seven minutes after the earlier start instead of following the original five-minute cadence. Fixed rate is a good match for recurring activity where planned start cadence is more important than leaving a full pause after completion. Refreshing counters, sampling internal values, or recalculating stored data at regular intervals can follow that timing model when the processing normally finishes comfortably inside its configured period.

Fixed delay and fixed rate therefore answer different timing needs. Fixed delay measures from completion, while fixed rate follows planned start intervals, and recognizing that difference early keeps scheduled behavior from becoming surprising later.

Controlling Job Timing

Clock-based scheduling gives recurring jobs a precise calendar instead of relying only on elapsed intervals. We can tell Spring to call a method at a particular minute, hour, weekday, or combination of calendar fields, then assign a time zone so the schedule follows the intended region. Timing also deserves closer attention as an application gains more scheduled jobs because scheduler threads, repeated declarations, asynchronous execution, and multiple application instances can affect when calls begin and how they interact.

Schedule With Cron

Cron expressions are a good choice when a job needs to follow the clock rather than wait for a repeating interval. Spring cron expressions contain six fields ordered as second, minute, hour, day of month, month, and day of week. Remembering the seconds field helps when moving between Spring and cron formats that contain five fields because leaving it out changes the meaning of the expression.

For a nightly archive job that should begin at 2:30 AM, we can place the full schedule directly on the method:

@Scheduled(cron = "0 30 2 * * *")
public void archiveOldEvents() {
    eventService.archiveOldEvents();
}

Reading from left to right, the first 0 selects second zero, 30 selects minute thirty, and 2 selects the 2 AM hour. The remaining wildcard fields allow every day of the month, every month, and every day of the week, so Spring schedules archiveOldEvents() for 2:30 AM each day.

Weekday schedules can narrow the final field to the days we want. If a report should be generated at 6 AM from Monday through Friday, we can express the schedule like this:

@Scheduled(cron = "0 0 6 * * MON-FRI")
public void createMorningReport() {
    reportService.createMorningReport();
}

We still have six fields, but the final field now limits execution to Monday through Friday. The first three values select second zero, minute zero, and hour six, while the two middle calendar fields allow every day of the month and every month. Spring therefore calls createMorningReport() at 6 AM on weekdays.

Clock-based jobs can also carry a defined time zone when a local business time needs to stay consistent across deployments. The zone attribute on @Scheduled tells Spring which region should be applied when it evaluates the cron expression:

@Scheduled(
    cron = "0 30 2 * * *",
    zone = "America/Chicago"
)
public void archiveOldEvents() {
    eventService.archiveOldEvents();
}

We now tie the 2:30 AM execution to America/Chicago, so Spring follows that region rather than relying on the default time zone of the host process. Region IDs such as America/Chicago also carry daylight saving time rules, which makes them appropriate for schedules tied to local civil time rather than a fixed UTC offset.

Keeping the cron expression outside Java can help when environments need different run times. Spring resolves property placeholders inside @Scheduled, so we can move both the expression and zone into application configuration:

@Scheduled(
    cron = "${jobs.archive.cron}",
    zone = "${jobs.archive.zone}"
)
public void archiveOldEvents() {
    eventService.archiveOldEvents();
}

The matching application.properties entries can hold the actual values:

jobs.archive.cron=0 30 2 * * *
jobs.archive.zone=America/Chicago

We leave the Java method unchanged while configuration supplies its clock time and region. Development, staging, and production can then carry different values without editing the scheduling method for every environment.

Spring also accepts cron macros such as @hourly, @daily, @weekly, @monthly, and @yearly. These macros replace common six-field expressions when their built-in timing matches what we need:

@Scheduled(cron = "@hourly")
public void refreshSummaryData() {
    summaryService.refresh();
}

We tell Spring to call refreshSummaryData() at the start of every hour, which corresponds to 0 0 * * * *. More specific schedules still benefit from an explicit six-field expression, such as a weekday run at 6:15 AM or a nightly operation tied to a named region.

Cron timing also affects what happens when an execution lasts long enough to pass a later cron time. With the regular thread-pool scheduler, Spring’s cron trigger calculates its next execution from completion of the preceding run, so a cron time that passes while that run is still active can be skipped rather than replayed immediately afterward. When virtual threads are active, Spring Boot uses SimpleAsyncTaskScheduler, which can start cron executions on new threads, so overlap behavior depends on the scheduler being used.

Prevent Unexpected Overlap

Concurrency becomes more relevant when several scheduled methods share the same application process or when more than one trigger can reach the same operation. With virtual threads turned off, Spring Boot starts with a scheduling pool size of 1, so a long-running scheduled method can occupy the scheduler thread while a different scheduled method is ready to begin.

The scheduler pool can be increased through configuration:

spring.task.scheduling.pool.size=4

We now give Spring four scheduler threads instead of the default single thread, allowing different scheduled methods to execute at the same time when capacity remains. The pool-size property does not control scheduler concurrency when Spring Boot virtual threads are active, so that configuration applies to the regular platform-thread scheduler.

More scheduler threads can reduce delays between unrelated jobs, but they also allow those jobs to reach the database, filesystem, remote services, or shared application state at the same time. Increasing the pool therefore changes more than execution capacity because operations that previously ran one after the other can now run concurrently. Repeated @Scheduled declarations deserve closer attention because Spring processes every declaration independently. The annotation is repeatable, so the same method can carry more than one schedule, and those schedules can reach the method independently if their timing becomes close enough.

We can protect a locally scheduled import with a ReentrantLock when several local triggers or other entry points can reach the same operation:

package com.example.scheduling;

import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

@Component
public class ImportJobs {

    private final Lock importLock = new ReentrantLock();
    private final UpdateService updateService;

    public ImportJobs(UpdateService updateService) {
        this.updateService = updateService;
    }

    @Scheduled(cron = "0 0 8 * * MON-FRI")
    @Scheduled(cron = "0 0 12 * * MON-FRI")
    public void importUpdates() {
        if (!importLock.tryLock()) {
            return;
        }

        try {
            updateService.importUpdates();
        } finally {
            importLock.unlock();
        }
    }
}

We give importUpdates() two independent weekday schedules, with calls planned for 8 AM and noon. Before starting the import, tryLock() checks the local lock. If an earlier invocation still owns it, the later call returns without starting a second import. The finally block releases the lock after updateService.importUpdates() finishes, including cases where that service call throws an exception.

That protection reaches only the current JVM. If the same Spring Boot application runs in several instances, every instance has its own scheduler and its own ReentrantLock, so each instance can still start the import independently. Jobs that must execute a single time across several application instances need coordination shared by those instances, such as a database-backed lock, distributed locking mechanism, or scheduler built for clustered execution.

Asynchronous execution changes the timing boundary as well. If a scheduled method sends processing to an asynchronous executor and returns before that processing finishes, the scheduler treats the method invocation as completed while the asynchronous operation remains active. Later triggers can then submit more processing before the earlier asynchronous call has finished, so operations that must remain single-flight need protection around the processing itself rather than only around the scheduling callback.

Conclusion

Spring Boot scheduling comes down to choosing how each recurring job should run, then matching that timing to @Scheduled. Fixed delay waits from the end of the previous run, fixed rate follows planned start intervals, and cron expressions handle clock-based schedules with optional time zones, while scheduler configuration and locking help keep overlapping executions from creating duplicate processing.

1. Spring Framework Task Execution and Scheduling

2. Spring @Scheduled Documentation

3. Spring CronExpression Documentation

4. Spring CronTrigger Documentation

5. Spring Boot Application Properties

6. Java ScheduledThreadPoolExecutor Documentation

Share Alexander Obregon's Substack