Use Spring Data JPA Projections to Optimize Your Database Queries

When working with large and complex datasets in a Java application using JPA (Java Persistence API), developers often face the challenge of inefficiency and performance bottlenecks. Fetching entire entity objects from the database, when only specific fields or properties are needed, can lead to increased network traffic, longer query execution times, and excessive memory consumption.

Enter ✨ JPA projections ✨  a powerful feature that allows developers to select and load only the necessary fields or properties of an entity from the database, optimizing query performance and improving overall application efficiency.

In this article, we will explore the concept of JPA projections in-depth. We will discuss how they work behind the scenes, the different types of projections available (such as entity projections and DTO projections), and the benefits they offer in terms of performance optimization, network traffic reduction and simplified data processing.

By the end of this article, you will fully understand how to leverage JPA projections in your Java applications to maximize efficiency and streamline database interactions.

Prerequisite

In order to follow this tutorial to the end, you should have some knowledge about:

• Java 8+
• Testing with JUnit 5
• Understanding Relational Databases

What is a JPA Projections

JPA projections are a feature of the Java Persistence API (JPA) that allow you to specify which fields and properties of an entity should be loaded from the database when executing a query.

Why Using JPA Projections

JPA Projections is useful when you want to retrieve a subset of data rather than fetching the entire entity object. They are also a few reasons where you will want to use JPA Projections.

(1) Reporting Queries —  Generating reports where only specific fields are needed.

(2) Search Results — Displaying results where only certain details are required.

(3) Optimizing Queries — Optimizing queries to reduce data load and performance.

Practice

To maintain a certain consistency, and for ease of understanding. I've decided to use the Layered Architecture for most of my tutorials on Spring Boot. So, in most of my articles on this blog, I'll use this mini-project structure:

src
  └ main
    └ java        
	    └ controller              # for handling the HTTP Requests
      └ entity                    # entity need to be managed by JPA   
      └ repository                # for accessing data from database
      └ service                   # for business logic implementation
    └ resources                    
      └ application.yml           # configurations of our app
test
	└ resources
		└ application-test.yaml   # configuration for test context
pom.xml                           # dependency management file

If you’re curious about a more detailed presentation of spring boot project architecture, and you want to know how all these things work under the hood, check out this article that I wrote.

Now that I’ve set this reminder, you can continue on this tutorial.

Dependencies

In order to run our production code for this tutorial, you will need the following dependencies in your pom.xml file.

<!-- ... -->
<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<scope>runtime</scope>
</dependency>
<dependency>
	<groupId>org.testcontainers</groupId>
	<artifactId>junit-jupiter</artifactId>
	<scope>test</scope>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-test</artifactId>
	<scope>test</scope>
</dependency>
<!-- ... -->

Entity

Now let’s define an entity class User in the entity package.

@Getter
@Setter
@Entity
@Builder
@ToString
@AllArgsConstructor
@Table(name = "users")
public class User {

    public User() {}

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(nullable = false)
    private Long id;

    private String username;

    private String password;

    private String email;

    private String firstName;

    private String lastName;

}

Repository

With the aim of accessing or creating data in database we need a JPA Repository class:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {}

Test Class

To confirm that our projection produce the correct response, we need a test class:

@Testcontainers
@SpringBootTest
@ActiveProfiles("test")
class ProjectionsTest extends AbstractIntegrationTest {
	//...
}

With the given annotations, Spring Boot creates the database and injects dependencies.

Now let’s start by creating our very first projection.

As you see in the code snippet above, we have the annotation @Testcontainers on our projection test class, and you can see it extends an AbstractIntegrationTest class. I wanted to emulate a real database behaviour with our created entities and SQL queries with JPA.

So in order to configure the Testcontainers for our demo project, you need to set up a config class for the test database.

import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.utility.DockerImageName;

public class AbstractIntegrationTest {

    private static final PostgreSQLContainer POSTGRES_CONTAINER;

    static {
        POSTGRES_CONTAINER = new PostgreSQLContainer<>(DockerImageName.parse("postgres:16-alpine"));
        POSTGRES_CONTAINER.start();
    }

    @DynamicPropertySource
    static void overrideTestProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", POSTGRES_CONTAINER::getJdbcUrl);
        registry.add("spring.datasource.username", POSTGRES_CONTAINER::getUsername);
        registry.add("spring.datasource.password", POSTGRES_CONTAINER::getPassword);
    }
}

