Spring Data JPA Projection support for native queries

In this blog I wanted to talk about usage of Spring Data JPA Projections, while building microservices which execute complex native SQL queries. Vlad Mihalcea’s blog talks about this in brief and covers projections using the JPA EntityManager. Here I focus mainly on using projections while executing native SQL queries with Spring Data JPA. Projecting nested objects and computing values using SpEL.

To understand the blog better it would help if you have a basic understanding of Spring Data JPA. You can also refer the sample project, used to explain the concept here.

What are Projections ?

Image 1. Component Diagram for Microservice

Imagine you have a nicely designed microservice with 3 main layers (as described in Image-1). The API layer exposing the operations over the web, a Service Layer which contains the business logic and a Domain layer that uses Repositories to handle, fetching and persisting Domain Entities from your database.

When the service layer requests for data, the Domain layer should only concern itself with the querying of entities and it is the job description of the Data Transfer Object (DTO) to be the carrier of state across layers. But then the DTO needs to be populated with data somewhere ! Thats where projections are needed. Projections in simple terms is a way to create and populate DTO’s from database entities / models.

Example

Let’s build the example considering a simple data model (referenced here) and a simple native query.

The Data Model

Let’s have a look at the Customer entity class:

and Order entity:

The Native Query

We now define our “named-native-query” in the orm.xml, the query lists all customers, whether they placed any order or not. Notice the alias for every field in the select query. This is important when we build the projections object.

The DTO’s

Next let’s create an interface based projection class, which will act as the DTO.

The projections interface CustomerDetailsDTO has accessor methods that match all the properties of the select query being fetched. Example: The native query being executed fetches the field “id” and assigns it to an alias “customerId”. This alias exactly maps the accessor method getCustomerId() defined in the DTO.

Projection interfaces can also be used to compute new values by using the @Value annotation, as shown in the CustomerDetailsDTO snippet above. The entity backing the projection is available in the target variable. In the above example the field customerName is constructed by combining the First Name and Last Name fetched by the query execution.

Now we build the OrderDTO and utility to showcase how we can build nested projections.

In the classCustomerDetailsDTO, notice how we implement the custom logic to create the OrderDTO using a Spring bean utility class MapperUtility.

@Value("#{@mapperUtility.buildOrderDTO(target.orderNumber, target.totalAmount)}")

The SpEL expression refers to the bean MapperUtility which invokes the buildOrderDTO(.,.) method and forwards the projection orderNumber and totalAmount as a method parameters. The custom method then returns an instance of OrderDTO making it a nested object in the CustomerDetailsDTO.

The Repository

Let’s define standard Spring Data JPA repositories for the Customer model.

The class CustomerRepository, defines a method getCustomerDetails() to execute the named query ‘customerEntity.getCustomerDetails’ defined in the orm.xml. Notice the return type of the method a list of projected DTO’s.

The Service Layer

The service layer now merely calls the repository instance and returns the projected DTO.

The API Layer

Finally the API layer that helps triggering the underlying code, invokes the service method and returns the projected DTO to the caller.

The Result

When invoked the REST endpoint returns the DTO object, along with the nested ‘order’ field.

Passing Parameters to the Native Query

The “named-native-query” in the orm.xml, can take in named parameters. The repository uses the Param annotation to pass values to the named parameters. As an example lets consider filtering the above query to fetch orders for a given ‘firstName’, the query with named parameter would be as below.

And the repository passing the value would be

Final Thoughts

Spring Data REST projections can be used to create custom views of our models. This help us reduce boiler plate code and makes the code more readable. Again You can refer the sample project here.