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.

Sergiy Pylypets

CORE ·

Updated Feb. 17, 21 · Tutorial

Likes (7)

Comment

Save

21.9K Views

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

     Java 
   
x
 
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Lob;
import javax.persistence.OneToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
@Entity
@Table(name="COLLECTION_ITEMS")
public class CollectionItem {
   @Id
   @SequenceGenerator(name = "CollectionItems_Generator", sequenceName = "CollectionItems_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CollectionItems_Generator")
   long id;
   BigDecimal price;
   @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE}, orphanRemoval=true)
   @JoinColumn(name="small_image", unique=true)
   Image smallImage;
   @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE}, orphanRemoval=true)
   @JoinColumn(name="image", unique=true)
   Image image;
   String name;
   String summary;
   @Lob
   String description;
   Short year;
   String country;
   @ElementCollection
   @CollectionTable(name="ITEMS_TOPICS", joinColumns=@JoinColumn(name="ITEM_ID"))
   @Column(name="TOPIC")
   Set<String> topics;
Getters and setters
. . .

Listing 2: Image.java

     Java 
   
xxxxxxxxxx
 
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
@Entity
@Table(name="IMAGES")
public class Image implements java.io.Serializable {
   private static final long serialVersionUID = 1L;
   @Id
   @SequenceGenerator(name = "Images_Generator", sequenceName = "Images_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "Images_Generator")
   private long id;
   @Lob
   private String content;
Getters and setters
.  .  .

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

     Java 
   
xxxxxxxxxx
 
import java.math.BigDecimal;
public class CollectionItemDto {
   long id;
   String name;
   String summary;
   Short year;
   String country;
   String smallImage;
   BigDecimal price;
   public CollectionItemDto() {}
   public CollectionItemDto(long id, String name, String summary, Short year, String country, BigDecimal price, String smallImage
) {
      this.id = id;
      this.name = name;
      this.summary = summary;
      this.year = year;
      this.country = country;
      this.price = price;
      this.smallImage = smallImage;
   }
   public long getId() {
      return id;
   }
   public String getName() {
      return name;
   }
Other getters
. . . .
}

Listing 4: CollectionItemDetailsDto.java

     Java 
   
xxxxxxxxxx
 
import java.math.BigDecimal;
public class CollectionItemDetailsDto {
   String name;
   String description;
   Short year;
   String country;
   String image;
   BigDecimal price;
   public CollectionItemDetailsDto() {}
   public CollectionItemDetailsDto(String name, String description, Short year, String country, BigDecimal price, String image) {
      this.name = name;
      this.description = description;
      this.year = year;
      this.country = country;
      this.price = price;
      this.image = image;
   }
   public String getName() {
      return name;
   }
   public String getDescription() {
      return description;
   }
Other getters
. . . .
}

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

     Java 
   
xxxxxxxxxx
 
@NamedQuery(name="findByCountry", query="SELECT new com.collections.entity.dto.CollectionItemDto(st.id, st.name, st.summary, st.year, st.country, st.price, st.smallImage.content) FROM CollectionItem st WHERE st.country = :country")
@Entity
@Table(name="COLLECTION_ITEMS")
public class CollectionItem {
   public static final String FIND_BY_COUNTRY = "findByCountry";
. . .

We use this query in the corresponding method of CollectionItemDao.

Listing 6: CollectionItemDao.findByCountry() method with the usage of named JPQL projection query

     Java 
   
xxxxxxxxxx
 
public Stream<CollectionItemDto> findByCountry(String country, int from, int rows) {
return em.createNamedQuery(CollectionItem.FIND_BY_COUNTRY, CollectionItemDto.class)
 .setParameter("country", country)
           .setFirstResult(from)
           .setMaxResults(rows)
           .getResultStream();
}

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

     Java 
   
xxxxxxxxxx
 
@NamedQuery(name="getDetailsById", query="SELECT new com.collections.entity.dto.CollectionItemDetailsDto(st.name, st.description, st.year, st.country, st.price, st.image.content) FROM CollectionItem st WHERE st.id = :id")

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

     Java 
   
xxxxxxxxxx
 
public Stream<CollectionItemDetailsDto> getDetails(long stampId) {
return em.createNamedQuery(CollectionItem.GET_DETAILS_BY_ID, CollectionItemDetailsDto.class)
 .setParameter("id", stampId)
 .getResultStream();
}

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

     Java 
   
xxxxxxxxxx
 
public Stream<CollectionItemDto> search(String country, short year, String ... topics) {
   CriteriaBuilder cb = em.getCriteriaBuilder();
   CriteriaQuery<CollectionItemDto> cq = cb.createQuery(CollectionItemDto.class);
   Root<CollectionItem> stamp = cq.from(CollectionItem.class);
   Join<CollectionItem, Image> smallImage = stamp.join(CollectionItem_.smallImage);
   Predicate topicFilter = cb.conjunction();
   for(String topic : topics) {
      topicFilter = cb.and(topicFilter, cb.isMember(topic, stamp.get(CollectionItem_.topics)));
   }
   cq.where(cb.equal(stamp.get(CollectionItem_.country), country),
      cb.equal(stamp.get(CollectionItem_.year), year),topicFilter
   );
   cq.select(cb.construct(CollectionItemDto.class,
      stamp.get(CollectionItem_.id),
      stamp.get(CollectionItem_.name),
      stamp.get(CollectionItem_.summary),
      stamp.get(CollectionItem_.year),
      stamp.get(CollectionItem_.country),
      stamp.get(CollectionItem_.price),
      smallImage.get(Image_.content))).distinct(true);
   TypedQuery<CollectionItemDto> tq = em.createQuery(cq);
   return tq.getResultStream();
}

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

     Java 
   
xxxxxxxxxx
 
@SqlResultSetMapping(name="findByCountryResultSet", classes={@ConstructorResult(targetClass=com.collections.entity.dto.CollectionItemDto.class, columns={
@ColumnResult(name="id", type=Long.class),
@ColumnResult(name="name"),
@ColumnResult(name="summary"),
@ColumnResult(name="year"),
@ColumnResult(name="country"),
@ColumnResult(name="price"),
@ColumnResult(name="content", type=String.class)
})})
@Entity
@Table(name="COLLECTION_ITEMS")
public class CollectionItem {
   public static final String FIND_BY_COUNTRY_SQL = "SELECT ci.id, ci.name, ci.summary, ci.year, ci.country, ci.price, img.content FROM collection_items ci JOIN images img WHERE ci.small_image=img.id AND ci.country = ?";
   public static final String FIND_BY_COUNTRY_RESULTSET = "findByCountryResultSet";
. . . .

Then, we use this stuff in the DAO method.

Listing 11: Implementation of CollectionItemDao.findByCountry() method with the usage of native SQL projection query

     Java 
   
xxxxxxxxxx
 
public Stream<CollectionItemDto> findByCountry(String country, int from, int rows) {
return em.createNativeQuery(CollectionItem.FIND_BY_COUNTRY_SQL, CollectionItem.FIND_BY_COUNTRY_RESULTSET)
   .setParameter(1, country)
   .setFirstResult(from)
   .setMaxResults(rows)
   .getResultStream();
}

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.

Database Data (computing) Listing (computer) Java (programming language) application

Opinions expressed by DZone contributors are their own.

Related

Trending