Then in the src/test/resources folder, create an application-test.yml and leave it empty.

Interface-Based Projection

Close Projection

When projecting properties of an entity, it’s natural to rely on an interface. As we won’t need to provide an implementation.

Looking back at the User class, we can see it has many properties, yet not all of them are helpful. For example, if you want to retrieve a user attached to a certain email address, just the username is enough.

Now, let’s declare a projection interface for the User class.

public interface UserView {

    String getUsername();
    
    String getEmail();
    
}

In our repository let’s write a method to retrieve all users who have gmail.com in their email address.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT u.username, u.email FROM public.users u " +
                   "WHERE u.email LIKE ('%' || :emailServer || '.com%') ",
           nativeQuery = true)
    List<UserView> fetchUsersByEmailServer(@Param("emailServer") String emailServer);
}

It’s easy to see that defining a repository method with a projection interface is mostly the same as with an entity class. The only difference is that the projection interface, rather than the entity class, is used as the element type in the returned collection.

Let’s do a quick test of the User projection.

First thing first, we have to populate some random users in a database (Postgres Database in a TestContainers) with a @BeforeEach annotation. As its name suggests, it runs all the code inside the setUp method before every test method.

@Testcontainers
@SpringBootTest
@ActiveProfiles("test")
class ProjectionsTest extends AbstractIntegrationTest {

	@Autowired
	private UserRepository userRepository;
	
	@BeforeEach
    void setUp() {
        userRepository.deleteAll();

        var users = new ArrayList<User>();

        var user1 = User.builder().lastName("kev").username("1kevinson").email("anomynous@gmail.com").build();
        var user2 = User.builder().lastName("brad").username("bradleyVost").email("bradley_v@yahoo.com").build();
        var user3 = User.builder().lastName("mel").username("melindaKane").email("kane_m@hotmail.fr").build();

        users.add(user1);
        users.add(user2);
        users.add(user3);

        userRepository.saveAll(users);
    }
	
}

Now, let verify that we have the right data when running our query.

@Test
void testFetchUserByEmailServerUsingOpenProjection() {
    List<UserView> userViews = userRepository.fetchUsersByEmailServer("gmail");

    assertThat(userViews).hasSize(1);
    assertThat(userViews.get(0).getUsername()).isEqualTo("1kevinson");
    assertThat(userViews.get(0).getEmail()).isEqualTo("anomynous@gmail.com");
}

Behind the scenes, when you use JPA projections in your queries, the JPA provider (such as Hibernate or Eclipse Link) modifies the generated SQL query and result processing to accommodate the selected fields or properties.

Open Projection

Up to this point, we’ve gone through closed projections, which indicate projection interfaces whose methods exactly match the names of entity properties.

There’s also another sort of interface-based projection, open projections. These projections enable us to define interface methods with unmatched names and with return values computed at runtime.

In our projection, we can now add a new method.

public interface UserView {
		//...
    @Value("#{target.firstName + ' ' + target.lastName}")
    String getFullName();
}

The argument to the @Value annotation is a SpEL expression, in which the target designator indicates the backing entity object.

But it doesn’t work with native query with @Query annotation. So we have to create a JPA method to find users where email contains ‘gmail’.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

   //...
   UserView findByEmailLike(String email);
}

The following test confirms that the projection object has worked perfectly well.

//... 

@Test
void testFetchFullNameByEmailServerUsingClosedProjection() {
    var user = userRepository.findOne(Example.of(User.builder().username("1kevinson").build()));

    if (user.isPresent()) {
        user.get().setFirstName("Kevin");
        user.get().setLastName("Kouomeu");
        userRepository.save(user.get());
    }

    var userView = userRepository.findByEmailLike("%gmail%");

    assertThat(userView.getUsername()).isEqualTo("1kevinson");
    assertThat(userView.getEmail()).isEqualTo("anomynous@gmail.com");
    assertThat(userView.getFullName()).isEqualTo("Kevin Kouomeu");
}

Open projections do have a drawback, though.

Spring Data can’t optimize query execution, as it doesn’t know in advance which properties will be used. Thus, we should only use open projections when closed projections aren’t capable of handling our requirements.

Some benefits of using projections

Before diving into Class-Based Projection, let’s talk about how JPA Projections can be beneficial in several scenarios:

Performance Optimization: If your entity has many fields, but you only need a few for a particular use case, using projections allows you to fetch only the necessary data from the database. This reduces the amount of data transferred over the network and can improve performance.

