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 ?
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.
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.
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 class
CustomerDetailsDTO, notice how we implement the custom logic to create the
OrderDTO using a Spring bean utility class
The SpEL expression refers to the bean
MapperUtility which invokes the
buildOrderDTO(.,.) method and forwards the projection
totalAmount as a method parameters. The custom method then returns an instance of
OrderDTO making it a nested object in the
Let’s define standard Spring Data JPA repositories for the Customer model.
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.
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
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.