Projection Queries: A Way to Optimize Data Traffic
JPA provides several solutions for projection queries and DTO mapping. It is up to us to choose the most appropriate solution for each of our use cases.
Join the DZone community and get the full member experience.Join For Free
In our modern, highly concurrent world, enterprise application developers have to deal with new challenges like huge data volumes, diversity of clients, and permanently changing business requirements. Now, it is a usual case when a microservice application has to serve various clients, and some of them are other microservices. These factors imply higher requirements for controlling data traffic. We cannot afford to send any excessive data and we need to respond to each request with data well-tailored for this particular client.
One option of customizing data traffic is the usage of projection queries; that is, queries that return a projection of domain objects. Almost all enterprise applications use some kind of ORM technology, and JPA is a standard way for its implementation. So, let's see how we can implement projection queries based on JPA 2.2 specification.
Suppose we are to implement a collection management online application. The domain system is the following.
Listing 1: CollectionItem.java
Listing 2: Image.java
Note: In web applications, it is often more efficient to store images in the file system and store the paths in the database.
According to the specifications, we need to display collection items (probably filtered by some search criteria) in a table with the following attributes included: id, name, summary, year, price, country, smallimage. Also, we need to display the following data for each selected item: name, description, year, country, price, image. To address these requirements, we can implement data transfer objects (DTO).
Listing 3: CollectionItemDto.java
Listing 4: CollectionItemDetailsDto.java
Now, we should find a way to populate these DTOs with data from the database. The modern JPA version provides support for mapping table data onto custom objects in JPQL, Criteria API, and for native queries.
1. Projection Queries in JPQL
In JPQL, we can use the new keyword to define the DTO in the body of the query. So, we create the following named query for filtering collection items by country:
Listing 5: Named JPQL projection query for retrieving collection item data
We use this query in the corresponding method of CollectionItemDao.
Listing 6: CollectionItemDao.findByCountry() method with the usage of named JPQL projection query
For retrieving details of the selected collection item, we define the following named query.
Listing 7: Named JPQL projection query for retrieving details of the selected collection item
And use it in CollectionItemDao the same way as the findByCountry query (see Listing 6):
Listing 8: CollectionItemDao.getDetails() method with the usage of named JPQL projection query
For advanced search, when customers can choose parameters by which to filter collection items, we can create query dynamically, with the usage of Criteria API.
2. Projection Queries in Criteria API
In the Criteria API, we can use the construct() method of CriteriaBuilder. So, we implement the following dynamic search method in CollectionItemDao:
Listing 9: Dynamically created query
This code assumes usage of metamodel objects, which are typically generated while building the application, e.g. with the usage of a Maven processor plugin.
Sometimes, we want to use native SQL queries instead of JPQL or criteria queries, e.g. for performance tuning related to a specific database.
3. Native SQL Projection Queries
To customize data mapping in native SQL queries, we use the @SqlResultSetMapping annotation. Suppose, for some reason, we want to use a native query for implementing the CollectionItemDao.findByCountry() method. In this case, we can create the SQL query and the SqlResultSetMapping definition as follows.
Listing 10: CollectionItemDao.findByCountry() method with the usage of native SQL projection query
Then, we use this stuff in the DAO method.
Listing 11: Implementation of CollectionItemDao.findByCountry() method with the usage of native SQL projection query
As we can see, JPA provides several solutions for projection queries and DTO mapping. It is up to us to choose the most appropriate solution for each of our use cases.
Opinions expressed by DZone contributors are their own.