Avoiding Lazy Loading Issues: If your entity has associations (like one-to-many or many-to-one relationships) with other entities, and you don't need those associations for a particular query, using projections can prevent unnecessary lazy loading and potential N+1 query issues.

Custom Views: Projections allow you to define custom views of your data that might not correspond directly to your entity structure. This can be useful when presenting data in a different format or when aggregating data from multiple entities.

Class-Based Projection

Here is an example of a projection class for the User entity:

@Getter
@EqualsAndHashCode
@AllArgsConstructor
public class UserDto {

    private String username;
    private String email;
}

For the project class to work, the parameters names in the DTO class should match the properties of the root entity class.

We must also define equals and hashCode implementations; they allow Spring Data to process projection objects in a collection.

The requirements above can be addressed by java records, thus making our code more precise and expressive:

public record UserDto (String username, String email) {}

Now let’s add a method to the UserRepository.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
		// ...
		
    UserDto findByUsername(String username);
}

Now let's a test to verify our class-based projections

// ...

@Test
void testFetchUserByUsernameUsingProjection() {
    var userDto = userRepository.findByUsername("1kevinson");

    assertThat(userDto.getEmail()).isEqualTo("anomynous@gmail.com");
}

Dynamic Projections

An entity class may have many projections. In some cases, we may use a certain type, but in other cases, we may need another type. Occasionally, we also need to use the entity class itself.

Defining separate repository interfaces or methods just to support multiple return types is cumbersome. To deal with this problem, Spring Data provides a better solution, dynamic projections.

We can apply dynamic projections just by declaring a repository method with a Class parameter:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
		// ...
		
    <T> T findByLastName(String lastName, Class<T> type);
}

By passing a projection type or the entity class to such a method, we can retrieve an object of the desired type:

@Test
void testFetchUserByLastnameUsingDynamicProjection() {
    User user = userRepository.findByLastName("kev", User.class);
    UserDto userDto = userRepository.findByLastName("kev", UserDto.class);
    UserView userView = userRepository.findByLastName("kev", UserView.class);

    assertThat(user.getUsername()).isEqualTo("1kevinson");
    assertThat(userDto.getUsername()).isEqualTo("1kevinson");
    assertThat(userView.getUsername()).isEqualTo("1kevinson");
}

Disadvantages of projections

While JPA Projections offer several advantages, they also come with some potential drawbacks:

Increased Complexity: Implementing and managing projections can add complexity to your codebase, especially when dealing with complex queries or when multiple projections are required for different use cases. This complexity can make the code harder to understand and maintain.

Maintenance Overhead: Projections typically involve defining separate DTOs (Data Transfer Objects) or interfaces for each projection scenario. This can lead to increased maintenance overhead, as any changes to the entity structure or query requirements may necessitate corresponding updates to the projections.

Potential for Data Inconsistency: If projections are not carefully managed, there is a risk of data inconsistency. For example, if the entity's state changes, but the projections are not updated accordingly, it may result in discrepancies between what is displayed or processed in different parts of the application.

Wrap up

In conclusion, JPA Projections offer a powerful way to optimize the performance and flexibility of data retrieval in Java applications. Key points from this article include:

1. Why Use JPA Projections: Projections are useful for retrieving only the necessary subset of data, which helps in reporting queries, search results, and optimizing database load.
2. Types of Projections: We explored different types of projections including DTO-based, interface-based (both closed and open), and class-based projections. Each type serves unique purposes and comes with its advantages and disadvantages.
3. Dynamic Projections: Dynamic projections offer a flexible way to retrieve data in different forms without defining multiple repository methods, making the codebase cleaner and more manageable.
4. Benefits: Projections help in performance optimization, avoiding lazy loading issues, and providing custom views of data.
5. Drawbacks: Despite their advantages, projections can add complexity to the codebase, increase maintenance overhead, and potentially lead to data inconsistency if not managed properly.

By carefully considering the use of JPA Projections in your projects, you can significantly enhance the efficiency and clarity of your database interactions. Always weigh the benefits against the potential drawbacks to make the best decision for your specific use case.

You can get the source code of this article on my GitHub

BLOG-TUTORIALS/Java/spring-data-jpa-projections at master · 1kevinson/BLOG-TUTORIALS

Curated list of repositories and links attached to my blog articles - 1kevinson/BLOG-TUTORIALS

GitHub1kevinson