Master Spring Data JPA Projections: Cut Redundant Queries with Practical Techniques

JPA Projection Overview

Projection is a JPA feature that selects only a subset of an entity’s columns, reducing database I/O, memory usage, and network transfer. Interface‑based projection implements this without modifying the entity class, without manual DTO mapping, and without JPQL constructor expressions.

Basic Example

Entity definition (Spring Boot 3, MySQL):

@Entity
@Table(name = "sys_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String realName;
    private String phone;
    private String email;
    private Integer status;
    // getters / setters omitted
}

Projection interface exposing only the required fields:

public interface UserBaseInfoProjection {
    Long getId();
    String getUsername();
    String getRealName();
}

Repository method returning the projection:

public interface UserRepository extends JpaRepository<User, Long> {
    List<UserBaseInfoProjection> findByStatus(Integer status);
}

Generated SQL:

select u.id, u.username, u.real_name from sys_user u where u.status = ?

Only the three columns are fetched; the result is accessed through the projection getters, eliminating mapping overhead.

Advanced Techniques

1. Field Renaming / Alias with @Value

SpEL expressions can compute or rename fields:

public interface UserNickInfoProjection {
    Long getId();
    @Value("#{target.username + '(' + target.realName + ')'}")
    String getUserNickName();
    @Value("#{target.phone}")
    String getUserContactPhone();
}

target

refers to the current entity instance, allowing arbitrary concatenation.

2. Association Projection (Many‑to‑One)

When User has a lazy many‑to‑one association to Department:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "dept_id")
private Department department;

public interface UserDeptProjection {
    String getUsername();
    String getDepartmentDeptName(); // JPA joins and selects department name
}

The generated SQL performs a left join but selects only username and dept_name, avoiding N+1 queries.

3. Closed vs. Open Projection

Closed projection : all getters map directly to entity fields; no @Value annotations.

Open projection : includes @Value or SpEL expressions.

Performance order: closed projection → open projection. Closed projection generates precise SQL, incurs no SpEL parsing, and fully benefits from JPA’s second‑level cache.

4. Pagination with Projection

Page<UserBaseInfoProjection> findByStatus(Integer status, Pageable pageable);

The Page object contains pagination metadata while the content consists of the projected fields only.

5. Dynamic Projection

A single repository method can return different projection types via a generic parameter:

<T> List<T> findByStatus(Integer status, Class<T> projectionClass);

// Usage examples
List<UserBaseInfoProjection> baseList = userRepository.findByStatus(1, UserBaseInfoProjection.class);
List<UserDeptProjection> deptList = userRepository.findByStatus(1, UserDeptProjection.class);

This eliminates duplicated query methods when the same filter criteria require different field sets.

Performance Comparison

Test scenario: sys_user table with 1 000 000 rows, fetching 100 rows.

Full‑field query (10+ columns): network payload increased by 30‑50 %; average response time grew by ~20 ms; potential N+1 lazy loads.

Closed projection (3‑5 columns): network payload decreased dramatically; no extra fields loaded; response time improved by 15‑40 %.

Benefits scale with high‑volume list endpoints and high‑concurrency services.

Common Pitfalls

Getter names must exactly match entity property names (case‑sensitive). Mismatches produce null values or query errors.

Open projection cannot use database indexes; applying it to indexed columns may degrade performance.

One‑to‑many projections can generate duplicate rows and break pagination; prefer DTOs with manual joins for such cases.

Projection interfaces are read‑only; they cannot be used with save or update operations.

Defining methods unrelated to projected fields triggers proxy parsing errors and adds unnecessary overhead.

Best‑Practice Checklist

Prefer closed projection for list and detail queries; fetch only required fields.

For many‑to‑one or one‑to‑one associations, embed nested projection interfaces to avoid loading whole associated entities.

In high‑concurrency or large‑dataset services, prohibit full‑entity queries and enforce projection usage.

Use dynamic projection to share query logic across different field sets.

Reserve open projection for field concatenation or formatting; avoid it for regular field selection.

Place projection interfaces in a dedicated package separate from entities and DTOs to maintain clear layering.