Spring Data JPA Projection support for native queries

Sohan Ganapathy
May 16, 2019 · 4 min read

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 , 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 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 defined in the DTO.

Projection interfaces can also be used to compute new values by using the annotation, as shown in the snippet above. The entity backing the projection is available in the variable. In the above example the field 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, notice how we implement the custom logic to create the using a Spring bean utility class .

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

The SpEL expression refers to the bean which invokes the method and forwards the projection and as a method parameters. The custom method then returns an instance of making it a nested object in the .

The Repository

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

The class , defines a method to execute the named query ‘customerEntity.getCustomerDetails’ defined in the . 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 , 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.

The Startup

Medium's largest active publication, followed by +585K people. Follow to join our community.

Sohan Ganapathy

Written by

Software Architect | Full-Stack Engineer

The Startup

Medium's largest active publication, followed by +585K people. Follow to join our community.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade