JPA projections

The goal of Spring Data JPA is to greatly simplify the creation of data access layers by leveraging the Object-Relational Mapping (ORM) capabilities that JPA offers. You are already familiar with how to use JPA to map your Java or Kotlin classes to SQL tables and the other way around. However, sometimes when you query the database, there's no need to get the whole entity or list of entities. You might only need some columns or even a result set that doesn't match the entity at all. In this topic, you will learn about a mechanism that helps you manage these issues easily and efficiently.

What are JPA projections?

In the context of JPA, a projection is a selection of certain columns in the result set of a query. Instead of fetching all fields of an entity, you can use projections to select only the ones you need. This technique can drastically improve the performance of your application by reducing the amount of data being transferred from the database to your application.

JPA projections are particularly useful in situations where:

1. Performance is a priority: If your entity features many columns or large data fields, fetching the entire entity could be inefficient, especially if you just need some fields.

2. Data transfer object (DTO) projection is required: At times, you may need a DTO comprised of different fields from various entities. Projections can be used to fetch this data in one query.

3. To avoid lazy loading problems: If you want to prevent lazy loading or the N+1 select issue, you could use a projection to fetch the needed data in one shot.

We will explore some of these scenarios in the sections to follow.

Demo project

Let's set up a simple playground to learn how to use projections. We must have the following minimum set of dependencies:

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	runtimeOnly 'com.h2database:h2'
}

Assume we have an entity named SalesOrder:

@Entity
class SalesOrder {
    @Id
    @GeneratedValue
    private Long id;
    private String customer;
    private Integer amount;

    public SalesOrder() {
    }

    public SalesOrder(String customer, Integer amount) {
        this.customer = customer;
        this.amount = amount;
    }

    // getters and setters
}

To manage the entity, we need a repository:

interface SalesOrderRepository extends CrudRepository<SalesOrder, Long> {
    // custom repository methods go here...
}

Lastly, we need to populate the database with a handful of records:

@Component
class Runner implements CommandLineRunner {
    private final SalesOrderRepository repository;

    Runner(SalesOrderRepository repository) {
        this.repository = repository;
    }

    @Override
    public void run(String... args) {
        repository.saveAll(List.of(
                new SalesOrder("company 1", 2000),
                new SalesOrder("company 2", 1845),
                new SalesOrder("company 1", 200),
                new SalesOrder("company 3", 4961),
                new SalesOrder("company 1", 5000)
        ));
}

Now, everything is set to explore how projections work.

Types of JPA projections

There are three major types of projections in JPA:

Scalar Projections: These are the simplest form of projection where you select individual fields of your entities. The result is a list of Object[].

Let's illustrate the use of scalar projections by adding the following method to the SalesOrderRepository:

interface SalesOrderRepository extends CrudRepository<SalesOrder, Long> {

    @Query("SELECT so.customer, so.amount FROM SalesOrder so")
    List<Object[]> getScalarProjections();

    // other methods go here...
}

Here, we're selecting the customer and amount fields from the SalesOrder entity. The result is a list of Object[], where each object array contains the customer and the amount of a sales order. If we call this method and print the result set to the console:

repository.getScalarProjections().forEach(rs -> System.out.println(Arrays.toString(rs)));

it will show:

[company 1, 2000]
[company 2, 1845]
[company 1, 200]
[company 3, 4961]
[company 1, 5000]

If you need to utilize the result set, you have to change each value to the appropriate type and keep track of the column order in the query. Generally, this is similar to the use of the ResultSet from JDBC.

DTO Projections: These involve selecting specific fields from your entities and getting them as a DTO. You should provide a constructor in your DTO that matches the selected fields.

Let's re-apply the previous case using a DTO. First, we define a DTO with a constructor that matches the selected fields:

record SalesOrderDto(String customer, Integer amount) { }

For example, if we place this record to the com.example.projection package (this is important!), the new method will be as follows:

interface SalesOrderRepository extends CrudRepository<SalesOrder, Long> {

    @Query("""
            SELECT new com.example.projection.SalesOrderDto(so.customer, so.amount)
            FROM SalesOrder so
            """)
    List<SalesOrderDto> getDtoProjections();

    // other methods go here...
}

Notice that we used an expression with the new keyword and the fully qualified name of the class and the appropriate constructor to initialize the object. If we call this method:

repository.getDtoProjections().forEach(System.out::println);

and print all the results, we will get this:

SalesOrderDto[customer=company 1, amount=2000]
SalesOrderDto[customer=company 2, amount=1845]
SalesOrderDto[customer=company 1, amount=200]
SalesOrderDto[customer=company 3, amount=4961]
SalesOrderDto[customer=company 1, amount=5000]

This is more handy if you want to have objects ready to use right away.

Dynamic Projections: These allow you to specify your projection at runtime by using an interface as the return type.

Suppose you need to query the database for a summary of sales orders by each customer:

SELECT
     customer AS companyName,
     SUM(amount) AS totalSum
FROM salesOrder
GROUP BY customer;

We can easily make it using a projection. Let's display it with a dynamic projection example. For dynamic projections, you define an interface with getter methods for the fields you want to select:

interface SalesSummary {
    String getCustomerName();
    Integer getTotalSum();
}

Then, you can use this interface in your query:

interface SalesOrderRepository extends CrudRepository<SalesOrder, Long> {

    @Query("""
            SELECT
                so.customer AS customerName,
                SUM(so.amount) AS totalSum
            FROM SalesOrder so
            GROUP BY so.customer
            """)
    List<SalesSummary> getDynamicProjections();

    // other methods go here...
}

Now we can call this method to print the results:

repository.getDynamicProjections().forEach(summary -> {
            var message = "Company: " + summary.getCustomerName() + ", total: " + summary.getTotalSum();
            System.out.println(message);
        });

And we get the following output:

Company: company 1, total: 7200
Company: company 2, total: 1845
Company: company 3, total: 4961

Pros and cons of JPA projections

Pros:

Performance: By only selecting the fields you need, you can shrink the amount of data transferred from the database to your application.

Flexibility: JPA projections allow you to shape your data as needed, which can make your application logic simpler.

Cons:

Complexity: Projections can make your queries more complex, especially if you're selecting many fields from different entities.

Maintenance: If your entity undergoes changes (for example, a field is added or removed), you might need to update your projections and DTOs.

Conclusion

In this topic, you learned about JPA projections, a flexible tool for optimizing your data access. You discovered the different types of JPA projections and their applications. By understanding the various types of projections and knowing when to apply each one, you can make your applications far more efficient and easier to maintain.